mydomain
No ADS
No ADS

Standard Database Auditing in Oracle

  1. Introduction about auditing an Oracle system
  2. Activate the standard audit  mode 
  3. Set up an Audit with DB parameter
  4. Set up Audit  with DB,EXTENDED parameter
  5. Set up Audit with XML parameter
  6. Set up Audit with XML,EXTENDED parameter
  7. 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 

No ADS
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

No ADS
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

No ADS
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

No ADS
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 

No ADS
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;
No ADS
No ADS
No ADS
No ADS

Newest Articles

Show More
No ADS