SQL audit data monitoring method implemented based on ES

Abstract

The invention relates to an SQL audit data monitoring method and device realized based on ES. The method comprises the following steps: starting an audit function of a Vastbase G100 database and configuring related parameters; collecting audit data by using an audit data collector, and storing the audit data to Elasticsearch; and storing and indexing the collected audit data for query and display. By efficiently collecting, storing and indexing the SQL audit data, real-time monitoring of database operation is achieved, and transparency and safety of database operation are effectively improved. The method has the advantages that a large amount of audit data can be analyzed in real time, detailed audit records are provided, a database administrator is helped to find and process abnormal operation in time, and the safety and compliance of a database are ensured. By combining the advantages of Vastbase G100 and ES, an efficient and reliable solution is provided for database auditing in a modern information system.

Description

Technical Field

[0001] The present application relates to the field of database audit technology, and in particular to a SQL audit data monitoring method, device and electronic device based on ES. Background Art

[0002] With the rapid development of information technology, the importance and scale of modern information systems in various fields have exploded. In this process, databases, as the core components of information systems, have become critical requirements for security and operational transparency.

[0003] However, with the continuous expansion of business and the dramatic growth in data volumes, traditional audit log management approaches face serious challenges. Traditional methods have numerous shortcomings when processing large-scale audit logs, making it difficult to meet the needs of efficient, real-time monitoring of SQL audit data. On the one hand, traditional methods are inefficient in data storage and retrieval. As audit logs continue to increase, storage space usage rapidly expands, and query response times significantly increase. This prevents timely access to critical audit information, leading to significant delays when rapid responses to security incidents or real-time analysis are required. On the other hand, traditional audit log analysis tools often lack the parallel processing capabilities and intelligent analysis capabilities for massive amounts of data, making it difficult to deeply explore potential patterns, abnormal behaviors, and correlations within audit data, making it difficult to provide valuable insights and decision support.

[0004] In addition, although some new database auditing methods and auditing systems have been developed, for example, the Chinese patent application "A Database Auditing System and Auditing Method Thereof" (CN104090941A) discloses a database auditing system comprising a database statement auditing module and a database user behavior auditing module. The database statement auditing module is used to parse the collected audit data to obtain SQL statements and then test the obtained SQL statements; the database user behavior auditing module is used to analyze the collected audit data to obtain user behavior and then test the obtained user behavior. However, this method still has many drawbacks in its application. For example, the data collection method relies on network mirroring, which is susceptible to factors such as network configuration and traffic, and may result in data loss or incompleteness; it is difficult to ensure the real-time and consistency of SQL audit data collection; data storage and query efficiency is low, especially when querying massive amounts of audit data; the handling of abnormal situations is not perfect, mainly relying on manual auditing; and there is a lack of data classification and cleanup mechanisms.

[0005] Therefore, there is an urgent need to develop a new and efficient SQL audit data monitoring method. Summary of the Invention

[0006] In order to address the above problems, the present invention proposes a new SQL audit data monitoring method based on Elasticsearch (ES).

[0007] This invention leverages the audit function of Vastbase G100 (the SQL audit function of the Vastbase G100 database must be enabled in advance) and uses a highly efficient SQL audit data collector to collect SQL audit data from the database in a multi-threaded manner. This data is then stored in Elasticsearch (ES). Elasticsearch then leverages Elasticsearch's efficient indexing and query capabilities to enable real-time monitoring of this SQL audit data. This approach allows database administrators to more efficiently manage SQL audit data, promptly identify and address abnormal operations, and improve database security and operational transparency.

[0008] In order to achieve the above objectives, this application provides the following technical solutions: The first aspect of the present application provides a SQL audit data monitoring method based on ES, the method comprising: S1. Enable the Vastbase G100 database audit function and configure SQL audit parameters. S2. Use the audit data collector to collect audit data and store it in ES; S3. Store and index the collected audit data for query and display.

[0009] Furthermore, when the Vastbase G100 database audit function is enabled as described in step S1 of the present application method, the configured SQL audit-related parameters include the storage path of the audit log, the detail level of the audit log, and the control parameters of various audit operations to ensure that all key operations are recorded.

