TiDB Cloud Dedicated Database Audit Logging (Preview)

TiDB Cloud provides an audit logging feature that records user access activities of your database, such as executed SQL statements.

To evaluate the effectiveness of user access policies and other information security measures of your organization, it is a security best practice to periodically analyze database audit logs.

The audit logging feature is disabled by default. To audit a cluster, you must first enable audit logging, and then specify the audit filter rules.

Prerequisites

• You are using a TiDB Cloud Dedicated cluster.

  Note

  • Database audit logging is not available for TiDB Cloud Starter.
  • For TiDB Cloud Essential, see Database Audit Logging (PREVIEW) for TiDB Cloud Essential.

• You are in the Organization Owner or Project Owner role of your organization. Otherwise, you cannot see the database audit-related options in the TiDB Cloud console. For more information, see User roles.

Enable audit logging

TiDB Cloud supports writing the audit logs of a TiDB Cloud Dedicated cluster to your cloud storage service. Before enabling database audit logging, configure your cloud storage service on the cloud provider where the cluster is located.

Enable audit logging for AWS

To enable audit logging for AWS, take the following steps:

Step 1. Create an Amazon S3 bucket

Specify an Amazon S3 bucket in your organization-owned AWS account as a destination to which TiDB Cloud writes the audit logs.

For more information, see Creating a bucket in the AWS User Guide.

Step 2. Configure Amazon S3 access

1. Get the TiDB Cloud Account ID and the External ID of the TiDB cluster for which you want to enable audit logging.

   1. In the TiDB Cloud console, navigate to the My TiDB page.

      Tip

      If you are in multiple organizations, use the combo box in the upper-left corner to switch to your target organization first.

   2. Click the name of your target TiDB Cloud Dedicated cluster to go to its overview page, and then click Settings > DB Audit Logging in the left navigation pane.

   3. On the DB Audit Logging page, click Enable in the upper-right corner.

   4. In the Database Audit Log Storage Configuration dialog, locate the AWS IAM Policy Settings section, and record TiDB Cloud Account ID and TiDB Cloud External ID for later use.

