Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

sys.aud$ not capturing DDL after turning on auditing

254913Nov 20 2012 — edited Nov 24 2012
Summary: 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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 22 2012
Added on Nov 20 2012
4 comments
1,490 views