Table-based database auditing method, apparatus and device, and medium

Abstract

The invention relates to the technical field of computer information, and discloses a table-based database auditing method, which comprises the following steps of: creating a system-level database, and adding a system table in the system-level database; adding, modifying, deleting, starting or disabling audit strategies are configured in an audit strategy table in the system table; obtaining user behavior information, and judging whether the current operation hits an auditing strategy according to the user behavior information and the scene type; when the current operation hits the auditing strategies, generating an auditing log for each hit auditing strategy, and writing the auditing logs into a system table; and querying the audit log in the audit log table through the query statement. According to the method, the system-level database, the audit strategy table and the log table are created, strategies are managed through operations such as addition, deletion and modification, the strategies are matched during user operation, the log write-in table is generated if the strategies are hit, logs can be queried, the audit strategy management process is optimized, and the audit log query performance is greatly improved.

Description

Technical Field

[0001] This invention relates to the field of computer information technology, and in particular to a table-based database auditing method, apparatus, device, and storage medium. Background Technology

[0002] In past technical practices, database auditing has faced numerous challenges. Early database systems and business scenarios were relatively simple, with low security requirements. By setting different configuration parameters, the database could clearly define the operations (such as SQL statements), users, databases, and related objects that needed auditing. When user operations matched the audit policies defined by these parameters, the database automatically generated audit logs. This parameter-based audit policy management method, with its simplicity and efficiency, could quickly meet the database auditing needs of the time. For example, small business databases could effectively audit a small number of routine business operations by simply setting a few parameters.

[0003] However, with the advancement of information technology, the scale of business in various industries continues to expand, business processes are becoming increasingly complex, and database security requirements are also rising. The number of audit strategies has surged, and their content is becoming increasingly detailed. Traditional parameter-based audit strategy management methods are gradually revealing their shortcomings in terms of flexibility and convenience. When adding, deleting, modifying, and querying audit strategies, especially when involving parameter union and intersection operations, the construction process becomes complex because the parameters are independent and lack logical connections. For example, large financial institutions need to develop diverse audit strategies to meet regulatory requirements. When adding a joint audit strategy targeting multiple operational stages of a specific business process, integrating existing parameters not only requires sorting through a large number of parameters but is also prone to errors due to logical relationships, seriously affecting management efficiency and accuracy.

[0004] In audit log management, file storage offers significant advantages when data volume is limited in the early stages. Small business databases generate only tens of KB to several MB of logs daily, and local disk storage can quickly record these logs. Technicians can quickly locate audit records for specific operations or time periods using text processing tools and simple search commands, facilitating preliminary analysis. However, as database size expands and business activities become more frequent, the volume of audit log data grows exponentially, posing a significant challenge to file-based audit log management. Querying records for specific operations or time periods relies primarily on sequential reading and text search, which becomes extremely inefficient when files are large. For example, in transaction audits of financial institutions, retrieving a client's fund transfer records for a specific quarter can take several minutes to traverse the file due to a lack of efficient indexing. In scenarios with high timeliness requirements, such as security checks and compliance reviews, this severely impacts the acquisition of critical information and may even delay the handling of security incidents, causing losses to the enterprise. Summary of the Invention

[0005] The main objective of this invention is to provide a table-based database auditing method, apparatus, device, and storage medium, which aims to solve the problems of complex policy management and low efficiency of audit log query in traditional database auditing.

[0006] To achieve the above objectives, the present invention provides a table-based database auditing method, comprising: Create a system-level database and add system tables to the system-level database; Configure the audit policy table in the system table to add, modify, delete, enable, or disable audit policies; Obtain user behavior information, and determine whether the current operation hits the audit policy based on the user behavior information and scenario type; When the current operation hits an audit policy, an audit log is generated for each hit audit policy, and the audit log is written to the system table; Use a query statement to retrieve the audit logs from the audit log table.

[0007] Furthermore, to achieve the above objectives, the present invention provides a table-based database auditing apparatus, comprising: The table system module is used to create a system-level database and add system tables to the system-level database; The audit operation module is used to add, modify, delete, enable, or disable audit policies in the audit policy table of the system table; The audit query module is used to obtain user behavior information and determine whether the current operation hits the audit policy based on the user behavior information and scenario type. The log generation module is used to generate audit logs for each audit policy that is hit when the current operation hits an audit policy, and write the audit logs to the system table. The log query module is used to query audit logs in the audit log table using query statements.

[0008] Furthermore, to achieve the above objectives, the present invention also provides a computer device, the computer device including a memory, a processor, and a table-based database auditing program stored in the memory and executable on the processor, wherein the table-based database auditing program, when executed by the processor, implements the steps of the table-based database auditing method as described above.

