Security Audit

About 7 min

Security Audit

1. Introduction

Audit logs serve as the record credentials of a database, enabling tracking of various operations (e.g., create, read, update, delete) to ensure information security. The audit log feature in IoTDB supports the following capabilities:

Supports enabling/disabling the audit log functionality through configuration
Supports configuring operation types and privilege levels to be recorded via parameters
Supports setting the storage duration of audit log files, including time-based rolling (via TTL) and space-based rolling (via SpaceTL)
Supports configuring parameters to count slow requests (with write/query latency exceeding a threshold, default 10000 milliseconds) within any specified time period
Audit log files are stored in encrypted format by default

Note: This feature is available from version V2.0.8 onwards.

2. Configuration Parameters

Edit the iotdb-system.properties file to enable audit logging using the following parameters:

V2.0.8.1

Parameter Name	Description	Data Type	Default Value	Activation Method
`enable_audit_log`	Whether to enable audit logging. true: enabled. false: disabled.	Boolean	false	Hot Reload
`auditable_operation_type`	Operation type selection. DML: all DML operations are logged; DDL: all DDL operations are logged; QUERY: all query operations are logged; CONTROL: all control statements are logged.	String	DML,DDL,QUERY,CONTROL	Hot Reload
`auditable_operation_level`	Permission level selection. global: log all audit events; object: only log events related to data instances. Containment relationship: object < global. For example: when set to global, all audit logs are recorded normally; when set to object, only operations on specific data instances are recorded.	String	global	Hot Reload
`auditable_operation_result`	Audit result selection. success: log only successful events; fail: log only failed events	String	success,fail	Hot Reload
`audit_log_ttl_in_days`	Audit log TTL (Time To Live). Logs older than this threshold will expire.	Double	-1.0 (never deleted)	Hot Reload
`audit_log_space_tl_in_GB`	Audit log SpaceTL. Logs will start rotating when total space reaches this threshold.	Double	1.0	Hot Reload
`audit_log_batch_interval_in_ms`	Batch write interval for audit logs	Long	1000	Hot Reload
`audit_log_batch_max_queue_bytes`	Maximum byte size of the queue for batch processing audit logs. Subsequent write operations will be blocked when this threshold is exceeded.	Long	268435456	Hot Reload

V2.0.9.2

Parameter Name	Description	Data Type	Default Value	Activation Method
`enable_audit_log`	Whether to enable audit logging. true: enabled. false: disabled.	Boolean	false	Hot Reload
`auditable_operation_type`	Operation type selection. DML: all DML operations are logged; DDL: all DDL operations are logged; QUERY: all query operations are logged; CONTROL: all control statements are logged.	String	DML,DDL,QUERY,CONTROL	Hot Reload
`auditable_dml_event_type`	Event types for auditing DML operations. `OBJECT_AUTHENTICATION`: object authentication, `SLOW_OPERATION`: slow operation	String	`OBJECT_AUTHENTICATION`,`SLOW_OPERATION`	Hot Reload
`auditable_ddl_event_type`	Event types for auditing DDL operations. `OBJECT_AUTHENTICATION`: object authentication, `SLOW_OPERATION`: slow operation	String	`OBJECT_AUTHENTICATION`,`SLOW_OPERATION`	Hot Reload
`auditable_query_event_type`	Event types for auditing query operations. `OBJECT_AUTHENTICATION`: object authentication, `SLOW_OPERATION`: slow operation	String	`OBJECT_AUTHENTICATION`,`SLOW_OPERATION`	Hot Reload
`auditable_control_event_type`	Event types for auditing control operations. `CHANGE_AUDIT_OPTION`: audit option change, `OBJECT_AUTHENTICATION`: object authentication, `LOGIN`: login, `LOGOUT`: logout, `DN_SHUTDOWN`: data node shutdown, `SLOW_OPERATION`: slow operation, `FAILED_TTL_DELETION`: TTL deletion failure (V2.0.11.1)	String	`CHANGE_AUDIT_OPTION`,`OBJECT_AUTHENTICATION`,`LOGIN`,`LOGOUT`,`DN_SHUTDOWN`,`SLOW_OPERATION`,`FAILED_TTL_DELETION`	Hot Reload
`auditable_operation_level`	Permission level selection. global: log all audit events; object: only log events related to data instances. Containment relationship: object < global. For example: when set to global, all audit logs are recorded normally; when set to object, only operations on specific data instances are recorded.	String	global	Hot Reload
`auditable_operation_result`	Audit result selection. success: log only successful events; fail: log only failed events	String	success,fail	Hot Reload
`audit_log_ttl_in_days`	Audit log TTL (Time To Live). Logs older than this threshold will expire.	Double	-1.0 (never deleted)	Hot Reload
`audit_log_space_tl_in_GB`	Audit log SpaceTL. Logs will start rotating when total space reaches this threshold.	Double	1.0	Hot Reload
`audit_log_batch_interval_in_ms`	Batch write interval for audit logs	Long	1000	Hot Reload
`audit_log_batch_max_queue_bytes`	Maximum byte size of the queue for batch processing audit logs. Subsequent write operations will be blocked when this threshold is exceeded.	Long	268435456	Hot Reload

