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

    Auditing 11g help needed

    oracleRaj

      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

          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

            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

              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

                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