Truncate Or Delete Rows From Audit Trail Table SYS.AUD$

1)Only appropriate privileged user can do delete operation on SYS.AUD$ table. The user must have either of the following privileges.
-SYS user.
-DELETE ANY TABLE system privilege. (If O7_DICTIONARY_ACCESSIBILITY=TRUE)
-A user to whom SYS has granted the object privilege DELETE on SYS.AUD$ table.

2)Before deleting any rows you may want to archive the table. You can achive this by creating a table from SYS.AUD$ and export that. Don't export SYS.AUD$ directly.
SQL>CREATE TABLE AUDIT_RECORD TABLESPACE users as select * from SYS.AUD$;
Now export the table as,
SQL> host exp tables=AUDIT_RECORD file=audit_record.dmp

3)To delete all records from audit trail table SYS.AUD$ issue,
SQL>DELETE FROM SYS.AUD$;

To delete all records of particular audited table from the audit trail issue,
SQL>DELETE FROM sys.aud$ WHERE obj$name='&table_nmae';

But deleting in this way will not reduce size on the system tablespace or aud$ table. In order to reduce size follow section 4.

4)Truncate audit table to reduce size.
SQL>CONN / as sysdba
SQL>TRUNCATE TABLE sys.aud$;

Truncate uses the DROP STORAGE clause but keeps only minextents extents, thus only 1 extent

Related Post:-

• Basic Database Auditing
• Moving SYS.AUD$ To Another Tablespace
• How To MOve Audit Table Out Of  SYSTEM Tablespace
• How To Reorganize Audit Trail SYS.AUD$ Table

Read More

How To Reorganize Audit Trail AUD.SYS$ Table

You may want to reorganize your auditing table if you optionally delete records from it regularly. In the following steps it is described.
1)Enable restricted session.
In order to ensure consistency in the auditing table temporary disable auditing activity. You can do this by opening database with STARTUP RESTRICT or during open make the system as restricted session.

SQL> connect / as sysdba
Connected.

SQL> alter system enable restricted session;
System altered.

Check if sessions are still connected by,
SQL> select sid, serial#, username from v$session;

If necessary kill these sessions with,
SQL> alter system kill session 'sid , serial#';

2)Copy SYS.AUD$ table.
SQL>CREATE TABLE audit_record TABLESPACE USERS as select * from SYS.AUD$;
You now can take a dump of audit_record.

3)Truncate SYS.AUD$ table.
SQL> truncate table sys.aud$;
Table truncated.

SQL> select count(*) from SYS.AUD$;
COUNT(*)
--------
0

4)Copy the rows back to SYS.AUD$.
SQL> insert into sys.aud$ select * from audit_record;

You can also import it if you exported it in step 2.

5)Drop the audit_record table(optional).
SQL>DROP TABLE audit_record;

Related Post:-

• Basic Database Auditing
• Moving SYS.AUD$ To Another Tablespace
• How To MOve Audit Table Out Of  SYSTEM Tablespace
• Truncate or Delete Rows From Audit Trail Tablespace

Read More

Move Audit Table Out Of SYSTEM Tablespace

Oracle strongly recommended to use DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
provided with the DBMS_MGMT package in order to move audit trail table out of SYSTEM tablespace. But the procedure SET_AUDIT_TRAIL_LOCATION by default is not available until 11g. It is available in 11g.

However with manual script you can move SYS.AUD$ table out of SYSTEM tablespace. But you need to remember moving AUD$ out of SYSTEM tablespace is not a
supported procedure. Oracle does not support changing ownership of AUD$, or any
triggers on it.

Below is the scripts that you can do as your own risk,
Step 01: Connect to database as SYS user.
conn / as sysdba

Step 02: Create tablespace where audit file will reside.
create tablespace AUDIT_TBS
datafile '/oradata2/datafile/aud01.dbf' size 10M autoextend on maxsize unlimited;

Step 03: Create audit table inside AUDIT_TBS
create table aud_aux tablespace AUDIT_TBS
as select * from aud$ where 1 = 2;
Note that no rows will be created in this state.

Step 04: Rename the original Audit table.
rename AUD$ to AUD$$;

Step 05: Rename the aud_aux to AUD$
rename aud_aux to aud$;

Step 06: Create Index on the AUD$ table.
create index aud_i
on aud$(sessionid, ses$tid)
tablespace AUDIT_TBS;

Related Post:-

