Skip to main content
๐Ÿ““ Tibero Online Manual Click here ๐Ÿ““

Search

Welcome to Tibero GTS!

DBMS Audit Types (Schema Object Privilege)

Document Type | Technical Information

Category | Monitoring/Inspection

Document Number | TMOTI039

 

Overview

The DBMS Audit feature supports DBAs in monitoring the operation history within the database and auditing specific activities to collect data.
Tibero DBMS Audit types support auditing of system privileges(System Privilege) and schema object privileges (Schema Object Privilege).
 
This document introduces auditing of schema objects (Schema Object Privilege Audit), which records the operation history on specific tables or objects.
 
 

Method

Audit of Schema Objects (Schema Object Privilege Audit)

You can audit DML (select, insert, delete, update) statements and DCL (revoke, grant) statements on specified objects.
The objects subject to audit include Table, View, Sequence, Package, Function, etc.
  • Usage Syntax
AUDIT statement 
ON { [ schema. ] object | DEFAULT  } 
[ BY  { SESSION | ACCESS } ] 
[ WHENEVER [NOT] SUCCESSFUL ]
  • statement : SQL statement to audit
  • ON { [ schema. ] object | DEFAULT }
    - [ schema. ] object : Object to audit
  • BY [SESSION | ACCESS]
    - BY SESSION : One audit record is created for the same type of SQL statement.
    - BY ACCESS : A record is created every time the audited statement is executed, even if the same statement is repeated.
    - If omitted, BY SESSION is applied.
  • WHENEVER
    - SUCCESSFUL : Records are created when the SQL statement executes successfully.
    - NOT SUCCESSFUL : Records are created when the specified SQL statement fails.
    - If omitted, both successful and failed executions are recorded.
  • Example of Auditing a Specific Object
    The following example audits insert operations performed by the AUD User on the AUDIT_TB TABLE.
 
1. Check the target Table for testing
$ tbsql sys/tibero 

SQL> select owner, object_name, object_type from dba_objects where owner='AUD'; 
 
OWNER        OBJECT_NAME     OBJECT_TYPE 
----------   --------------  -------------------- 
AUD           AUDIT_TB        TABLE 
 
1 row selected.

2. Perform AUDIT
SQL> AUDIT insert 
      on AUD.AUDIT_TB 
      by access; 
 
Audited.

3. Check the applied AUDIT
SQL> select OWNER, OBJECT_NAME, OBJECT_TYPE, INS  from DBA_OBJ_AUDIT_OPTS; 
 
OWNER        OBJECT_NAME     OBJECT_TYPE   INS 
----------   --------------  ------------- ------- 
AUD          AUDIT_TB        TABLE          A/A 
 
1 row selected.

4. Create TEST USER
SQL> create user test identified by test; 
 
User 'TEST' created. 
 
 
SQL> grant connect, resource to test; 
 
Granted. 
 
 
SQL> grant insert on AUD.AUDIT_TB to test; 
 
Granted

5. Test the applied Audit
SQL> conn test/test 
Connected to Tibero. 
 
SQL> insert into AUD.AUDIT_TB values ( 1, 'TMAXDATA'); 
 
1 row inserted.

6. Check the Audit Log
When AUDIT_TRAIL = DB_EXTENDED is set, how to check using View
SQL> select USERNAME, USERHOST, TIMESTAMP, OWNER, OBJ_NAME, SQL_TEXT 
   from DBA_AUDIT_TRAIL; 
 
USERNAME   USERHOST              TIMESTAMP           OWNER      OBJ_NAME    SQL_TEXT
---------- --------------------- ------------------- ---------- ----------  ----------------------------------------------------------
TEST       127.0.0.1             2014-08-19:10:51:22 AUD        AUDIT_TB    insert into AUD.AUDIT_TB values ( 1, 'TMAXDATA') 
 
1 row selected.
 
When AUDIT_TRAIL = OS is set, how to check the LOG
$ cat audit.log 
 
2014/08/19 11:00:57.708 
SESS_ID:[21] SERAIL_NO:[39856] AUD_NO:[3] STMT_ID:[1608] USER_NAME:[TEST] 
USER_HOST:[127.0.0.1] OS_USER:[tibero] CLIENT_ID:[tbsql] PRIV_NO:[3] ACTION:[S] 
OBJ_OWNER:[AUD] OBJ_NAME:[AUDIT_TB] USGMT_ID:[10] SLOTNO:[19] WRAPNO:[32] 
TSN:[158353] PID:[2845] SQLTEXT:[insert into AUD.AUDIT_TB values (1, 'TMAXDATA')]
Note
General users must have insert privileges on [schema. object].
 
 
 

Related to