This discussion is archived
4 Replies Latest reply: Oct 13, 2013 6:56 PM by andrewmy RSS

audit dml on few table for non application users.

Niket Kumar Pro
Currently Being Moderated

I want to audit  dml on few table for non application users.can I omit only application users from audit so that whenever new user is created we need not to add audit for user.do i have to create audit logon trigger for this which check first the application user names from table and if logged user is not application user auditing will start for it..

or is there any other way to make this more simpler and performance friendly :-)

  • 1. Re: audit dml on few table for non application users.
    sb92075 Guru
    Currently Being Moderated

    NiketKumar wrote:

     

    I want to audit  dml on few table for non application users.can I omit only application users from audit so that whenever new user is created we need not to add audit for user.do i have to create audit logon trigger for this which check first the application user names from table and if logged user is not application user auditing will start for it..

    or is there any other way to make this more simpler and performance friendly :-)

     

    issue AUDIT statement as necessary after CREATE USER is issued

  • 2. Re: audit dml on few table for non application users.
    andrewmy Journeyer
    Currently Being Moderated

    There are a few ways you can do this:

     

    a) Individually issue the audit commands for all non-application users you want to audit on the selected tables.

    AUDIT UPDATE, INSERT, DELETE ON <table_name>  BY <user_to_audit> BY ACCESS;

    You will have to repeat this command for any new non-application users that you create in the database. This method keeps the audit entries to the minimum required to satisfy your requirement but at the cost of having to issue the audit commands every time you create a new user.

     

    b) Issue the audit commands for all users on the selected tables (just filter out the application users when you generate the audit report)

    AUDIT UPDATE, INSERT, DELETE ON <table_name>  BY ACCESS;

    You don't have to repeat this for every individual user you want to audit since it covers current and all future users, but at the cost of more audit entries created.

     

    I would go (a) if there are a limited number of users you want to audit and new users in that category do not get created often and you are able to enforce the control that the audit statements must be are issued by DBA after creating the users. I would go (b) if you have hundreds of non-application users you want to audit and more are being created frequently or created outside of your control.

  • 3. Re: audit dml on few table for non application users.
    Niket Kumar Pro
    Currently Being Moderated

    Can you gie me example for this

     

    AUDIT UPDATE, INSERT, DELETE ON <table_name>  BY <user_to_audit> BY ACCESS;


    I am not able to do this for particular object and particular user.


     

     

    SQL> audit SELECT on hr.emp by scott by access;

    audit SELECT on hr.emp by scott by access

                              *

    ERROR at line 1:

    ORA-01708: ACCESS or SESSION expected

     

    I think we cannot use object and user in same audit statement...



  • 4. Re: audit dml on few table for non application users.
    andrewmy Journeyer
    Currently Being Moderated

    My bad. I committed the cardinal sin of assuming instead of testing it. If you want to audit a selected object, you cannot specify a selected user as well. You can only specify a user to audit if you audit a specific statement:

     

    a) Audit all DML on selected tables by all users

     

    SQL> audit insert, update, delete on testuser1.employees by access;

     

    Audit succeeded

     

    b) Audit DML on all tables by selected user

     

    SQL> audit insert table, update table, delete table by testuser2 by access;

     

    Audit succeeded

Legend

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