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_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_EXTENDED setting 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, SYSTEM tablespace. 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

• Tibero Topic Guide > Administration > Database Administration > User Management and Database Security
• Tibero SQL Reference Manual > Data Definition Language > AUDIT