4 Replies Latest reply: Dec 28, 2013 12:26 PM by Shivendra Narain Nirala RSS

Auditing 11g help needed

oracleRaj Newbie
Currently Being Moderated

Dear Gurus,


I need help in configuring AUDITING. My Oracle version is 11.2.0.1.0.

 

System Background .. We are using database level users there are around 200 users one users is ERP and other database users are end users.

We have given SELECT / INSERT / UPDATE / DELETE rights to users on ERP schema.

We are using forms 6i based application, users connect with database level user id and password, they perform actions according to their privileges.

 

We want to AUDIT database users (end users).

 

I have already done but could not get my desired result.

----------------------------------------------------------------------------

SQL> show parameter audit

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

audit_file_dest                      string      D:\APP\ADMINISTRATOR\ADMIN\ERP11G\ADUMP

audit_sys_operations                 boolean     FALSE

audit_trail                          string      XML, EXTENDED

-----------------------------------------------------------------------------

SQL> select * from dba_stmt_audit_opts;

 

USER_NAME                      PROXY_NAME                     AUDIT_OPTION                             SUCCESS    FAILURE

------------------------------ ------------------------------ ---------------------------------------- ---------- ----------

                                                              ALTER SYSTEM                             BY ACCESS  BY ACCESS

                                                              SYSTEM AUDIT                             BY ACCESS  BY ACCESS

                                                              CREATE SESSION                           BY ACCESS  BY ACCESS

                                                              CREATE USER                              BY ACCESS  BY ACCESS

                                                              ALTER USER                               BY ACCESS  BY ACCESS

                                                              DROP USER                                BY ACCESS  BY ACCESS

                                                              PUBLIC SYNONYM                           BY ACCESS  BY ACCESS

                                                              DATABASE LINK                            BY ACCESS  BY ACCESS

                                                              ROLE                                     BY ACCESS  BY ACCESS

                                                              PROFILE                                  BY ACCESS  BY ACCESS

                                                              CREATE ANY TABLE                         BY ACCESS  BY ACCESS

 

USER_NAME                      PROXY_NAME                     AUDIT_OPTION                             SUCCESS    FAILURE

------------------------------ ------------------------------ ---------------------------------------- ---------- ----------

                                                              ALTER ANY TABLE                          BY ACCESS  BY ACCESS

                                                              DROP ANY TABLE                           BY ACCESS  BY ACCESS

ERP                                                       INSERT TABLE                             BY ACCESS  BY ACCESS

ERP                                                       UPDATE TABLE                             BY ACCESS  BY ACCESS

ERP                                                       DELETE TABLE                             BY ACCESS  BY ACCESS

                                                              CREATE PUBLIC DATABASE LINK              BY ACCESS  BY ACCESS

                                                              GRANT ANY ROLE                           BY ACCESS  BY ACCESS

                                                              SYSTEM GRANT                             BY ACCESS  BY ACCESS

                                                              ALTER DATABASE                           BY ACCESS  BY ACCESS

                                                              CREATE ANY PROCEDURE                     BY ACCESS  BY ACCESS

                                                              ALTER ANY PROCEDURE                      BY ACCESS  BY ACCESS

 

USER_NAME                      PROXY_NAME                     AUDIT_OPTION                             SUCCESS    FAILURE

------------------------------ ------------------------------ ---------------------------------------- ---------- ----------

                                                              DROP ANY PROCEDURE                       BY ACCESS  BY ACCESS

                                                              ALTER PROFILE                            BY ACCESS  BY ACCESS

                                                              DROP PROFILE                             BY ACCESS  BY ACCESS

                                                              GRANT ANY PRIVILEGE                      BY ACCESS  BY ACCESS

                                                              CREATE ANY LIBRARY                       BY ACCESS  BY ACCESS

                                                              EXEMPT ACCESS POLICY                     BY ACCESS  BY ACCESS

                                                              GRANT ANY OBJECT PRIVILEGE               BY ACCESS  BY ACCESS

                                                              CREATE ANY JOB                           BY ACCESS  BY ACCESS

                                                              CREATE EXTERNAL JOB                      BY ACCESS  BY ACCESS

 

31 rows selected.

 

-----------------------------------------------------------------------------------------------------------

When I check the auditing with below query, I can not find DML statements of end users.

select db_user, os_user, os_host, TO_CHAR(EXTENDED_TIMESTAMP,'fmDd-MM-YYYY HH:MI:SS AM'), sql_text, sql_bind

from v$xml_audit_trail

------------------------

Can anybody help me what I am missing ? What should I need to do to audit all users DML statements ?

  • 1. Re: Auditing 11g help needed
    Girish Sharma Guru
    Currently Being Moderated

    I think you have a schema ERP, you have given required privileges to different db users and now wish to audit of those users for ERP schema right ?  If this is then setting at FGA at schema level is your answer, see demo at below link :

    Oracle help at your desk....:-): Setting FGA at schema level

     

    Other way is create a spool file by something like this :

     

    select 'audit all on '||owner||'.'||object_name||' by access' from all_objects

    where owner='ERP' and object_type='TABLE'

    /

     

    and now just execute the above spooled sql. (by removing unwanted lines!)

     

    Regards

    Girish Sharma

  • 2. Re: Auditing 11g help needed
    oracleRaj Newbie
    Currently Being Moderated

    Dear Girish,

     

    Thanks I have tried this, but when I query the v$xml_audit_trail, I can not find much detail. I need SQL_Text and SQL_Bind values, which are updated from front end application.


    In addition, I can find that values updated from SQL*PLUS or SQL Developer are audited but front end application (forms 6i) values are not captured.

  • 3. Re: Auditing 11g help needed
    Girish Sharma Guru
    Currently Being Moderated

    Two questions are there in your original question :

    1.>I can not find DML statements of end users.

    and now,

    2.>I need SQL_Text and SQL_Bind values, which are updated from front end application.

     

    So, for the first part, I have replied above.  If that has been resolved then now, second :

    You should query v$bind_variable_capture.

    V$SQL_BIND_CAPTURE displays information on bind variables used by SQL cursors. Each row in the view contains information for one bind variable defined in a cursor.

    V$SQL_BIND_CAPTURE


    A demo at https://community.oracle.com/thread/665781 thread.


    Regards

    Girish Sharma

  • 4. Re: Auditing 11g help needed
    Shivendra Narain Nirala Journeyer
    Currently Being Moderated

    Hi

     

    If you have enabled auditing and using sys.aud$ table , then you need to run cataudit.sql as well . Without running this file, many views don't populate and please be sure that

    you have enough space at audit_file_dest location and continuously watch table size of sys.aud$ , because it grows too fast .

     

    Regards

    Shivendra Narain Nirala

Legend

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