TiDB Audit Tools

Introduction

Data security and compliance are critical concerns for any organization managing sensitive information in their databases. TiDB, a distributed SQL database designed for elastic scale and real-time analytics, offers several auditing tools to monitor and track database activities. In this article, we'll explore TiDB's native audit capabilities and how integrating with DataSunrise, a database security platform, can further enhance your auditing and compliance efforts.

Native TiDB Audit 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.

TiDB offers two types of 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.

Other Available Built-In TiDB Audit Tools

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;
   

1. General Log: TiDB supports the general log feature, but it uses a different configuration name compared to MySQL. In TiDB, you can enable the general log by setting the tidb_general_log variable to 1 or ON. This will cause TiDB to record all SQL queries executed on the server.

    SET GLOBAL tidb_general_log =  1;
   

External TiDB Audit Tools

Some external auditing tools are compatible with TiDB:

• TiDB Binlog: TiDB Binlog is a tool that collects binlog data from TiDB and provides real-time data backup and replication. It can be used for incremental data auditing.

• Syslog Integration: TiDB logs can be forwarded to external syslog servers for centralized logging and auditing. Configure the log.file.filename option to send logs to syslog:

• Audit Log Analysis Tools: TiDB offers built-in Grafana integration for monitoring. Audit logs can be forwarded to Prometheus using tools like Fluentd or Filebeat, and Grafana dashboards can be customized for real-time analysis.

• DataSunrise Database Security: Our solution that provides comprehensive database activity monitoring and auditing capabilities for TiDB deployments.

Summary

TiDB provides multiple built-in auditing features, with the enterprise database auditing and legacy audit plugin offering the most comprehensive capabilities. Some external tools like TiDB Binlog can also assist in auditing. Choose the appropriate auditing solution based on your edition and requirements.

Advanced TiDB Audit Tools with DataSunrise

While TiDB's native audit tools provide a solid foundation, integrating with DataSunrise can significantly boost your auditing capabilities. DataSunrise is a comprehensive database security platform that offers advanced features for activity monitoring, data audit, and compliance management.

DataSunrise seamlessly integrates with TiDB, enabling you to:

1. Establish a centralized auditing system: DataSunrise consolidates audit data from multiple TiDB instances and over 40 supported data storage systems providing a unified view of database activities across your entire infrastructure.

1. Define granular audit rules: With DataSunrise, you can create customized audit rules based on various criteria, such as user roles, IP addresses, and specific SQL statements. This flexibility allows you to focus on the events that matter most to your organization.

1. Monitor database traffic in real-time: DataSunrise's real-time monitoring capabilities provide immediate visibility into TiDB database traffic, enabling you to detect and respond to potential security threats promptly.

1. Generate comprehensive audit reports: With DataSunrise, you can easily generate detailed audit reports for compliance audits, internal reviews, and forensic investigations. The platform's intuitive reporting features help you demonstrate adherence to regulatory requirements and maintain a strong security posture.

Conclusion

Auditing is a crucial aspect of maintaining the security and integrity of your TiDB databases. By leveraging TiDB's native audit tools and integrating with DataSunrise, you can establish a robust auditing framework that meets your organization's unique requirements.

DataSunrise with its advanced features, seamless integration, and user-friendly interface makes it an ideal choice for enhancing your TiDB auditing capabilities.

To experience the benefits of DataSunrise for your TiDB environment firsthand, schedule a demo with our experts today. Take the first step towards strengthening your database security and compliance posture.