This discussion is archived
4 Replies Latest reply: Nov 24, 2012 3:03 AM by Roger25 RSS

sys.aud$ not capturing DDL after turning on auditing

254913 Newbie
Currently Being Moderated
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.
  • 1. Re: sys.aud$ not capturing DDL after turning on auditing
    IBarr Explorer
    Currently Being Moderated
    You appear to be auditing the CREATE ANY TABLE privilege rather than the CREATE TABLE statement. This will only audit users creating tables who have the CREATE ANY TABLE privilege and are making use of it, not users who have the CREATE TABLE privilege.

    Try issuing the AUDIT TABLE statement.

    Regards,

    Iain Barr,
    Ategrity Solutions Ltd

    Edited by: IBarr on Nov 20, 2012 10:40 PM
  • 2. Re: sys.aud$ not capturing DDL after turning on auditing
    254913 Newbie
    Currently Being Moderated
    I have tried both "audit table" and "audit table by access". Neither one generates audits of my create/alter/drop table statements. I tried creating a public dblinks and that also failed to audit.

    Edited by: psynk on Nov 21, 2012 1:38 PM
  • 3. Re: sys.aud$ not capturing DDL after turning on auditing
    254913 Newbie
    Currently Being Moderated
    I dropped all my audits and ran $ORACLE_HOME/rdbms/admin/secconf.sql. Table create/drop/alters were still not being audited. It's when I did the following that I finally started getting some auditing:

    noaudit CREATE ANY TABLE;
    noaudit DROP ANY TABLE;
    noaudit ALTER ANY TABLE;
    audit table;
    audit ALTER TABLE;

    Things now audit nicely.

    Audit grant any object privilege by access; was part of the script, but "grant select on synktemp;" still is not being audited. So, slight progress....
  • 4. Re: sys.aud$ not capturing DDL after turning on auditing
    Roger25 Explorer
    Currently Being Moderated
    Sorry, but what's the difference between the two?
    If user X has the create any table privilege, then isn't audited also the create table statements? So if a user creates a table in his schema, why this statement is not audited? And what table should create user X so that statement is audited?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points