getting DDL audit to work

From: NetComrade <netcomradeNSPAM_at_bookexchange.net>
Date: Mon, 20 Mar 2006 17:24:44 -0500
Message-ID: <4q8u12h1jkpraqdibf4nrajpm92oh0bigq@4ax.com>

I've been trying to get DDL auditing to work on a test database.

I've changed db_audit=db parameter and bounced the db.

system_at_TEST> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
audit_file_dest                      string      ?/rdbms/audit
audit_sys_operations                 boolean     FALSE
audit_trail                          string      DB
transaction_auditing                 boolean     TRUE

then I issued
audit create procedure;
audit create any table;
audit alter any procedure;

system_at_TEST> select audit_option,success,failure  from dba_stmt_audit_opts
  union
 select privilege,success,failure
 from dba_priv_audit_opts

system_at_TEST> /

AUDIT_OPTION                             SUCCESS    FAILURE
---------------------------------------- ---------- ----------
ALTER ANY PROCEDURE                      BY ACCESS  BY ACCESS
CREATE PROCEDURE                         BY ACCESS  BY ACCESS
CREATE TABLE                             BY ACCESS  BY ACCESS

in a test user account
create procedure dummy as begin null; end;
-- this gets recorded

create or replace procedure dummy as begin null; null; end;
-- this doesn't

create table t as (c1 number);
-- this doesn't

What am I doing wrong?

  1* select username, owner, obj_name, action_name from dba_audit_trail
system_at_TEST> /

USERNAME OWNER OBJ_NAME ACTION_NAME

---------- ---------- -------------------- ---------------------------
TEST       TEST       DUMMY                CREATE PROCEDURE

.......
We run Oracle 9.2.0.6 on RH4 AMD
remove NSPAM to email Received on Mon Mar 20 2006 - 16:24:44 CST