[0009] Furthermore, to achieve the above objectives, the present invention also provides a computer-readable storage medium storing a table-based database auditing program, which, when executed by a processor, implements the steps of the table-based database auditing method as described above.

[0010] Beneficial Effects: This invention relates to the field of computer information technology and discloses a table-based database auditing method, comprising: creating a system-level database and adding a system table to the system-level database; configuring the addition, modification, deletion, enabling, or disabling of audit policies in the audit policy table of the system table; acquiring user behavior information and determining whether the current operation matches an audit policy based on the user behavior information and scenario type; when the current operation matches an audit policy, generating an audit log for each matched audit policy and writing the audit log to the system table; and querying the audit log in the audit log table using a query statement. This invention creates a system-level database, an audit policy table, and a log table, manages policies through operations such as adding, deleting, and modifying, matches policies when users operate, generates logs when a policy matches, writes them to the table, and allows querying of logs, thus optimizing the audit policy management process and significantly improving the performance of audit log querying. Attached Figure Description

[0011] The present invention will be further described below with reference to the accompanying drawings and embodiments. In the accompanying drawings: Figure 1 This is a schematic diagram of an application environment for a table-based database auditing method according to an embodiment of the present invention; Figure 2 This is a flowchart illustrating an embodiment of the table-based database auditing method of the present invention; Figure 3 This is a flowchart illustrating step S200 in the table-based database auditing method of the present invention. Figure 4 This is a flowchart illustrating step S300 in the table-based database auditing method of the present invention. Figure 5 This is a flowchart illustrating step S400 in the table-based database auditing method of the present invention. Figure 6 This is a schematic diagram of the log cleanup process in the table-based database auditing method of the present invention; Figure 7 This is a schematic diagram of the functional modules of a preferred embodiment of the table-based database auditing device of the present invention; Figure 8 This is a schematic diagram of the structure of a computer device according to an embodiment of the present invention; Figure 9 This is another structural schematic diagram of a computer device according to one embodiment of the present invention. Detailed Implementation

[0012] It should be understood that the specific embodiments described herein are for illustrative purposes only and are not intended to limit the scope of the invention.

[0013] The table-based database auditing method provided in this invention can be applied to, for example... Figure 1In this application environment, the user terminal communicates with the server via a network. The server can create a system-level database through the user terminal and add a system table to the system-level database; configure the addition, modification, deletion, enabling, or disabling of audit policies in the audit policy table of the system table; obtain user behavior information and determine whether the current operation hits an audit policy based on the user behavior information and scenario type; when the current operation hits an audit policy, generate an audit log for each hit audit policy and write the audit log to the system table; and query the audit log in the audit log table using a query statement. This invention creates a system-level database, an audit policy table, and a log table, manages policies through operations such as adding, deleting, and modifying, matches policies when users operate, generates logs and writes them to the table when a policy is hit, and allows querying of logs, thus optimizing the audit policy management process and significantly improving the performance of audit log querying. The user terminal can be, but is not limited to, various personal computers, laptops, smartphones, tablets, and portable wearable devices. The server can be implemented using a standalone server or a server cluster composed of multiple servers. The invention will be described in detail below through specific embodiments.

[0014] Please see Figure 2 , Figure 2 This is a flowchart illustrating an embodiment of the table-based database auditing method provided by the present invention. It should be noted that although a logical order is shown in the flowchart, in some cases, the steps shown or described may be performed in a different order than that shown here.

[0015] like Figure 2 As shown, the table-based database auditing method proposed in this invention includes the following steps: S100. Create a system-level database and add a system table to the system-level database; S200. Configure the audit policy table in the system table to add, modify, delete, enable, or disable audit policies; S300. Obtain user behavior information and determine whether the current operation hits the audit policy based on the user behavior information and scenario type. S400. When the current operation hits an audit policy, an audit log is generated for each hit audit policy, and the audit log is written to the system table. S500 queries the audit logs in the audit log table using a query statement.

[0016] In this embodiment, to achieve a complete and efficient database auditing function, a dedicated system-level database, _gdb_audit, was created to store all audit-related data and objects. This design ensures the independence and integrity of the auditing function.

[0017] The `_gdb_audit` database contains two core tables: `_gdb_audit.dba_audit_policies` and `_gdb_audit.dba_audit_trail`. The `_gdb_audit.dba_audit_policies` table stores and manages audit policies, defining which users, operation types, and database objects need to be audited. The `_gdb_audit.dba_audit_trail` table records detailed information for each audit, including operation type, user information, operation time, and executed SQL statements.

[0018] To better manage and maintain the auditing functionality, the following stored procedures and functions were also created: _gdb_audit.purge_audit_trail: Used to clean up old audit log records to prevent the log table from becoming bloated.

