Enabling SQL Audit

Scenarios

If you enable the SQL audit function, all SQL operations will be logged for your download and query. By default, SQL audit is disabled. Enabling this function may affect database performance. The performance impact depends on the audit log throughput.

This section describes how to enable, modify, and disable SQL audit.

Supported Kernel Versions

The kernel version must be 2.0.60.241200 or later. If your DB engine version is too early, upgrade it to the latest version by referring to Upgrading the Minor Version of a DB Instance. For details about how to check the kernel version, see How Can I Check the Version of a TaurusDB Instance?

Constraints

**Table 1** Audit log constraints
Audit Log Type	Constraint
SQL audit logs	This function is available to TaurusDB Enterprise Edition instances and serverless instances, but not to multi-primary instances. Only SQL statements that have been executed in the kernel are logged. During specification changes, configurations need to be synchronized. As a result, some SQL operations are not logged. SQL audit logs use the Coordinated Universal Time (UTC) time, which is not affected by the time zone configuration. After SQL audit is enabled, TaurusDB records SQL operations in audit logs. The generated audit logs are temporarily stored in the instance and then uploaded to OBS and stored in the backup space. SQL audit logs that are beyond the retention period are cleared every hour. If you change the retention period of audit logs, expired audit logs will be deleted one hour later.
Audit logs reported to LTS	After this function is enabled, audit logs record all requests sent to your instance and are stored in LTS. Once configured, there is a delay of approximately 10 minutes before the changes are applied. 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 may be lost while being uploaded to LTS in certain situations. If audit logging is enabled, you can download all audit logs from OBS. Some logs may be lost when the service traffic is heavy, audit logs are generated too fast, or the LTS service fails. Each audit log record uploaded to LTS cannot exceed 512 KB. Larger records will be truncated. If log reporting is disabled, logs generated for the DB instance will not be reported to LTS. The request for disabling log reporting is not applied immediately. There is a delay of about 10 minutes.

Configuring Audit Logs

Log in to the TaurusDB console.
Click in the upper left corner and select a region and project.
On the Instances page, click the instance name.
In the navigation pane, choose SQL Audits.

Click Set SQL Audit. In the displayed dialog box, enable Audit Log and set Retention Period and Operations. 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

After SQL audit is enabled, Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and other operation types are supported. For details, see the following tables.

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.

**Table 2** DDL types and operations
Type	Operation
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
DROP	drop_db, drop_event, drop_function, drop_index, drop_procedure, drop_table, drop_trigger, drop_view
RENAME	rename_table
TRUNCATE	truncate
REPAIR	repair
OPTIMIZE	optimize

**Table 3** DML types and operations
Type	Operation
INSERT	insert, insert_select
DELETE	delete, delete_multi
UPDATE	update, update_multi
REPLACE	replace, replace_select
SELECT	select

**Table 4** DCL types and operations
Type	Operation
CREATE_USER	create_user
DROP_USER	drop_user
RENAME_USER	rename_user
GRANT	grant_roles, grant
REVOKE	revoke, revoke_all, revoke_roles
ALTER_USER	alter_user
ALTER_USER_DEFAULT_ROLE	alter_user_default_role

**Table 5** Other types and operations
Type	Operation
BEGIN/COMMIT/ROLLBACK	begin, commit, release_savepoint, rollback, rollback_to_savepoint, savepoint
PREPARED_STATEMENT	execute_sql, prepare_sql, dealloc_sql
CALL_PROCEDURE	call_procedure
KILL	kill
SET_OPTION	set_option
CHANGE_DB	change_db
UNINSTALL_PLUGIN	uninstall_plugin
INSTALL_PLUGIN	install_plugin
SHUTDOWN	shutdown
SLAVE_START	slave_start
SLAVE_STOP	slave_stop
LOCK_TABLES	lock_tables
UNLOCK_TABLES	unlock_tables
FLUSH	flush
XA	xa_commit,xa_end,xa_prepare,xa_recover,xa_rollback,xa_start

Click OK to save the settings.
To disable SQL audit, click Set SQL Audit, change the status of Audit Log from to , and click OK.

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

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

Log in to the TaurusDB console.
Click in the upper left corner and select a region and project.
On the Instances page, click the instance name.
In the navigation pane, choose SQL Audits.
Click next to Report Audit Logs to LTS.
In the displayed dialog box, select a log group and log stream, and click OK.

Figure 2 Reporting audit logs to LTS
To change the log group and log stream, click Edit.

Figure 3 Changing the log group and log stream
In the displayed dialog box, click OK.

Disabling Audit Log Reporting

Log in to the TaurusDB console.
Click in the upper left corner and select a region and project.
On the Instances page, click the instance name.
In the navigation pane, choose SQL Audits.
Click next to Report Audit Logs to LTS.
In the displayed dialog box, click Yes.

Figure 4 Disabling audit log reporting

FAQs

If SQL Audit Is Disabled but Audit Log Reporting to LTS Is Enabled, Will SQL Audit Logs Still Be Generated?

Yes.

If Audit Log Reporting to LTS Is Enabled but the Corresponding Log Stream Is Deleted, Will SQL Audit Logs Still Be Generated?

If the log stream is deleted, TaurusDB will continue to generate audit logs and will try to report the logs. If the number of logs that fail to be reported exceeds the upper limit 50 (at most 50 logs can be kept locally), the excess logs will be deleted.