Oracle 11gR2 – Default Audit Policies

Oracle 11gR2 – Default Audit Policies

By default, when we create a new database instance in Oracle, the default value for AUDIT_TRAIL is set to “none”. When you set it to “DB” or “DB,EXTENDED”, it will have a set of default logging policies.

SQL> select * from dba_priv_audit_opts;

USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
 CREATE EXTERNAL JOB BY ACCESS BY ACCESS
 CREATE ANY JOB BY ACCESS BY ACCESS
 GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS
 EXEMPT ACCESS POLICY BY ACCESS BY ACCESS
 CREATE ANY LIBRARY BY ACCESS BY ACCESS
 GRANT ANY PRIVILEGE BY ACCESS BY ACCESS
 DROP PROFILE BY ACCESS BY ACCESS
 ALTER PROFILE BY ACCESS BY ACCESS
 DROP ANY PROCEDURE BY ACCESS BY ACCESS
 ALTER ANY PROCEDURE BY ACCESS BY ACCESS
 CREATE ANY PROCEDURE BY ACCESS BY ACCESS
 ALTER DATABASE BY ACCESS BY ACCESS
 GRANT ANY ROLE BY ACCESS BY ACCESS
 CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS
 DROP ANY TABLE BY ACCESS BY ACCESS
 ALTER ANY TABLE BY ACCESS BY ACCESS
 CREATE ANY TABLE BY ACCESS BY ACCESS
 DROP USER BY ACCESS BY ACCESS
 ALTER USER BY ACCESS BY ACCESS
 CREATE USER BY ACCESS BY ACCESS
 CREATE SESSION BY ACCESS BY ACCESS
 AUDIT SYSTEM BY ACCESS BY ACCESS
 ALTER SYSTEM BY ACCESS BY ACCESS

By default, it will log ALL sessions. This will increase the audit log size by a tremendous amount. Thus, unless you have a unique business requirement, it will be wiser if we just log failure login attempts only.

Disable audit logging on all session creation. Enable audit logging on unsuccessful session creation.

SQL> noaudit create session;
SQL> audit create session whenever not successful;

Verify Settings.

SQL> select * from dba_priv_audit_opts;

USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
 CREATE SESSION NOT SET BY ACCESS

Hope this helps!

Regards,
Wei Shan

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: