Lindorm provides the SQL audit log feature. You can view audit logs to check SQL execution records within a specific period of time and filter the records based on specific conditions.
Prerequisites
For more information about how to enable the SQL audit log feature, see Enable the SQL log audit feature.
The LindormTable version is 2.7.6 or later. For information about how to view or update the minor version of LindormTable, see LindormTable release notes and Upgrade the minor engine version of a Lindorm instance.
Audit log types
Lindorm SQL audit logs record the following types of SQL operations:
DDL operations, such as CREATE DATABASE, CREATE TABLE, ALTER TABLE, and DROP TABLE
DML operations, such as INSERT, UPSERT, DELETE, and UPDATE
DQL operations, such as SELECT
Typically, DML and DQL operations are frequent, and logging these operations can affect the performance and stability of your instance. Therefore, the Wide Table Engine records only DDL SQL audit logs by default. To enable DML and DQL audit logs, you can adjust the audit level by using the AUDIT_LEVEL system variable. For detailed instructions, see Modify the audit log level.
Log retention period
By default, Lindorm SQL audit logs are retained for 30 days. You can modify the log retention period in the Lindorm console. Logs are automatically deleted after the retention period expires.
Modify the log retention period
Log on to the Lindorm console.
In the upper-left corner of the page, select the region where the instance is deployed.
On the Instances page, click the ID of the target instance or click View Instance Details in the Actions column for the instance.
In the left-side navigation pane, choose . In the upper-right corner, click Modify retention period and set the new retention period in the dialog box that appears.
Click Confirm Modifications.
View audit logs
Log on to the Lindorm console.
In the upper-left corner of the page, select the region where the instance is deployed.
On the Instances page, click the ID of the target instance or click View Instance Details in the Actions column for the instance.
In the left-side navigation pane, choose to view the audit log details for the Wide Table Engine.
NoteBy default, the log details in the last 15 minutes are displayed. You can also change the time period based on your business requirements.
(Optional) On the Raw Log tab, you can filter logs by various criteria. Use the Index Fields panel on the left to filter by fields such as db, clientIp, and fail. The right pane displays the detailed fields for each log, including
sqlType,user,affectRows,checkRows,clientIp,clientPort, andconnectionId. For example, select default under the db field to filter for audit records from a specific database.
Fields in audit logs
Field | Description |
instance_id | The ID of the Lindorm instance. |
reqId | The unique ID that identifies the operation audit log. |
user | The username corresponding to the operation audit log. |
fail | Indicates whether the SQL statement was executed successfully. A value of |
reason | The error message in the operation audit log. This message is returned only when an error occurs in the operation. |
connectionId | The connection ID of the client. |
protocol | The client protocol type. Valid values: Avatica and MySQL. |
db | The name of the selected database. |
sql | The SQL statement that was executed. |
sqlType | The type of the SQL statement. Valid values include |
params | The parameters that are specified in the SQL statement. Separate multiple parameters with commas (,). This field is available in query scenarios where parameters are bound. |
sqlTime | The time when the SQL statement was executed. |
responseTime | The response time. Unit: milliseconds. |
checkRows | The number of rows scanned, which indicates the number of rows returned from the data storage engine during the query. |
affectRows | The number of rows affected by the statement. For INSERT, UPDATE, or DELETE operations, affectRows indicates the number of modified rows. For a query statement, affectRows indicates the number of returned rows. |
dbVersion | The SQL version on the server that is connected. |
hostname | The host name on the server node. |
clientIp | The IP address of the client. |
clientPort | The port number of the client. |
Change the type of audit logs
You can execute the ALTER SYSTEM statement to modify the value of the AUDIT_LEVEL parameter and adjust the output level of SQL audit logs.
Valid values:
0: Records DDL logs.
1: Records DDL and DML logs.
2: Records DDL and DQL logs.
3: Records DDL, DML, and DQL logs.
Example
Modify the audit log level to record DDL and DQL logs.
ALTER SYSTEM SET AUDIT_LEVEL = 2;Verify the result
Execute the SHOW SYSTEM VARIABLES LIKE 'AUDIT_LEVEL'; statement to verify that the change was successful.
For more information about system variables, see ALTER SYSTEM.