sys.aud$ not capturing DDL after turning on auditing
254913Nov 20 2012 — edited Nov 24 2012Summary: Auditing enabled, but DDLs are not being captured in SYS.AUD$
Environment:
select * from v$version;
BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
We've just turned on auditing (DB) by changing the init.ora and bouncing the database:
select * from V$PARAMETER
where name like '%audit%'
order by name;
NUM NAME TYPE VALUE DISPLAY_VALUE ISDEFAULT ISSES_MODIFIABLE ISSYS_MODIFIABLE ISINSTANCE_MODIFIABLE
ISMODIFIED ISADJUSTED ISDEPRECATED ISBASIC DESCRIPTION UPDATE_COMMENT HASH
1768 audit_file_dest 2 /app/admin/boact/adump /app/admin/boact/adump FALSE FALSE DEFERRED TRUE FALSE FALSE
FALSE FALSE Directory in which auditing files are to reside 2188534560
1608 audit_sys_operations 1 FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE enable sys
auditing 2991425711
1769 audit_syslog_level 2 TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE Syslog
facility and level 1189300432
1794 audit_trail 2 DB DB FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE enable system
auditing 4289193100
We ran the following as sysdba:
audit create session by access;
audit audit system by access;
audit grant any privilege by access;
audit grant any object privilege by access;
audit grant any role by access;
audit create user by access;
audit create any table by access;
audit create public database link by access;
audit create any procedure by access;
audit alter user by access;
audit alter any table by access;
audit alter any procedure by access;
audit alter database by access;
audit alter system by access;
audit alter profile by access;
audit drop user by access;
audit drop any procedure by access;
audit drop any table by access;
audit drop profile by access;
and gave access to 2 specific users:
grant select any dictionary to <user>;
select * from dba_stmt_audit_opts
union
select * from dba_priv_audit_opts;
gives me:
USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE
ALTER ANY PROCEDURE BY ACCESS BY ACCESS
ALTER ANY TABLE BY ACCESS BY ACCESS
ALTER DATABASE BY ACCESS BY ACCESS
ALTER PROFILE BY ACCESS BY ACCESS
ALTER SYSTEM BY ACCESS BY ACCESS
ALTER USER BY ACCESS BY ACCESS
AUDIT SYSTEM BY ACCESS BY ACCESS
CREATE ANY PROCEDURE BY ACCESS BY ACCESS
CREATE ANY TABLE BY ACCESS BY ACCESS
CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS
CREATE SESSION BY ACCESS BY ACCESS
CREATE USER BY ACCESS BY ACCESS
DROP ANY PROCEDURE BY ACCESS BY ACCESS
DROP ANY TABLE BY ACCESS BY ACCESS
DROP PROFILE BY ACCESS BY ACCESS
DROP USER BY ACCESS BY ACCESS
GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS
GRANT ANY PRIVILEGE BY ACCESS BY ACCESS
GRANT ANY ROLE BY ACCESS BY ACCESS
SYSTEM AUDIT BY ACCESS BY ACCESS
select * from DBA_AUDIT_MGMT_CONFIG_PARAMS ;
PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL
DB AUDIT TABLESPACE SYSAUX STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE SYSAUX FGA AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 OS AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 XML AUDIT TRAIL
AUDIT FILE MAX AGE 5 OS AUDIT TRAIL
AUDIT FILE MAX AGE 5 XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE 1000 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE 1000 XML AUDIT TRAIL
Connections and Disconnections are audited just fine (select * from DBA_AUDIT_SESSION).
Dropping users are audited just fine (select * from DBA_AUDIT_OBJECT).
DDLs do not seem to be audited (select * from DBA_AUDIT_OBJECT, not found just by select * from SYS.AUD$):
create table synktemp (col1 varchar2(100));
alter table synktemp add (col2 number);
alter table synktemp drop column col1;
drop table synktemp;
I know I'm probably missing something simple, but it escapes me.... Any help would be appreciated.