SOP document for auditing in Oracle

SOP Document for auditing in Oracle

Auditing is the monitoring and recording of selected user database actions.
Auditing is normally used to:
· Investigate suspicious activity
· Monitor and gather data about specific database activities.
Oracle Audit is required for security of database. Oracle audit can help detect unauthorized access and misuse of the data held in the database.
CMT Central database will use the below audit options.
1. Standard Auditing

Setup Auditing
Login into database through sys user

$sqlplus sys as sysdba
SQL>show parameter audit

Check the database audit parameters and set them according to requirement. For database we will set below database parameter in database.

Login into database through sys user

$sqlplus sys as sysdba
SQL>alter system set audit_trail=db,extended scope=spfile;
SQL>shutdown database
    Database closed.
    Database dismounted.
    ORACLE instance shut down.

Check the auditing is enabled:

SQL> Show parameter audit;

Configure Auditing
Login into database through sys user

$sqlplus sys as sysdba
SQL> audit all by  pretups_oam by access;
SQL>audit select  table,update table,insert table,delete table by pretups_oam by access;
SQL> audit execute procedure by pretups_oam by access;

The above auditing option will cover all DML and DDL. We can modify them according to requirement.
Purging Policy:
Created procedure for generating xls file for the audit data and truncate audit table.
Location:  F:\oracle\product\10.2.0\admin\CMTCCERT\adump
File:  Audit_reports_DDMMYYYY.xls, Audit_sql_report_DDMMYYYY.xls
Business Impact Analysis:
· Disk space should be monitor daily because database will not work if there is no space for audit file generation.
· Database tablespace should be monitored daily. Transactions will stop if no space left in tablespace.
· Due to DR site configured on database, audit generates more archive log which applied on DR database.

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.