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

Question

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,

• Gerard H. Pille: Would a trace do? You could activate that using a logon trigger. But watch out, if he's using Toad or not, how are you going to make sure? What if he renames the executable to Golden6? dba.stackexchange.com/questions/154937/…

Answer 1

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.

• Vnaman: I have created the policy, but after I check the contents of the audit trail 'unified_audit_trail' doesn't return any value!
• eliatou: Hi Balazs Papp, I want to do exactly the same thing but unfortunatelly we run on 11.2 database. Is there any possibility to do the same fonctionality in 11.2 Best

Answer 2

Oracle unified_audit_trail view keeps track on client_program_name of the audited actions and Toad.exe can be found there along the others. Sys_context makes it possible to make audit policy conditions that take some session parameters into consideration, but there seem to be none for the client_program_name (client_info is not the same) - wonder where the uat finds the information?

More about sys_context here: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htmpo