Audit Trails in oracle database
Auditing Trails Records are stored in two ways
1) Database Table (sys.aud$)
2) OS Directory (audit_file_dest)
Option 1 : If we use DB table to store auditing records Performance will be degrade, Because these tables are under SYSTEM Tablespace by default, also we can move this sys.aud$ table to dedicated tablespace for this we need to create new Tablespace. Oracle recommends to use OS Directory to store Audit Trail Records.
Table Growth: The Audit Trails table growth depends on what are the Auditing are enabled, for example: If whenever a table has been altered, updated, inserted or deleted.
SQL> AUDIT ALTER, GRANT, INSERT, UPDATE, DELETE
ON DEFAULT;
Purging Audit Trails Table: The purging Audit Trails Records in the tables can be manually purge or automatic purge.
Option 2: If we use OS Directory to store auditing records there will not be any performance issue also easy to manage those log files, we can define its size and age. Purging also enabled as automatic purging.
Our Plan:
As per Oracle recommendation we will implement OS Directory to store these Audit Trail Records.
Procedure:
1) Modify the initialization file for enabling Audit trail.
2) Set the Audit file destination in initialization file.
3) Define the Size of its log file.
4) Define days to create new log file.
5) Define Purging days for automatic purging.
6) Define Audit Trails in Database.
7) Bounce the Database.
8) Create new pfile from spfile.
Step 1: Modify the initialization file for enabling Audit trail.
SQL> ALTER SYSTEM SET audit_trail=os SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET AUDIT_SYS_OPERATIONS=TRUE SCOPE=SPFILE;
System altered.
Step 2: Set the Audit file destination in initialization file.
#Default Location is $ORACLE_HOME/rdbms/audit
If we like to change to location for log files the below command will change its location.
SQL> ALTER SYSTEM SET audit_file_dest=/backup/Audit_Trails/ SCOPE=SPFILE;
System altered.
Note : some latest it will work otherwise need to edit on pfile , bounce Database using new audit_file_dest=’<new location >’.
Step 3: Define the Size of its log file.
If we like to create new file after reaching 100MB, the following procedure calls new log file after reaching 100MB.
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
audit_trail_property => DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE,
audit_trail_property_value => 102400 /* 100MB*/ );
END;
/
Step 4: Define days to create new log file.
If we like to create new file after one day, the following procedure calls new file after 1 day.
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
audit_trail_property => DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE,
audit_trail_property_value => 1 /* days */);
END;
/
Step 5: Define Purging days for automatic purging.
If we like to purge all Auditing Records after 7 days, the following procedure will purge after 7days once.
BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
AUDIT_TRAIL_PURGE_INTERVAL => 168,
AUDIT_TRAIL_PURGE_NAME => 'Standard_Audit_Trail_PJ',
USE_LAST_ARCH_TIMESTAMP => FALSE );
END;
/
Step 6: Define Audit Trails in Database.
To protect unauthorized access of all Table, we will Audit any Create, Alter, Update and Delete on all tables using the following query,
SQL> AUDIT DELETE ANY TABLE, ALTER ANY TABLE, CREATE ANY TABLE, UPDATE ANY TABLE BY SYS,APPS;
Step 7: Bounce the Database.
SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP
Step 8: create new pfile from spfile
SQL > CREATE PFILE FROM SPFILE;
