Auditing in XML

Create database audit trails in XML.

January/February 2006

One of the cornerstones of the security infrastructure in Oracle Database is accountability—the ability to record who did what. Oracle Database 10g provides auditing to record the activities of users in the database. When the activities occur (for example, a user updates a certain table), the database records the facts in the audit trails, which can be in the database in a special table called AUD$ in the SYS schema or in special files in the operating system (OS). When stored in the database, audit trail data is protected by database backups, and it is easy for a DBA to query it by using standard SQL. However, with this data in the database, anyone with access to the SYS schema can potentially erase the audit trail after perpetrating a malicious update.

OS-based audit trails are owned by the Oracle software owner, so storing audit trail data in special OS files is one way to protect it from those with SYS access. You can have individual OS accounts for DBAs that allow them to administer the database and even have SYSDBA privileges, but SYSDBA privileges do not give DBAs the ability to remove or alter the audit trail files from the server directly. With careful, limited OS and database access, using file system audit trail files may satisfy the security and compliance requirements of many organizations.

Another advantage of OS-based audit trails is that they are available even if the database is down. A disadvantage of such an audit trail is the fact that the audit trail files have to be parsed—using a tool suitable for that OS and the audit file format—to be interpreted.

In Oracle Database 10g Release 2, the OS audit functionality has been extended to create file-based audit trails in standard XML format. Because XML is easily recognizable and many tools (running on many OSs) are available to read and format it, these audit trails are easy to handle. There is also a SQL interface to the audit trails for easy querying. In this article, I will show you how to set up the audit trails in XML format and how to use them effectively.

By default, standard auditing is not set in Oracle Database 10g Release 2. To enable standard auditing and write audit trails in XML format, all you have to do is put the following line in the initialization file:

AUDIT_TRAIL = XML

This is a static parameter, so you will have to restart the database for it to take effect.

Set up the sample data for this article by running the following as SYSDBA:

SQL> CREATE USER bank IDENTIFIED BY bank;
SQL> GRANT CONNECT, RESOURCE TO bank;
SQL> CONNECT bank/bank
SQL> CREATE TABLE accounts (accno NUMBER);
SQL> GRANT SELECT ON accounts TO SCOTT;
SQL> INSERT INTO accounts VALUES (104);

Next, enable auditing for the appropriate table. Here you want to audit if anyone selects from the ACCOUNTS table in the BANK schema. To do so, issue the following:

AUDIT SELECT ON bank.accounts;

The BANK user (the user who owns the table) or any other user with the AUDIT ANY system privilege can issue this statement and set up auditing for the table. After this step, when any user with SELECT privileges on this table selects anything from it, the facts are recorded in the audit trail. For instance, if the SCOTT user connects to the database and selects something from the table by issuing

CONNECT scott/tiger
...
SELECT * FROM bank.accounts WHERE accno = 104;

the SELECT statement generates an audit trail. And because the AUDIT_TRAIL parameter is set to XML, the trail is generated as an XML file.

The audit trail files are written in the directory that the AUDIT_FILE_DEST initialization parameter specifies, which defaults to $ORACLE_BASE/admin/$ORACLE_SID/adump. You can change the location dynamically, without restarting the database. If you want to create the files in another directory, such as /audit_trail, for example, issue the following (as SYSDBA):

ALTER SYSTEM SET AUDIT_FILE_DEST = '/audit_trail' DEFERRED; 

Audit trail entries for sessions that started after this command was given will go to this directory.

Now that you know where the audit trail is generated, you can examine the trail. It will be an XML file in the directory that the AUDIT_FILE_DEST initialization parameter specifies. Listing 1 shows the XML file generated by the action (the SELECT statement) of the SCOTT user mentioned earlier. Let's see how to interpret the file.

Code Listing 1: Audit trail in XML format

<?xml version="1.0" encoding="UTF-8" ?>
<Audit xmlns="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-10_2.xsd" xmlns:
xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/
oracleas/schema/dbserver_audittrail-10_2.xsd">
  <Version>10.2</Version>
  <AuditRecord>
    <Audit_Type>1</Audit_Type>
    <Session_Id>108802</Session_Id>
    <StatementId>9</StatementId>
    <EntryId>1</EntryId>
    <Extended_Timestamp>2005-10-09T00:20:02.284327</Extended_Timestamp>
    <DB_User>SCOTT</DB_User>
    <OS_User>oracle</OS_User>
    <Userhost>prolin1</Userhost>
    <OS_Process>22158</OS_Process>
    <Terminal>pts/3</Terminal>
    <Instance_Number>0</Instance_Number>
    <Object_Schema>BANK</Object_Schema>
    <Object_Name>ACCOUNTS</Object_Name>
    <Action>103</Action>
    <Returncode>0</Returncode>
    <Scn>6447392335</Scn>
    <SesActions>---------S------</SesActions>
  </AuditRecord>
