//
you're reading...
Administration, Security

Securing Oracle Database – Standard Auditing (II)

In the previous post we talk about Standard Auditing and AUDIT_TRAIL parameter with the value set to DB and DB_EXTENDED. In this part we will talk about AUDIT_TRAIL parameter with OS, XML and XML_EXTENDED values.

As we know that when AUDIT_TRAIL is set to OS, XML or XML_EXTENDED the audit_trail is write to the Operating System files.

To set the AUDIT_TRAIL parameter to OS, use the following statement and restart the database.

dbadmin@O11R1>ALTER SYSTEM SET AUDIT_TRAIL=os SCOPE=spfile;
System altered.

sys@O11R1>SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

sys@O11R1>startup
ORACLE instance started.

Total System Global Area    962527232 bytes
Fixed Size                    1303972 bytes
Variable Size               562039388 bytes
Database Buffers            394254576 bytes
Redo Buffers                  4919296 bytes
Database mounted.
Database opened.

dbadmin@O11R1>show parameter audit_trail;
NAME                 TYPE          VALUE
--------------       ----------    -----------------
audit_trail          string        OS

dbadmin@O11R1>AUDIT SELECT ON hr.employees;
Audit succeeded.

Now user SCOTT performs an SELECT operation on EMPLOYEES table.

scott@O11R1>SELECT * FROM hr.employees;

If you are connected through UNIX or LINUX operating system then the audit file can normally be found in directory specified by the parameter AUDIT_FILE_DEST.

dbadmin@O11R1>SHOW PARAMETER AUDIT_FILE_DEST;
NAME                 TYPE          VALUE
---------------      ----------    -----------------
audit_file_dest      string        /u01/app/oracle/admin/O11R1/adump
[oracle@ora ~]$ cat /u01/app/oracle/admin/O11R1/adump/ora_6611.aud
Audit file /u01/app/oracle/admin/O11R1/adump/ora_6611.aud
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1
System name:  Linux
Node name:    ora.home.com
Release:      2.6.18-92.el5
Version:      #1 SMP Tue Apr 29 13:16:12 EDT 2008
Machine:      i686
Instance name: O11R1
Redo thread mounted by this instance: 1
Oracle process number: 26
Unix process pid: 6611, image: oracle@ora.home.com (TNS V1-V3)

Mon Aug 27 05:45:20 2012

SESSIONID: "60108" ENTRYID: "1" STATEMENT: "1" USERID: "SCOTT" USERHOST: "ora.home.com" TERMINAL: "pts/2" ACTION: "100" RETURNCODE: "0" COMMENT$TEXT: "Authenticated by: DATABASE" OS$USERID: "oracle" PRIV$USED: 5

Mon Aug 27 05:45:30 2012

SESSIONID: "60108" ENTRYID: "2" STATEMENT: "12" USERID: "SCOTT" USERHOST: "ora.home.com" TERMINAL: "pts/2" ACTION: "3" RETURNCODE: "0" OBJ$CREATOR: "HR" OBJ$NAME: "EMPLOYEES" OS$USERID: "oracle"

On windows, Oracle writes Audit records in event log and you can view these logs through Event Viewer.

Event Type:   Information
Event Source: Oracle.o11r2
Event Category:      None
Event ID:     34
Date:         8/27/2012
Time:         12:29:10
User:         N/A
Computer:     INTEGER

Description:
Audit trail:
LENGTH: "351"
SESSIONID:[5] "62722"
ENTRYID:[1] "1"
STATEMENT:[2] "20"
USERID:[7] "SCOTT"
USERHOST:[17] "WORKGROUP\INTEGER"
TERMINAL:[7] "INTEGER"
ACTION:[3] "103"
RETURNCODE:[1] "0"
OBJ$CREATOR:[2] "HR"
OBJ$NAME:[9] "EMPLOYEES"
SES$ACTIONS:[16] "---------S------"
SES$TID:[5] "73948"
OS$USERID:[19] "INTEGER\Madhurendra"
DBID:[10] "3533758454"
PRIV$USED:[2] "47".


Now take a look at XML and
XML_EXTENDED options. Set the AUDIT_TRAIL parameter and restart the database.

dbadmin@O11R1>ALTER SYSTEM SET AUDIT_TRAIL=xml SCOPE=spfile;
System altered.

sys@O11R1>SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

sys@O11R1>startup
ORACLE instance started.

