Enabling SQL Audit

After you enable the SQL audit function, all SQL operations will be recorded in log files. You can download audit logs to view log details.

By default, SQL audit is disabled because enabling this function may affect database performance. This section describes how to enable, modify, or disable SQL audit.

Both primary DB instances and read replicas support SQL audit logging.
Times in audit logs use the Coordinated Universal Time (UTC) format, regardless of the time zone configured for the DB instance.
After SQL auditing is enabled, RDS records SQL operations in audit logs. The generated audit log files are temporarily stored in the instance and then uploaded to OBS and stored in the backup space. If there is not enough free backup space available for generated audit logs, the additional space required is billed.
Audit logs are cleared every hour. After you change the retention period of audit logs, expired audit logs will be deleted 1 hour later.
After SQL auditing is enabled, a large number of audit logs may be generated during peak hours. As a result, there are many audit log files temporarily stored in the instance, and the storage may be full. You are advised to enable storage autoscaling.

Supported Database Versions

Only the versions listed below support SQL audit. If your DB engine version is too old, upgrade it to the latest version by referring to Upgrading the Minor Version.

RDS for MySQL 5.6 instances using cloud disks: 5.6.43 and later versions
RDS for MySQL 5.7 instances using cloud disks: 5.7.23 and later versions
RDS for MySQL 8.0

Constraints

While a standby read replica is acting as a primary read replica due to an exception in the primary, the audit logs generated are invisible.

If your instance is deployed in LA-Mexico City2 and you want to enable SQL audit for it, you need to contact customer service.

Enabling SQL Audit

Log in to the management console.
Click in the upper left corner and select a region and a project.
Click in the upper left corner of the page and choose Databases > Relational Database Service.
On the Instances page, click the target DB instance.

In the navigation pane on the left, choose SQL Audits. On the displayed page, click Set SQL Audit above the list. In the displayed dialog box, configure information as required and click OK.

Enabling or setting SQL audit

To enable SQL audit, toggle (disabled) to (enabled).
Audit logs can be retained from 1 to 732 days and are retained for 7 days by default.

Figure 1 Setting SQL audit
Click to enlarge

The SQL statements executed by PreparedStatement and scheduled tasks through a MySQL client will be treated as PREPARED_STATEMENT and CREATE, respectively. However, the SQL statements executed by PreparedStatement through JDBC will not be recorded.

After SQL auditing is enabled, Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and other operation types are supported. The details are as follows:

**Table 1** DDL types and operations
Type	Operation	Remarks
CREATE	create_db, create_event, create_function, create_index, create_procedure, create_table, create_trigger, create_udf, create_view	-
ALTER	alter_db, alter_db_upgrade, alter_event, alter_function, alter_instance, alter_procedure, alter_table, alter_tablespace	The alter_user operation is no longer supported from January 26, 2024.
DROP	drop_db, drop_event, drop_function, drop_index, drop_procedure, drop_table, drop_trigger, drop_view	-
RENAME	rename_table	-
TRUNCATE	truncate	-
REPAIR	repair	Added on January 26, 2024
OPTIMIZE	optimize	Added on January 26, 2024

**Table 2** DML types and operations
Type	Operation	Remarks
INSERT	insert, insert_select	-
DELETE	delete, delete_multi	-
UPDATE	update, update_multi	The update_multi operation was added on January 26, 2024.
REPLACE	replace, replace_select	-
SELECT	select	-

**Table 3** DCL types and operations
Type	Operation	Remarks
CREATE_USER	create_user	-
DROP_USER	drop_user	-
RENAME_USER	rename_user	-
GRANT	grant_roles, grant	The grant_roles operation was added on January 26, 2024.
REVOKE	revoke, revoke_all, revoke_roles	The revoke_roles operation was added on January 26, 2024.
ALTER_USER	alter_user	Added on January 26, 2024
ALTER_USER_DEFAULT_ROLE	alter_user_default_role	Added on January 26, 2024

**Table 4** Other types and operations
Type	Operation	Remarks
BEGIN/COMMIT/ROLLBACK	begin, commit, release_savepoint, rollback, rollback_to_savepoint, savepoint	-
PREPARED_STATEMENT	execute_sql,prepare_sql, dealloc_sql	The dealloc_sql operation was added on January 26, 2024.
CALL_PROCEDURE	call_procedure	Added on January 26, 2024
KILL	kill	Added on January 26, 2024
SET_OPTION	set_option	Added on January 26, 2024
CHANGE_DB	change_db	Added on January 26, 2024
UNINSTALL_PLUGIN	uninstall_plugin	Added on January 26, 2024
INSTALL_PLUGIN	install_plugin	Added on January 26, 2024
SHUTDOWN	shutdown	Added on January 26, 2024
SLAVE_START	slave_start	Added on January 26, 2024
SLAVE_STOP	slave_stop	Added on January 26, 2024
LOCK_TABLES	lock_tables	Added on January 26, 2024
UNLOCK_TABLES	unlock_tables	Added on January 26, 2024
FLUSH	flush	Added on January 26, 2024
XA	xa_commit,xa_end,xa_prepare,xa_recover,xa_rollback,xa_start	Added on January 26, 2024

Disabling SQL audit

To disable SQL audit, toggle (enabled) to (disabled).

If you select the check box "I acknowledge that after audit log is disabled, all audit logs are deleted." and click OK, all audit logs will be deleted.

Deleted audit logs cannot be recovered. Exercise caution when performing this operation.

Enabling Audit Log Reporting to LTS

To enable audit log reporting to LTS, contact customer service to apply for the required permissions.
After this function is enabled, audit logs record all requests sent to your DB instance and are stored in LTS.
This function does not take effect immediately. There is a delay of about 10 minutes.
You will be billed for enabling this function. For details, see LTS pricing details.
After this function is enabled, all audit policies are reported by default.

Keep the following points in mind before you enable audit logging or audit log reporting to LTS:
- Enabling audit logging or audit log reporting to LTS generates audit logs and the sensitive information in the audit logs is not anonymized.
- If you enable audit logging first and then enable audit log reporting to LTS, LTS reuses the audit policy set for your instance and you will also be billed for reporting audit logs to LTS. Only after you disable audit logging, billing for audit logging will be terminated.
- If you enable audit logging first and then enable audit log reporting to LTS, you are not advised to disable audit logging before audit log reporting to LTS is running properly.
Audit logs uploaded to LTS may be lost in the scenarios described below. If audit logging is enabled, you can download all audit log files from OBS.
- There is a low probability that some logs are lost when the service traffic is heavy, audit logs are generated too fast, or the LTS service fails.
- The maximum size of a single audit log record that can be uploaded to LTS is 512 KB. If the size of an audit log record exceeds this limit, the audit log record will be truncated.

Log in to the management console.
Click in the upper left corner and select a region and a project.
Click in the upper left corner of the page and choose Databases > Relational Database Service.
On the Instances page, click the DB instance name.
In the navigation pane, choose SQL Audits.
Click next to the Report Audit Logs to LTS field.
Select an LTS log group and log stream and click OK.

Figure 2 Enabling audit log reporting to LTS