This is for someone who is PSADMIN and not expert in Oracle DB Security setups and wants to enable quick and safe, especially when the SOX auditor is asking you to enable it. And also this is specific for PeopleSoft Application DBs.

Init.ora:
This file needs a line audit_trail=DB or audit_trial=OS [Needs restart of the DB to take effective]

Tables sys.aud$ and dba_audit_trail should return no rows. If there are any rows, you want to clean them up before you turn on auditing. Else run this script, as SYS internal, $ORACLE_HOME/rdbms/admin/cataudit.sql
with OAV.

For audit_trial=OS:

If you want to have the audit to OS level file, it should be audit_trial=OS Make sure to change the location where the file should be written, with the parameter AUDIT_FILE_DEST, if you don’t want it to write in $ORACLE_HOME/rdbms/audit location.

For audit_trial =DB:

Check if any audit option is enabled with the query, select * from DBA_STMT_AUDIT_OPTS. It should not return any rows, before the start of audit.

For PeopleSoft, SYSADM  the following should be enough, unless you want to add more or your SOX auditor wants you to add anything specific:

In one command:

audit all by sysadm by access;

   or if you want to do one by one and you can keep or remove anything from the list. 

audit UPDATE ANY TABLE by sysadm by access;
audit CREATE ANY TABLE by sysadm by access;
audit INSERT ANY TABLE by sysadm by access;
audit DROP ANY TABLE by sysadm by access;
audit DELETE ANY TABLE by sysadm by access;
audit SELECT ANY TABLE by sysadm by access;
audit CREATE USER by sysadm by access;
audit ALTER USER by sysadm by access;
audit DROP USER by sysadm by access;
audit DROP TABLESPACE by sysadm by access;
audit ALTER DATABASE by sysadm by access;
audit ALTER SYSTEM by sysadm by access;
audit CREATE TRIGGER by sysadm by access;
audit CREATE PROCEDURE by sysadm by access;
audit CREATE SYNONYM by sysadm by access;

CAUTION:

DONOT enable audit by session, unless you are sure that you want to.
This would create a row for each session and remember that he Application servers and process schedulers create session and I feel it is not that much important compared to the ones mentioned above.

noaudit ALL by sysadm;

or just copy and paste the following in SQL prompt. 

noaudit UPDATE ANY TABLE by sysadm;
noaudit CREATE ANY TABLE by sysadm;
noaudit INSERT ANY TABLE by sysadm;
noaudit DROP ANY TABLE by sysadm;
noaudit DELETE ANY TABLE by sysadm;
noaudit SELECT ANY TABLE by sysadm;
noaudit CREATE USER by sysadm;
noaudit ALTER USER by sysadm;
noaudit DROP USER by sysadm;
noaudit DROP TABLESPACE by sysadm;
noaudit ALTER DATABASE by sysadm;
noaudit ALTER SYSTEM by sysadm;
noaudit CREATE TRIGGER by sysadm;
noaudit CREATE PROCEDURE by sysadm;
noaudit CREATE SYNONYM by sysadm;

To ensure that nothing is being audited, check DBA_STMT_AUDIT_OPTS if there are any rows returned. If any run noaudit command for those things.

 More to come..

Well, don’t forget to archive the entries frequently or delete them once the report is taken to avoid unwanted data in the DB. Also make sure that the SYSTEM tablespace or the tablespace where the sys.aud$ table is assigned and the default storage for the table sys.aud$ , DOES NOT HAVE  PCTINCREASE as 50%. The default storage for system tablespace is 50% and remember to change it to 0% else, you will run into trouble when the table size grows, especially if there are any brutal attacks on your system.

If you want to get into details of auditing, you should consider Oracle Audit Vault, especially you have many production instances to manage and managing then would be easier with OAV.

Be first to comment this article

Write Comment

Thankyou for your comments. Feel free to comment if there are any mistake in the postings also. If you have a Blog or website, feel free to provide the link. We will take a peak. Just ensure to *Refresh* your browser for a new security code to be displayed prior to clicking on the 'Send' button. Keep in mind that the above process only applies if you simply entered the wrong security code.
Name:
Homepage
BBCode:
Comment:
Code:*
	I wish to be contacted by email regarding additional comments

Copy Right: AKOComments v.1.4.6