[0019] _gdb_audit.generate_audit_option: Used to generate the encoding of audit options, making it easy to convert operation types (such as INSERT, UPDATE, etc.) into bit field encoding.

[0020] _gdb_audit.convert_audit_option: Used to convert the encoding of audit options back to readable option names for easier understanding and analysis.

[0021] _gdb_audit.audit_option_contains: Used to check whether the audit options contain specific operations, such as whether they contain INSERT operations.

[0022] The lifecycle of audit policies can be managed through the `_gdb_audit.dba_audit_policies` table. This includes: Creating a new audit policy: Inserting a new record into the `_gdb_audit.dba_audit_policies` table, defining the audit policy's name, applicable users, operation type, object scope, etc. Modifying an existing audit policy: Updating an existing record in the `_gdb_audit.dba_audit_policies` table, modifying the audit policy's attributes. Deleting an unnecessary audit policy: Deleting the record in the `_gdb_audit.dba_audit_policies` table. Enabling or disabling an audit policy: Controlling whether an audit policy is active by modifying the value of the `enabled` field.

[0023] When a user performs a database operation, the system captures user behavior information and determines whether the operation needs to be audited based on this information and the audit policy. The captured context information includes user identity, client information, server information, operation time, session information, operation content, and operation result. Key features, such as username, operation type, and the names of the databases and tables involved, are extracted from this information. Then, these key features are used to query the `_gdb_audit.dba_audit_policies` table to find matching audit policies and check if the operation type is set to require auditing in the `audit_option` field. If the user operation matches an audit policy, a detailed audit log is recorded and inserted into the `_gdb_audit.dba_audit_trail` table.

[0024] Audit log records can be retrieved and reviewed by querying the audit log table `_gdb_audit.dba_audit_trail` for audit analysis. SQL queries can be used to retrieve audit log records from the `_gdb_audit.dba_audit_trail` table and filtered based on user, time range, operation type, audit policy, and other criteria. Furthermore, query performance can be optimized by combining indexes in the audit log table (such as `ext_tm_idx`), thereby efficiently analyzing query results, reviewing user behavior, and checking for any abnormal or unauthorized operations.

[0025] Through the steps described above, this embodiment constructs an efficient, flexible, and easy-to-manage auditing system. Each step has a clear purpose and operation, collectively achieving a complete database auditing function, providing strong support for database security, compliance, and operation and maintenance.

[0026] In one embodiment, step S100 includes: S101. Create a system-level database; S102. Create an audit policy table in the system-level database; S103. Create an audit log table in the system-level database; S104. Create auxiliary stored procedures and several functions based on the system-level database.

[0027] In this embodiment, the present invention aims to implement database auditing functionality by creating a dedicated system-level database, _gdb_audit. This database serves as the core repository for the auditing function, storing audit policies, audit logs, and related stored procedures and functions.

[0028] First, create an audit policy table named `dba_audit_policies` in the `_gdb_audit` database to define and manage audit policies. The table structure includes the following fields: `user` (specifies the user name to which the audit policy applies), `policy_name` (a unique name for the audit policy), `enabled` (indicates whether the audit policy is enabled), `audit_option` (defines the type of operation to be audited, represented by a bit field), `object_schema` (the database name where the object to which the audit policy applies resides), and `object_name` (the name of the object to which the audit policy applies, such as a table name). Furthermore, set a primary key and indexes for this table to optimize query performance.

[0029] Secondly, create an audit log table named `dba_audit_trail` in the `_gdb_audit` database to record detailed information for each audit operation. The table structure includes the following fields: `id` (unique identifier for each audit log record), `client_ip` and `client_port` (client IP and port), `server_id`, `server_ip`, and `server_port` (server ID, IP, and port), `extended_timestamp` (audit time, recording the time the operation occurred), `db_user` (database user performing the operation), `userhost` (server IP), `session_id` (session identifier), `statement_id` (statement identifier), `policy_name` (policy name that triggered the audit), `object_type` (object type involved in the operation), `database_name` (database name involved in the operation), `option_name` (operation type being audited, such as INSERT, UPDATE, etc.), `sql_text` (the specific SQL statement executed), `returncode` (error code returned by the operation), and `sql_start_time` (start time of the SQL statement).

