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 your 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.

Enable SQL audit

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. On the SQL Audit tab, select a tenant in the upper-left corner.

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

8. In the pop-up window, configure Open Object. After selecting this option, SQL audit will be enabled for all tenants under this instance.

9. In the pop-up window, complete the SQL audit push settings, then click Enable:

   • 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.

     | Parameter | Description | |----------|-----------------------------------------------| | Project | The name of the log service project. | | Logstore | The name of the log service logstore. | | AccessKey ID | The access ID of 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

     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 object storage.
     Access Key Secret	The AccessKey Secret for accessing object storage.

     Note

     • Only Alibaba Cloud and AWS instances support pushing SQL audit data to object storage.
     • SQL audit data is stored in the specified object storage bucket.

   • 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 versions 4.2.5 to 4.4.1: Connection: logins/logouts/connects/disconnects Table access: queries/DML (SELECT/INSERT/UPDATE/DELETE) General events: DDL/privilege changes/system management

   • 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 record. SQL categories include: connection, table access, DDL, and privilege change. Audit rules for Oracle-compatible mode tenants themselves require specifying the SQL statement type. By default, expansion to subtypes is supported, 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 V4.5.0.0, and V4.6.1.0 and later.

     Note

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

View storage configuration

On the SQL Audit page, you can view the storage configuration for the current SQL audit data.

• The Storage configuration section shows where the current audit data is stored:

  • OceanBase Cloud: stored in the default storage provided by OceanBase Cloud.
  • Custom Log Service: stored in the log service, displaying project (Project) and logstore (Logstore) information.
  • Object Storage: stored in object storage, displaying the bucket name.

• Click Modify Push Configuration to modify the storage target and related parameters.

View and download SQL audit records

1. On the SQL Audit tab, click Expand.

2. Filter the results by conditions. The following items are optional.

   Filter Item	Description
   Database	Select one or more databases under the tenant to view.
   Node	Select one or more nodes to view.
   Keyword	Enter 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 address 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 cannot exceed 6 hours.

3. In the query results, view the following information: 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.

Modify the retention period

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

2. Configure the relevant parameters in the dialog box:

   • Set Object: Displays the current tenant name.
   • 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. In the text box of the pop-up window, enter close, and then click Close.