Document Type | Technical Information
Category | Installation
Applicable Product Version | 7FS02PS
Document Number | TADTI226
Overview
Method for configuring Audit functionality to record Tibero DB account (user) access and executed query logs, along with considerations during operation.
Method
1. Key Audit Parameter Settings
The parameters below can be applied dynamically without restarting the DB .
-
AUDIT_TRAIL=DB_EXTENDED-
Audit records are stored inside the DB (
SYS.AUD$table), including detailed logging of executed SQL statements and bind variable values. - If set to "AUDIT_TRAIL=OS" logs can be saved as files on the OS.
-
Audit records are stored inside the DB (
-
AUDIT_SYS_OPERATIONS=Y- Includes all activities performed with administrator privileges (SYSDBA, SYSOPER) in the audit scope.
2. Audit Configuration and Query Examples (based on tibero account)
To record each executed individual query(DML, SELECT, etc.)every time, the optionBY ACCESS must be used.It is required.
Audit Policy Setup:
-- 1. Record connection and disconnection of tibero user
AUDIT SESSION BY tibero;
-- 2. Detailed record of every operation (DML, DDL, SELECT) on tibero.t1 table by each access
AUDIT ALL ON tibero.t1 BY ACCESS;Check Audit Logs (verify SQL_TEXT):
SELECT USERNAME, TIMESTAMP, ACTION, SQL_TEXT
FROM DBA_AUDIT_TRAIL
WHERE USERNAME = 'TIBERO'
ORDER BY TIMESTAMP DESC;
3. Key Precautions When Configuring
โ Enable audit for objects owned by self (_DDL_AUDIT_OWN_OBJ) By default, operations performed by the table owner on their own objects may not be recorded. To monitor all direct actions by the owner, set the following parameter additionally.
ALTER SYSTEM SET _DDL_AUDIT_OWN_OBJ=Y;
โก Performance overhead and storage management
-
Performance degradation:
DB_EXTENDEDsetting records the full SQL text for every query, which can cause I/O and CPU load in environments with many transactions. -
SYSTEM tablespace management: Audit logs accumulate in the DB_EXTENDED option case,
SYSTEMtablespace. A rapid increase in logs may cause tablespace shortage, so periodic backup and deletion policies forAUDIT should be considered.
4. Recommended Application Guide
Initially, rather than monitoring all targets, it is recommended to specify specific users or critical tables important for security first, BY ACCESS option and apply it while monitoring system load and gradually expanding targets.
The above guide describes only basic functions and Audit settings can be adjusted using various options.
Note