Instructions for Object Authentication and Slow Operations:

When the parameters auditable_dml_event_type, auditable_ddl_event_type, auditable_query_event_type, or auditable_control_event_type are set to OBJECT_AUTHENTICATION, the corresponding event types will be recorded in the audit log.
When the parameters auditable_dml_event_type, auditable_ddl_event_type, auditable_query_event_type, or auditable_control_event_type are set to SLOW_OPERATION, only the corresponding event types whose execution time exceeds the value of the slow_query_threshold parameter (default: 10000 ms) will be recorded in the audit log. The value of the slow_query_threshold parameter can be configured in the iotdb-system.properties file.

Instructions for TTL Deletion Failure Audit Logs:

The event type for TTL deletion failure audit logs is FAILED_TTL_DELETION, which is recorded only when a deletion fails; successful deletions are not logged. Logs are written to the tree-model device path root.__audit.log.node_{id}.u_5, where u_5 corresponds to the internal user __internal_ttl_auditor.

Configuration switch: FAILED_TTL_DELETION is enabled by default in the auditable_control_event_type whitelist. To disable it, remove it from the configuration.

Failure scenarios and log formats:

Failure Scenario	Trigger Condition	Log Format	Log Description	Log Count
Object file scan failure	An exception (e.g., disk I/O error, insufficient permissions) occurs while the system scans and deletes expired Object files.	TTL object scan failed: errors=[ClassName1 xN1(msg1),ClassName2 xN2(msg2)]	The system aggregates exceptions encountered during a single scan on a per-table basis. Exceptions of the same type are merged with their occurrence count, and the first occurrence's message is retained.	During each scan, one audit log is generated per table where an exception occurred.
TTL check startup failure	An exception occurs before the Object file scan begins per-table checks, or an exception occurs while Settle Compaction selects a task, causing the current round of TTL check to fail to start.	TTL check failed to start: {msg} error=ClassName(exceptionMsg)	The TTL check failed before determining the tables to process; the log does not record table information, only the failure stage and the exception cause.	One audit log is generated per check startup failure.
Settle Compaction execution failure	Settle Compaction returns a failure result (isSuccess=false) after execution.	TTL settle compaction failed: error=...	The log records the failure cause of Settle Compaction and the TTL tables involved in this task; table names are collected by the Selector while scanning devices.	When Settle Compaction execution fails, one audit log is generated per involved TTL table.

3. Access Methods

Supports direct reading of audit logs via SQL.

3.1 SQL Syntax

SELECT (<audit_log_field>, )* log FROM <AUDIT_LOG_PATH> WHERE whereclause ORDER BY order_expression

AUDIT_LOG_PATH: Audit log storage location root.__audit.log.<node_id>.<user_id>
audit_log_field: Query fields refer to the metadata structure below
Supports WHERE clause filtering and ORDER BY sorting

3.2 Metadata Structure

Field	Description	Data Type
`time`	The date and time when the event started	timestamp
`username`	User name	string
`cli_hostname`	Client hostname identifier	string
`audit_event_type`	Audit event type, e.g., WRITE_DATA, GENERATE_KEY, SLOW_OPERATION, etc. From V2.0.11.1, supports SESSION_TIME_EXCEEDED (session timeout), DESTROY_KEY (key destruction operation), AUDIT_STORAGE_FULL (audit log storage full), and FAILED_TTL_DELETION (TTL deletion failure)	string
`operation_type`	Operation type, e.g., DML, DDL, QUERY, CONTROL	string
`privilege_type`	Privilege used, e.g., WRITE_DATA, MANAGE_USER	string
`privilege_level`	Event privilege level, global or object	string
`result`	Event result, success=1, fail=0	boolean
`database`	Database name	string
`sql_string`	User's original SQL statement	string
`log`	Detailed event description	string

3.3 Usage Examples

Query times, usernames and host information for successfully executed queries:

IoTDB> select username,cli_hostname from root.__audit.log.** where operation_type='QUERY' and result=true align by device
+-----------------------------+---------------------------+--------+------------+
|                         Time|                     Device|username|cli_hostname|
+-----------------------------+---------------------------+--------+------------+
|2026-01-23T10:39:21.563+08:00|root.__audit.log.node_1.u_0|    root|   127.0.0.1|
|2026-01-23T10:39:33.746+08:00|root.__audit.log.node_1.u_0|    root|   127.0.0.1|
|2026-01-23T10:42:15.032+08:00|root.__audit.log.node_1.u_0|    root|   127.0.0.1|
+-----------------------------+---------------------------+--------+------------+
Total line number = 3
It costs 0.036s

