Auditing records are only supported for persistence through log files. Currently, auditing records are written to logs by using the system logging mechanism, with each business tenant having a background thread responsible for printing auditing logs.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support the auditing feature.
Auditing log format
An example of an auditing log is as follows:
"xx.xx.xx.xx",57000,0,3615915,"",3221487702,"xx.xx.xx.xx",33281,1006,"audit_mysql",1006,500002,"admin",0,"xx.xx.xx.xx",500001,"test","B9DBED5D556A12ADAE8EB1650B9761E9","table_access"," SELECT id, k, c, pad\n FROM sbtest\n WHERE k IN (78154, 78112, 77817, 78105, 78504, 78111, 78155, 78141, 78145, 78113)\n ",324,0,10,2,0,0,0,0,0,0,0,0,0,1,0,0,1,1700012758556689,364,0,1,46,1,47,259,0,0,0,0,10,0,20,0,0,0,3,0,20,10,10,20,10,0,0,0,139264,0,0,"",0,-1,-1,0,1700012758543334815,2,0,"",-7363743333437867606,0,"","",1,0,0,"00060a27-1096-5dc2-fa04-cea1f06b019a"
"xx.xx.xx.xx",57000,0,3615914,"",3221487709,"xx.xx.xx.xx",33293,1006,"audit_mysql",1006,500002,"admin",0,"xx.xx.xx.xx",500001,"test","B9DBED5D556A12ADAE8EB1650B9761E9","table_access"," SELECT id, k, c, pad\n FROM sbtest\n WHERE k IN (34851, 34386, 34384, 34393, 34974, 34566, 34397, 34379, 33844, 34353)\n ",324,0,10,2,0,0,0,0,0,0,0,0,0,1,0,0,1,1700012758556662,417,0,1,7,1,37,362,0,0,0,0,10,0,20,0,0,0,3,0,20,10,10,20,10,0,0,0,131072,0,0,"",0,-1,-1,0,1700012758543334815,2,0,"",-7363743333437867606,0,"","",1,0,0,"00060a27-1096-5dc2-131b-e7f9c98dd9d9"
Log field description
The fields in auditing records are extracted from the existing [G]V$OB_SQL_AUDIT view, but there are some differences:
- The
EVENT_CLASSfield is added to indicate the type of auditing event. Its values match those defined in the filter, includingconnection,table_access,general. - Only user session requests are recorded. Internal SQL logs are not recorded.
- SQL queries are converted to
utf8and escaped before they are output. Logon and logoff events are recorded asLOGON/LOGOFF. - For excessively long SQL statements, memory is allocated using the current thread’s Allocator during asynchronous log printing, with a fallback to synchronous printing if needed.
The following table describes the specific fields of the auditing log. The field types only affect the printing format. Only string and integer types are supported, and string types are enclosed in quotation marks.
| Field | Type | Description |
|---|---|---|
| SVR_IP | varchar(46) | IP address. |
| SVR_PORT | bigint(20) | Port number. |
| REQUEST_ID | bigint(20) | Request ID. |
| SQL_EXEC_ID | bigint(20) | The execution ID, which is a monotonically increasing number on a single node. |
| TRACE_ID | varchar(128) | The trace ID of the statement. |
| SID | bigint(20) unsigned | Session connection ID. |
| CLIENT_IP | varchar(46) | |
| CLIENT_PORT | bigint(20) | The port number of the client that sends the request. |
| TENANT_ID | bigint(20) | The ID of the tenant that sends the request. |
| TENANT_NAME | varchar(64) | The name of the tenant that sends the request. |
| EFFECTIVE_TENANT_ID | bigint(20) | The ID of the tenant. |
| USER_ID | bigint(20) | The ID of the user that sends the request. |
| USER_NAME | varchar(64) | The name of the user that sends the request. |
| USER_GROUP | bigint(20) | The ID of the resource group to which the user belongs. |
| USER_CLIENT_IP | varchar(46) | The IP address of the client that sends the request. |
| EVENT_CLASS | varchar(128) | The event type, which is used to distinguish non-SQL statements. |
| DB_ID | bigint(20) unsigned | The ID of the database. |
| DB_NAME | varchar(128) | The name of the database. |
| SQL_ID | varchar(32) | The ID of the SQL statement. |
| QUERY_SQL | longtext | The actual SQL statement. |
| PLAN_ID | bigint(20) | The ID of the execution plan. |
| AFFECTED_ROWS | bigint(20) | The number of affected rows. |
| RETURN_ROWS | bigint(20) | The number of returned rows. |
| PARTITION_CNT | bigint(20) | The number of partitions involved in the request. |
| RET_CODE | bigint(20) | The execution result return code: |
| QC_ID | bigint(20) unsigned | The scheduler ID in a parallel execution scenario. |
| DFO_ID | bigint(20) | The ID of the current sub-plan in a parallel execution scenario. |
| SQC_ID | bigint(20) | The ID of the local coordinator in a parallel execution scenario. |
| WORKER_ID | bigint(20) | The ID of the worker thread in a parallel execution scenario. |
| EVENT | varchar(64) | The name of the longest waiting event. |
| P1TEXT | varchar(64) | Parameter 1 of the waiting event. |
| P1 | bigint(20) unsigned | The value of parameter 1 of the waiting event. |
| P2TEXT | varchar(64) | Parameter 2 of the waiting event. |
| P2 | bigint(20) unsigned | The value of parameter 2 of the waiting event. |
| P3TEXT | varchar(64) | Parameter 3 of the waiting event. |
| P3 | bigint(20) unsigned | The value of parameter 3 of the waiting event. |
| LEVEL | bigint(20) | The level of the waiting event. |
| WAIT_CLASS_ID | bigint(20) | The ID of the class to which the waiting event belongs. |
| WAIT_CLASS# | bigint(20) | The index of the class to which the waiting event belongs. |
| WAIT_CLASS | varchar(64) | The name of the class to which the waiting event belongs. |
| STATE | varchar(19) | The state of the waiting event. |
| WAIT_TIME_MICRO | bigint(20) | The time the waiting event waited, in microseconds. |
| TOTAL_WAIT_TIME_MICRO | bigint(20) | The total time of all waits during execution, in microseconds. |
| TOTAL_WAITS | bigint(20) | The total number of waits during execution. |
| RPC_COUNT | bigint(20) | The number of RPCs sent. |
| PLAN_TYPE | bigint(20) | The type of the execution plan: |
| IS_INNER_SQL | tinyint(4) | Indicates whether the request is an internal SQL request. |
| IS_EXECUTOR_RPC | tinyint(4) | Indicates whether the current request is an RPC request. |
| IS_HIT_PLAN | tinyint(4) | Indicates whether the plan cache is hit. |
| REQUEST_TIME | bigint(20) | The start time of the request, in microseconds. |
| ELAPSED_TIME | bigint(20) | The total time consumed from receiving the request to executing it, in microseconds. |
| NET_TIME | bigint(20) | The time from sending an RPC to receiving the request, in microseconds. |
| NET_WAIT_TIME | bigint(20) | The time from receiving the request to entering the queue, in microseconds. |
| QUEUE_TIME | bigint(20) | The time the request spent waiting in the queue, in microseconds. |
| DECODE_TIME | bigint(20) | The time after the request is dequeued for decoding, in microseconds. |
| GET_PLAN_TIME | bigint(20) | The time from the start of processing to obtaining the plan, in microseconds. |
| EXECUTE_TIME | bigint(20) | The time consumed for plan execution, in microseconds. |
| APPLICATION_WAIT_TIME | bigint(20) unsigned | The total time of all application events, in microseconds. |
| CONCURRENCY_WAIT_TIME | bigint(20) unsigned | The total time of all concurrency events, in microseconds. |
| USER_IO_WAIT_TIME | bigint(20) unsigned | The total time of all user_io events, in microseconds. |
| SCHEDULE_TIME | bigint(20) unsigned | The time of all schedule events, in microseconds. |
| ROW_CACHE_HIT | bigint(20) | The number of row cache hits. |
| BLOOM_FILTER_CACHE_HIT | bigint(20) | The number of Bloom filter cache hits. |
| BLOCK_CACHE_HIT | bigint(20) | The number of block cache hits. |
| DISK_READS | bigint(20) | The number of physical reads. |
| RETRY_CNT | bigint(20) | The number of retries. |
| TABLE_SCAN | tinyint(4) | Indicates whether the request contains a full table scan. |
| CONSISTENCY_LEVEL | bigint(20) | The consistency level. Valid values:
|
| MEMSTORE_READ_ROW_COUNT | bigint(20) | The number of rows read from MemStore. |
| SSSTORE_READ_ROW_COUNT | bigint(20) | The number of rows read from SSStore. |
| DATA_BLOCK_READ_CNT | bigint(20) | The number of data microblocks accessed. |
| DATA_BLOCK_CACHE_HIT | bigint(20) | The number of data microblock cache hits. |
| INDEX_BLOCK_READ_CNT | bigint(20) | The number of intermediate-layer microblocks accessed. |
| INDEX_BLOCK_CACHE_HIT | bigint(20) | The number of intermediate-layer microblock cache hits. |
| BLOCKSCAN_BLOCK_CNT | bigint(20) | The number of data microblocks scanned on one side. |
| BLOCKSCAN_ROW_CNT | bigint(20) | The number of data rows scanned on one side. |
| PUSHDOWN_STORAGE_FILTER_ROW_CNT | bigint(20) | The number of rows after the storage filter is pushed down. |
| REQUEST_MEMORY_USED | bigint(20) | The memory consumed by the request. |
| EXPECTED_WORKER_COUNT | bigint(20) | The expected number of worker threads. |
| USED_WORKER_COUNT | bigint(20) | The actual number of worker threads used. |
| SCHED_INFO | varchar(16384) | The scheduling information of the request. |
| FUSE_ROW_CACHE_HIT | bigint(20) | This field is not supported. The field is set to NULL by default. |
| PS_CLIENT_STMT_ID | bigint(20) | The Prepare ID of the request:
|
| PS_INNER_STMT_ID | bigint(20) | The Prepare ID of the request:
|
| TX_ID | bigint(20) | The hash value of the transaction corresponding to the request. |
| SNAPSHOT_VERSION | bigint(20)unsigned | The snapshot read version number of the current statement. |
| REQUEST_TYPE | bigint(20) | The type of the request:
|
| IS_BATCHED_MULTI_STMT | tinyint(4) | Indicates whether the batch multi-statement optimization is performed. |
| OB_TRACE_INFO | varchar(4096) | The trace information set by the user. |
| PLAN_HASH | bigint(20) unsigned | The hash value of the execution plan. |
| LOCK_FOR_READ_TIME | bigint(20) | The time spent waiting for a lock when reading data, in microseconds. |
| PARAMS_VALUE | longtext | The parameter values during PS execution. |
| FLT_TRACE_ID | varchar(1024) | The trace ID for full-link tracing of the record. If this field is empty, the record is not monitored by full-link tracing. This field is a UUID, which is different from a trace. The format is similar to: 000600d6-a5de-038c-6c80-df07e4e79149. |