SQL audit

The SQL audit feature allows you to query SQL statements in the current cluster by execution user, access IP address, or operation type. You can also export the query results to enhance security auditing. This topic describes how to use SQL audit.

Considerations

SQL audit is a paid feature. Fees vary by cloud provider, region, and billing item. For more information, see SQL audit billing.

Enable 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 and click its name to go to the instance workspace.

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

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

6. In the upper-left corner of the SQL Audit tab, select a tenant.

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

8. Configure Enable For in the dialog box. If you select this option, SQL audit is also enabled for all tenants in the instance.

9. Configure SQL audit push settings in the dialog box, and then click Enable:

   • OceanBase Cloud

     Parameter	Description
     Total Storage Duration	SQL execution records can be stored for 7 to 720 days. Records older than the specified duration are deleted.
     Hot Storage Duration	Within the total storage duration, data is automatically moved to cold storage after the specified hot storage duration.

   • Custom log service

     Note

     Only Alibaba Cloud and Baidu 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 used by the log service to identify the user.
     AccessKey Secret	The key used to encrypt a signature string and by the log service to verify the signature. Keep this key 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 used to access object storage.
     Access Key Secret	The AccessKey Secret used to access object storage.

     Note

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

   • MySQL

     If the storage destination is object storage, configure the following audit rules:

     Parameter	Description
     Target Database Account	Select the database accounts to audit. You can select all accounts, including existing accounts and accounts created in the future, or specify accounts. All accounts are selected by default. When you switch to specified accounts, an account selection field appears. You can select multiple accounts or search for accounts.
     SQL Type	Select the SQL types to record. Versions 4.2.5 to 4.4.1: Connection: Login/Logout/Connect/Disconnect Table access: Query/DML (SELECT/INSERT/UPDATE/DELETE) General event: DDL/Privilege change/System administration

     Note

     • All SQL types are selected by default. This means that all types of SQL statements are recorded.
     • Audit rules can be configured only when the storage destination is object storage.
     • This feature supports only tenants in MySQL mode on clusters of OceanBase Database V4.2.5 or later.

View the storage configuration

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

• The Storage Configuration section displays the destination service in which the current audit data is stored:

  • OceanBase Cloud: Data is stored in the default storage provided by OceanBase Cloud.
  • Custom log service: Data is stored in a log service. The Project and Logstore information is displayed.
  • Object storage: Data is stored in object storage. The bucket name is displayed.

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

View and download SQL audit records

1. On the SQL Audit tab, click Expand.

2. Specify filter conditions. All the following filter items are optional.

   Filter item	Description
   Database	Select one or more databases in the tenant.
   Node	Select one or more nodes.
   Keyword	Enter one or more keywords that may appear in the SQL statements. Use “and” or “or” to define the relationship between keywords.
   Username	Enter one or more tenant names.
   Operation Type	Select one or more operation types from the drop-down list.
   Client IP	Enter a client IP address.
   Scanned Records	Enter a range for the number of scanned records.
   Execution Duration (ms)	Enter an execution duration range for SQL statements.
   Time Range	Select a SQL execution time range. The time range 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 Duration (ms), Scanned Rows, and Updated Rows.

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

   Note

   You can currently download a maximum of 100 audit records. If the query returns more than 100 records, only the first 100 records are downloaded based on the page sort order.

Modify the storage duration

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

2. Configure the following parameters in the dialog box:

   • Configuration Scope: Displays the current tenant name.
   • Apply to Other Tenants in the Instance: Select this option to synchronize the configuration to other tenants in the instance for which SQL audit is enabled.
   • Total Storage Duration: Set a value from 7 to 720 days. SQL execution records older than the specified duration are 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, and then click Disable.