5 Replies Latest reply: Jun 23, 2010 11:43 PM by TEJAS_DBA RSS

    how to audit all grants and revoke statements?

    733414
      I tried the following and checked dba_audit_trail, but it does not seem to work. I need to audit every time someone issues a grant and a revoke statement.


      audit grant any object privilege by access;
      audit grant any privilege by access;
      audit grant any role by access;
        • 1. Re: how to audit all grants and revoke statements?
          P.Forstmann
          For Oracle 10.2:
          audit system grant;
          audit grant directory;
          audit grant procedure;
          audit grant sequence;
          audit grant table;
          audit grant type;
          See http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4007.htm#BABEFEAC
          and http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4007.htm#g2274817.
          • 2. Re: how to audit all grants and revoke statements?
            733414
            when I check dba_audit_trail I see the system audit where i issue the grant table command. Then I issue a grant table to another user. I re-query the dba_audit_trail and I do not see the grant audited.
            • 3. Re: how to audit all grants and revoke statements?
              P.Forstmann
              Please post output of:
              select * from v$version;
              show parameter audit
              and the full statement you are using to query audit trail view.

              Exemple with Oracle XE (10.2.0.1):
              SQL> select * from v$version;
              
              BANNER
              ----------------------------------------------------------------
              Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
              PL/SQL Release 10.2.0.1.0 - Production
              CORE    10.2.0.1.0      Production
              TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
              NLSRTL Version 10.2.0.1.0 - Production
              
              SQL> show parameter audit;
              
              NAME                                 TYPE        VALUE
              ------------------------------------ ----------- ------------------------------
              audit_file_dest                      string
              audit_sys_operations                 boolean     TRUE
              audit_trail                          string      DB_EXTENDED
              
              SQL> audit grant table;
              
              SQL> show user;
              USER est "SYSTEM"
              SQL> desc t;
               Nom                                       NULL ?   Type
               ----------------------------------------- -------- ----------------------------
              
               X                                                  NUMBER
              
              SQL> grant select on t to hr;
              
              Autorisation de privilèges (GRANT) acceptée.
              
              SQL> grant select on t to test;
              
              Autorisation de privilèges (GRANT) acceptée.
              
              
              SQL> select username, timestamp, sql_text 
              from dba_audit_trail
              where trunc(timestamp)=trunc(sysdate) and sql_text like '% t %';
              
              USERNAME                       TIMESTAMP
              ------------------------------ ------------
              SQL_TEXT
              --------------------------------------------------------------------------------
              
              SYSTEM                         22-jun 08:39
              grant select on t to hr
              
              SYSTEM                         22-jun 08:39
              grant select on t to test
              
              
              SQL> select sysdate from dual;
              
              SYSDATE
              ------------
              22-jun 08:40
              
              SQL>
              Edited by: P. Forstmann on 22 juin 2010 20:41
              • 4. Re: how to audit all grants and revoke statements?
                733414
                we only have DB and not db_extended. this is in 11.1.0.7. We probably need to change that.
                • 5. Re: how to audit all grants and revoke statements?
                  TEJAS_DBA
                  HI,,

                  turn on the audits on all,, like

                  audit grant system on all by session,,,,,,,,,,


                  by session auditing is good compared to access auditing as it writes only record for grant and revoke respectively..

                  first turn on auditing on the users whom u need to audit,,,

                  regards,
                  sisya....