This discussion is archived
5 Replies Latest reply: Sep 6, 2012 8:17 AM by DBA_1976 RSS

how to grant privileges to execute AUDIT command

715689 Newbie
Currently Being Moderated
Hi All,

I need to find whether SYSDBA user can grant 'AUDIT' command to any other user that has no DBA role granted. so that other user should be able to execute commands like 'audit select on user.inventory_part_view'.

If it is possible, please let me know what is the correct command syntax that can be used.

I am using Oracle 11g standard edition. (Not Enterprise Edition)

Any expert ideas are highly appriciated as this is a an urgent issue.

Regards,
Nipuna

Edited by: nipuna86 on Jun 7, 2012 3:37 AM
  • 1. Re: how to grant privileges to execute AUDIT command
    BSalesRashid Explorer
    Currently Being Moderated
    Hi!

    First i checked if exists any privilege that mention 'AUDIT':

    17:36:16 system@atrac1> select * from dba_sys_privs where privilege like '%AUDIT%';

    GRANTEE PRIVILEGE ADMIN_OPTION
    ------------------------------ ---------------------------------------- ------------
    SYS AUDIT SYSTEM NO
    DBA AUDIT ANY YES

    Then i gave the privilege 'audit any' to user BRUNO :

    17:38:54 system@atrac1> grant AUDIT ANY to BRUNO;

    And after this i connected with user BRUNO and tried a command to audit a random table:

    17:39:48 bruno@atrac1> audit select on brunos.teste;

    Audit succeeded

    I hope this answer your question.



    Regards,
  • 2. Re: how to grant privileges to execute AUDIT command
    715689 Newbie
    Currently Being Moderated
    Thank you very much for the response. and it was helpful.

    I have one more thing to get clarified.
    I used the below command to enable auditing on various database views in our database.
    "audit select on customer_order_line by access;"
    Is there a way that we can find on what database views that we have enabled auditing?
    I am asking this since i cannot remember exactly on what views that i have enabled auditing currently.

    Best Regards,
    Nipuna86

    Edited by: nipuna86 on Jun 10, 2012 4:46 AM
  • 3. Re: how to grant privileges to execute AUDIT command
    IBarr Explorer
    Currently Being Moderated
    You can use the following command to see what is being audited:

    select * from dba_obj_audit_opts where object_type='VIEW';

    Hope that helps,

    Regards,

    Iain Barr
    Ategrity Solutions Ltd
  • 4. Re: how to grant privileges to execute AUDIT command
    715689 Newbie
    Currently Being Moderated
    Hi,

    Thank you very much for your kind reply. It was really helpful and I could find all the views that I have enabled auditing.

    I have came across another problem when accumulating auditing records in to the SYS.AUD$. That is I could observe some large number(nearly 500) of records being inserted to this table that are not really executed by a real user. The OS_USERNAME is recorded as the SYSTEM, while USERHOST is the host machine where we have installed the database. The time-stamp for all those records are almost the same.

    Highly appreciate if you guys can spare little time to share your ideas. I simply want to track what data is being viewed by which user. I could audit the users that I want, but at the same time these un-relevant data are also there in the SYS.AUD$ . Here I am mostly worried about the performance hit that can cause by this large number of unwanted records specially when creating auditing reports.

    Highly appreciated all of your cooperation. :)

    Best Regards,
    Nipuna86
  • 5. Re: how to grant privileges to execute AUDIT command
    DBA_1976 Explorer
    Currently Being Moderated
    Please mark the thread answered. It will help people who are looking for the answers and make people who helped you happy.

Legend

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