[0010] Furthermore, the use of the audit data collector to collect audit data in step S2 of the present method includes: Added the audit data task table sql_audit_task to record audit data collection information for each database instance. The key information is the time conditions (start time and end time) used for database audit data collection. Also added was the audit data task record table sql_audit_task_record to record each audit data collection, including the amount of data collected, time conditions, start time, and completion time. Start the scheduled collection task, collect data according to the configured period. If the collection is not completed within the period, trigger the multi-threaded collection. Get a connection from the Vastbase G100 database connection pool. If there is no connection, wait for the next scheduled collection task. Initialize the collection task and set the collection time interval to ensure the consistency of the collection time interval, and persist the collection time interval to the configuration library; Calibrate the collection time interval. The calibration rule of the collection time interval is to determine whether calibration is needed based on the length of the collection time interval that lags behind the current time. Collect audit data of the current collection time interval in batches from the database; Persist the collected audit data in ES; Record the collection tasks in the sql_audit_task_record table.

[0011] Furthermore, the calibration of the collection time interval in the method of the present application further includes: if the length of the collection time interval lags behind the current time by more than a preset time threshold, directly resetting the collection time interval and updating it to the configuration library.

[0012] Furthermore, when the collected audit data is persisted to ES as described in the method of this application, SQL is classified into DDL and DML.

[0013] Furthermore, the storage and indexing of the collected audit data in step S3 of the present method includes: The audit data is stored in the ES audit data index. The data structure includes audit SQL, database name, user name, SQL type, and execution time. All fields can be used as conditions for fuzzy search of audit data. The front-end display device uses the data information stored in ES through audit data as index conditions to query the required data and display it.

[0014] Furthermore, the audit data collector described in the method of the present application has the function of regularly clearing old data from the ES and collection task record table. The audit data retention time is pre-set, and data exceeding the retention time will be regularly cleared.

[0015] A second aspect of the present application provides an SQL audit data monitoring device implemented based on ES, the device comprising: Vastbase G100 database module: used to enable the Vastbase G100 database audit function, generate audit data, and configure SQL audit-related parameters; Audit data collector module: used to collect audit data and store it in ES; Elasticsearch module: used to store and index the collected audit data for query and display.

[0016] The device implements the steps of the aforementioned SQL audit data monitoring method based on ES when running.

[0017] A third aspect of the present application provides an electronic device, comprising: a memory and a processor; Memory: used to store computer programs; Processor: used to execute the computer program to implement the steps of the aforementioned SQL audit data monitoring method based on ES.

[0018] A fourth aspect of the present application provides a computer-readable storage medium having a computer program stored thereon. When the computer program is executed by a processor, the steps of the aforementioned SQL audit data monitoring method based on ES are implemented.

[0019] In summary, the present invention has the following significant advantages and beneficial effects: (1) Efficiency: By combining the audit function of Vastbase G100 with the efficient indexing and query capabilities of Elasticsearch, the present invention can perform real-time analysis on large amounts of audit data, which can meet the needs of modern information systems for efficient processing and fast query of database audit data.

[0020] (2) Transparency: The present invention provides detailed audit data records, covering various database operations, including start and stop, connection, DDL, DML and DCL, etc., which provides strong support for database administrators to fully understand the database operation status and conduct effective database management and monitoring, and helps to improve the transparency of database operations.

[0021] (3) Security: Through the present invention, database administrators can manage SQL audit data more efficiently, discover and handle abnormal operations in a timely manner, and improve the security of the database.

[0022] Other features and advantages of the present invention will be described in detail in the following description, or may be understood through implementation of the relevant technical solutions of this application. The objectives and other advantages of this application may be achieved through the technical features and technical means clearly indicated in the description, claims, and drawings, and may be obtained through the implementation of these technical contents. BRIEF DESCRIPTION OF THE DRAWINGS

[0023] To more clearly illustrate the technical solutions of the embodiments of the present application, the following briefly introduces the drawings involved in the description of the embodiments. It should be noted that the drawings only illustrate some embodiments of the present application. Those skilled in the art can deduce other relevant drawings based on these drawings without engaging in creative work.

[0024] Figure 1 The figure is a flow chart of the overall implementation of the method of the present invention.

[0025] Figure 2 This is an example diagram of data collection information recorded in the audit data task table and the audit data task record table in the method of the present invention.

[0026] Figure 3 The present invention is a flow chart of the collector collecting audit data units.

[0027] Figure 4 This is the audit data flow diagram in the method of the present invention.

[0028] Figure 5 It is a structural diagram of the composition of the device of the present invention.

