5 Replies Latest reply: Jan 29, 2013 4:04 PM by 509471 RSS

    User Level Auditing in dataguard

    Meenakshy singh
      Hi Gurus,

      can anyone tell me how to do user level auditing in dataguard mode.

      1.Will auditing enable in production datbase will automatically sync in DR database?

      2.Or we need to enable user level auditing manually in DR server?

      3.How much down time is require to enable user level audit?

      4. please provide me steps by steps procedure of user level auditing in dataguard enable.
        • 1. Re: User Level Auditing in dataguard
          mseberg
          Hello;

          Assuming Oracle 11 ( Oracle 10 may have issues )
          1.Will auditing enable in production datbase will automatically sync in DR database?
          Yes.
          2.Or we need to enable user level auditing manually in DR server?
          No
          3.How much down time is require to enable user level audit?
          The time it takes to bounce a system.
          4. please provide me steps by steps procedure of user level auditing in dataguard enable.
          Steps ( On Primary )
           alter system set audit_trail = DB scope=spfile
          
          Restart database
          Common audits
          audit grant any object privilege;
          audit create session;
          audit alter user;
          audit create user;
          audit drop user;
          audit drop tablespace;
          audit grant any role;
          audit grant any privilege;
          audit alter system;
          audit alter session;
          audit delete on AUD$ by access;
          audit insert on AUD$ by access;
          audit update on AUD$ by access;
          audit delete table;
          audit create tablespace;
          audit alter database;
          audit create role;
          audit create table;
          audit alter any procedure;
          audit create view;
          audit drop any procedure;
          audit drop profile;
          audit alter profile;
          audit alter any table;
          audit create public database link;
          Note: To check what is set run: SELECT * FROM DBA_STMT_AUDIT_OPTS;

          A few checks
          By Session
          
          SELECT A.USERNAME,
              OS_USERNAME,
              A.TIMESTAMP,
              A.RETURNCODE,
              TERMINAL,
              USERHOST
              FROM DBA_AUDIT_SESSION A
          WHERE USERNAME = ?
          
          
          By Create User
          
          SELECT
            OS_USERNAME,
            USERNAME,
            USERHOST,
            OBJ_NAME,
            TIMESTAMP
          FROM
            DBA_AUDIT_OBJECT
          WHERE
            ACTION_NAME = 'CREATE USER';
          
          By Alter User
          
          SELECT
            OS_USERNAME,
            USERNAME,
            USERHOST,
            OBJ_NAME,
            TIMESTAMP
          FROM
            DBA_AUDIT_OBJECT
          WHERE
            ACTION_NAME = 'ALTER USER';
            
          
          By Drop User
          
          SELECT
            OS_USERNAME,
            USERNAME,
            USERHOST,
            OBJ_NAME,
            TIMESTAMP
          FROM
            DBA_AUDIT_OBJECT
          WHERE
            ACTION_NAME = 'DROP USER';
          
          Audit SELECT
          
          select username, priv_used, ses_actions from dba_audit_object
          where obj_name='EMP' and owner='SCOTT';
          
          How to Audit User Connection, Disconnection Date and Time 
          
          select sessionid, to_char(ntimestamp#,'DD-MON-YY:HH24:MI:SS') login,
          userid, to_char(logoff$time,'DD-MON-YY:HH24:MI:SS') logoff
          from sys.aud$ where userid='SCOTT';
          Best Regards

          mseberg
          • 2. Re: User Level Auditing in dataguard
            Meenakshy singh
            Hi All,

            My oracle version is Oracle9i Enterprise Edition Release *9.2.0.5.0* - 64bit & Oracle Database 10g Enterprise Edition Release *10.2.0.5.0* - 64bit.

            1.I have to enable user level audit logs in 9i,10g , but am not sure whether in Oracle 9i,10g it will automatically sync or not?

            2.If not then how to do manually user level auditing in oracle 9i,10g in both production & DR.

            3.If not then please provide me step by step procedure.

            4.After enable audit, any log issue or not?I mean logs will be in sync or not?


            My production database version is Oracle9i Enterprise Edition Release *9.2.0.8.0* and DR database version is Oracle9i Enterprise Edition Release *9.2.0.8.1* .
            If manually will do auditing,will version effect user level auditing or not?




            Thanks
            Meena

            Edited by: Meenakshy singh on Nov 27, 2012 2:02 PM

            Edited by: Meenakshy singh on Nov 27, 2012 2:10 PM
            • 3. Re: User Level Auditing in dataguard
              mseberg
              Hello;

              Oracle 9 and 10 are going to be a problem.

              I have these short notes on this :
              Current primary database must have "audit_trail" set to "none"
              
              audit trail parameter should be in OS or NONE when standby database configured.
              If you are using a version lower than 11 you will probably get this error if set to DB :

              ORA-16006: audit_trail destination incompatible with database open mode
              Here's my note ( to self ) on this :

              http://www.visi.com/~mseberg/audit_trail_and_data_guard.html

              So no its not going to sync, the best you can hope for is some OS audit files.

              Once you upgrade to 11 everything you want is handled by Oracle.

              Best Regards

              mseberg
              • 4. Re: User Level Auditing in dataguard
                Meenakshy singh
                Hi all,

                If i set parameter audit_trail parameter = DB in primary database and set parameter audit_trail =OS in DR database, and after 4 month will delete data from both Production and DR database.In that case what happen.. (9i,10G)

                1.can I easily delete data from DR database since audit_trail=OS and in production which is set to audit_trail =DB? or need take some precaution before deleting.

                2.or can i set audit_trail =OS in both production and DR database?Any issues?

                3.will it affect the system performance after enable audit_trail =OS?

                4.since audit_trail=OS in both production and DR ,it will create a audit file in OS level.will it create any problem while deleting these files from OS LEVEL from both production and DR datbase.
                • 5. Re: User Level Auditing in dataguard
                  509471
                  Hi Meenakshy singh,

                  Were you ever able to enable auditing on your primary 10g db without causing standby switchover problems? If so, what did you do? set audit_trail =OS in both production and DR database? or something else?

                  Thank you