This discussion is archived
2 Replies Latest reply: Sep 7, 2012 4:22 AM by 200386 RSS

Oracle 11g - AUDIT - XML, Extended

200386 Newbie
Currently Being Moderated
Hi
I am trying to Set up the Audit- XML, EXTENDED Based.

---------------
REQUIREMENT
-----------------
I want to TURN ON Default Statement level Auditing which Oracle 11g feature provide as below for all the USERS + SYS + SYSTEM under the given Database. I should be able to see the XML files as well under the SQL_TEXT Column in V$XML_AUDIT_TRAIL for WHENEVER SUCCESSFUL and WHENEVER NOT SUCCESSFUL.

Auditing Options | Alter any procedure     |Create any library |Drop any table |Alter any table|Create any procedure|Drop profile|
Alter database     |Create any table|Drop user|Alter profile|Create external job     |Exempt access policy|Alter system
Create public database link|Grant any object privilege|Alter user|Create session|Grant any privilege|Audit system|Create user
Grant any role|Create any job|Drop any procedure

-----------------------------------------------------
----STEPS TO TURN ON AUDIT UNDER 11G----
-----------------------------------------------------

I did following Step

1. ALTER SYSTEM SET audit_trail=XML,EXTENDED SCOPE=SPFILE;
2. shutdown immediate;
3. STARTUP;

4. show parameter audit;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      C:\APP\JRUGHANI\ADMIN\ABG\ADUMP
audit_sys_operations             boolean    TRUE
audit_trail                             string      XML, EXTENDED

5.Setup Audit Trail through Initialization Parameter
Ran the $ORACLE_HOME/rdbms/admin/cataudit.sql script while connected as SYS.

6. I ran the following Commands
SQL>  SELECT FROM dba_stmt_audit_opts ;*

USER_NAME                      PROXY_NAME            AUDIT_OPTION                                   SUCCESS    FAILURE
EXEMPT ACCESS POLICY                     BY ACCESS  BY ACCESS
SQL> SELECT from dba_priv_audit_opts;*

USER_NAME                      PROXY_NAME            PRIVILEGE                                         SUCCESS    FAILURE
EXEMPT ACCESS POLICY                     BY ACCESS  BY ACCESS

---------------------
Question -
---------------------

When I ran the DDL / CREATE PROC/ CREATE VIEWS from SCHEMA - JRUGHANI
1. create table JRUGHANI.audit_test_new (id1 integer)
2. alter table JRUGHANI.audit_test_new add id2 integer;
3. drop table JRUGHANI.audit_test_new;

I neither see details under XML files nor under SQL_TXT Column in V$XML_AUDIT_TRAIL table. Only thing I see is Logon and Logoff sessions by user/sys/system under XML files and V$XML_AUDIT_TRAIL table

Do I need to run more commands to make the DDL/SQL COMMANDS logged under XML files and SQL_TXT- V$XML_AUDIT_TRAIL table ?

Thanks
Jitesh.
  • 1. Re: Oracle 11g - AUDIT - XML, Extended
    KuljeetPalSingh Guru
    Currently Being Moderated
    Do I need to run more commands to make the DDL/SQL COMMANDS logged under XML files and SQL_TXT- V$XML_AUDIT_TRAIL table ?
    enable statement level auditing to capture create/alter/drop event.
    like
    audit create table by JRUGHANI;
    audit alter table by JRUGHANI;

    and check record in V$XML_AUDIT_TRAIL

    http://docs.oracle.com/cd/B19306_01/network.102/b14266/cfgaudit.htm#i1007245
  • 2. Re: Oracle 11g - AUDIT - XML, Extended
    200386 Newbie
    Currently Being Moderated
    Kuljeet

    Why I need to run the following syntax on individual schema as below ?

    audit create table by JRUGHANI;
    audit alter table by JRUGHANI;

    As per Oracle 11g

    http://www.oracle.com/technetwork/articles/sql/11g-security-100258.html

    In Oracle Database 11g, two simple changes have been made to provide an even more powerful auditing solution. First, the database parameter audit_trail is now set to DB by default, not NONE, as it was in previous versions. This allows you to turn on auditing on any object, statement, or privilege without recycling the database.

    The second change is more statements have been placed under audit by default. Here is the list:+

    ALTER SYSTEM
    SYSTEM AUDIT
    CREATE SESSION
    CREATE USER
    ALTER USER
    DROP USER
    ROLE
    CREATE ANY TABLE
    ALTER ANY TABLE
    DROP ANY TABLE
    CREATE PUBLIC DATABASE LINK
    GRANT ANY ROLE
    ALTER DATABASE
    CREATE ANY PROCEDURE
    ALTER ANY PROCEDURE
    DROP ANY PROCEDURE
    ALTER PROFILE
    DROP PROFILE
    GRANT ANY PRIVILEGE
    CREATE ANY LIBRARY
    EXEMPT ACCESS POLICY
    GRANT ANY OBJECT PRIVILEGE
    CREATE ANY JOB
    CREATE EXTERNAL JOB

    My understanding is once I implement the default audit, auditing will be taken care for all the schema's_ under the Database. So if new schema request comes I need not worry about allocating the special grants.

    Thanks
    Jitesh

Legend

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