Standard Database Auditing in Oracle

Introduction about auditing an Oracle system
Activate the standard audit mode
Set up an Audit with DB parameter
Set up Audit with DB,EXTENDED parameter
Set up Audit with XML parameter
Set up Audit with XML,EXTENDED parameter
View relevant parameters

-

1. Introduction about auditing an Oracle system

Auditing means monitoring and recording the actions that have been configured in a database, including actions from both user kinds such as database user and nondatabase user.

Nonedatabase Users:

Nondatabase Users: These are the users of an application. This application uses an Oracle database, and therefore, they can execute actions for the database. These users need to be identified in the database using the CLIENT_IDENTIFIER attribute. Auditing these users is more complex than auditing database users.

Auditing is a necessary and important job for a database administrator. It is an effective tool against wrongdoing and always is first implemented in any security initiative in the Oracle.

In addition, some other reasons need to be audited:

An auditor audits users using the privilege to access sensitive objects.
Audit Trail prevents users from doing wrongdoing effectively
Audit Trail is mandatory thing if you have to investigate to learn about what matter is wrong.
Audit Trail warns you of suspicious activities, you can carry out an analysis of what you do not know.
Audit Trail is very important when defining access control, especially in existing applications.
It is difficult to define that "access auditing policy" will not break up business processes unless you know the current state and what they are doing.

Audit Trail

Auditing actions create an Audit Trail, including the records allowing search of what is done in the database.
Base on it to know what users have done and what privileges have been used ...
For each record, there is important information such as:
- Who has performed it
- Where it has been performed (which Schema or which Object)
- When it has been performed.
- How it has been performed (Which SQL statements have been performed).
- As well as other useful information in investigation and monitoring.

2. Activate the standard audit mode

Standard Audit is the most comprehensive and sufficient audit base in the Oracle database. It allows auditing an action, action kind, object, privilege, access user .

There are 2 stages related to using the Standard Audit.

Activate audit mode.
Define the items to be audited e.i. define what actions to be created audit trail (Create Audit Trail).

To activate the database audit mode, you need to access the Oracle with an user having the sysdba privilege.

sys/<password> as sysdba

Audit trails can be a data table or a file on an operating system. If an auditor does not have DBA privilege, it is best to store the audited information on files. There are 4 ways to set up storing manners "audit trails".

Parameter	Description
DB	Trails which are stored in an AUD$ table, contain only statements other than a full text.
DB,EXTENDED	Trails which are stored in an AUD$, full text ( value of variables for each record..)
XML	Trails which are stored in the file of operating system in XML format, of which content is the same as DB parameter.
XML,EXTENDED	Trails which are stored in the file of operating system in XML format, of which content is the same as DB,EXTENDED parameter.

3. Set up an Audit with DB parameter

First of all, you need to activate audit mode with a DB parameter (audit_trail = DB) to execute the following statements:

* SQLPLUS *

alter system set audit_trail=db scope=spfile;

shutdown immediate;

startup

In this example, we will audit a table for Scott.EMP, with a DB parameter, which means that "audit trail" will be stored in the database. (Specifically stored in the AUD$ table).

Audit all on scott.emp by access;

Adopt another user to perform some actions on the Scott.EMP table, for example, update. These actions will be saved on the AUD$ table.

-- Test

update scott.EMP set sal=sal*0.95 where job='MANAGER';

Query:

column username format a9
column owner format a5
column obj_name format a10
column action_name format a11
column sql_text format a40

-- Query view DBA_AUDIT_TRAIL:
select username, owner, obj_name,
       action_name, sql_text from dba_audit_trail;

4. Set up Audit with DB,EXTENDED parameter

Establish an audit mode with the audit_trail = DB,EXTENDED parameter.

alter system set audit_trail=db,extended scope=spfile;

shutdown immediate;

startup

Do update action on the Scott.EMP table:

Audit all on scott.emp by access;

Use another user to do an action on the Scott.EMP table:

-- Update

update scott.EMP set sal=sal + 10 where job='MANAGER';

column username format a9
column owner format a5
column obj_name format a10
column action_name format a11
column sql_text format a40

