SQLAuditStore is a tool developed by using Go to persist SQL audit data stored in the V$SQL_AUDIT view. It can periodically pull the V$SQL_AUDIT data that may be lost due to the eviction mechanism from the memory, and store 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 pushed by SQLAuditStore to SLS can be received by Sensitive Data Discovery and Protection (SDDP). You can monitor the SQL operation records related to OceanBase Database in the SDDP console, and implement more comprehensive protection on the database based on the extended audit feature of SDDP.
This topic describes how to use OceanBase Admin Toolkit (OAT) to install and uninstall SQLAuditStore.
Prerequisites
Before you install SQLAuditStore, make sure that the following conditions are met:
- You have installed OAT and logged on as either a super administrator or an O&M engineer. For more information, see Install OAT.
- You have added the server for installing SQLAuditStore. For more information, see Add a server.
- You have added the SQLAuditStore image file.
- Your server meets the installation requirements of SQLAuditStore.
Install SQLAuditStore
To install SQLAuditStore, perform the following steps:
In the left-side navigation pane, choose Product Service > Component Management.
On the Component Management page, click Create Component in the upper-right corner and select Create SQLAuditStore.
On the Create SQLAuditStore page, configure the parameters.
The following table describes the basic parameters of SQLAuditStore.
Parameter Description 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 where SQLAuditStore is to be installed. CPU The number of CPU cores on the server available for SQLAuditStore. Recommended value: 4C.Memory The memory on the server available for SQLAuditStore. Recommended value: 16G.Storage Mode Select File, SLS, or OpenSearch. The following tables describe the startup parameters for different data storage modes of SQLAuditStore.
- Startup parameters of the File storage mode
Parameter Description scanInterval The collection interval, in seconds. SQL records are collected at the specified interval. batchSendSize The number of SQL records collected each time. You can adjust the value based on the collection interval and onsite environment. By default, 50,000 records are collected at an interval of 10 seconds. clusterName The name of the OceanBase cluster whose SQL records are to be collected, for example, test_obcluster, of which the:xxxpart is omitted.connectionHost The host address for connecting to the cluster, which can be an IP address or domain name, for example, 10.1.1.1orobproxy-xx.com.connectionPort The proxy port for connecting to the cluster, which is usually 2883 or 3306. clusterPassword The password of the sys tenant in the cluster. SQLAuditStore will automatically encrypt the password. tenantIds The list of IDs of tenants in the cluster, for example, 1001,1002,1003,1004. If this parameter is not specified, SQLAuditStore will collect the SQL records of all tenants in the cluster, except the sys tenant. We recommend that you specify tenant IDs as needed to reduce resource usage.- Startup parameters of the SLS storage mode
Parameter Description scanInterval The collection interval, in seconds. SQL records are collected at the specified interval. batchSendSize The number of SQL records collected each time. You can adjust the value based on the collection interval and onsite environment. By default, 50,000 records are collected at an interval of 10 seconds. clusterName The name of the OceanBase cluster whose SQL records are to be collected, for example, test_obcluster, of which the:xxxpart is omitted.connectionHost The host address for connecting to the cluster, which can be an IP address or domain name, for example, 10.1.1.1orobproxy-xx.com.connectionPort The proxy port for connecting to the cluster, which is usually 2883 or 3306. clusterPassword The password of the sys tenant in the cluster. SQLAuditStore will automatically encrypt the password. tenantIds The list of IDs of tenants in the cluster, for example, 1001,1002,1003,1004. If this parameter is not specified, SQLAuditStore will collect the SQL records of all tenants in the cluster, except the sys tenant. We recommend that you specify tenant IDs as needed to reduce resource usage.projectName The SLS project name of. If SLS interconnects with SDDP, the project name is ali-yundun-sddp-ob. You can specify a custom value in other cases.logStoreName The SLS LogStore name of. If SLS interconnects with SDDP, the LogStore name is sddp_ob_log. You can specify a custom value in other cases.logStoreTTL The data retention period for the LogStore in SLS. Recommended value: 7 days. shardCnt The number of shards for the LogStore. Recommended value: 16. accessKey The AccessKey ID. accessSecret The AccessKey secret. region The region of Apsara Stack. - Startup parameters of the OpenSearch storage mode
Parameter Description scanInterval The collection interval, in seconds. SQL records are collected at the specified interval. batchSendSize The number of SQL records collected each time. You can adjust the value based on the collection interval and onsite environment. By default, 50,000 records are collected at an interval of 10 seconds. clusterName The name of the OceanBase cluster whose SQL records are to be collected, for example, test_obcluster, of which the:xxxpart is omitted.connectionHost The host address for connecting to the cluster, which can be an IP address or domain name, for example, 10.1.1.1orobproxy-xx.com.connectionPort The proxy port for connecting to the cluster, which is usually 2883 or 3306. clusterPassword The password of the sys tenant in the cluster. SQLAuditStore will automatically encrypt the password. tenantIds The list of IDs of tenants in the cluster, for example, 1001,1002,1003,1004. If this parameter is not specified, SQLAuditStore will collect the SQL records of all tenants in the cluster, except the sys tenant. We recommend that you specify tenant IDs as needed to reduce resource usage.openSearchUser The username of the OpenSearch cluster. openSearchPass The password of the OpenSearch cluster. openSearchEndpoint The connection string of OpenSearch, in the ip:portformat. Separate multiple nodes with commas, for example,ip1:port,ip2:port,ip3:port.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 > Product Management.
Find the target SQLAuditStore service and click Uninstall in the Actions column.
You can also search for the SQLAuditStore service by name at the top of the page.
In the confirmation dialog box that appears, click Uninstall.
OAT displays a message indicating that the service is being uninstalled. OAT also creates an uninstallation task. In the confirmation dialog box that appears, you can click the task ID to view the task progress.
Notice
If SQLAuditStore is associated with another product, the uninstall will fail.
What to do next
After you install SQLAuditStore, you can install other products in the OceanBase ecosystem on multiple nodes.