</Audit>

The recording of the audit trail follows the regular XML style shown below:

<Audit>
  <Audit_Record>
      <Audit_Type>...
      <Session_Id>...
      <StatementId>...
      <EntryId>...
      <Extended_Timestamp>...
      <DB_User>...
      <OS_User>...
      <Userhost>...
      <OS_Process>...
      <Terminal>...
      <Instance_Number>...
      <Object_Schema>...
      <Object_Name>...
      <Action>...
      <Returncode>...
      <Scn>...
      <SesActions>...
  </Audit_Record>
</Audit>

The audit trail is contained inside the tags <Audit_Record> and </Audit_Record>, with the specific tags showing the detailed information captured in the trail. For example, <DB_User> shows the database user that initiated the action that produced the trail. If a session initiated more than one action, then the file shows a set of details for each activity enclosed by the <Audit_Record> tags.

The first tag for each <Audit_Record> is <Audit_Type>, which indicates the type of the audit trail. In Listing 1, the value of <Audit_Type> is 1, which indicates standard XML audit. You can use XML format for fine-grained auditing as well, in which case the tag will show the value 2. If you have enabled auditing of SYS operations ( SYS operations are not audited by default), by setting the AUDIT_SYS_OPERATIONS initialization parameter to TRUE, then the tag will show the value 4. Finally, mandatory XML audit trails show a value of 8. Examples of mandatory audit trails are the ones produced during database startup and shutdown, activities that generate an audit trail regardless of the setting of the AUDIT_TRAIL initialization parameter. All the records in an XML audit trail include this tag; it helps to differentiate the type of audit trail.

The next tag, <Session_Id>, shows the audit session ID (not the database SID) of the session that produced the trail. Note that you can see the audit session ID of a session by issuing the following query on the AUDSID column in V$SESSION:

SELECT AUDSID
FROM V$SESSION
WHERE SID = <SID>;

Inside the same session, there could be multiple statements issued by a user, each of which is shown by a separate <StatementId>. The time of the recording of the audit trail is shown in the tag <Extended_Timestamp>. In Listing 1, note the value recorded: 2005-10-09T00:20:02.284327. The value is not shown in the local time zone but in UTC (coordinated universal time, also known as GMT); hence the seemingly strange format of the time stamp.

The rest of the tags show the user who performed the actions and other relevant details of the actions. The tags <DB_User>, <OS_User>, <Userhost>, <OS_Process>, <Terminal>, <Instance_Number>, <Object_Schema>, <Object_Name>, and <Action> show the database username, the OS username, the host (or the machine) the user connected from, the OS process ID, the terminal of the host the user is connected from, the instance number the user is connected to (in case of an Oracle Real Application Clusters database), the owner of the table the user manipulated, the name of the table the user manipulated, and the action the user performed, respectively.

The result of the action audited in Listing 1 was successful, so the <Returncode> tag shows the value 0. Note that if the action were unsuccessful, the Oracle error number would have been shown as the value. For example, if you attempted to drop a table and that table didn't exist, you would have gotten an ORA-00955 error and the value in this <Returncode> tag would have been 955.

This successful action in this audit trail was performed when the System Change Number (SCN) was 6447392335, shown by the tag <Scn>. This is very useful in flashback queries to find out the values of the columns at a certain point in time. For example, suppose the value of the column BALANCE has changed significantly over a period of time. How can you know what exact value the user saw? You could use a flashback query to see the value of BALANCE at that SCN:

SELECT balance
FROM accounts
AS OF SCN 6447392335
WHERE accno = 104;

The <SesActions> tag shows what happened in the session. It has a string of 16 characters, of which the first 12 are important. It shows the result of the actions the user performed, with each position showing the result of a specific action. The 12 positions are Alter, Audit, Comment, Delete, Grant, Index, Insert, Lock, Rename, Select, Update, and Flashback. For instance, in Listing 1 the <SesActions> tag value in the trail is --------- S------ and it shows S—indicating "Success"—in the 10th position, the SELECT position. This trail indicates that the SCOTT user performed one or more successful SELECT actions in the session and no other auditable actions.

If SCOTT had also performed a successful ALTER in this session, the first position would have been S instead of "-". If the SELECT statement

select * from bank.accounts where accno = 104;