-- Query view DBA_AUDIT_TRAIL:
select username, owner, obj_name,
       action_name, sql_text from dba_audit_trail;

5. Set up Audit with XML parameter

Using audit_trail = xml parameter, "audit trails" will be stored in a file with XML format.

alter system set audit_trail=xml scope = spfile;

shutdown immediate;

startup

Audit files are usually located inadump directory. However, to know exactly, you can use the following statement to check the name of directory containing the Audit files.

show parameter audit_file_dest;

db12c_ora_2328_20171107022919280000831625.xml

<?xml version="1.0" encoding="UTF-8"?>
  <Audit xmlns="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-11_2.xsd"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-11_2.xsd">
   <Version>11.2</Version>
<AuditRecord><Audit_Type>8</Audit_Type><EntryId>1</EntryId><Extended_Timestamp>2017-11-06T19:29:19.280000Z</Extended_Timestamp><DB_User>/</DB_User><OS_User>tran-pc\tran</OS_User><Userhost>WORKGROUP\TRAN-PC</Userhost><OS_Process>1848:2328</OS_Process><Terminal>TRAN-PC</Terminal><Instance_Number>0</Instance_Number><Returncode>0</Returncode><OSPrivilege>SYSDBA</OSPrivilege><DBID>1409002769</DBID>
<Sql_Text>CONNECT</Sql_Text>
</AuditRecord>
<AuditRecord><Audit_Type>4</Audit_Type><Session_Id>4294967295</Session_Id><StatementId>1</StatementId><EntryId>2</EntryId><Extended_Timestamp>2017-11-06T19:29:34.903000Z</Extended_Timestamp><DB_User>/</DB_User><Ext_Name>tran-pc\tran</Ext_Name><OS_User>tran-pc\tran</OS_User><Userhost>WORKGROUP\TRAN-PC</Userhost><OS_Process>1848:2328</OS_Process><Terminal>TRAN-PC</Terminal><Instance_Number>0</Instance_Number><Returncode>0</Returncode><OSPrivilege>SYSDBA</OSPrivilege><DBID>1409002769</DBID>
<Sql_Text>ALTER DATABASE OPEN</Sql_Text>
</AuditRecord>

6. Set up Audit with XML,EXTENDED parameter

When you set up an audit with Audit_trail = xml,extended, the result obtained is like using Audit_trail = xml, however, the XML files will add information on the statements executed.

alter system set audit_trail=os scope=spfile;

shutdown immediate;

startup

db12c_ora_3956_20171108021424022000979538.xml

<?xml version="1.0" encoding="UTF-8"?>
  <Audit xmlns="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-11_2.xsd"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-11_2.xsd">
   <Version>11.2</Version>
<AuditRecord><Audit_Type>1</Audit_Type><Session_Id>1350961</Session_Id><StatementId>13</StatementId><EntryId>1</EntryId><Extended_Timestamp>2017-11-07T19:14:24.025000Z</Extended_Timestamp><DB_User>SCOTT</DB_User><OS_User>tran-pc\tran</OS_User><Userhost>WORKGROUP\TRAN-PC</Userhost><OS_Process>1856:3956</OS_Process><Terminal>TRAN-PC</Terminal><Instance_Number>0</Instance_Number><Object_Schema>SCOTT</Object_Schema><Object_Name>EMP</Object_Name><Action>6</Action><TransactionId>030017001E0E0000</TransactionId><Returncode>0</Returncode><Scn>17925302</Scn><DBID>1409002769</DBID>
<Sql_Text>Update Emp set Sal = Sal + 10 where job = &apos;MANAGER&apos;
</Sql_Text>
</AuditRecord>

7. View relevant parameters

When the audit mode has been established for database, you can check the used parameter.

-- Show all parameter in database.

show parameter

show parameter audit_trail;

Check the name of folder containing audit files to be created.

show parameter audit_file_dest;

The name of the directory containing Audit files is stored ina spfile<SID>.ora file.

Change the directory of Audit files:

alter system set audit_file_dest='C:\newdir' SCOPE=SPFILE;