[0030] To better manage and maintain the auditing functionality, the following auxiliary stored procedures and functions were also created. The stored procedure `_gdb_audit.purge_audit_trail` is used to clean up the audit log, periodically deleting old audit log records to prevent the log table from becoming too large. The function `_gdb_audit.generate_audit_option` is used to generate the encoding of audit options, generating the corresponding bit field encoding based on the user-specified operation type (such as INSERT, UPDATE, etc.). The function `_gdb_audit.convert_audit_option` is used to convert the encoding of audit options back to readable option names, for example, converting the bit field encoding 000000000011100011 to INSERT, UPDATE, and DELETE. The function `_gdb_audit.audit_option_contains` is used to check whether an audit option contains a specific operation, such as checking whether an audit option contains an INSERT operation.

[0031] This invention implements database operation auditing functionality by adding new system tables (audit policy table and audit log table) and related stored procedures and functions. The audit policy table is used to define and manage audit policies, while the audit log table records detailed information for each operation. By using bit fields to represent audit options and leveraging indexes to optimize query performance, this method significantly improves the efficiency and flexibility of database auditing.

[0032] In one embodiment, step S200 includes: S201. Configure the creation of a new audit strategy in the audit strategy table, and insert the new audit strategy into the audit strategy table; S202. Configure and modify the audit strategy in the audit strategy table, and update the corresponding audit strategy record in the audit strategy table; S203. Configure the deletion of one or more audit policies in the audit policy table, and remove the corresponding one or more audit policy records from the audit policy table; S204. Configure one or more audit policies to be enabled in the audit policy table, and update the enabling status of the corresponding audit policies in the audit policy table; S205. Configure the audit strategy table to disable one or more audit strategies, and update the enabling status of the corresponding audit strategies in the audit strategy table.

[0033] In this embodiment, as Figure 3As shown, the database provides a set of audit policy syntax interfaces for flexibly defining and managing audit policies. These operations mainly include adding, modifying, deleting, enabling, and disabling audit policies, all of which are implemented by manipulating the _gdb_audit.dba_audit_policies table.

[0034] Specifically, when creating a new audit policy, a record needs to be inserted into the `_gdb_audit.dba_audit_policies` table. The parameters for creation include: `policy_name` (a unique name for the audit policy), `OPTIONS` (specifying the types of operations to be audited, which can be ALL or a list of specific operations such as INSERT, SELECT, etc.), `[USER username]` (an optional parameter specifying the user to whom the policy applies; if not specified, it applies to all users), `[DATABASE database_name]` (an optional parameter specifying the database to which the policy applies; if not specified, it applies to all databases), and `[TABLE table_name]` (an optional parameter specifying the table to which the policy applies; if not specified, it applies to all tables). If `USER`, `DATABASE`, or `TABLE` is not specified, it defaults to applying to all users, databases, or tables, and the values ​​of these fields in the `_gdb_audit.dba_audit_policies` table will be set to empty strings.

[0035] When modifying an audit policy, you need to update one existing record in the `_gdb_audit.dba_audit_policies` table. The parameters for modification include: `policy_name` (the name of the audit policy to be modified), `OPTIONS` (the new audit operation type), `[USER username]` (optional parameter, specifying the new user), `[DATABASE database_name]` (optional parameter, specifying the new database), and `[TABLE table_name]` (optional parameter, specifying the new table).

[0036] The operations for deleting an audit policy include: deleting an audit policy with a specified name, deleting all audit policies under a specified user, and deleting all audit policies. The operations for enabling an audit policy include: enabling an audit policy with a specified name, enabling all audit policies under a specified user, and enabling all audit policies. The operations for disabling an audit policy include: disabling an audit policy with a specified name, disabling all audit policies under a specified user, and disabling all audit policies. These operations are all implemented through simple SQL statements, providing flexible audit policy management capabilities to meet the needs of different business scenarios. In one embodiment, step S300 includes: S301. When a user performs a database operation, obtain the user behavior information of the current operation; S302. Analyze the user behavior information and extract key features; S303. Based on the key features and scenario type, query the audit strategy that matches the query and obtain the query results; S304. Combine the query results of each scenario to obtain the overall audit strategy hit by the current operation.

