Skip to main content

Search

Welcome to Tibero GTS!

How to Set Audit for Object Owner When Configuring AUDIT (General User)

Document Type | Technical Information

Category | Administration

Applicable Product Version | 7FS02PS

Document Number | TADTI092

Overview

In Tibero, through the AUDIT feature, you can audit DML (SELECT, INSERT, UPDATE, DELETE) statements and DCL (GRANT, REVOKE) statements on specified objects.

However, by default, when the object owner performs DML or DCL on their own objects, audit logs are not recorded. In such cases, if you enable the _DDL_AUDIT_OWN_OBJ parameter, you can also audit DML/DCL operations performed by the object owner on their own objects.

This document guides you on how to configure auditing to include operations performed by the object owner using this parameter.

 

Method

Objects subject to auditing include Table, View, Sequence, Package, Function, etc.

_DDL_AUDIT_OWN_OBJ is a hidden parameter that can be enabled by changing its option, and since it is a dynamic parameter, it can be set during operation.

 

How to Check the Parameter

SQL> col NAME for a20
col DFLT_VALUE for a5
col VALUE for a5
col IS_DYNAMIC for 999999999
col IS_HIDDEN for 999999999
select NAME, DFLT_VALUE, VALUE, IS_DYNAMIC, IS_HIDDEN  from vt_parameter where name='_DDL_AUDIT_OWN_OBJ';

NAME                 DFLT_ VALUE IS_DYNAMIC  IS_HIDDEN
-------------------- ----- ----- ---------- ----------
_DDL_AUDIT_OWN_OBJ   NO    YES            1          1

 

1. Create Test User and Table

Create a test user and table for performing the example.
SQL> create user TIBERO_TEST identified by 'TIBERO_TEST'; 
User 'TIBERO_TEST' created.

SQL> grant connect, resource to TIBERO_TEST;
Granted

SQL> conn TIBERO_TEST/TIBERO_TEST
Connected to Tibero.

SQL> create table T1 (col1 number, col2 varchar(100));
Table 'T1' created.

 

2. Set AUDIT in DB (Set as OS)

Check the audit setting parameters and configure audit log parameters.
Depending on the AUDIT_TRAIL parameter setting, audit logs can be stored inside the database or as OS files. (This example sets it to OS.)
SQL> show param AUDIT
NAME                                                  TYPE     VALUE
----------------------------------------------------- -------- ----------------------------------------------------
AUDIT_FILE_DEST                                       DIRNAME  /home/t723/tibero7/instance/t723/audit/
AUDIT_FILE_SIZE                                       INT32    104857600
AUDIT_LOG_TOTAL_SIZE_LIMIT                            INT64    314572800
AUDIT_SYS_OPERATIONS                                  Y_N      NO
AUDIT_TRAIL                                           STRING   NONE

SQL> conn sys
SQL> alter system set AUDIT_TRAIL=OS;
System altered.

SQL> show param AUDIT
NAME                                                  TYPE     VALUE
----------------------------------------------------- -------- ----------------------------------------------------
AUDIT_FILE_DEST                                       DIRNAME  /home/t723/tibero7/instance/t723/audit/
AUDIT_FILE_SIZE                                       INT32    104857600
AUDIT_LOG_TOTAL_SIZE_LIMIT                            INT64    314572800
AUDIT_SYS_OPERATIONS                                  Y_N      NO
AUDIT_TRAIL                                           STRING   OS

 

3. Set AUDIT on User's Table

Set AUDIT on the target object you want to audit.
SQL> AUDIT insert on TIBERO_TEST.T1 by access;
Audited.

 

4. Set _DDL_AUDIT_OWN_OBJ=N and Perform INSERT

Set the _DDL_AUDIT_OWN_OBJ parameter, which records audit logs, to N, then perform an INSERT on the table to audit.
SQL> conn TIBERO_TEST/TIBERO_TEST
Connected to Tibero.

SQL> insert into T1 values(100, 'TIBERO'); 
1 row inserted.

SQL> !cat /home/t723/tibero7/instance/t723/audit/audit.log // No AUDIT log recorded

 

5. Set _DDL_AUDIT_OWN_OBJ=Y and Perform INSERT

You can confirm that the INSERT was logged in the Audit.log.
 
SQL> conn sys
SQL> alter system set _DDL_AUDIT_OWN_OBJ=Y;
System altered.

SQL> conn TIBERO_TEST/TIBERO_TEST
Connected to Tibero.

SQL> insert into TIBERO_TEST.T1 values (200,'TMAX'); 1 row inserted.
SQL> !cat /home/t723/tibero7/instance/t723/audit/audit.log    // Log recorded

2025/10/16 15:57:01.050  96 SESS_ID:[96] SERIAL_NO:[62203] AUD_NO:[1] STMT_ID:[242122] USER_NAME:[TIBERO_TEST] USER_HOST:[127.0.0.1] OS_USER:[t723] CLIENT_ID:[] PRIV_NO:[3] ACTION:[S] OBJ_OWNER:[TIBERO_TEST] OBJ_NAME:[T1]USGMT_ID:[8] SLOTNO:[48] WRAPNO:[161] TSN:[477329] PID:[5536] SQLTEXT:[insert into TIBERO_TEST.T1 values (200,'TMAX')]

For the SYS user, auditing is done differently from other users for security reasons.

  • SYS user is excluded from auditing by default, so you cannot enable or disable auditing using AUDIT or NOAUDIT commands.
  • To audit commands by the SYS user, set the AUDIT_SYS_OPERATIONS parameter in the $TB_SID.tip file to 'Y'. When auditing of SYS user commands is enabled, all actions performed by SYS are recorded in OS files and, for security reasons, are not recorded in the database.

 

 

 

Related to