Total System Global Area    962527232 bytes
Fixed Size                    1303972 bytes
Variable Size               562039388 bytes
Database Buffers            394254576 bytes
Redo Buffers                  4919296 bytes
Database mounted.
Database opened.

dbadmin@O11R1>SHOW PARAMETER AUDIT_TRAIL;
NAME                 TYPE           VALUE
-----------------    ----------     --------------
audit_trail          string         XML

dbadmin@O11R1>AUDIT CREATE SESSION BY scott;
Audit succeeded.

User scott connect to database.

[oracle@ora ~]$ sqlplus scott

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Aug 27 05:55:46 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

scott@O11R1>

You can find xml file under adump directory. The following is the content of the XML file generated:

<?xml version="1.0" encoding="UTF-8"?>
<Audit xmlns="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-11_1.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-11_1.xsd">

<Version>11.1</Version>
<AuditRecord>
   <Audit_Type>1</Audit_Type>
   <Session_Id>60119</Session_Id>
   <StatementId>1</StatementId>
   <EntryId>1</EntryId>
   <Extended_Timestamp>2012-08-27T05:55:46.445298</Extended_Timestamp>
   <DB_User>SCOTT</DB_User>
   <OS_User>oracle</OS_User>
   <Userhost>ora.home.com</Userhost>
   <OS_Process>6833</OS_Process>
   <Terminal>pts/2</Terminal>
   <Instance_Number>0</Instance_Number>
   <Action>100</Action>
   <TransactionId>0000000000000000</TransactionId>
   <Returncode>0</Returncode>
   <Scn>617554</Scn>
   <Comment_Text>Authenticated by: DATABASE</Comment_Text>
</AuditRecord>
</Audit>

The last option is XML_EXTENDED. Set the AUDIT_TRAIL value to XML_EXTENDED or XML, EXTENDED and restart the database.

dbadmin@O11R1>SHOW PARAMETER AUDIT_TRAIL;
NAME           TYPE        VALUE
-------------- ----------- ---------------
audit_trail    string      XML_EXTENDED

dbadmin@O11R1>AUDIT INSERT TABLE, DELETE TABLE BY user$plsql BY ACCESS;
Audit succeeded.

User$plsql@O11R1>INSERT INTO test VALUES('011');
1 row inserted.

User$plsql@O11R1>DELETE FROM test;
1 row deleted.

A XML_EXTENDED record looks like the following and include the SQL TEXT.

<?xml version="1.0" encoding="UTF-8"?>
<Audit xmlns="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-11_1.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-11_1.xsd">

<Version>11.1</Version>
<AuditRecord>
   <Audit_Type>1</Audit_Type>
   <Session_Id>60131</Session_Id>
   <StatementId>11</StatementId>
   <EntryId>1</EntryId>
   <Extended_Timestamp>2012-08-27T06:09:45.241330</Extended_Timestamp>
   <DB_User>USER$PLSQL</DB_User>
   <OS_User>oracle</OS_User>
   <Userhost>ora.home.com</Userhost>
   <OS_Process>7115</OS_Process>
   <Terminal>pts/2</Terminal>
   <Instance_Number>0</Instance_Number>
   <Object_Schema>USER$PLSQL</Object_Schema>
   <Object_Name>TEST</Object_Name>
   <Action>2</Action>
   <TransactionId>0800130001020000</TransactionId>
   <Returncode>0</Returncode>
   <Scn>618556</Scn>
   <Sql_Text>
   insert into test values('011')
   </Sql_Text>
</AuditRecord>

<AuditRecord>
   <Audit_Type>1</Audit_Type>
   <Session_Id>60131</Session_Id>
   <StatementId>21</StatementId>
   <EntryId>3</EntryId>
   <Extended_Timestamp>2012-08-27T06:10:20.086341</Extended_Timestamp>
   <DB_User>USER$PLSQL</DB_User>
   <OS_User>oracle</OS_User>
   <Userhost>ora.home.com</Userhost>
   <OS_Process>7115</OS_Process>
   <Terminal>pts/2</Terminal>
   <Instance_Number>0</Instance_Number>
   <Object_Schema>USER$PLSQL</Object_Schema>
   <Object_Name>TEST</Object_Name>
   <Action>7</Action>
   <TransactionId>0800130001020000</TransactionId>
   <Returncode>0</Returncode>
   <Scn>618592</Scn>
   <Sql_Text>
   delete from test
   </Sql_Text>
</AuditRecord>
</Audit>

Hope you will enjoy. Happy Learning 🙂

Advertisements

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: