Auditing in XMLBy Arup Nanda
Create database audit trails in XML.
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);
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 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;
Examining the Audit Trail
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>
<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 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>;
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;
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;
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
After extended auditing was set and the database was restarted, SCOTT issued another SELECT statement:
SELECT * FROM accounts WHERE accno = :i;
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>
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.
Examining the Files in Relational View
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;
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
Protecting Even Further
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
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:
Oct 13 01:26:55 oradba Oracle Audit: 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"
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.
Arup Nanda (email@example.com) has been an Oracle DBA since 1993, handling all aspects of database administration—from performance tuning to security and disaster recovery. He is a coauthor of the book PL/SQL for DBAs (O'Reilly Media). He was the Oracle Magazine DBA of the Year in 2003.