[0029] Figure 6 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0030] In order to make the purpose, technical solutions and advantages of the embodiments of the present application clearer, the technical solutions in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. It should be understood that the described embodiments are only some embodiments of the present application, not all embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0031] In this document, the term "including" and any variations thereof (such as "including," "comprising," etc.) are open-ended expressions and should be understood as meaning "including but not limited to," meaning that the listed contents are not exhaustive and may include other contents not explicitly mentioned. The term "based on" should be understood as meaning "based at least in part on," meaning that the basis or condition referred to may not be the only factor and may also involve other relevant factors. The term "one embodiment" should be understood as meaning "at least one embodiment," meaning that the described embodiment is not the only possible implementation method and that other similar embodiments may exist.

[0032] In this application, the terms "a" and "a plurality" are used to modify related elements or features in an illustrative, non-restrictive manner. Unless the context clearly indicates otherwise, "a" should be understood as meaning "at least one," and "a plurality" should be understood as meaning "at least two." Those skilled in the art should interpret these terms appropriately based on the semantics and logical relationships of the context to ensure that they encompass the possibility of "one or more."

[0033] Explanation of terms: Vastbase G100 database: a database product developed by our company and sold publicly for a long time.

[0034] Figure 1The following is the overall implementation process of the SQL audit data monitoring method based on ES provided by this application, including the following steps: S1. Enable the Vastbase G100 database audit function and configure SQL audit parameters. S2. Use the audit data collector to collect audit data and store it in ES; S3. Store and index the collected audit data for query and display.

[0035] In order to more clearly illustrate the technical solution of the present application, the following will further illustrate it through embodiments of specific scenarios.

[0036] The specific implementation steps of the method of the present invention are as follows: (1) Enable the Vastbase G100 database audit function Enable the Vastbase G100 database audit function and configure SQL audit-related parameters.

[0037] (2) Collecting audit data using the audit data collector 1. If Figure 2 As shown, the audit data task table sql_audit_task is added to record audit data collection information for each database instance. The key information is the time conditions for database audit data collection (start time and end time). The audit data task record table sql_audit_task_record is added to record each audit data collection, including the collected data volume, time conditions, collection start time, and completion time.

[0038] 2. If Figure 3 As shown, the collector collects audit data units (1) The collector starts the scheduled collection task and collects data according to the configured period. If the collection is not completed within the period, multi-threaded collection will be triggered. The maximum number of threads can be set according to the business volume; (2) Obtain a connection from the Vastbase G100 database connection pool. If there is no connection, wait for the next scheduled collection task. (3) Initialize the SQL audit collection task, set the collection time interval, and persist this time interval to the configuration library. This time interval is consistent with the previous collection time interval, ensuring the consistency of the audit data; (4) Calibrate the collection time interval. If a network anomaly or server anomaly causes the collector to be interrupted for a period of time, the collection time interval will lag behind the current time. At this time, it is necessary to calibrate the collection time interval of the current database audit collection task to the current time. The calibration rule is to determine whether the time needs to be calibrated by setting the length of the lag time. If the collection time interval lags behind the current time for too long, the collection time interval is directly reset and updated to the configuration library. (5) Collect audit data. Use the select * from pg_query_audit(beginTime,endTime) method to collect audit data for the current time interval in batches from the database. During the collection process, pay attention to the following points: a. The time field obtained from the database does not record the time when the SQL statement was executed, but the time when the SQL statement was audited. b. If data cannot be collected from the database due to connection anomalies, database audit function anomalies, or other reasons, the collection task will be recorded as an error and skipped without recollection. (6) When persisting the collected audit data to ES, SQL is classified as DDL / DML. If an exception occurs during the persistence process, the collection task will be stopped and skipped, and will not be collected again; (7) Record the tasks executed in this collection into the sql_audit_task_record table.

[0039] 3. Because audit data that is too old has no reference value, the collector has the function of regularly cleaning up old data from ES and collection task record tables. The number of days to retain audit data can be set, and data that exceeds the time limit will be regularly cleaned up by the collector.

[0040] (3) Storage and indexing of audit data like Figure 4 As shown, audit data collected by the collector is stored in the ES audit data index. The data structure includes audit SQL, database name, user name, SQL type, and execution time. All fields can be used as conditions for fuzzy search of audit data to improve query performance, achieving more accurate and rapid inspections, with information viewing rates reaching milliseconds. Front-end display devices (display terminals) can use the audit data stored in ES as index conditions to accurately query and display the required data.

[0041] The following is a specific example to demonstrate how the solution of the present invention can be combined with Vastbase G100 and Elasticsearch to implement SQL audit data monitoring: 1. Configure the Vastbase G100 database audit function (see the table below for relevant parameters) Enable the audit function of Vastbase G100 and set the storage path and related parameters of the audit log.

