1

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,

1

2 Answers 2

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.

2
  • I have created the policy, but after I check the contents of the audit trail 'unified_audit_trail' doesn't return any value!
    – Vnaman
    Commented Apr 18, 2018 at 13:05
  • 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
    – eliatou
    Commented Aug 21, 2018 at 8:54
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.