had failed, then the 10th position value would have been F—indicating "Failure"—instead of S. If SCOTT had performed more than one SELECT and some had failed and some were successful, the 10th position would have shown B (for both "Success" and "Failure").

In Listing 1, the audit trail shows the action ( SELECT) and the object the action was performed on ( ACCOUNTS). However, it does not show the exact SQL statement the SCOTT user issued. Auditing has a facility to extend the functionality to record that as well. To capture the SQL issued in the audit trail, set the following in the initialization parameter file:

AUDIT_TRAIL = XML, EXTENDED

and restart the database.

After extended auditing was set and the database was restarted, SCOTT issued another SELECT statement:

SELECT * FROM accounts WHERE accno = :i;

The full XML file of the extended audit trail is shown in Listing 2. It includes the following two additional items that are not part of the standard audit trail shown in Listing 1:

Code Listing 2: Extended XML format

<?xml version="1.0" encoding="UTF-8" ?>
<Audit xmlns="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-10_2.xsd" xmlns:
xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/
oracleas/schema/dbserver_audittrail-10_2.xsd">
  <Version>10.2</Version>
  <AuditRecord>
    <Audit_Type>1</Audit_Type>
    <Session_Id>108844</Session_Id>
    <StatementId>10</StatementId>
    <EntryId>1</EntryId>
    <Extended_Timestamp>2005-10-10T18:26:18.720548</Extended_Timestamp>
    <DB_User>SCOTT</DB_User>
    <OS_User>oracle</OS_User>
    <Userhost>prolin1</Userhost>
    <OS_Process>22584</OS_Process>
    <Terminal>pts/3</Terminal>
    <Instance_Number>0</Instance_Number>
    <Object_Schema>BANK</Object_Schema>
    <Object_Name>ACCOUNTS</Object_Name>
    <Action>103</Action>
    <Returncode>0</Returncode>
    <Scn>6447496045</Scn>
    <SesActions>---------S------</SesActions>
    <Sql_Bind>#1(3):107</Sql_Bind>
    <Sql_Text>select * from bank.accounts where accno = :i</Sql_Text>
  </AuditRecord>
</Audit>
<Sql_Bind>#1(3):107</Sql_Bind>
<Sql_Text>select * from bank.accounts where accno = :i</Sql_Text>

Here the tag <Sql_Text> shows the actual SQL issued by the SCOTT user. In this particular SQL statement, there is a bind variable ( :i). The value of the bind variable is captured in the tag <Sql_Bind>. The value is shown in the format #VariablePosition(LengthOfVarVal):ValueofBindVar. Here the value shows that there is only one bind variable ( #1), that the value inside was three characters long ( 3), and that the value of the bind variable was 107. Using the extended XML audit, you can capture SQL statements and values of bind variables used.

Note that the extended XML audit included only the SQL statement that SCOTT ran after setting AUDIT_TRAIL=XML, EXTENDED and restarting the database. The XML audit in Listing 1 was for a different session; the XML audit in Listing 2 is for the current session.

The XML files produced in the audit trail are regular OS files, which can be viewed by any XML viewer, but you may miss the old familiar database-based audit trail that you could query by using SQL. No worries; you can still use regular SQL to query data that's written to those XML audit trail files. A new data dictionary view— V$XML_AUDIT_TRAIL—exposes the contents of the files. To see the audit trail information, you can select all columns from V$XML_AUDIT_TRAIL, as follows:

SELECT * FROM
V$XML_AUDIT_TRAIL;

Code Listing 3: Contents of V$XML_AUDIT_TRAIL

AUDIT_TYPE                      : 1
SESSION_ID                      : 108844
PROXY_SESSIONID                 : 0
STATEMENTID                     : 10
ENTRYID                         : 1
EXTENDED_TIMESTAMP              : 10-OCT-05 06.26.18.720548 PM -04:00
GLOBAL_UID                      :
DB_USER                         : SCOTT
CLIENTIDENTIFIER                :
EXT_NAME                        :
OS_USER                         : oracle
OS_HOST                         : prolin1
OS_PROCESS                      : 22584
TERMINAL                        : pts/3
INSTANCE_NUMBER                 : 0
OBJECT_SCHEMA                   : BANK
OBJECT_NAME                     : ACCOUNTS
POLICY_NAME                     :
NEW_OWNER                       :
NEW_NAME                        :
ACTION                          : 103
STATEMENT_TYPE                  : 0
TRANSACTIONID                   :
RETURNCODE                      : 0
SCN                             : 6447496045
COMMENT_TEXT                    :
AUTH_PRIVILEGES                 :
GRANTEE                         :
PRIV_USED                       : 0
SES_ACTIONS                     : ---------S------
OS_PRIVILEGE                    :
ECONTEXT_ID                     :
SQL_BIND                        :  #1(3):107
SQL_TEXT                        : select * from bank.accounts where accno = :i

