Steps To Enable Auditing on RAC Database

Auditing has two types, there are

• standard - auditing based on statement, privilege and object-level
• fine-grained - auditing, which is based on content

Standard auditng has 3 levels.

statement - audit all actions on any type of object

privilege - audit actions that stem from system privileges

object-level - specific audit actions like select, update, insert or delete

Auditing depends on which value we pass on audit_trail

the values are..

• none - disables auditing
• os - records the information to a o/s file (uses parameter audit_file_dest for the file location)
• db - records the information in the database, use the view dba_audit_trail ( view accesses table sys.aud$ ) to display audit information.
• db, extended - as per the db value but also populates the sqlbind and sqltext clob columns
• xml - audits to the o/s file but in xml format (uses parameter audit_file_dest for the file location)
• xml,extended - as per xml option but also populates the sqlbind and sqltext clob columnstandard

For all 3 levels of auditing you can choose to audit by access (audit every time you access) or by session (audit only once per access during the session).

Steps to enable auditing ...   Ex  11gR2 2 Node RAC setup.

We had to drop old audit (aud$) table, for that we need to keep our database in restricted mode.

Steps:

srvctl stop listener -n nodename1

srvctl stop listener -n nodename2

sqlplus>

alter system set cluster_database=false scope=spfile

alter system set audit_trail=db,extended scope=spfile

$srvctl stop database -d dbname -o immediate

>startup restrict

>truncate table aud$

>alter system set cluster_database=true scope=spfile

>shu immediate;

$srvctl start database -d dbname

$srvctl start listener -n nodename1

$srvctl start listener -n nodename2

create users

----------------------------------------

AUDIT ALL BY username BY ACCESS;

AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY username BY ACCESS;

AUDIT EXECUTE PROCEDURE BY username BY ACCESS;