TiDB Audit Log

Introduction

In today's data-driven world, maintaining comprehensive audit log of database activities is crucial for security, compliance, and operational insights. TiDB, a powerful distributed SQL database platform, provides native audit capabilities that can be enhanced with sophisticated third-party solutions. As organizations face mounting pressure to protect their data assets and comply with regulations, implementing robust database auditing becomes essential.

According to recent security trends, database-related security incidents continue to rise, making it critical to maintain detailed audit logs of all database activities. A proper audit logging system helps organizations track user activities, monitor sensitive data access, and ensure compliance with various regulatory requirements. I'll provide a comprehensive overview with query examples and incorporate links directly in the text.

Understanding TiDB Audit Log

TiDB provides flexible logging options for monitoring and troubleshooting. You can adjust these settings dynamically using SQL commands:

Querying Log Settings

To retrieve the current log-related settings in TiDB, you can use the following queries:

SHOW VARIABLES LIKE 'tidb%log%';

This will display the variables related to logging in TiDB.

Alternatively, you can query the INFORMATION_SCHEMA.CLUSTER_CONFIG table for more detailed configuration information:

SELECT *
FROM INFORMATION_SCHEMA.CLUSTER_CONFIG
WHERE `KEY` LIKE '%log%';

Log Configuration Example:

Modifying Log Settings

Some log settings in TiDB can be modified dynamically using SET GLOBAL queries, while others require changes to the config.toml file. Here's a breakdown:

• Dynamic Changes (via SET GLOBAL):
  Certain settings can be adjusted on-the-fly, such as:

  SET GLOBAL tidb_slow_log_threshold = 300; -- Modify the slow query log threshold
  SET GLOBAL tidb_query_log_max_len = 4096; -- Adjust the maximum query length
  

• Changes in config.toml File:
  Some settings, such as log.format (for log output format) and log.level (for log verbosity), must be modified directly in the config.toml file. These settings cannot be dynamically changed via SQL queries and require a restart of the TiDB instance to take effect.

1. Slow Query Logging

Enable, set threshold, and log execution plans:

SET GLOBAL tidb_enable_slow_log = 1;
SET GLOBAL tidb_slow_log_threshold = 500;  -- in milliseconds
SET GLOBAL tidb_record_plan_in_slow_log = 1;

• Default threshold: 300 ms.

2. Expensive Queries Logging

Track high-resource queries based on row count:

SET GLOBAL tidb_expensive_query_time_threshold = 300;

• Queries exceeding the set threshold (in ms) will be logged.

3 Log File Management

Configure log storage and retention in config.toml:

[log.file]
filename = "/var/log/tidb/tidb.log"
max-size = 300   # MB before rotation
max-days = 7     # Retention period
max-backups = 5  # Number of backups
compression = "gzip"

For more details, see TiDB Documetation.

Querying TiDB Audit Log

To review recent queries, you can query the INFORMATION_SCHEMA.CLUSTER_SLOW_QUERY table. This table captures slow queries across the entire TiDB cluster.

Example: Retrieve the Last 50 Executed Queries

SELECT
    Time,
    Query,
    Query_time
FROM INFORMATION_SCHEMA.CLUSTER_SLOW_QUERY
ORDER BY Time DESC
LIMIT 50;

This query:

• Fetches the last 50 queries executed across the cluster.
• Includes execution time (Query_time) for performance analysis.
• Useful for identifying long-running or resource-intensive queries.

Enabling General Logs for Full Query History

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;

(This setting starts recording all executed queries in the general log)

Step 2: Query General Logs Within a Time Range:

You can use the following query to fetch the last 50 general log entries within a specified time range. This query also filters based on the log message content.

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 '%'; -- narrow down results
ORDER BY Time DESC
LIMIT 50;

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

Query Output Example:

DataSunrise: Extended Capabilities for TiDB Audit Log, Security and Compliance

DataSunrise provides a comprehensive database audit solution that significantly enhances TiDB's native capabilities with advanced features and granular control.

Real-Time Monitoring and Analysis

DataSunrise implements continuous database activity monitoring that captures and analyzes all database operations in real-time. The platform provides detailed insights into:

• SQL query execution
• User session activities
• Schema modifications
• Data access patterns

Advanced Audit Trail Management

The solution maintains comprehensive audit trails with sophisticated filtering and storage capabilities. Administrators can easily search, analyze, and export audit data for compliance reporting or security investigations.

Automated Compliance Reporting

DataSunrise streamlines compliance with various regulations through automated compliance reporting. The platform includes pre-configured templates for common compliance frameworks and allows customization for specific requirements.

Key Features

1. Continous Data Protection with real-time monitoring and alerting
2. Comprehensive Security Controls with role-based access management
3. Behavioral Analytics for detecting anomalous activities
4. Real-Time Notifications for security events
5. Automated Reports Generation for compliance and security needs

Conclusion

While TiDB provides essential native audit logging capabilities, organizations with advanced security and compliance requirements can benefit significantly from DataSunrise's comprehensive audit solution. The combination of TiDB's robust database platform and DataSunrise's sophisticated audit capabilities creates a powerful environment for maintaining security, ensuring compliance, and gaining operational insights.

Ready to enhance your TiDB database security and compliance? Schedule a demo today to see how DataSunrise can transform your database auditing capabilities.