This discussion is archived
1 Reply Latest reply: Sep 22, 2012 1:27 AM by 921598 RSS

how to exclude apps user when audit is set to OS and by selected users only

844538 Newbie
Currently Being Moderated
I have a request for to audit 5 users in our oracle apps and I have set the following:

audit_trail=OS
AUDIT_FILE_DEST=/d01/app/oracle/product/11.2.0/rdbms/audit

restarted the instance and issued command:

AUDIT SESSION BY user1, user2 , user3....

however I am getting hundreds of audit files generated by the APPS, APPLSYS, etc users.

yes I can remove these files after the fact bur why the extra IO when not needed.

HOW CAN I EXCLUDE THESE USERS?

Thanks in advance.
  • 1. Re: how to exclude apps user when audit is set to OS and by selected users only
    921598 Explorer
    Currently Being Moderated
    if you have complex Auditing requirements, you should use Fine Grained Auditing Feature using the DBMS_FGA package.

    The following code illustrates how to exclude a certain user
    connect scott/tiger

    create table mytab (col1 number, col2 varchar2(20));

    insert into mytab values (1,'world');

    grant update on mytab to public;

    execute sys.DBMS_FGA.ADD_POLICY(-
    object_schema => 'SCOTT', -
    object_name => 'MYTAB', -
    policy_name => 'mypolicy1', -
    audit_condition => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') <> ''TST'' ',-
    audit_column => 'col1', -
    enable => TRUE, -
    statement_types => 'UPDATE');

    Example:
    connect scott/tiger
    update scott.mytab set col1=col1+1;

    connect tst/tst
    update scott.mytab set col1=col1+2;
    conn / as sysdba

    select DB_USER,OBJECT_SCHEMA "SCHEMA",OBJECT_NAME, POLICY_NAME,SQL_TEXT
    from dba_fga_audit_trail;

    DB_USER SCHEMA OBJECT_NAME POLICY_NAME SQL_TEXT
    ------- ------ ----------- ----------- -----------------------------------
    SCOTT SCOTT MYTAB MYPOLICY1 update scott.mytab set col1=col1+1

    Regards
    Inam Bukhari
    dbmentors.blogspot.com

    Edited by: Inam Bukhari on Sep 22, 2012 1:27 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points