This discussion is archived
5 Replies Latest reply: Jan 29, 2013 2:04 PM by 509471 RSS

User Level Auditing in dataguard

Meenakshy singh Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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