The result of this query is shown in Listing 3, in a vertical format for easy viewing. Note that if there were multiple XML audit trail records, you would see one record in this view per XML audit record. All the columns of the view are the same as the tags in the XML audit trail file; for example, <DB_User> in the file is shown as DB_USER in the view. The time stamp is shown in the EXTENDED_TIMESTAMP column, but the value is shown in local time, not in UTC as in the XML audit trail file. Columns that are not populated in the XML file display NULL values in the view.

Naturally, you want to tighten the security of the audit infrastructure to augment accountability. The above setup has one potential issue—anyone with the execution privilege on the UTL_FILE- supplied package can remove the audit trail file from the OS, using the FREMOVE procedure. To mitigate that risk, you can limit the capabilities through the use of the UTL_FILE package, by either

• Revoking the privilege from PUBLIC

• Revoking the CREATE DIRECTORY system privilege from PUBLIC

The first choice is a little drastic, but it is a sure way to limit the risk. The second is probably more practical. To remove the file, users must have access to or be able to create a directory object defined on that OS directory. If you revoke the CREATE DIRECTORY system privilege from PUBLIC, only users with the DBA role can create the directory and it's not possible for typical users to create the directory on the OS directory where the audit trails exist. If they cannot create the directory object, they cannot delete the file with UTL_FILE. The CREATE DIRECTORY system privilege should be revoked from PUBLIC anyway, as a best practice.

The OS user who owns the Oracle software still owns the XML audit trail files, and anyone with access to the server and that user account can delete them. However, by limiting the privileges, you can achieve a reasonable level of security.

Oracle offers another type of auditing. This type causes the audit trails to be written to the OS system logs (syslogs), which are owned by the superuser (such as "root" in UNIX systems) and cannot be erased by any other user, including the Oracle software owner.

The above options will not work if the owner of the database software, typically the "oracle" user, decides to remove all traces of these XML audit trail files. To protect them further, you can use the OS-level syslog feature. Syslog writes messages into a special file owned by the superuser of the OS (the "root" user). Since it's owned by root, no one else can remove it. You can set parameters so that XML audit trails are written to the syslog. You have to set the following initialization parameter values and restart the database:

AUDIT_TRAIL=OS
AUDIT_SYSLOG_LEVEL=USER.ALERT

All the syslog entries are written as those of a facility (kernel, user, and so on) and at a certain level (such as notice, warning, error, and so on). The second line of code above indicates that the audit trail entries are written as facility user and at level alert. If you don't specify anything else, the alert log entries will go into the default message file of the server, which is usually /var/log/messages for a Linux server. However, since all messages including those from the OS itself go to that file, you may want to create a different file—say, audit.log—for this purpose only. Place the file location for the facility in the syslog configuration file, usually located at /etc/syslog.conf, as shown below:

user.alert /var/log/audit.log

This indicates that the messages of the facility user at the level alert should go to the /var/log/audit.log file. Now restart the syslog process. The actual command varies from system to system, and your system administrator has to supply the right command. As the UNIX "root" user, issue this command:

/etc/init.d/syslog restart

This restarts the syslog process and enables it to write the messages of facility user and level alert to the /var/log/audit.log file. After this, when the user selects from the ACCOUNTS table, the following line will be present in that file:

Oct 13 01:26:55 oradba Oracle Audit[28955]: SESSIONID: "25386"
ENTRYID: "1" STATEMENT: "8" USERID: "SCOTT" USERHOST: "prolin1"
TERMINAL: "pts/2" ACTION: "103" RETURNCODE: "0" OBJ$CREATOR: "ARUP" OBJ$NAME:
"ACCOUNTS" SES$ACTIONS: "---------S------"
SES$TID: "76564" OS$USERID: "oracle"

Unfortunately, this is not in XML format, but the actions are quite understandable. This file is owned by root; hence the oracle user cannot remove or alter it, providing very good security.

XML audit trails in Oracle Database 10g Release 2 give you the best of both worlds—an audit trail separate from the database for enhanced security and the same familiar SQL interface to query the data, which improves productivity. This is very useful in achieving compliance for many security laws and requirements. You can use many publicly available third-party XML parsers, and you—or possibly others in a department that requires oversight of your team but lacks SQL query abilities—can use XML parsers with stylesheets to create custom reports based on the audit trail files.