SQL audit logs-Lindorm(Lindorm)-阿里云帮助中心

Prerequisites

The minor version of LindormTable is 2.7.6 or later. For information about how to view or update the minor version of LindormTable, see Release notes of LindormTable and Upgrade the minor engine version of a Lindorm instance.

Usage notes

The SQL audit log feature is not supported for Lindorm Serverless and single-node instances.
If you enable the audit log feature as a RAM user, you must grant the AliyunLogFullAccess permission to the RAM user. For more information about how to grant permissions to a RAM user, see Grant permissions to a RAM user.
After the audit log feature is enabled, Lindorm creates a project and a dedicated Logstore in the region where the Lindorm instance is deployed.
- The naming format for a Project is aliyun-product-Alibaba Cloud Account ID-region ID.
- The name of the dedicated Logstore is lindorm-sql-audit-log.
You cannot perform specific operations on dedicated Logstores. For example, you cannot write data to a dedicated Logstore or modify or delete the indexes of a dedicated Logstore. In addition, you cannot modify the attributes of a dedicated Logstore. However, no limits are imposed on features such as data query, statistical analysis, and alerting.
Make sure that SLS is available. If SLS becomes unavailable, the full log analysis feature cannot be used.

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

Important

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.

Enable the SQL audit log feature

Billing

Log Service charges pay-as-you-go fees for SQL audit logs based on storage space and retention period. For detailed pricing information, see Log Service Pricing.

Procedure

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, click Wide Table Engine.
Click the SQL Audit Logs tab, and then click Enable audit logs.
In the Enable SQL Audit Logs dialog box, click OK.

View SQL audit logs

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 Logs and Auditing > Audit Logs. 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 Logs and Auditing > Audit Logs to view the audit log details for the Wide Table Engine.
Note
By 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, and connectionId. 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 `0` indicates success. Any value other than `0` indicates failure.
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 `select`, `insert`, `update`, `delete`, `create`, `drop`, `alter`, and `other`.
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.