SQL audit

The SQL audit feature helps you quickly identify SQL statements that may have issues and enhances your security audit capabilities. This feature allows you to query SQL statements in the current cluster by execution user, access IP, and operation type, and also supports exporting the query results. This topic describes the specific operation steps.

Considerations

SQL audit is a paid feature. The cost varies depending on the cloud vendor, 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, click its name, and go to the instance overview page.

4. In the left-side navigation pane, click Diagnostics, and then 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 the tenant.

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

8. In the dialog box, configure Open Object. After you select this option, SQL audit is enabled for all tenants under this instance.

9. In the dialog box, configure the SQL audit push settings, and then click Enable:

   • OceanBase Cloud | Parameter | Description | |----------|-----------------------------------------------| | Total Storage Duration | The SQL execution records stored for more than the specified duration will be deleted. The duration ranges from 7 days to 720 days. | | Hot Storage Duration | After the hot storage duration is exceeded, the data will automatically be moved to cold storage within the total storage duration. |

   • Alibaba Cloud SLS | Parameter | Description | |----------|-----------------------------------------------| | Project | The project name of the SLS service. | | Logstore | The logstore name of the SLS service. | | AccessKey ID | The AccessKey ID of the SLS service, used to identify the user. | | AccessKey Secret | The AccessKey secret of the SLS service, used to encrypt and sign the string and verify the signature. This key must be kept confidential. |

View and download SQL audit records

1. On the SQL Audit tab, click Expand.

2. Filter the records based on the following optional criteria:

   Filter	Description
   Database	Select one or more databases under the corresponding tenant to view.
   Node	Select one or more nodes to view.
   Keyword	Select one or more keywords that may exist in the SQL statement to view. You can enter multiple keywords, which can be related by "and" or "or".
   Tenant Name	Enter one or more tenant names to view.
   Operation Type	Select one or more operation types from the drop-down list to view.
   Client IP	Enter the client IP address to view.
   Scanned Records	Enter the range of scanned records to view.
   Execution Time (ms)	Enter the range of execution time for the SQL statement to view.
   Time Range	Select the time range for SQL execution. 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 Time (ms), Scanned Rows, and Affected Rows.

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

   Note

   Currently, you can download a maximum of 100 audit records. If the query result contains more than 100 records, only the first 100 records will be downloaded based on the page sorting order.

Modify SQL audit settings

1. On the SQL Audit tab, click Service Settings in the upper-right corner.

2. In the dialog box, modify the relevant parameters and click OK.

Disable SQL audit

1. On the SQL Audit tab, click Disable Service in the upper-right corner.

2. In the text box of the dialog box, enter close, and then click Close.