[0042] Configure the audit log detail level to ensure that all critical operations are recorded.

[0043]

[0044] 2. Install Elasticsearch Install and configure Elasticsearch to ensure it can receive and store audit data sent from the collector. Record the ES installation information in the configuration repository. Also, initialize the ES connection information in the plugin configuration table in the configuration repository so that the collector can obtain the ES connection information through the plugin configuration table.

[0045] 3. Install the audit data collector instance-exporter Configure the database connection information and the server user information for installing the database in the collection configuration file application.yml, record the ES connection information in the plugin (configuration) table of the configuration library, then use the script to start the audit data collector and record the collection task information in the configuration library.

[0046] 4. Audit data query By indexing SQL_AUDIT_INDEX and adding query conditions (audit SQL, database name, user name, SQL type, execution time, etc.), audit data can be retrieved from ES in pages and displayed on the page.

[0047] Figure 5 The following is a SQL audit data monitoring device based on ES proposed in this application, which includes: Vastbase G100 database module: used to enable the Vastbase G100 database audit function, generate audit data, and configure SQL audit-related parameters; Audit data collector module: used to collect audit data and store it in ES; Elasticsearch module: used to store and index the collected audit data for query and display.

[0048] When the above device is running, the steps of the SQL audit data monitoring method based on ES disclosed in this application are implemented.

[0049] The flowcharts and block diagrams in the accompanying drawings illustrate possible implementations of the apparatus, methods, and computer program products according to various embodiments of the present application, including architecture, functions, and operations. In these figures, each box may represent a module, a program segment, or a portion of a code, which contains one or more executable instructions for implementing a specified logical function. It should be noted that each box in the block diagram and / or flowchart, and the combination of these boxes, can be implemented using a dedicated hardware-based system to implement the specified function or operation, or can be implemented by a combination of dedicated hardware and computer instructions.

[0050] like Figure 6 As shown, an embodiment of the present application further discloses an electronic device, comprising: a processor 310, a communication interface 320, a memory 330 for storing a computer program executable by the processor, and a communication bus 340. The processor 310, the communication interface 320, and the memory 330 communicate with each other via the communication bus 340. The processor 310 executes the executable computer program to implement the steps of the above-mentioned ES-based SQL audit data monitoring method.

[0051] It is understood that, in addition to the memory and processor, the electronic device may also include an input device (e.g., a keyboard), an output device (e.g., a display), and other communication modules. These input devices, output devices, and other communication modules all communicate with the processor via an I / O interface (i.e., an input / output interface).

[0052] The operation of the present application can be implemented by writing computer program code using one or more programming languages ​​or a combination thereof. The programming languages ​​include but are not limited to the following types: Object-oriented programming languages, such as Java, Smalltalk, C++, etc.; A conventional procedural programming language, such as "C" or a similar programming language.

[0053] The execution methods of the program code include but are not limited to: Executes entirely on the user's computer; Partially executed on the user's computer and partially on a remote computer; Executed as a standalone software package; Executes entirely on the remote computer or server.

[0054] In scenarios involving a remote computer, the remote computer can be connected to the user's computer via any type of network, including but not limited to a local area network (LAN) or a wide area network (WAN). Additionally, the remote computer can be connected to an external computer via an Internet service provider, such as the Internet.

[0055] Furthermore, the present application also discloses a computer-readable storage medium. When the instructions in the computer-readable storage medium are executed by a processor of an electronic device, the electronic device is enabled to execute the various steps of the SQL audit data monitoring method based on ES implementation disclosed in the present application.

[0056] In the context of this application, computer-readable storage media refers to tangible media that can store computer program code and related data. Specific examples include, but are not limited to, the following: (1) Portable computer disk: A removable magnetic storage medium such as a floppy disk.

[0057] (2) Hard disk: includes fixed storage devices such as mechanical hard disks and solid-state hard disks.

[0058] (3) Random Access Memory (RAM): Volatile storage medium used for temporary storage of data and program code.

[0059] (4) Read-only memory (ROM): A non-volatile storage medium used to store fixed programs and data.

[0060] (5) Erasable Programmable Read-Only Memory (EPROM) or Flash Memory: A non-volatile storage medium that supports multiple erasing and programming.

[0061] (6) Fiber optic storage device: storage medium based on fiber optic technology.

[0062] (7) Compact Disc Read-Only Memory (CD-ROM): A read-only medium that stores data in the form of an optical disc.

[0063] (8) Optical storage devices: storage media based on optical principles, such as DVDs and Blu-ray discs.

