Auditing in Oracle.

Auditing  is used to track the occurrence of SQL statements in subsequent user sessions.

In this article will see how can dba  track all types of sql operations  (select,insert,update,delete drop,create and alter) of particular user.

Auditing is a default feature of the Oracle server. The initialization parameters that influence its behaviour can be displayed using the SHOW PARAMETER

Auditing is disabled by default, but can enabled by setting the AUDIT_TRAIL static parameter, which has the following allowed values.

The following list provides a description of each setting:

• none or false –  Auditing is disabled.
• db or true –        Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
• db,extended –   As db, but the SQL_BIND and SQL_TEXT columns are also populated.
• xml–                     Auditing is enabled, with all audit records stored as XML format OS files.
• xml,extended – As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
• os–                       Auditing is enabled, with all audit records directed to the operating system’s audit trail.

To enable auditing and direct audit records to the database audit trail, we would do the following.

Now we will create user called aud_test with required privileges.

Give permissions to audit all operations to the  AUD_TEST user.

These options audit all DDL and DML, along with some system events.

–> DDL (CREATE, ALTER & DROP of objects)

–>DML (INSERT UPDATE, DELETE, SELECT, EXECUTE).

–>SYSTEM EVENTS (LOGON, LOGOFF etc.)

connect to AUD_TEST  and create  a table object then perform DML operations.

In the next section we will look at how we view the contents of the audit trail.

The audit trail is stored in the SYS.AUD$ table. Its contents can be viewed directly or via the following views.

The most basic view of the database audit trail is provided by the DBA_AUDIT_TRAIL view, which contains a wide variety of information. The following query displays the some of the information from the database audit trail.

SELECT few of the columns from DBA_AUDIT_TRAIL Data Dictionary View.

As a DBA we will come to know  which os user and from system IP address user connected to perform the transaction.

All the information regarding AUDITING stored in sys.aud$.

Simple we can  truncate those information by using TRUNCATE command.

In above query the result is no rows selected  because we truncated info from sys.aud$.

 

Thank you ………..