[0037] In this embodiment, as Figure 4 As shown, to implement database operation auditing, it is first necessary to capture behavioral information related to the user's database operations (such as querying, inserting, updating, deleting, etc.). This information forms the basis for subsequent auditing decisions. Specifically, the captured context information includes: user identity (the database user executing the operation, db_user), client information (client's IP address, client_ip, and client_port), server information (server's ID, server_id, server_ip, and server_port), operation time (the timestamp of the operation, extended_timestamp), session information (session identifier, session_id, and statement identifier, statement_id), operation content (the specific SQL statement executed, sql_text), and operation result (the error code returned by the operation, returncode).

[0038] Next, features related to the audit strategy are extracted from the captured user behavior information to facilitate subsequent matching of the audit strategy. Key extracted features include: user name (the user performing the operation), operation type (the type of operation performed, such as INSERT, UPDATE, DELETE, etc.), operation object (the database objects involved in the operation, such as table names, view names, etc., including the database name object_schema and the object name object_name), and operation time (the timestamp extended_timestamp of the operation).

[0039] Based on the extracted key features and specific scenario types, query the audit policy table `_gdb_audit.dba_audit_policies` to find matching audit policies. Query conditions may include: username (whether it matches the `user` field in the audit policy), operation type (whether it is set to require auditing in the `audit_option` field of the audit policy), operation object (whether it matches the `object_schema` and `object_name` fields in the audit policy), and whether the audit policy is enabled (only enabled policies will take effect). The query results will contain all matching audit policies.

[0040] Since an operation may involve multiple scenarios (e.g., the operation may trigger multiple audit policies simultaneously), the query results from these scenarios need to be merged. The merged overall audit policy will determine whether to audit the operation and how to log the audit results. If multiple audit policies have different requirements for the same operation (e.g., different audit options or different logging methods), a final decision needs to be made based on priority or rules.

[0041] Specifically, the audit module retrieves the username of the user performing the current operation. Then, it parses the current operation (SQL) to obtain the SQL option names; if the user operation uses a database and a table, it retrieves the database name and table name. Next, the audit module performs the following queries in sequence: if the current user operation uses a table, it queries the audit policy for the specified table; if the current user operation uses a database, it queries the audit policy for the specified database; it queries the audit policy for the specified user; and it queries the audit policy for the specified operation. Finally, the audit module merges the results of the above queries for the specified table, database, user, and operation, and returns the audit policy matched by the current user operation, i.e., the merged query results from the previous step, thus ensuring accurate matching of the audit policy corresponding to the user operation.

[0042] In one embodiment, step S400 includes: S401. Determine whether the current operation matches the audit policy; S402. If the current operation hits an audit policy, an audit log is generated for each hit audit policy. S403. Write each audit log to the audit log table in the system table.

[0043] In this embodiment, as Figure 5 As shown, users send SQL statements to the database through a database client or application, requesting the execution of related operations. The auditing module intervenes in this process to audit the operations performed by the user.

[0044] First, the audit module parses the SQL statements executed by the user, extracting key information, including the database user name executing the SQL statement, the type of the SQL statement (such as INSERT, SELECT, UPDATE, DELETE, etc.), and the database and table names specified in the SQL statement. Then, the audit module uses this key information to query the `_gdb_audit.dba_audit_policies` table to check for matching audit policies. If an audit policy is matched, an audit log record is generated for each matched audit policy.

[0045] Each audit log entry contains the following details: operation type (e.g., INSERT, SELECT, etc.), the username of the user performing the operation, the names of the databases and tables involved, the specific SQL statement executed, the time the operation occurred, and other relevant information such as client IP, server IP, and session ID. For example, if a user performs an INSERT operation and both audit policies (policy1 and policy2) are triggered, two audit log entries will be generated.

[0046] Next, the audit module inserts the generated audit log records into the _gdb_audit.dba_audit_trail table. Each audit log record contains detailed audit information, ensuring the transparency and traceability of the operation.

[0047] Finally, the audit module ends the audit process for the current operation. After completing the logging, the audit module cleans up temporary data, closes relevant connections, and returns the audit results. If the operation complies with the audit policy, the audit log has been successfully written to the `_gdb_audit.dba_audit_trail` table; if the operation does not comply with any audit policy, no audit log record is generated.

[0048] The entire process of generating audit logs is a systematic operation, starting from the user executing the SQL statement, to the audit module querying the matched audit policies, to generating and writing audit log records, and finally ending the audit process. This process provides strong support for database security and compliance.

[0049] In one embodiment, step S500 includes: S501. When querying the audit log of a specified operation, scan the audit log in the audit log table line by line; S502. Filter audit logs that match specific operations using a column filtering algorithm; S503. When querying audit logs for a specified time period, pre-index the audit time field in the audit log table; S504. Query the audit logs within a preset time range using the index.

[0050] In this embodiment, audit logs are stored in the `_gdb_audit.dba_audit_trail` table. Users can query specific audit log records by executing a SELECT statement. During the query, log records for specific fields can be retrieved as needed and sorted in descending order by audit time (extended_timestamp). The fields in the table include: `option_name` (the type of operation being audited, such as INSERT, UPDATE, etc.), `db_user` (the database user executing the operation), `policy_name` (the name of the policy that triggered the audit), `database_name` (the name of the database involved in the operation), `object_type` (the type of object involved in the operation, such as a table, view, etc.), `sql_text` (the specific SQL statement executed), and `extended_timestamp` (the audit time, recording the time the operation occurred).

[0051] To efficiently query audit logs, this embodiment employs two query algorithms: a sequential scan column filtering algorithm and an index scan algorithm.

[0052] For audit log queries with a specified operation type, a sequential scan column filtering algorithm is used. The database scans the table row by row, starting from the beginning, and filters records that meet specific conditions based on column values. Compared to traditional file queries, this method can handle large amounts of data more efficiently, reducing unnecessary data reading and processing, thereby improving query speed.

[0053] For audit log queries covering a specified time period, an index scan algorithm is used. In the `_gdb_audit.dba_audit_trail` table, the `extended_timestamp` field is set as an index (INDEX ext_tm_idx(extended_timestamp)). When querying audit logs for a specified time period, the database can use the index to quickly locate records within the matching time range without scanning the entire table row by row. Index scans significantly improve query efficiency, especially when dealing with massive amounts of audit log data. For example, in transaction audits at financial institutions, it might be necessary to query all transaction records for a specific date. With an index scan, these records can be located quickly in a short time, while a sequential scan might take several minutes or even longer.

[0054] By combining sequential scan column filtering and index scan algorithms, this embodiment ensures the efficiency and accuracy of audit log queries in massive data environments. The sequential scan column filtering algorithm is suitable for querying audit logs of specific operation types, reducing unnecessary data processing through row-by-row scanning and column value filtering. Index scan is suitable for querying audit logs within a specified time period, significantly improving query speed by quickly locating records using indexes. These optimizations provide strong support for database security, compliance, and operation and maintenance.

[0055] In one embodiment, step S600 includes: S601. Invoke the cleanup statement to clean up the audit log for a preset time. S602, or, enable the time scheduler and clear the audit logs at the set time through the event scheduler.

[0056] In this embodiment, as Figure 6 As shown, the audit log table _gdb_audit.dba_audit_trail accumulates a large number of records over time, which can lead to data bloat, excessive storage space consumption, and impact query performance. Therefore, it is necessary to regularly clean up old audit log records to maintain efficient system operation. Cleanup can be done manually or automatically.

[0057] Manually cleaning up audit logs is done by calling the stored procedure `_gdb_audit.purge_audit_trail`. Users can specify the number of days to clean up to delete audit log records older than a specified number of days. For example, a parameter of 10 indicates cleaning audit log records older than 10 days. The stored procedure will delete records whose `extended_timestamp` field is less than the current time minus the specified number of days. This method is suitable for scenarios requiring flexible control over the cleanup time.

[0058] Automatic cleanup is achieved through MySQL's event scheduler (event_scheduler). The event scheduler can periodically execute predefined tasks. The specific operation is as follows: 1. Enable the event scheduler.

[0059] 2. Create an event that defines how to automatically clean up audit log records older than 7 days every day.

[0060] 3. The event is triggered once a day, calling the stored procedure _gdb_audit.purge_audit_trail to clean up audit log records older than 7 days.

[0061] This approach is suitable for scenarios that require regular automatic maintenance of audit logs, reducing manual intervention and ensuring the long-term stable operation of the system.

[0062] Cleaning up the audit log is a crucial part of database maintenance, preventing the audit log table from becoming bloated and improving system performance. Manual cleanup involves calling the stored procedure `_gdb_audit.purge_audit_trail` to remove audit log records older than a specified number of days. Automatic cleanup involves enabling the event scheduler to periodically remove audit log records older than 7 days. The appropriate method can be chosen based on actual needs to ensure that the size and performance of the audit log table are reasonably controlled.

[0063] In one embodiment, a table-based database auditing apparatus is provided, which corresponds one-to-one with the table-based database auditing methods described in the above embodiments. (Refer to...) Figure 7 , Figure 7 This is a schematic diagram of the functional modules of a preferred embodiment of the table-based database auditing device of the present invention. The modules include a table system module 10, an audit operation module 20, an audit query module 30, a log generation module 40, and a log query module 50. Detailed descriptions of each functional module are as follows: Table system module 10 is used to create a system-level database and add system tables to the system-level database; Audit operation module 20 is used to add, modify, delete, enable or disable audit policies in the audit policy table of the system table; The audit query module 30 is used to obtain user behavior information and determine whether the current operation hits the audit policy based on the user behavior information and the scenario type. Log generation module 40 is used to generate audit logs for each audit policy that is hit when the current operation hits an audit policy, and write the audit logs to the system table; The log query module 50 is used to query the audit logs in the audit log table using query statements.

[0064] In one embodiment, the table system module 10 includes: A database unit is used to create a system-level database. The strategy table unit is used to create an audit strategy table in the system-level database. The log table unit is used to create audit log tables in the system-level database. The stored procedure and function unit is used to create auxiliary stored procedures and several functions based on the system-level database.

[0065] In one embodiment, the audit operation module 20 includes: An operation unit is added, which is used to configure the creation of a new audit policy in the audit policy table and insert the new audit policy into the audit policy table. The modification operation unit is used to configure and modify the audit strategy in the audit strategy table and update the corresponding audit strategy record in the audit strategy table. The deletion operation unit is configured to delete one or more audit policies in the audit policy table and remove the corresponding one or more audit policy records from the audit policy table. The activation operation unit is used to configure the activation of one or more audit policies in the audit policy table and update the activation status of the corresponding audit policies in the audit policy table; The disable operation unit is used to configure the disablement of one or more audit policies in the audit policy table and update the enable status of the corresponding audit policies in the audit policy table.

[0066] In one embodiment, the audit query module 30 includes: The data acquisition unit is used to acquire user behavior information for the current operation when a user performs a database operation; The data analysis unit is used to analyze the user behavior information and extract key features; The strategy query unit is used to query the hit audit strategy based on the key features and scenario type, and obtain the query results; The overall audit strategy unit is used to combine the query results of each scenario to obtain the overall audit strategy hit by the current operation.

[0067] In one embodiment, the log generation module 40 includes: The hit detection unit is used to determine whether the current operation hits the audit policy; The log generation unit is used to generate an audit log for each audit policy that is hit if the current operation hits the audit policy. The log storage unit is used to write each audit log to the audit log table in the system table.

[0068] In one embodiment, the log query module 50 includes: The specified operation unit is used to scan the audit logs in the audit log table line by line when querying the audit logs of the specified operation; The filtering unit is used to filter audit logs that match specific operations using a column filtering algorithm; Specify a time unit, which is used to pre-index the audit time field in the audit log table when querying audit logs for a specified time period; The index query unit is used to query audit logs within a preset time range using an index.

[0069] In one embodiment, the log cleanup module includes: The manual cleanup unit is used to call cleanup statements to clean up audit logs for a preset time. The automatic cleanup unit is used to activate the time scheduler and clean up the audit logs at a set time via the event scheduler.

[0070] In one embodiment, a computer device is provided, which may be a server, and its internal structure diagram may be as follows: Figure 8 As shown, the computer device includes a processor, memory, network interface, and database connected via a system bus. The processor provides computing and control capabilities. The memory includes non-volatile and / or volatile storage media and internal memory. The non-volatile storage media stores the operating system, computer programs, and database. The internal memory provides an environment for the operation of the operating system and computer programs stored in the non-volatile storage media. The network interface is used for communication with external user terminals via a network connection. When the computer program is executed by the processor, it implements the functions or steps of a table-based database auditing method on the server side.

[0071] In one embodiment, a computer device is provided, which may be a user terminal, and its internal structure diagram may be as follows: Figure 9 As shown, the computer device includes a processor, memory, network interface, display screen, and input devices connected via a system bus. The processor provides computing and control capabilities. The memory includes non-volatile storage media and internal memory. The non-volatile storage media stores the operating system and computer programs. The internal memory provides an environment for the operation of the operating system and computer programs in the non-volatile storage media. The network interface is used to communicate with an external server via a network connection. When the computer program is executed by the processor, it implements the user-side functions or steps of a table-based database auditing method. In one embodiment, a computer device is provided, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to perform the following steps: Create a system-level database and add system tables to the system-level database; Configure the audit policy table in the system table to add, modify, delete, enable, or disable audit policies; Obtain user behavior information, and determine whether the current operation hits the audit policy based on the user behavior information and scenario type; When the current operation hits an audit policy, an audit log is generated for each hit audit policy, and the audit log is written to the system table; Use a query statement to retrieve the audit logs from the audit log table. In one embodiment, a computer-readable storage medium is provided having a computer program stored thereon, the computer program performing the following steps when executed by a processor: Create a system-level database and add system tables to the system-level database; Configure the audit policy table in the system table to add, modify, delete, enable, or disable audit policies; Obtain user behavior information, and determine whether the current operation hits the audit policy based on the user behavior information and scenario type; When the current operation hits an audit policy, an audit log is generated for each hit audit policy, and the audit log is written to the system table; Use a query statement to retrieve the audit logs from the audit log table.

[0072] It should be noted that the functions or steps that can be implemented by the computer-readable storage medium or computer device described above can be referred to the relevant descriptions on the server side and user side in the foregoing method embodiments. To avoid repetition, they will not be described one by one here.

[0073] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium, and when executed, it can include the processes of the embodiments of the above methods. Any references to memory, storage, databases, or other media used in the embodiments provided in this application can include non-volatile and / or volatile memory. Non-volatile memory can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM), or flash memory. Volatile memory can include random access memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in various forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), dual data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link DRAM (SLDRAM), Rambus direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM), etc.

