Oracle Database Security – Audit Trail for specific user and specific module application

auditoracleSecurity

I want to enable audit trail for specific user, but only for sessions when this user connect to database using one specific module applications.

For ex. I want to enable audit trail for user test1 only when user test1 is connected to database using TOAD, but not when the user test1 is connected to database using Golden6.

Is this type of audit possible?

Thanks,

Best Answer

You can do this with Unified Auditing, which is available starting with version 12.1.

Example:

create user u1 identified by u1 quota unlimited on users;
grant create session, create table to u1;
grant audit_viewer to u1;

Now create a Unified Audit policy, that audits all actions when the user is U1, and module is not Golden6:

create audit policy mypolicy1
actions all 
when 'sys_context(''userenv'', ''current_user'') = ''U1'' and sys_context(''userenv'', ''module'') != ''Golden6'''
evaluate per statement
;

SQL> audit policy mypolicy1;

Audit succeeded.

Then test it:

SQL> conn u1/u1
Connected.

SQL> select sys_context('userenv', 'current_user') as current_user, sys_context('userenv', 'module') as module from dual;

CURRENT_USER         MODULE
-------------------- --------------------
U1                   SQL*Plus

SQL> create table t1 (c1 number);

Table created.

SQL> exec dbms_application_info.set_module('Golden6', null);

PL/SQL procedure successfully completed.

SQL> create table t2 (c1 number);

Table created.

SQL> exec dbms_application_info.set_module('SQL*Plus', null);

PL/SQL procedure successfully completed.

SQL> create table t3 (c1 number);

Table created.

Now check the contents of the audit trail:

SQL> select event_timestamp, DBUSERNAME, ACTION_NAME, sql_text, RETURN_CODE from unified_audit_trail where dbusername = 'U1' and sql_text like 'create%' order by event_timestamp;

EVENT_TIMESTAMP                DBUSERNAME ACTION_NAME          SQL_TEXT                       RETURN_CODE
------------------------------ ---------- -------------------- ------------------------------ -----------
17-APR-18 02.04.29.381770 PM   U1         CREATE TABLE         create table t1 (c1 number)              0
17-APR-18 02.04.59.233856 PM   U1         CREATE TABLE         create table t3 (c1 number)              0

As you can see, the creation of table t2 statement, that was issued with the module set to Golden6, was not audited.

Related Question