Query latest operation details:

IoTDB> select username,cli_hostname,operation_type,sql_string  from root.__audit.log.** order by time desc limit 1 align by device
+-----------------------------+---------------------------+--------+------------+--------------+------------------------------------------------------------------------------------------------------------------+
|                         Time|                     Device|username|cli_hostname|operation_type|                                                                                                        sql_string|
+-----------------------------+---------------------------+--------+------------+--------------+------------------------------------------------------------------------------------------------------------------+
|2026-01-23T10:42:32.795+08:00|root.__audit.log.node_1.u_0|    root|   127.0.0.1|         QUERY|select username,cli_hostname from root.__audit.log.** where operation_type='QUERY' and result=true align by device|
+-----------------------------+---------------------------+--------+------------+--------------+------------------------------------------------------------------------------------------------------------------+
Total line number = 1
It costs 0.033s

Query failed operations:

IoTDB> select database,operation_type,log  from root.__audit.log.** where result=false align by device
+-----------------------------+-------------------------------+-----------+--------------+---------------------------------------------------------------------------------+
|                         Time|                         Device|   database|operation_type|                                                                              log|
+-----------------------------+-------------------------------+-----------+--------------+---------------------------------------------------------------------------------+
|2026-01-23T10:49:55.159+08:00|root.__audit.log.node_1.u_10000|           |       CONTROL|        User user1 (ID=10000) login failed with code: 801, Authentication failed.|
|2026-01-23T10:52:04.579+08:00|root.__audit.log.node_1.u_10000|  [root.**]|         QUERY|   User user1 (ID=10000) requests authority on object [root.**] with result false|
|2026-01-23T10:52:43.412+08:00|root.__audit.log.node_1.u_10000|root.userdb|           DDL| User user1 (ID=10000) requests authority on object root.userdb with result false|
|2026-01-23T10:52:48.075+08:00|root.__audit.log.node_1.u_10000|       null|         QUERY|User user1 (ID=10000) requests authority on object root.__audit with result false|
+-----------------------------+-------------------------------+-----------+--------------+---------------------------------------------------------------------------------+
Total line number = 4
It costs 0.024s

Query audit records for user 'u_0' on node 'node_1' with event types 'SLOW_OPERATION'

IoTDB> select * from root.__audit.log.node_1.u_0 where audit_event_type='SLOW_OPERATION' align by device
+-----------------------------+---------------------------+------+---------------+--------------+--------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------+----------------+------------+--------+
|                         Time|                     Device|result|privilege_level|privilege_type|database|operation_type|                                                                                                                                                                    log|                                                                                                                          sql_string|audit_event_type|cli_hostname|username|
+-----------------------------+---------------------------+------+---------------+--------------+--------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------+----------------+------------+--------+
|2026-05-06T14:43:55.088+08:00|root.__audit.log.node_1.u_0|  true|         OBJECT|   [READ_DATA]|        |         QUERY|           SLOW_QUERY: cost 60 ms, select * from root.__audit.log.node_1.u_0 where audit_event_type='SLOW_OPERATION' or audit_event_type='LOGIN'limit 1 align by device|select * from root.__audit.log.node_1.u_0 where audit_event_type='SLOW_OPERATION' or audit_event_type='LOGIN'limit 1 align by device|  SLOW_OPERATION|   127.0.0.1|    root|
|2026-05-06T14:44:08.715+08:00|root.__audit.log.node_1.u_0|  true|         OBJECT|  [WRITE_DATA]|        |           DML|              Execution: insert into root.ln.wf02.wt02(timestamp, status, hardware) values (2, false, 'v2') cost 290 ms, with status code: TSStatus(code:200, message:)|                                                  insert into root.ln.wf02.wt02(timestamp, status, hardware) values (2, false, 'v2')|  SLOW_OPERATION|   127.0.0.1|    root|
|2026-05-06T14:44:11.684+08:00|root.__audit.log.node_1.u_0|  true|         OBJECT|  [WRITE_DATA]|        |           DML|Execution: insert into root.ln.wf02.wt02(timestamp, status, hardware) VALUES (3, false, 'v3'),(4, true, 'v4') cost 6 ms, with status code: TSStatus(code:200, message:)|                                  insert into root.ln.wf02.wt02(timestamp, status, hardware) VALUES (3, false, 'v3'),(4, true, 'v4')|  SLOW_OPERATION|   127.0.0.1|    root|
+-----------------------------+---------------------------+------+---------------+--------------+--------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------+----------------+------------+--------+
Total line number = 3
It costs 0.010s

