The SQL audit feature records Data Manipulation Language (DML) and Data Definition Language (DDL) operations on your database in real time and lets you search them.
Limitations
The SQL audit log does not record the following operations:
-
INSERT INTO VALUES -
REPLACE INTO VALUES -
UPSERT INTO VALUES
Enable or disable SQL audit
Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. Find the cluster that you want to manage and click the cluster ID.
-
Navigate to the SQL Audit page.
-
For clusters of Enterprise Edition, Basic Edition, and Data Lakehouse Edition, choose in the left-side navigation pane.
-
For clusters of Data Warehouse Edition, click Data Security in the left-side navigation pane, and then click SQL Audit.
-
-
In the upper-right corner, click Configure SQL Audit.
-
In the dialog box that appears, select Yes and click OK.
-
To disable the SQL audit feature, repeat Steps 1 to 3 to open the Configure SQL Audit dialog box, and then select No.
ImportantDisabling the SQL audit feature clears all existing SQL audit logs. We recommend that you query and export your SQL audit logs before you disable this feature. When you re-enable SQL audit, logs are generated from that point on.
Query and export SQL audit logs
On the SQL Audit page, you can query SQL audit logs for a specific period based on criteria such as Operation Type or Execution Status.
-
You can only query SQL audit logs from the last 30 days. Queries for data older than 30 days return empty results.
-
A single query can span up to 24 hours. To save the logs on the current page to a local file, click Export Current Page.
FAQ
Double audit logs for INSERT OVERWRITE
INSERT OVERWRITE statements are executed asynchronously within AnalyticDB for MySQL. An access node distributes the query task to different storage nodes and returns the final result to the client. Therefore, the audit log records the IP addresses of both the originating client and the AnalyticDB for MySQL access node.
Missing DROP statements in audit logs
If the table recycle bin feature is enabled for the cluster, DROP statements are not written to the SQL audit log. To check if the table recycle bin is enabled, run the following command:
SHOW ADB_CONFIG KEY=DROP_FORCE;
-
If the command returns
false, the table recycle bin is enabled. The cluster rewritesDROPstatements and moves the dropped tables to the recycle bin. As a result, theDROPstatements do not appear in the SQL audit log. -
If the command returns
true, the table recycle bin is disabled.DROPstatements are recorded in the SQL audit log as expected.
Missing UPDATE statements after DTS synchronization
When Data Transmission Service (DTS) synchronizes data from ApsaraDB RDS, it rewrites source UPDATE statements as REPLACE INTO VALUES statements or a combination of DELETE and INSERT statements before writing the data to the destination. Because the SQL audit feature does not record REPLACE INTO VALUES statements (see the Limitations section), the original UPDATE statements are not directly visible in the audit log.
API reference
|
Edition |
API |
|
Enterprise Edition, Basic Edition, and Data Lakehouse Edition |
|
|
Data Warehouse Edition |
|