How to Audit TiDB

As the digital landscape grows more complex, data security and auditing practices are critical for organizations managing sensitive information. In this guide, we'll explore how to effectively audit TiDB to ensure your database activities remain secure, compliant, and transparent. While TiDB's native auditing capabilities provide essential features, enhancing your auditing process with advanced solutions is key to meeting regulatory and security requirements.

According to cybersecurity statistics, cyberattacks are on the rise, making database audit processes more important than ever. A robust auditing framework can help organizations stay ahead of these threats.

How to Audit TiDB with Native Tools

TiDB provides built-in auditing features that allow you to track and record database activities, such as connections, query execution, and data changes. These native tools help you maintain a comprehensive audit trail for compliance and security purposes.

Enterprise Auditing Solutions

TiDB offers two types of enterprise-grade auditing solutions:

1. Database Auditing (New in TiDB v7.1): Starting from TiDB v7.1, a redesigned database auditing feature is available in TiDB Enterprise Edition, replacing the legacy audit plugin. It provides more powerful auditing capabilities and supports different log formats (text, JSON). You can enable database auditing using a system variable:

   SET GLOBAL tidb_audit_enabled = ON;
   

2. Audit Plugin (Legacy): Prior to TiDB v7.1, the TiDB Audit Plugin was the main auditing solution available in TiDB Enterprise Edition. It allows specifying which events to audit and where to store the audit logs. Enable the audit plugin like this:

   INSTALL PLUGIN audit SONAME 'audit_plugin.so';
   SET GLOBAL audit_log_policy = 'ALL';
   

Note that both database auditing and audit plugin features are only available in the TiDB Enterprise Edition. They are not included in the community version.

Community Edition Audit Methods

Apart from the enterprise auditing solutions, TiDB provides other features that can be used for auditing purposes across all versions:

1. Slow Query Log: TiDB's Slow Query Log records SQL statements exceeding a specified execution time threshold. Enable it by setting the tidb_slow_log_threshold variable:

   SET GLOBAL tidb_slow_log_threshold = 200;
   

2. Statement Summary Tables ): Statement summary tables aggregate statistics about SQL statements. Query the statements_summary and statements_summary_history tables to access this data.

3. TiDB Log : The TiDB log records database events and supports log redaction. Disable redaction if needed:

   SET GLOBAL tidb_redact_log = 0;
   

4. General Log: TiDB supports the general log feature with a different configuration compared to MySQL. Enable the general log to record all SQL queries:

   SET GLOBAL tidb_general_log = 1;
   

Implementing Complete Query History Logging

If you need a complete query history, enable general logging and retrieve logs using INFORMATION_SCHEMA.CLUSTER_LOG.

Step 1: Enable General Logging:

SET GLOBAL tidb_general_log = 1;

Step 2: Query General Logs Within a Time Range:

SELECT * FROM information_schema.cluster_log
WHERE time > '2024-02-18 00:00:00'
AND time < '2025-02-18 23:59:59'
AND message LIKE '%' 
ORDER BY Time DESC
LIMIT 50;

This query retrieves the 50 most recent log entries matching the specified time range. The message LIKE '%' condition is a wildcard search, returning all logs. You can replace it with more specific keywords (e.g., '%select%', 'insert%') to filter by log type or query content.

In summary, TiDB's native audit logs provide insights into query execution times, user logins, and other relevant database activities. To delve deeper into TiDB's built-in audit features, check out the official TiDB documentation on monitoring and logging.

How to Audit TiDB in the Most Efficient Way with DataSunrise

TiDB offers essential audit trail features, but by integrating DataSunrise, you can enhance the auditing process with intuitive controls, detailed rule creation, and clear visual audit logs. This guide will walk you through the process of setting up and using DataSunrise to audit TiDB effectively.

Step 1: Establish a Connection with TiDB

Connect your TiDB database to DataSunrise by entering your server's host, port, and authentication details. Once connected, DataSunrise automatically begins monitoring your TiDB instance in real time.

Step 2: Set Up Custom Audit Rules

Create tailored audit rules to monitor specific activities like failed login attempts, data modifications, or queries executed by privileged users. These rules ensure that all important actions are captured for analysis.

Step 3: Review TiDB Audit Trails

Access the Transactional Trails section to view comprehensive audit logs. Filter records by user, query type, or timestamp to gain deeper insights into database activity and efficiently track TiDB operations.

Why Choose DataSunrise for TiDB?

• Seamless Integration: DataSunrise integrates easily with TiDB and offers multiple deployment options to suit your specific needs.

• Customizable Audit Rules: With DataSunrise, you can create detailed audit rules tailored to specific TiDB objects, ensuring comprehensive tracking of database events.

• Advanced Threat Detection: In addition to basic auditing, DataSunrise offers cutting-edge vulnerability assessment tool using AI-driven behavioral analytics to spot potential security risks.

Conclusion

While TiDB’s native auditing tools are sufficient for basic tracking, DataSunrise offers an enhanced solution for organizations needing more advanced auditing, compliance management, and security. By implementing DataSunrise, you ensure your TiDB database is properly monitored, secure, and compliant.

Take your TiDB auditing to the next level by scheduling a live demonstration today and discover how DataSunrise can transform your data security and compliance framework.