asked on

oracle auditing

we need to enable auditing for dml and ddl but exclude app users I have 3 users that I want to exclude from auditing but enable auditing for the rest how can I do that?

thanks

mrjoltcola

Using standard auditing you can audit everything a specific user does:

It is recommended to use explicit list of privileges instead of using ALL.

See Oracle docs:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4007.htm

--To audit most DDL, you can use:
audit all by user1 by access;
 
--To audit DML:
audit insert any table, update any table, delete any table by user1;

Open in new window

mrjoltcola

Make sure to set audit_trail to a valid setting (db or os is common). It requires reboot.

alter system set audit_trail = db scope = spfile;

shutdown immediate

startup open

ASKER CERTIFIED SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

it-rex

ASKER

now I have 3 heavy users that i need to turn off auditing for all of them what should i do???
please

SOLUTION

mrjoltcola

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

it-rex

ASKER

"You will need to revert all blanket auditing in the database, then activate auditing by user, only including the users you want to audit"
how can I do that?

Mark Geerlings

If you used a statement like this to turn on auditing for all users in the past:
AUDIT SELECT TABLE, UPDATE TABLE, DELETE TABLE, INSERT TABLE
whenever successful;

Use a statement like this to turn it off:
noaudit SELECT TABLE, UPDATE TABLE, DELETE TABLE, INSERT TABLE;

If you aren't sure which audit options or actions have been turned on, use these queries to help you find them:

select * from dba_priv_audit_opts;
select * from dba_stmt_audit_opts;
select * from dba_obj_audit_opts
where ALT || AUD || COM || DEL || GRA || IND || INS || LOC
|| REN || SEL || UPD || REF || EXE || CRE || REA ||WRI || FBK
<> '-/--/--/--/--/--/--/--/--/--/--/--/--/--/--/--/--/-';

(Note: that last statement may return a few "false positives" that have a pattern like this:
/ / / / / / / / / / / -/- / / / / /
but they can be ignored.)
' / / / / / / / / / / / -/- / / / / / ');