SQL audit

The SQL audit feature supports querying SQL statements in the current cluster by execution user, access IP, and operation type. It also supports exporting query results to help you enhance security audit capabilities. This topic describes the specific procedure.

Considerations

SQL audit is a paid feature. Fees vary by cloud vendor, region, and billing item. For details, see SQL audit billing.

The same SQL audit storage configuration applies to all tenants in an instance. You can modify the configuration only on the instance page, not on a tenant page.

Configure storage

1. Log in to the OceanBase Cloud console.

2. In the left navigation pane, click Instances.

3. In the instance list, find the target instance, click its name to enter the instance workspace.

4. In the left navigation pane, click Diagnostics, then select the Real-Time Diagnostics tab on the Diagnostics page.

5. In the middle of the page, click the SQL Audit tab.

6. Click Modify Storage Configuration next to Storage configuration and modify the storage configuration in the pop-up window.

   • OceanBase Cloud

     Total storage duration: You can set the total storage duration from 7 days to 720 days. SQL execution records exceeding this duration will be automatically deleted.

   • Custom log service

     Note

     • Only Alibaba Cloud instances support pushing SQL audit data to a custom log service.
     • The following API permissions must be granted to the AccessKey ID and AccessKey Secret:
       • PostLogStoreLogs: Write logs to Logstore
       • GetLogStore: View Logstore metadata

     Parameter	Description
     Project	The name of the log service project.
     Logstore	The name of the log service logstore.
     AccessKey ID	The access ID for the log service, used to identify the user.
     AccessKey Secret	The key used to encrypt the signature string and for the log service to verify the signature string; must be kept confidential.

   • Object storage

     Note

     • Only Alibaba Cloud and AWS instances support pushing SQL audit data to object storage.
     • SQL audit data will be stored in the object storage bucket you specify.

     Parameter	Description
     Bucket	The name of the object storage bucket.
     Directory	The directory in the object storage bucket. Multi-level directories are supported.
     Access Key ID	The AccessKey ID for accessing the object storage.
     Access Key Secret	The AccessKey Secret for accessing the object storage.

7. Click Modify push configuration to modify the storage target and related parameters.

   Note

   After modifying the storage configuration, new audit data will be pushed to the new storage target. Data in the original storage location is not affected.

Enable tenant SQL audit

1. Log in to the OceanBase Cloud console.

2. In the left-side navigation pane, click Instances.

3. In the instance list, find the target instance, click its name, and go to the instance workspace.

4. In the left-side navigation pane, click Diagnostics, then select the Real-Time Diagnostics tab on the Diagnostics page.

5. In the middle of the page, click the SQL Audit tab.

6. On the SQL Audit tab, select a tenant in the upper-left corner.

7. On the SQL Audit tab, click Enable SQL Audit.

8. Configure the audit rules in the pop-up window.

   Note

   • By default, all SQL types are selected, meaning all types of SQL are recorded.
   • Audit rules can only be configured when the storage is set to object storage.
   • This feature is only supported for tenants in MySQL-compatible mode and Oracle-compatible mode with a cluster version greater than or equal to 4.2.5.

   • MySQL

     When the storage target is configured as object storage, you need to configure audit rules:

     Parameter	Description
     Target Database Account	Select the database accounts to audit. You can select all accounts (including existing and future accounts) or one or more specified accounts. All accounts are selected by default. When you choose to specify accounts, an account selection box appears. You can select multiple accounts or search for accounts.
     SQL Type	Select the SQL type to record. For database versions V4.2.5 to 4.4.2.1: filtering by SQL category is supported; expanding to SQL subtypes is not supported. SQL categories include: connection, table access, DDL, and privilege change. For database versions V4.4.2.1 to 4.5.0.0 and V4.6.1.0 and later: in addition to the above categories, further expansion to subtypes is supported, such as SELECT/INSERT/UPDATE/DELETE under table access, CREATE/ALTER/DROP under DDL, and GRANT/REVOKE under privilege change.

   • Oracle

     When the storage target is configured as object storage, you need to configure audit rules:

     Parameter	Description
     Target Database Account	Select the database accounts to audit. You can select all accounts (including existing and future accounts) or one or more specified accounts. All accounts are selected by default. When you choose to specify accounts, an account selection box appears. You can select multiple accounts or search for accounts.
     SQL Type	Select the SQL type to be recorded. The main SQL categories include: connection, table access, DDL, and privilege change. Oracle-compatible tenant audit rules themselves require specifying the SQL statement type. By default, it supports expansion to subtypes, such as DELETE TABLE/INSERT TABLE/SELECT SEQUENCE/SELECT TABLE/UPDATE TABLE under table access, INDEX/OUTLINE/PROCEDURE/PROFILE/ROLE/SEQUENCE/SYNONYM/TABLE/TABLESPACE under DDL, and ALTER SYSTEM/CLUSTER/SYSTEM AUDIT/SYSTEM GRANT/EXECUTE PROCEDURE/GRANT PROCEDURE/GRANT SEQUENCE/GRANT TABLE under privilege change. Supported database versions: V4.4.2.1 to 4.5.0.0, and V4.6.1.0 and later.

9. You can click the icon in the tenant actions column to modify the rule or disable SQL auditing.

Modify the storage duration

1. In the tenant list on the SQL Audit tab, click Modify retention period in the Actions column of the target tenant.

2. Configure the relevant parameters in the dialog box:

   • Set Object: Displays the name of the current tenant.
   • Modify for Other Tenants in the Instance: An optional checkbox. If selected, the configuration will be synchronized to other tenants in the instance that have SQL audit enabled.
   • Total Storage Duration: Supports setting from 7 days to 720 days. SQL execution records exceeding the set duration will be deleted.

3. Click OK.

Disable SQL audit

1. In the tenant list on the SQL Audit tab, click Disable SQL audit in the Actions column of the target tenant.

2. Enter close in the text box of the dialog box, then click Close.

View and download SQL audit records

1. On the SQL Audit tab, click Expand.

2. Filter by conditions. The following items are all optional.

   Filter Item	Description
   Database	Select one or more databases under the corresponding tenant to view.
   Node	Select one or more nodes to view.
   Keyword	Select keywords that may exist in the SQL statements to view. You can enter multiple keywords, which can be related by "and" or "or".
   Username	Enter the name of one or more tenants to view.
   Operation Type	Select one or more operation types to view from the drop-down list.
   Client IP	Enter the client IP to view.
   Scanned Records	Enter the range of scanned record numbers to view.
   Execution Time (ms)	Enter the execution time range of the SQL statements to view.
   Time range	Select the SQL execution time range. The time range span cannot exceed 6 hours.

3. View the following information in the query results: SQL Statement, Database, User, Client IP, Operation Type, Execution Result, Request Time, Execution Time (ms), Scanned Rows, and Affected Rows.

4. Click the icon in the upper-right corner to download the query result list.

   Note

   You can download up to 100 audit records. If a query returns more than 100 records, only the first 100 records in the current sort order are downloaded.