[0074] Those skilled in the art will clearly understand that, for the sake of convenience and brevity, the above-described division of functional units and modules is used as an example. In practical applications, the above functions can be assigned to different functional units and modules as needed, that is, the internal structure of the device can be divided into different functional units or modules to complete all or part of the functions described above.

[0075] It should be noted that if any software tools or components not belonging to this company appear in the embodiments of this application, they are merely illustrative examples and do not represent actual use. The embodiments described above are only used to illustrate the technical solutions of the present invention, and not to limit them; although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features; and these modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present invention, and should all be included within the protection scope of the present invention.

Claims

1. A table-based database auditing method, characterized in that, Includes the following steps: Create a system-level database and add system tables to the system-level database; Configure the audit policy table in the system table to add, modify, delete, enable, or disable audit policies; Obtain user behavior information, and determine whether the current operation hits the audit policy based on the user behavior information and scenario type; When the current operation hits an audit policy, an audit log is generated for each hit audit policy, and the audit log is written to the system table; Use a query statement to retrieve the audit logs from the audit log table.

2. The table-based database auditing method as described in claim 1, characterized in that, The creation of a system-level database and the addition of system tables within that database include: Create a system-level database; Create an audit policy table in the system-level database; Create an audit log table in the system-level database; Create auxiliary stored procedures and several functions based on the system-level database.

