SQLAuditStore is a tool developed by using Go to persist SQL audit data recorded by the V$SQL_AUDIT view. It periodically pulls data recorded by the V$SQL_AUDIT view that may be lost due to the eviction mechanism from the memory, and stores the data in the CSV format on your local computer, in Simple Log Service (SLS) of Alibaba Cloud, or in an OpenSearch cluster. Historical CSV files can be compressed. In Apsara Stack, the data that SQLAuditStore pulls to SLS can be received by Sensitive Data Discovery and Protection (SDDP). You can monitor the SQL operation records of OceanBase Database in the SDDP console and implement more comprehensive protection on the database by using the extended audit feature of SDDP.
This topic describes how to install and uninstall SQLAuditStore in the OceanBase Admin Toolkit (OAT) console.
Prerequisites
Before you install SQLAuditStore, make sure that the following conditions are met:
- You have installed OAT and logged in as a super administrator or an O&M engineer. For more information, see Install OAT.
- You have added a server to install SQLAuditStore. For more information, see Add a server.
- You have added a SQLAuditStore image file.
- The server meets the requirements for installing SQLAuditStore.
Install SQLAuditStore
To install SQLAuditStore, perform the following steps:
In the left-side navigation pane, choose Product Service > Components.
On the Components page, click Create Component in the upper-right corner and choose Create SQLAuditStore.
On the Create SQLAuditStore page, configure the parameters.
The following table describes the basic parameters of SQLAuditStore.
Parameter Description SQLAuditStore Image The Docker image of SQLAuditStore. Component Name The name of the component, which must be unique. Default value: SQLAuditStore.Server The IP address of the server on which you want to install SQLAuditStore. CPU The number of CPU cores available to SQLAuditStore. Default value: 4. Memory The size of memory available to SQLAuditStore, in GB. Default value: 16. Storage mode The data storage mode of SQLAuditStore. Valid values: File, SLS, and OpenSearch. The following table describes the data storage parameters of SQLAuditStore.
- Startup parameters for the File storage mode
Parameter Description scanInterval The interval between each collection of SQL audit records, in seconds. batchSendSize The number of SQL audit records to be collected each time. You can modify the value based on the value of the scanInterval parameter and the actual situation. By default, 50,000 SQL audit records are collected each time at an interval of 10 seconds. clusterName The name of the OceanBase cluster from which SQL audit records are to be collected. Take note that :xxxin the original cluster name must be omitted. Example:test_obcluster.connectionHost The host address for connecting to the cluster, which can be an IP address or a domain name. Examples: 10.1.1.1andobproxy-xx.com.connectionPort The proxy port for connecting to the cluster. In most cases, the proxy port number is 2883 or 3306. clusterPassword The password of the sys tenant in the cluster. SQLAuditStore automatically encrypts the password. tenantIds The IDs of tenants whose SQL audit records are to be collected in the cluster. Example: 1001,1002,1003,1004. If you do not specify this parameter, the SQL audit records of all tenants except the sys tenant are collected. We recommend that you specify this parameter to reduce resource usage.- Startup parameters for the SLS storage mode
Parameter Description scanInterval The interval between each collection of SQL audit records, in seconds. batchSendSize The number of SQL audit records to be collected each time. You can modify the value based on the value of the scanInterval parameter and the actual situation. By default, 50,000 SQL audit records are collected each time at an interval of 10 seconds. clusterName The name of the OceanBase cluster from which SQL audit records are to be collected. Take note that :xxxin the original cluster name must be omitted. Example:test_obcluster.connectionHost The host address for connecting to the cluster, which can be an IP address or a domain name. Examples: 10.1.1.1andobproxy-xx.com.connectionPort The proxy port for connecting to the cluster. In most cases, the proxy port number is 2883 or 3306. clusterPassword The password of the sys tenant in the cluster. SQLAuditStore automatically encrypts the password. tenantIds The IDs of tenants whose SQL audit records are to be collected in the cluster. Example: 1001,1002,1003,1004. If you do not specify this parameter, the SQL audit records of all tenants except the sys tenant are collected. We recommend that you specify this parameter to reduce resource usage.projectName The name of the SLS project. If SLS is connected to SDDP, set this parameter to ali-yundun-sddp-ob. Otherwise, you can specify a custom project name.logStoreName The name of the Logstore. If SLS is connected to SDDP, set this parameter to sddp_ob_log. Otherwise, you can specify a custom Logstore name.logStoreTTL The data retention period of the Logstore. We recommend that you set the data retention period to seven days. shardCnt The number of shards in the Logstore. We recommend that you set this parameter to 16. accessKey The AccessKey ID. accessSecret The AccessKey secret. region The region of Apsara Stack. - Startup parameters for the OpenSearch storage mode
Parameter Description scanInterval The interval between each collection of SQL audit records, in seconds. batchSendSize The number of SQL audit records to be collected each time. You can modify the value based on the value of the scanInterval parameter and the actual situation. By default, 50,000 SQL audit records are collected each time at an interval of 10 seconds. clusterName The name of the OceanBase cluster from which SQL audit records are to be collected. Take note that :xxxin the original cluster name must be omitted. Example:test_obcluster.connectionHost The host address for connecting to the cluster, which can be an IP address or a domain name. Examples: 10.1.1.1andobproxy-xx.com.connectionPort The proxy port for connecting to the cluster. In most cases, the proxy port number is 2883 or 3306. clusterPassword The password of the sys tenant in the cluster. SQLAuditStore automatically encrypts the password. tenantIds The IDs of tenants whose SQL audit records are to be collected in the cluster. Example: 1001,1002,1003,1004. If you do not specify this parameter, the SQL audit records of all tenants except the sys tenant are collected. We recommend that you specify this parameter to reduce resource usage.openSearchUser The username of the OpenSearch cluster. openSearchPass The password corresponding to the username. openSearchEndpoint The connection string of OpenSearch in the ip:portformat. Separate multiple connection strings with a comma (,). Example:ip1:port,ip2:port,ip3:port.After you configure the parameters, click Submit.
You can also click Reset to restore to the default configurations.
Uninstall SQLAuditStore
To uninstall SQLAuditStore, perform the following steps:
In the left-side navigation pane, choose Product Service > Components.
Find the SQLAuditStore component that you want to uninstall and click Uninstall in the Actions column.
Note
You can also search for the required SQLAuditStore component by name at the top of the page.
In the message that appears, click Uninstall.
Then, OAT displays a message indicating that the component is being uninstalled and creates an uninstallation task. You can click the task ID in the message to view the task progress.
Note
If the SQLAuditStore component is associated with another service, the uninstallation fails.
What to do next
After you install SQLAuditStore, you can install other services in the OceanBase ecosystem on multiple nodes.