Skip to main content

Search

Welcome to Tibero GTS!

DBMS Audit Types (System Privilege)

Document Type | Technical Information

Category | Monitoring/Inspection

Document Number | TMOTI038


Overview

The DBMS Audit feature supports DBAs in monitoring work history within the database and auditing specific activities to collect data.
Tibero DBMS Audit types support auditing for system privileges(System Privilege) and schema object privileges (Schema Object Privilege).
 
This document introduces system privilege audit (System Privilege Audit), which records whether a user has used specific privileges.
 
 

Method

System Privilege Audit (System Privilege Audit)

Auditing system privileges means auditing system privileges. Users granted system privileges use those privileges, and all statements are audited and saved in the Audit Trail.
  • Usage Syntax
AUDIT  System_Privileges 
BY USER 
[BY SESSION | ACCESS]  
[WHENEVER [NOT] SUCCESSFUL ]
  • System_Privileges : The system privileges to audit.
  • BY USER : Specifies auditing for the designated user. (If omitted, audits all users)
  • BY [SESSION | ACCESS]
    - BY SESSION : Creates one audit record for the same type of SQL statement.
    - BY ACCESS : Records each time the audited statement is executed, even if the same statement is repeated.
    - If omitted, BY SESSION is applied.
  • WHENEVER
    - SUCCESSFUL : Records when the SQL statement is successfully executed.
    - NOT SUCCESSFUL : Records when the specified SQL statement fails.
    - If omitted, both success and failure of SQL statements are recorded.
  • Example of auditing Create Table privilege
    The following is an example where the AUD user is audited when successfully creating a table.
 
1. Create Sample User
$ tbsql sys/tibero 
 
SQL> create user AUD identified by AUD; 
User 'AUD' created. 
 
SQL> grant CONNECT, RESOURCE to AUD; 
Granted.

2. Apply and check AUDIT
SQL> AUDIT create table 
      by AUD  
      by access 
      whenever successful; 
 Audited. 
 
SQL> select * from DBA_PRIV_AUDIT_OPTS 
 where  user_name ='AUD'; 
 
USER_NAME  PROXY_NAME PRIVILEGE       SUCCESS    FAILURE 
---------- ---------- ------------    ---------- ---------- 
AUD                   CREATE TABLE    BY ACCESS  NOT SET 
 
1 row selected.

3. Audit TEST applied
SQL> conn AUD/AUD  
Connected to Tibero. 
 
SQL> create table AUDIT_TB ( num number, name varchar2(10)); 
 
Table 'AUDIT_TB' created.

4. Check Audit Log
How to check View when AUDIT_TRAIL = DB_EXTENDED is set
SQL > select USERNAME, USERHOST, TIMESTAMP, OWNER, OBJ_NAME, SQL_TEXT 
    from user_audit_trail; 
 
USERNAME   USERHOST         TIMESTAMP           OWNER      OBJ_NAME          SQL_TEXT 
---------- -------------    ------------------- ---------- ----------------  --------------------------------------------------------
AUD        127.0.0.1        2014-08-18:16:23:26  AUD       AUDIT_TB          create table AUDIT_TB ( num number, name varchar2(10)) 
 
1 row selected.

How to check LOG when AUDIT_TRAIL = OS is set
$ cat audit.log 
 
2014/08/18 15:42:07.341 
SESS_ID:[19] SERAIL_NO:[14242] AUD_NO:[2] STMT_ID:[0] USER_NAME:[AUD] 
USER_HOST:[127.0.0.1] OS_USER:[tibero] CLIENT_ID:[tbsql] PRIV_NO:[-489] ACTION:[S] 
OBJ_OWNER:[AUD] OBJ_NAME:[AUDIT_TB] USGMT_ID:[8] SLOTNO:[32] WRAPNO:[29] TSN:[145806] 
PID:[2845] SQLTEXT:[create table AUDIT_TB ( num number, name varchar2(10))]

Note

A regular user needs the AUDIT SYSTEM system privilege to audit system privileges.
$ tbsql AUD/AUD 
 
SQL> AUDIT create table by AUD; 
TBR-17004: Permission denied. 
 
SQL> conn sys/tibero 
Connected to Tibero. 
 
SQL> grant AUDIT SYSTEM to AUD; 
 
Granted. 
 
SQL> conn AUD/AUD 
Connected to Tibero. 
 
SQL> AUDIT create table by AUD; 
 
Audited.

 

 

 

Related to