5 Replies Latest reply: Sep 6, 2012 10:17 AM by DBA_1976 RSS

    how to grant privileges to execute AUDIT command

    715689
      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
          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
            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
              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
                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
                  Please mark the thread answered. It will help people who are looking for the answers and make people who helped you happy.