3. The table-based database auditing method as described in claim 1, characterized in that, The configuration of adding, modifying, deleting, enabling, or disabling audit policies in the system table's audit policy table includes: Configure the creation of a new audit policy in the audit policy table, and insert the new audit policy into the audit policy table; Configure and modify the audit strategy in the audit strategy table, and update the corresponding audit strategy record in the audit strategy table; Configure the deletion of one or more audit policies in the audit policy table, and remove the corresponding one or more audit policy records from the audit policy table; Configure and enable one or more audit policies in the audit policy table, and update the enabling status of the corresponding audit policies in the audit policy table; Configure the audit policy table to disable one or more audit policies, and update the enabled status of the corresponding audit policies in the audit policy table.

4. The table-based database auditing method as described in claim 1, characterized in that, The step of obtaining user behavior information and determining whether the current operation matches the audit policy based on the user behavior information and the scenario type includes: When a user performs a database operation, obtain the user's behavior information for the current operation; The user behavior information is analyzed to extract key features; Based on the key features and scenario types, the query results are obtained by querying the audit strategies that match the criteria. The query results for each scenario are combined to obtain the overall audit strategy for the current operation.

5. The table-based database auditing method as described in claim 1, characterized in that, When the current operation hits an audit policy, an audit log is generated for each hit audit policy, and the audit log is written to a system table, including: Determine whether the current operation matches the audit policy; If the current operation hits an audit policy, an audit log is generated for each hit audit policy; Write each audit log to the audit log table in the system table.