Query all TTL deletion failure audit logs

IoTDB> select * from root.__audit.log.*.u_5 where audit_event_type='FAILED_TTL_DELETION' order by time desc align by device
+-----------------------------+---------------------------+------+---------------+--------------+-----------------+--------------+-----------------------------------------------------------------------------+-------------------+-------------------+---------------+----------------------+
|                         Time|                     Device|result|privilege_level|privilege_type|         database|operation_type|                                                                          log|         sql_string|   audit_event_type|   cli_hostname|              username|
+-----------------------------+---------------------------+------+---------------+--------------+-----------------+--------------+-----------------------------------------------------------------------------+-------------------+-------------------+---------------+----------------------+
|2026-07-30T10:21:14.735+08:00|root.__audit.log.node_1.u_5| false|         GLOBAL|        [null]|ttl_settle_verify|       CONTROL|TTL settle compaction failed: error=settle task did not complete successfully|settle_fail_runtime|FAILED_TTL_DELETION|127.0.0.1:10730|__internal_ttl_auditor|
+-----------------------------+---------------------------+------+---------------+--------------+-----------------+--------------+-----------------------------------------------------------------------------+-------------------+-------------------+---------------+----------------------+
Total line number = 1

Query all session timeout audit logs

IoTDB> select * from root.__audit.log.*.u_4 where audit_event_type='SESSION_TIME_EXCEEDED' order by time desc align by device
+-------------------------------+-----------------------------+--------+-----------------+----------------+----------+----------------+-----------------------------------------------------+------------+-----------------------+--------------+--------------------+
|                          Time|                       Device| result|  privilege_level|  privilege_type| database|  operation_type|                                                  log|  sql_string|       audit_event_type|  cli_hostname|            username|
+-------------------------------+-----------------------------+--------+-----------------+----------------+----------+----------------+-----------------------------------------------------+------------+-----------------------+--------------+--------------------+
| 2026-08-10T16:19:00.232+08:00|  root.__audit.log.node_1.u_4|   true|           GLOBAL|        [SYSTEM]|     null|         CONTROL|Session idle time exceeded threshold (test harness)|        null|SESSION_TIME_EXCEEDED|          null|__internal_auditor|
+-------------------------------+-----------------------------+--------+-----------------+----------------+----------+----------------+-----------------------------------------------------+------------+-----------------------+--------------+--------------------+
Total line number = 1

Query all key destruction audit logs

IoTDB> select * from root.__audit.log.*.u_4 where audit_event_type='DESTROY_KEY' order by time desc align by device
+-------------------------------+-----------------------------+--------+-----------------+----------------+----------+----------------+----------------------------------------------------------------------+------------+------------------+--------------+--------------------+
|                          Time|                       Device| result|  privilege_level|  privilege_type| database|  operation_type|                                                                   log|  sql_string|  audit_event_type|  cli_hostname|            username|
+-------------------------------+-----------------------------+--------+-----------------+----------------+----------+----------------+----------------------------------------------------------------------+------------+------------------+--------------+--------------------+
|2026-08-10T16:19:00.234+08:00|root.__audit.log.node_1.u_4|   true|           GLOBAL|      [SECURITY]|     null|         CONTROL|The encrypt key is deleted successfully in DataNode 1 (test harness)|        null|       DESTROY_KEY|          null|__internal_auditor|
+-------------------------------+-----------------------------+--------+-----------------+----------------+----------+----------------+----------------------------------------------------------------------+------------+------------------+--------------+--------------------+
Total line number = 1

Query all audit log storage full records

IoTDB> select * from root.__audit.log.*.u_4 where audit_event_type='AUDIT_STORAGE_FULL' order by time desc align by device
+-------------------------------+-----------------------------+--------+-----------------+----------------+----------+----------------+------------------------------------------------------------------------------------+------------+--------------------+--------------+--------------------+
|                          Time|                       Device| result|  privilege_level|  privilege_type| database|  operation_type|                                                                                 log|  sql_string|    audit_event_type|  cli_hostname|            username|
+-------------------------------+-----------------------------+--------+-----------------+----------------+----------+----------------+------------------------------------------------------------------------------------+------------+--------------------+--------------+--------------------+
|2026-08-10T16:19:00.235+08:00|root.__audit.log.node_1.u_4|   true|           GLOBAL|         [AUDIT]|     null|         CONTROL|Audit log storage is full, total space: 0.00 GB, max space: 0.00 GB (test harness)|        null|  AUDIT_STORAGE_FULL|          null|__internal_auditor|
+-------------------------------+-----------------------------+--------+-----------------+----------------+----------+----------------+------------------------------------------------------------------------------------+------------+--------------------+--------------+--------------------+
Total line number = 1