1 Reply Latest reply on Jun 30, 2014 3:06 PM by JudWilliford

    Problem With Auditing

    user574431

      Hi,

       

      I'm new to the Oracle database and I'm having some difficulty with auditing. I've switched it all on but no matter which table I look at, I can only see log on's and log off's. I tested the auditing by creating a new table, altering it and then dropping it and all I got was log on and log off. This is my setup...

       

      select * from v$version;

      BANNER

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

      Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

      PL/SQL Release 11.2.0.1.0 - Production

      CORE 11.2.0.1.0      Production

      TNS for Linux: Version 11.2.0.1.0 - Production

      NLSRTL Version 11.2.0.1.0 – Production

       

      show parameter audit;

       

      NAME                                 TYPE        VALUE

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

      audit_file_dest                 string                 /u01/app/oracle/admin/jdevm/adump

      audit_sys_operations   boolean               FALSE

      audit_syslog_level         string

      audit_trail                          string               DB, EXTENDED

       

       

       

      ALTER SYSTEM                             BY ACCESS  BY ACCESS

      SYSTEM AUDIT                             BY ACCESS  BY ACCESS

      CREATE SESSION                           BY ACCESS  BY ACCESS

      TABLE                                   BY ACCESS  BY ACCESS

      CREATE TABLESPACE                        BY ACCESS  BY ACCESS

      ALTER TABLESPACE                         BY ACCESS  BY ACCESS

      DROP TABLESPACE                          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

      CREATE ROLLBACK SEGMENT                  BY ACCESS  BY ACCESS

      ALTER ROLLBACK SEGMENT                   BY ACCESS  BY ACCESS

      DROP ROLLBACK SEGMENT                    BY ACCESS  BY ACCESS

      ROLE                                     BY ACCESS  BY ACCESS

      PROFILE                                  BY ACCESS  BY ACCESS

      CREATE ANY TABLE                         BY ACCESS  BY ACCESS

      ALTER ANY TABLE                          BY ACCESS  BY ACCESS

      DROP ANY TABLE                           BY ACCESS  BY ACCESS

      ALTER TABLE                              BY ACCESS  BY ACCESS

      CREATE ANY VIEW                          BY ACCESS  BY ACCESS

      DROP ANY VIEW                            BY ACCESS  BY ACCESS

      CREATE SEQUENCE                          BY ACCESS  BY ACCESS

      CREATE ANY SEQUENCE                      BY ACCESS  BY ACCESS

      DROP ANY SEQUENCE                        BY ACCESS  BY ACCESS

      CREATE DATABASE LINK                     BY ACCESS  BY ACCESS

      CREATE PUBLIC DATABASE LINK              BY ACCESS  BY ACCESS

      DROP PUBLIC DATABASE LINK                BY ACCESS  BY ACCESS

      CREATE ROLE                              BY ACCESS  BY ACCESS

      DROP ANY ROLE                            BY ACCESS  BY ACCESS

      GRANT ANY ROLE                           BY ACCESS  BY ACCESS

      ALTER 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

      DROP ANY PROCEDURE                       BY ACCESS  BY ACCESS

      CREATE PROFILE                           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

      ALTER PUBLIC DATABASE LINK               BY ACCESS  BY ACCESS

       

      All I seem to be getting out of DBA_AUDIT_TRAIL or SYS.AUD$ is listed below...

       

      garethsSYSTEMSSMQ01SSMQ0130-06-2014 13:50100LOGON
      garethsSYSTEMSSMQ01SSMQ0130-06-2014 13:50101LOGOFF
      garethsSYSTEMSSMQ01SSMQ0130-06-2014 13:50100LOGON
      garethsSYSTEMSSMQ01SSMQ0130-06-2014 13:50101LOGOFF
      garethsSYSTEMSSMQ01SSMQ0130-06-2014 13:50100LOGON
        • 1. Re: Problem With Auditing
          JudWilliford

          Your audit options query result is not adequate to debug your problem.  It looks from your audit log query that you are logging in as 'SYSTEM'.  Can you run this query?

           

          select 'PRIV' audtype, privilege, success, failure from dba_priv_audit_opts

            where user_name is null or user_name = 'SYSTEM'

            union

            select 'STMT' audtype, audit_option, success, failure from dba_stmt_audit_opts

            where user_name is null or user_name = 'SYSTEM'

            order by 2, 1

          ;