[0064] (9) Magnetic storage devices: storage media based on magnetic principles, such as magnetic tapes and disks.

[0065] (10) Any suitable combination of the above: for example, combining multiple storage media to meet different storage requirements.

[0066] These computer-readable storage media can be used to store the program code and related data described in this application to support the operation of the program and the persistent storage of data.

[0067] In particular, according to embodiments of the present application, the processes described in the flowcharts can be implemented as computer software programs. For example, embodiments of the present application relate to a computer program product comprising a computer program carried on a non-transitory computer-readable medium. The computer program includes program code for executing the SQL audit data monitoring method based on ES disclosed in the present application. When the computer program is executed by a processing device, the above-mentioned functions defined in the embodiments of the present application can be implemented.

[0068] Although the above discussion contains several specific implementation details, these details should not be interpreted as limiting the scope of this application. The above description is only a preferred embodiment of the present application and an illustration of the technical principles used. Those skilled in the art should understand that the scope of disclosure involved in this application is not limited to the technical solutions formed by the specific combination of the above technical features. At the same time, this application should also cover other technical solutions formed by any combination of the above technical features or their equivalent features without departing from the above disclosed concepts.

[0069] Those skilled in the art should also understand that they may modify the technical solutions described in the aforementioned embodiments, or replace some of the technical features therein with equivalents, without departing from the spirit and scope of the technical solutions of the embodiments of the present application. Such modifications or replacements will not cause the essence of the corresponding technical solutions to deviate from the core spirit and scope of the technical solutions of the embodiments of the present application.

Claims

1. A SQL audit data monitoring method based on ES, characterized in that: The method comprises: S1. Enable the Vastbase G100 database audit function and configure SQL audit parameters. S2. Use the audit data collector to collect audit data and store it in ES; S3. Store and index the collected audit data for query and display.

2. The method according to claim 1, characterized in that When the Vastbase G100 database audit function is enabled in step S1, the configured SQL audit-related parameters include the storage path of the audit log, the detail level of the audit log, and the control parameters of various audit operations to ensure that all key operations are recorded.

3. The method according to claim 1, characterized in that The step S2 of collecting audit data using the audit data collector includes: Added the audit data task table sql_audit_task to record the audit data collection information of each database instance. The key information is the time conditions for database audit data collection. Also added was the audit data task record table sql_audit_task_record to record each audit data collection, including the amount of data collected, time conditions, start time of collection, and completion time of collection. Start the scheduled collection task, collect data according to the configured period. If the collection is not completed within the period, trigger the multi-threaded collection. Get a connection from the Vastbase G100 database connection pool. If there is no connection, wait for the next scheduled collection task. Initialize the collection task and set the collection time interval to ensure the consistency of the collection time interval, and persist the collection time interval to the configuration library; Calibrate the collection time interval. The calibration rule of the collection time interval is to determine whether calibration is needed based on the length of the collection time interval that lags behind the current time. Collect audit data of the current collection time interval in batches from the database; Persist the collected audit data in ES; Record the collection tasks in the sql_audit_task_record table.

4. The method according to claim 3, characterized in that The calibrating the collection time interval further includes: if the length of the collection time interval that lags behind the current time exceeds a preset time threshold, directly resetting the collection time interval and updating it in the configuration library.

5. The method according to claim 3, characterized in that When the collected audit data is persisted to ES, SQL is classified into DDL and DML.

6. The method according to claim 1, characterized in that The storage and indexing of the collected audit data in step S3 includes: The audit data is stored in the ES audit data index. The data structure includes audit SQL, database name, user name, SQL type, and execution time. The front-end display device uses the data information stored in ES through audit data as index conditions to query the required data and display it.

7. The method according to claim 1, characterized in that The audit data collector has the function of regularly clearing old data from the ES and collection task record table. The audit data retention time is pre-set, and data exceeding the retention time will be regularly cleared.

8. A SQL audit data monitoring device based on ES, characterized in that: The device comprises: Vastbase G100 database module: used to enable the Vastbase G100 database audit function, generate audit data, and configure SQL audit-related parameters; Audit data collector module: used to collect audit data and store it in ES; Elasticsearch module: used to store and index the collected audit data for query and display.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the SQL audit data monitoring method based on ES as described in any one of claims 1 to 8 are implemented.

10. An electronic device, characterized in that: include: memory and processor; Memory: used to store computer programs; Processor: used to execute the computer program to implement the steps of the SQL audit data monitoring method based on ES as described in any one of claims 1-8.

Images