6. The table-based database auditing method as described in claim 1, characterized in that, The step of querying the audit logs in the audit log table using a query statement includes: When querying the audit logs for a specified operation, the audit logs in the audit log table are scanned line by line. Filter audit logs that match specific operations using a column filtering algorithm; When querying audit logs for a specified time period, an index is pre-set for the audit time field in the audit log table; Audit logs within a preset time range can be retrieved using an index.

7. The table-based database auditing method as described in claim 1, characterized in that, After retrieving the audit logs from the audit log table using a query statement, the process also includes: Invoke the cleanup statement to clean up the audit logs for a preset time period; Alternatively, enable the time scheduler and clear the audit logs at set times through the event scheduler.

8. A table-based database auditing device, characterized in that, The table-based database auditing device includes: The table system module is used to create a system-level database and add system tables to the system-level database; The audit operation module is used to add, modify, delete, enable, or disable audit policies in the audit policy table of the system table; The audit query module is used to obtain user behavior information and determine whether the current operation hits the audit policy based on the user behavior information and scenario type. The log generation module is used to generate audit logs for each audit policy that is hit when the current operation hits an audit policy, and write the audit logs to the system table. The log query module is used to query audit logs in the audit log table using query statements.

9. A computer device, characterized in that, The computer device includes a memory, a processor, and a table-based database auditing program stored on the memory and executable on the processor, wherein the table-based database auditing program, when executed by the processor, implements the steps of the table-based database auditing method as described in any one of claims 1-7.

10. A computer-readable storage medium, characterized in that, The storage medium stores a table-based database auditing program, which, when executed by a processor, implements the steps of the table-based database auditing method as described in any one of claims 1-7.

Images