How are ORA-1s being audited?

From: Rich Jesse <rjoralist2_at_society.servebeer.com>
Date: Tue, 8 Mar 2011 10:15:37 -0600 (CST)
Message-ID: <7c01097f9915c14680cbf463e70d7c5f.squirrel_at_society.servebeer.com>

Howdy,

I need to add auditing of ORA-1 unique constraint violations to a 10.1.0.5.0 production DB.  I already have this in another, so I looked at the usual DBA%AUDIT% views.  However, I'm unable to determine exactly how I had originally setup the auditing so that ORA-1s are captured (and there are a few every day):

SELECT privilege, success, failure FROM
dba_priv_audit_opts;

PRIVILEGE              
SUCCESS      FAILURE  
ALTER
SYSTEM            BY
ACCESS    BY ACCESS
AUDIT
SYSTEM            BY
ACCESS    BY ACCESS

CREATE SESSION    
     NOT SET      BY ACCESS
ALTER DATABASE          BY

ACCESS    BY ACCESS
ALTER TABLESPACE    
   BY ACCESS    BY ACCESS

ALTER
USER              BY
ACCESS    BY ACCESS SELECT audit_option, success, failure FROM dba_stmt_audit_opts ORDER BY 1;

AUDIT_OPTION         
  SUCCESS      FAILURE   ALTER DATABASE          BY
ACCESS    BY ACCESS
ALTER SEQUENCE     
    BY ACCESS    BY ACCESS
ALTER
SYSTEM            BY
ACCESS    BY ACCESS
ALTER TABLE    
        BY ACCESS    BY ACCESS
ALTER
TABLESPACE        BY ACCESS    BY
ACCESS
ALTER USER              BY
ACCESS    BY ACCESS
CREATE SESSION      
   NOT SET      BY ACCESS

DATABASE
LINK           BY ACCESS    BY
ACCESS
DIRECTORY               BY
ACCESS    BY ACCESS
GRANT
DIRECTORY         BY
ACCESS    BY ACCESS
GRANT PROCEDURE    
    BY ACCESS    BY ACCESS
GRANT SEQUENCE
         BY ACCESS    BY ACCESS
GRANT TABLE             BY
ACCESS    BY ACCESS
GRANT TYPE      
       BY ACCESS    BY ACCESS
INDEX
                  BY
ACCESS    BY ACCESS
PROCEDURE      
        BY ACCESS    BY ACCESS

PROFILE                

BY ACCESS    BY ACCESS
PUBLIC DATABASE
LINK    BY ACCESS    BY ACCESS
PUBLIC
SYNONYM          BY ACCESS   
BY ACCESS
ROLE              
     BY ACCESS    BY ACCESS

SEQUENCE

               BY
ACCESS    BY ACCESS
SYNONYM        

        BY ACCESS    BY ACCESS
SYSTEM AUDIT            BY
ACCESS    BY ACCESS
SYSTEM GRANT      
     BY ACCESS    BY ACCESS

TABLE  
                BY
ACCESS    BY ACCESS

TABLESPACE      
       BY ACCESS    BY ACCESS
TRIGGER                 BY
ACCESS    BY ACCESS
TYPE      
             BY ACCESS   
BY ACCESS
USER              
     BY ACCESS    BY ACCESS

VIEW  
                 BY
ACCESS    BY ACCESS Both the USER_NAME and
PROXY_NAME columns for the above two queries are NULL.

Also,
after digging through the contents of DBA_OBJ_AUDIT_OPTS, I confirmed there is no object-level auditing by querying the underlying tables:

SELECT t.audit$, u.name, o.name objname
FROM
sys.tab$ t, sys.obj$ o, sys.user$ u
WHERE t.audit$ !=

'--------------------------------------'

AND o.owner# = u.user# AND
o.obj# = t.obj# AND u.name = 'MYSCHEMA';

...which returns
no rows.

I would have thought that I would have seen an INSERT/UPDATE WHENEVER NOT SUCCESSFUL BY ACCESS in DBA_STMT_AUDIT_OPTS, but there is none.  Also, FGA is not used,  there are no triggers to account for the audits, and the only EVENT in the spfile is for 1652 (to determine what session ran us out of TEMP).

I've been reading
through the AUDIT sections of the manual, but I think I need another pair of eyes to look at this and my glasses apparently don't count. 

Thoughts anyone?  It's probably something simple that I failed to document...

Thanks!
Rich

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 08 2011 - 10:15:37 CST