• Basic Database Auditing
• Moving SYS.AUD$ To Another Tablespace
• How To Reorganize Audit Trail SYS.AUD$ Table
• Truncate or Delete Rows From Audit Trail Tablespace

Read More

Moving SYS.AUD$ To Another Tablespace

Oracle stores audit trail records in the SYS.AUD$ base data dictionary table which resides in SYSTEM tablespace.

Based on the auditing option this SYS.AUD$ table grows out of order inside the SYSTEM tablespace and must have records deleted from it or be truncated, otherwise it will take up all the room in the SYSTEM tablespace. This deleting and truncating of the SYS.AUD$ table will fragment the SYSTEM tablespace.

So it may be one's choice to move the audit table out of the SYSTEM tablespace and then enable auditing without touching SYSTEM tablespace.

Until 11g the way to move out SYS.AUD$ is not supported. If you want to do it you have to do it manually which is discussed in How to move audit table out of SYSTEM tablespace which is not supported. 'Not supported' means Oracle Support cannot involve development if problems arise because of the triggers you put on the AUD$ table.

In 11g you can do it by the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION provided with the DBMS_MGMT package.

Related Post :- 

• Basic Database Auditing 
• How To Move Audit Table Out Of SYSTEM Tablespace
• How To Reorganize Audit Trail SYS.AUD$ Table
• Truncate or Delete Rows From Audit Trail Tablespace

Read More

Basic Database Auditing

Auditing

Auditing is the monitoring and recording of selected user database actions. It can be based on individual actions, such as the type of SQL statement executed, or on combinations of factors that can include user name, application, time, and so on. Security policies can trigger auditing when specified elements in an Oracle database are accessed or altered, including the contents within a specified object.

Why we need it?

Enable future accountability for current actions taken in a particular schema, table, or row, or affecting specific content.
Deter users (or others) from inappropriate actions based on that accountability.
Investigate suspicious activity.
Notify an auditor that an unauthorized user is manipulating or deleting data and that the user has more privileges than expected which can lead to reassessing user authorizations.
Monitor and gather data about specific database activities.
Detect problems with an authorization or access control implementation.
For example, you can create audit policies that you expect will never generate an audit record because the data is protected in other ways. However, if these policies do generate audit records, then you will know the other security controls are not properly implemented.

Oracle allows audit options to be focused or broad, enabling you to audit the following:

Successful statement executions, unsuccessful statement executions, or both.
Statement executions once in each user session or once every time the statement is executed.
Activities of all users or of a specific user

Auditing Types

1.Statement Auditing.(DDL statement and DML statements)
2.Privilege Auditing(system privilege)
3.Schema Object Auditing
4.Fine-Grained Auditing(Monitor Data Acess based on Content)

Audit Records and Audit Trails

Audit records include information about the operation that was audited, the user performing the operation, and the date and time of the operation. Audit records can be stored in either a data dictionary table, called the database audit trail, or in operating system files, called an operating system audit trail.

The two general types of auditing are standard auditing, which is based on privileges, schemas, objects, and statements, and fine-grained auditing. Standard audit records can be written either to DBA_AUDIT_TRAIL (the sys.aud$ table) or to the operating system. Fine-grained audit records are written to DBA_FGA_AUDIT_TRAIL (the sys.fga_log$ table) and the DBA_COMMON_AUDIT_TRAIL view, which combines standard and fine-grained audit log records.

When Audit Records Created?

SQL statements inside PL/SQL program units are individually audited, as necessary, when the program unit is executed.

The generation and insertion of an audit trail record is independent of a user transaction being committed. That is, even if a user transaction is rolled back, the audit trail record remains committed.

Statement and privilege audit options in effect at the time a database user connects to the database remain in effect for the duration of the session. Setting or changing statement or privilege audit options in a session does not take effect in that session. The modified statement or privilege audit options take effect only when the current session ends and a new session is created.

In contrast, changes to schema object audit options become effective for current sessions immediately.

Operations by the SYS user and by users connected through SYSDBA or SYSOPER can be fully audited with the AUDIT_SYS_OPERATIONS initialization parameter. Every successful SQL statement from SYS is audited. This specialized form of auditing audits all actions performed by every user with the SYSDBA privilege and writes only to an operating system location. It is not dependent on the standard auditing parameter, AUDIT_TRAIL.

Related Post:- 

• Moving SYS.AUD$ To Another Tablespace
• How To Move Audit Table Out Of SYSTEM Tablespace
• How To Reorganize Audit Trail SYS.AUD$ Table
• Truncate or Delete Rows From Audit Trail Tablespace

Read More