2. In the AWS Management Console, go to IAM > Access Management > Policies, and then check whether there is an IAM policy with the s3:PutObject write-only permission.

   • If yes, record the matched policy for later use.

   • If not, go to IAM > Access Management > Policies > Create Policy, and define an IAM policy according to the following policy template.

     {
         "Version": "2012-10-17",
         "Statement": [
             {
                 "Effect": "Allow",
                 "Action": "s3:PutObject",
                 "Resource": "<Your S3 bucket ARN>/*"
             }
         ]
     }
     

     In the template, <Your S3 bucket ARN> is the Amazon Resource Name (ARN) of your S3 bucket where the audit log files are to be written. You can go to the Properties tab in your S3 bucket and get the ARN value in the Bucket Overview area. In the "Resource" field, you need to add /* after the ARN. For example, if the ARN is arn:aws:s3:::tidb-cloud-test, you need to configure the value of the "Resource" field as "arn:aws:s3:::tidb-cloud-test/*".

3. Go to IAM > Access Management > Roles, and then check whether a role whose trust entity corresponds to the TiDB Cloud Account ID and the External ID that you recorded earlier already exists.

   • If yes, record the matched role for later use.
   • If not, click Create role, select Another AWS account as the trust entity type, and then enter the TiDB Cloud Account ID value into the Account ID field. Then, choose the Require External ID option and enter the TiDB Cloud External ID value into the External ID field.

4. In IAM > Access Management > Roles, click the role name from the previous step to go to the Summary page, and then take the following steps:

   1. Under the Permissions tab, check whether the recorded policy with the s3:PutObject write-only permission is attached to the role. If not, choose Attach Policies, search for the needed policy, and then click Attach Policy.
   2. Return to the Summary page and copy the Role ARN value to your clipboard.

Step 3. Enable audit logging

In the TiDB Cloud console, go back to the Database Audit Log Storage Configuration dialog box where you got the TiDB Cloud account ID and the External ID values, and then take the following steps:

1. In the Bucket URI field, enter the URI of your S3 bucket where the audit log files are to be written.
2. In the Bucket Region drop-down list, select the AWS region where the bucket locates.
3. In the Role ARN field, fill in the Role ARN value that you copied in Step 2. Configure Amazon S3 access.
4. Click Test Connection and Next to verify whether TiDB Cloud can access and write to the bucket. If the connection is successful, the dialog navigates to the next step for database audit logging settings.

Enable audit logging for Google Cloud

To enable audit logging for Google Cloud, take the following steps:

Step 1. Create a GCS bucket

Specify a Google Cloud Storage (GCS) bucket in your organization-owned Google Cloud account as a destination to which TiDB Cloud writes audit logs.

For more information, see Creating storage buckets in the Google Cloud Storage documentation.

Step 2. Configure GCS access

1. Get the Google Cloud Service Account ID of the TiDB cluster for which you want to enable audit logging.

   1. In the TiDB Cloud console, navigate to the My TiDB page.

      Tip

      If you are in multiple organizations, use the combo box in the upper-left corner to switch to your target organization first.

   2. Click the name of your target TiDB Cloud Dedicated cluster to go to its overview page, and then click Settings > DB Audit Logging in the left navigation pane.

   3. On the DB Audit Logging page, click Enable in the upper-right corner.

   4. In the Database Audit Log Storage Configuration dialog, locate the Google Cloud Service Account ID section, and record Service Account ID for later use.

2. In the Google Cloud console, go to IAM & Admin > Roles, and then check whether a role with the following write-only permissions for objects in the storage bucket exists.

   • storage.objects.create
   • storage.objects.delete

   If yes, record the matched role for the TiDB cluster for later use. If not, go to IAM & Admin > Roles > CREATE ROLE to define a role for the TiDB cluster.

3. Go to Cloud Storage > Browser, select the GCS bucket you want TiDB Cloud to access, and then click SHOW INFO PANEL.

   The panel is displayed.

4. In the panel, click ADD PRINCIPAL.

   The dialog box for adding principals is displayed.

5. In the dialog box, take the following steps:

   1. In the New Principals field, paste the Google Cloud Service Account ID of the TiDB cluster.
   2. In the Role drop-down list, choose the role of the target TiDB cluster.
   3. Click SAVE.

Step 3. Enable audit logging

In the TiDB Cloud console, go back to the Database Audit Log Storage Configuration dialog box where you got the Google Cloud Service Account ID, and then take the following steps:

1. In the Bucket URI field, enter your full GCS bucket name.
2. In the Bucket Region field, select the GCS region where the bucket locates.
3. Click Test Connection and Next to verify whether TiDB Cloud can access and write to the bucket. If the connection is successful, the dialog navigates to the next step for database audit logging settings.

Enable audit logging for Azure

To enable audit logging for Azure, take the following steps:

Step 1. Create an Azure storage account

Create an Azure storage account in your organization's Azure subscription as the destination to which TiDB Cloud writes the database audit logs.

For more information, see Create an Azure storage account in Azure documentation.

Step 2. Configure Azure Blob Storage access

1. In the Azure portal, create a container used for storing database audit logs.

   1. In the left navigation pane of the Azure portal, click Storage Accounts, and then click the storage account for storing database audit logs.

      Tip

      If the left navigation pane is hidden, click the menu button in the upper-left corner to toggle its visibility.

   2. In the navigation pane for the selected storage account, click Data storage > Containers, and then click + Container to open the New container pane.

   3. In the New container pane, enter a name for your new container, set the anonymous access level (the recommended level is Private, which means no anonymous access), and then click Create. The new container will be created and displayed in the container list in a few seconds.

2. Get the URL of the target container.

   1. In the container list, select the target container, click ... for the container, and then select Container properties.
   2. On the displayed properties page, copy the URL value for later use, and then return to the container list.

3. Generate a SAS token for the target container.

   1. In the container list, select the target container, click ... for the container, and then select Generate SAS.

   2. In the displayed Generate SAS pane, select Account key for Signing method.

   3. In the Permissions drop-down list, select Read, Write, and Create to allow writing audit log files.

   4. In the Start and Expiry fields, specify a validity period for the SAS token.

      Note

      • The audit feature needs to continuously write audit logs to the storage account, so the SAS token must have a sufficiently long validity period. However, longer validity increases the risk of token leakage. For security, it is recommended to replace your SAS token every six to twelve months.
      • The generated SAS token cannot be revoked, so you need to set its validity period carefully.
      • Make sure to regenerate and update the SAS token before it expires to ensure continuous availability of audit logs.

   5. For Allowed protocols, select HTTPS only to ensure secure access.

   6. Click Generate SAS token and URL, and then copy the displayed Blob SAS token for later use.

Step 3. Enable audit logging

1. In the TiDB Cloud console, navigate to the My TiDB page.

   Tip

   If you are in multiple organizations, use the combo box in the upper-left corner to switch to your target organization first.

2. Click the name of your target TiDB Cloud Dedicated cluster to go to its overview page, and then click Settings > DB Audit Logging in the left navigation pane.

3. On the DB Audit Logging page, click Enable in the upper-right corner.

4. In the Database Audit Log Storage Configuration dialog, provide the blob URL and SAS token that you obtained from Step 2. Configure Azure Blob access:

   • In the Blob URL field, enter the URL of the container where audit logs will be stored.
   • In the SAS Token field, enter the SAS token for accessing the container.

5. Click Test Connection and Next to verify whether TiDB Cloud can access and write to the container. If the connection is successful, the dialog navigates to the next step for database audit logging settings.

Configure database audit logging settings

After configuring storage for your cloud provider, complete the step for database audit logging settings:

1. Set the log file rotation policy.

   You can rotate audit log files based on either file size or time interval. When either condition is met, TiDB Cloud generates a new audit log file.

   Note

   Log file rotation based on time interval only applies to TiDB v8.5.2 or later. If the TiDB version of your TiDB Cloud Dedicated cluster is earlier than v8.5.2, you can only rotate audit log files based on file size.

2. Configure log redaction.

   Log redaction is enabled by default. When enabled, sensitive information in the SQL text is replaced with ? in audit logs.

3. Click Save and Enable to apply the settings and enable audit logging.

Specify audit filter rules

After enabling audit logging, you must specify auditing filter rules to control which user access events to capture and write to audit logs. If no filter rules are specified, TiDB Cloud does not log anything.

To specify auditing filter rules for a cluster, take the following steps:

1. On the DB Audit Logging page, click Add Filter Rule in the Audit Filters section to add an audit filter rule.

2. In the Add Filter Rule dialog, configure the following items:

   • Filter Name: Enter a name for the filter rule.
   • SQL User: Enter the SQL user in the <user>@<host> format. The username and hostname can use % to match any value or _ to match any single character. The @ symbol and <host> are optional.
   • Filter Events: Select the events to log. For the supported filter events, see Audit Filter Events.

3. Click Confirm to add the filter rule.

View audit logs

By default, TiDB Cloud stores database audit log files in your storage service, so you need to access the audit logs from your storage service.

TiDB Cloud audit logs are readable text files with the cluster ID, node ID, and log creation date incorporated into the fully qualified file paths.

For example, 13796619446086334065/tidb-0/tidb-audit-2022-04-21T18-16-29.529.log. In this example, 13796619446086334065 indicates the cluster ID and tidb-0 indicates the node ID.

Disable audit logging

If you no longer want to audit a cluster, take the following steps:

1. Navigate to the My TiDB page in the TiDB Cloud console, and then click the name of your target TiDB Cloud Dedicated cluster.
2. In the left navigation pane, click Settings > DB Audit Logging.
3. In the Database Audit Logging section, click ... next to Settings, and then click Disable.

Audit filter events

The following table shows all event classes in database audit logging:

Audit logging fields

For each database event record in audit logs, TiDB Cloud provides the following fields:

General information

All classes of audit logs contain the following information:

SQL statement information

When the event class is QUERY or a subclass of QUERY, the audit logs contain the following information:

Connection information

When the event class is CONNECTION or a subclass of CONNECTION, the audit logs contain the following information:

Audit operation information

When the event class is AUDIT or a subclass of AUDIT, the audit logs contain the following information:

Audit logging limitations

TiDB Cloud Dedicated does not guarantee that audit logs are written in chronological order, which means that you might have to review all log files to find the most recent events. To sort the logs chronologically, you can use the TIME field in the audit logs.

Legacy database audit logging reference

If you are currently relying on the legacy audit logging plugin, see Database Audit Logging (Legacy).