9 Verifying Security Access with Auditing

What Is Standard Auditing?

In standard auditing, you audit SQL statements, privileges, schema objects, and network activity. You configure standard auditing by using the AUDIT SQL statement and NOAUDIT to remove this configuration. You can write the audit records to either the database audit trail or to operating system audit files.

Who Can Perform Standard Auditing?

Any user can configure auditing for the objects in his or her own schema, by using the AUDIT statement. To undo the audit configuration for this object, the user can use the NOAUDIT statement. No additional privileges are needed to perform this task. Users can run AUDIT statements to set auditing options regardless of the AUDIT_TRAIL parameter setting. If auditing has been disabled, the next time it is enabled, Oracle Database will record the auditing activities set by the AUDIT statements. "Enabling or Disabling the Standard Audit Trail" explains how to enable standard auditing.

Note the following:

• To audit objects in another schema, the user must have the AUDIT ANY system privilege.

• To audit system privileges, the user must have the AUDIT SYSTEM privilege.

• If the O7_DICTIONARY_ACCESSIBILITY initialization parameter has been set to FALSE (the default), then only users who have the SYSDBA privilege can perform DML actions on the audit data in the SYS.AUD$ and SYS.FGA_LOG$ tables. For greater security, set the O7_DICTIONARY_ACCESSIBILITY parameter to FALSE so that non-SYSDBA users cannot audit SYS objects.

When Are Standard Audit Records Created?

You, as the security administrator, enable or disable standard auditing for the entire database. If it is disabled, then no audit records are created. Configuring audit options is described in the previous section, "Who Can Perform Standard Auditing?"

When auditing is enabled in the database and an action configured to be audited occurs, Oracle Database generates an audit record during or after the execution phase of the SQL statement. Oracle Database individually audits SQL statements inside PL/SQL program units, as necessary, when the program unit is run.

The generation and insertion of an audit trail record is independent of a user transaction being committed. That is, even if a user transaction is rolled back, the audit trail record remains committed.

Statement and privilege audit options in effect at the time a database user connects to the database remain in effect for the duration of the session. When the session is already active, setting or changing statement or privilege audit options does not take effect in that session. The modified statement or privilege audit options take effect only when the current session ends and a new session is created.

In contrast, changes to schema object audit options become immediately effective for current sessions.

Enabling or Disabling the Standard Audit Trail

You enable the standard audit trail by setting the AUDIT_TRAIL initialization parameter. This setting determines whether to create the audit trail in the database audit trail, write the audit activities to an operating system file, or to disable auditing.

To enable or disable the standard audit trail, log in to SQL*Plus with administrative privileges, and use the ALTER SYSTEM statement. Afterwards, you need to restart the database instance.

To check the current value of the AUDIT_TRAIL parameter, use the SHOW PARAMETER command in SQL*Plus.

Example 9-1 shows how to check the AUDIT_TRAIL parameter setting.

SHOW PARAMETER AUDIT_TRAIL

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
audit_trail                          string      DB

Example 9-2 shows how to log onto SQL*Plus, enable the standard audit trail, and then restart the database instance.

CONNECT SYSTEM
Enter password: password

ALTER SYSTEM SET AUDIT_TRAIL=DB SCOPE=SPFILE;
System altered.

CONNECT SYS/AS SYSOPER
Enter password: password

SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.

STARTUP
ORACLE instance started.

This example uses the SCOPE clause because the database instance had been started using a server parameter file (SPFILE). Starting the database with a server parameter file is the preferred way of starting a database instance. See Oracle Database Administrator's Guide for information about creating configuring server parameter files.

Settings for the AUDIT_TRAIL Initialization Parameter

Table 9-2 lists the settings you can use for the AUDIT_TRAIL initialization parameter.

ALTER SYSTEM SET AUDIT_TRAIL=DB,EXTENDED SCOPE=SPFILE;
ALTER SYSTEM SET AUDIT_TRAIL=DB, EXTENDED SCOPE=SPFILE;
ALTER SYSTEM SET AUDIT_TRAIL='DB','EXTENDED' SCOPE=SPFILE;
ALTER SYSTEM SET AUDIT_TRAIL=EXTENDED,DB SCOPE=SPFILE;
ALTER SYSTEM SET AUDIT_TRAIL=EXTENDED, DB SCOPE=SPFILE;

ALTER SYSTEM SET AUDIT_TRAIL='DB, EXTENDED' SCOPE=SPFILE;

ALTER SYSTEM SET AUDIT_TRAIL=XML, EXTENDED SCOPE=SPFILE;
ALTER SYSTEM SET AUDIT_TRAIL='XML','EXTENDED' SCOPE=SPFILE;

ALTER SYSTEM SET AUDIT_TRAIL='XML, EXTENDED' SCOPE=SPFILE;

Note the following:

• You do not need to restart the database after you run the AUDIT or NOAUDIT statements. You only need to restart the database if you made a universal change, such as changing the AUDIT_TRAIL initialization parameter.

• You do not need to set AUDIT_TRAIL to enable either fine-grained auditing or SYS auditing. For fine-grained auditing, you add and remove fine-grained audit policies as necessary, applying them to the specific operations or objects you want to monitor. To enable SYS auditing, set the AUDIT_SYS_OPERATIONS parameter to TRUE.

About the Operating System Trail

As an alternative to creating standard audit records in the DBA_AUDIT_TRAIL (SYS.AUD$ table), you can create standard audit records in operating system files. The operating system file that contains the audit trail can include any of the following data:

• Database audit trail records

• Mandatory audit records (that is, database actions that are always audited)

• Audit records for administrative users (SYS)

You can write the operating system audit records to either a text file or an XML file.

What Do Operating System Audit Trail Records Look Like?

The operating system audit trail files are in either text or XML file format. Be aware that the contents of the text and XML operating system files have some differences, and that the formats may change across different releases. With each release of Oracle Database, new enhancements, such as the audit type, have been made to the XML file, but not the text file. The text operating system file has a different presentation for the timestamp, for example:

Wed May  6 00:57:36 2009 -07:00

However, this timestamp does not appear in the event log or syslog, which have their own format for timestamps. The timestamp string only appears in the text operating system audit files.

Example 9-3 shows a typical text operating system audit trail for a logon operation on an Oracle database that is installed on Microsoft Windows. (The text in the actual record wraps around, but for this manual, each item is separated onto its own line for easier readability.)

Audit trail: 
LENGTH: "349" 
SESSIONID:[5] "43464" 
ENTRYID:[1] "1" 
STATEMENT:[1] "1" 
USERID:[6] "DBSNMP" 
USERHOST:[7] "SHOBEEN" 
TERMINAL:[3] "MAU" 
ACTION:[3] "100" 
RETURNCODE:[1] "0" 
COMMENT$TEXT:[97] "Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.0.2.4)(PORT=2955))" 
OS$USERID:[19] "NT AUTHORITY\SYSTEM" 
DBID:[10] "1212547373" 
PRIV$USED:[1] "5"

In this example:

• LENGTH refers to the total number of bytes used in this audit record. This number includes the trailing newline bytes (\n), if any, at the end of the audit record.

• [] brackets indicate the length of each value for each audit entry. For example, the USERID entry, DBSNMP, is 6 bytes long.

• SESSIONID indicates the audit session ID number. You can also find the session ID by querying the AUDSID column in the V$SESSION data dictionary view.

• ENTRYID indicates the current audit entry number, assigned to each audit trail record. The audit ENTRYID sequence number is shared between fine-grained audit records and regular audit records.

• STATEMENT is a numeric ID assigned to the statement the user runs. It appears for each statement issued during the user session, because a statement can result in multiple audit records.

• ACTION is a numeric value representing the action the user performed. The corresponding name of the action type is in the AUDIT_ACTIONS table. For example, action 100 refers to LOGON.

• RETURNCODE indicates if the audited action was successful. 0 indicates success. If the action fails, the return code lists the Oracle Database error number. For example, if you try to drop a non-existent table, the error number is ORA-00903 invalid table name, which in turn translates to 903 in the RETURNCODE setting.

• COMMENT$TEXT indicates additional comments about the audit record. For example, for LOGON audit records, it can indicate the authentication method.It corresponds to the COMENT_TEXT column of the DBA_COMMON_AUDIT_TRAIL data dictionary view.

• DBID is a database identifier calculated when the database is created. It corresponds to the DBID column of the V$DATABASE data dictionary view.

• ECONTEXT_ID indicates the application execution context identifier.

• PRIVS$USED refers to the privilege that was used to perform an action. To find the privilege, query the SYSTEM_PRIVILEGE_MAP table. For example, privilege 5 refers to -5 in this table, which means CREATE SESSION. PRIVS$USED corresponds to the PRIV_USED column in the DBA_COMMON_AUDIT_TRAIL, which lists the privilege by name.

Other possible values are as follows:

• SCN (for example, SCN:8934328925) indicates the System Change Number (SCN). Use this value if you want to perform a flashback query to find the value of a setting (for example, a column) at a time in the past. For example, to find the value of the ORDER_TOTAL column of the OE.ORDERS table based on the SCN number, use the following SELECT statement:

  SELECT ORDER_TOTAL 
  FROM OE.ORDERS
  AS OF SCN = 8934328925
  WHERE ORDER_TOTAL = 86;
  

• SES_ACTIONS indicates the actions that took place during the session. This field is present only if the event was audited with the BY SESSION clause. Because this field does not explain in detail the actions that occurred during the session, you should configure the audit event with the BY ACCESS clause.

  The SES_ACTIONS field contains 16 characters. Positions 14, 15, and 16 are reserved for future use. In the first 12 characters, each position indicates the result of an action. They are: ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE, and FLASHBACK. For example, if the user had successfully run the ALTER statement, the SES_ACTIONS setting is as follows:

  S---------------
  

  The S, in the first position (for ALTER), indicates success. Had the ALTER statement failed, the letter F would have appeared in its place. If the action resulted in both a success and failure, then the letter is B.

• SES$TID indicates the ID of the object affected by the audited action.

• SPARE2 indicates whether the user modified SYS.AUD$ table. 0 means the user modified SYS.AUD$; otherwise, the value is NULL.

Similarly, Example 9-4 shows how an XML audit trail record appears. The text wraps around in the actual record, but for this manual, each element appears on its own line for easier readability. To find all the tags that appear in the XML audit file, you can view its schema in a Web browser at

http://www.oracle.com/technology/oracleas/schema/dbserver_audittrail-11_2.xsd

<?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>43535</Session_Id>
       <StatementId>1</StatementId>
       <EntryId>1</EntryId>
       <Extended_Timestamp>2009-04-29T18:32:26.062000Z</Extended_Timestamp>
       <DB_User>SYSMAN</DB_User>
       <OS_User>SYSTEM</OS_User>
       <Userhost>shobeen</Userhost>
       <OS_Process>3164:3648</OS_Process>
       <Terminal>mau</Terminal>
       <Instance_Number>0</Instance_Number>
       <Action>100</Action>
       <TransactionId>0000000000000000</TransactionId> 
       <Returncode>0</Returncode>
       <Comment_Text>Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.0.2.4)(PORT=3536))</Comment_Text>
       <Priv_Used>5</Priv_Used>
</AuditRecord>
</Audit>

In this example:

• AuditRecord element contains the entire audit record. (See Example 9-3 for more information about the elements within the Audit_Record element.)

• Audit_Type indicates the type of audit trail. Possible values are as follows:

  • 1: Standard audit record

  • 2: Fine-grained audit record

  • 4: SYS audit record

  • 8: Mandatory audit record

  This field only appears in the XML audit files, not the OS text audit files.

• Extended_Timestamp indicates the time of the audited operation (timestamp of user login for entries created by AUDIT SESSION), in Coordinated Universal Time (UTC) or Greenwich Mean Time (GMT). This field only appears in the XML audit files, not the OS text audit files.

• Instance_Number indicates the instance number to which the user is connected, for an Oracle Real Application Clusters environment. In this example, the number is 0, which is used for single-instance database installations. The INSTANCE_NUMBER initialization parameter specifies this number.

The following values can appear if you set the AUDIT_TRAIL parameter to XML, EXTENDED. Both are listed in the DBA_COMMON_AUDIT_TRAIL data dictionary view.

• Sql_Bind (for example, <Sql_Bind>#1(5):89</Sql_Bind>) shows the value of the bind variable. The syntax is as follows:

  VariablePosition(LengthOfVariableValue):ValueofBindVariable
  

  The example #1(5):89 indicates that there is 1 bind variable; its value is 5 characters long; and the value of the bind variable is 89.

• Sql_Text (for example, <Sql_Text>begin procedure_one(:num); end; </Sql_Text>) appears if you have set the AUDIT_TRAIL parameter to XML, EXTENDED. It shows the SQL text that the user entered.

Advantages of the Operating System Audit Trail

Using the operating system audit trail offers these advantages:

• It reduces the likelihood of a denial-of-service (DoS) attack.

• It makes it easier to secure the audit trail. If the auditor is distinct from the database administrator, then you must use the OS, XML, or XML, EXTENDED setting. Otherwise, a database administrator can view and modify any auditing information that is stored in the database.

• Because you are writing the audit trail to a specific location that you can restrict to specific users, the operating system audit trail enforces separation of duty concepts.

• Writing the audit trail to an operating system file results in the least amount of overhead on the database. For this reason, it is excellent for very large databases.

• Audit records stored in operating system files can be more secure than database-stored audit records because access can require file permissions that database administrators do not have. Greater availability is another advantage to operating system storage for audit records, because they remain available even if the database is temporarily inaccessible.

• If the AUDIT_TRAIL initialization parameter is set to XML (or XML, EXTENDED), then Oracle Database writes audit records to the operating system as XML files. You can use the V$XML_AUDIT_TRAIL view to make XML audit records available to database administrators through a SQL query, providing enhanced usability.

• The DBA_COMMON_AUDIT_TRAIL view includes the standard and fine grained audit trails written to database tables, XML-format audit trail records, and the contents of the V$XML_AUDIT_TRAIL dynamic view (standard, fine grained, SYS and mandatory).

• Using your operating system audit trail can enable you to consolidate audit records from multiple sources, including Oracle Database and other applications. Examining system activity can be more efficient with all audit records in one place. If you use XML audit records, then you can use of any standard XML editing tool to review or extract information from those records.

How the Operating System Audit Trail Works

The operating system audit trail writes the audit data to an operating system file. You can enable this feature by setting the AUDIT_TRAIL initialization parameter to one of the following values:

• OS: Writes the audit trail records to a text operating system file on UNIX systems and to the applications Event Viewer on Microsoft Windows.

• XML: Writes the audit trail records to an XML file.

• XML, EXTENDED: Writes the audit trail records to an XML file and includes SQL text and SQL bind information in the operating system XML audit files.

The AUDIT_FILE_DEST initialization parameter sets the location of the operating system audit file. If you want to audit top-level statements issued by users who log in to the database with the SYSDBA or SYSOPER privilege, then set the AUDIT_SYS_OPERATIONS parameter to TRUE. See Table 9-2, "AUDIT_TRAIL Initialization Parameter Settings" for more information about these settings.

The records that are written to an operating system file are not recorded to the SYS.AUD$ and SYS.FGA_LOG$ tables. You can still view the contents of the XML operating system audit files by querying the DBA_COMMON_AUDIT_TRAIL data dictionary views. Querying this view parses all XML files (all files with an .xml extension) in the AUDIT_FILE_DEST directory, and then presents them in relational table format. Because XML is a standard document format, many utilities are available to parse and analyze XML data. Consult the operating system-specific Oracle Database documentation to find if this feature has been implemented on your operating system.

Specifying a Directory for the Operating System Audit Trail

Use the AUDIT_FILE_DEST initialization parameter to specify an operating system directory into which the audit trail is written, when the AUDIT_TRAIL initialization parameter is set to OS, XML, or XML, EXTENDED. You must set AUDIT_FILE_DEST to a valid directory with permissions restricted to the owner of the Oracle software and the DBA group. Mandatory auditing information also goes into that directory, as do audit records for user SYS if the AUDIT_SYS_OPERATIONS initialization parameter is specified. You can change the AUDIT_FILE_DEST parameter by using the following ALTER SYSTEM statement, which enables the new destination to be effective for all subsequent sessions.

ALTER SYSTEM SET AUDIT_FILE_DEST = directory_path DEFERRED;

To find the current setting of the AUDIT_FILE_DEST parameter, issue the following command:

SHOW PARAMETER AUDIT_FILE_DEST

The location of the operating system files depends on the following:

• If the database is not running and you have not set the AUDIT_FILE_DEST parameter, then the operating system files are placed in the first default location $ORACLE_BASE/admin/$ORACLE_SID/adump directory.

• If the database is not running and the first default location, the $ORACLE_BASE/admin/$ORACLE_SID/adump directory, is inaccessible or cannot be written to, or the Oracle process cannot identify the environment variables, then the second default location, $ORACLE_HOME/rdbms/audit is used.

• When the database is open and Oracle Database reads the initialization file (initSID.ora) for the database instance, the value of AUDIT_FILE_DEST parameter is used as the operating system audit file directory.

• For UNIX and Solaris systems, all operating system files are written to a directory in the operating system. For Windows, the operating system text records are available from the Windows Event Viewer, but operating system XML files are available from an operating system directory, as explained in the preceding bulleted items.

About the Syslog Audit Trail

A potential security vulnerability for the operating system audit trail is that a privileged user, such as a database administrator, can modify or delete database audit records. To minimize this risk, you can use a syslog audit trail. Syslog is a standard protocol on UNIX-based systems for logging information from different components of a network. Applications call the syslog() function to log information to the syslog daemon, which then determines where to log the information. You can configure syslog to log information to a file or to a dedicated host by editing the syslog.conf file. You can also configure syslog to alert a specified set of users when information is logged.

Because applications, such as an Oracle process, use the syslog() function to log information to the syslog daemon, a privileged user would not have permissions to the file system where syslog messages are logged. For this reason, audit records stored using a syslog audit trail can be more secure than audit records stored using an operating system audit trail. In addition to restricting permissions to a file system for a privileged user, for a syslog audit trail to be secure, neither privileged users nor the Oracle process should have root access to the system where the audit records are written.

Format of the Information Stored in the Syslog Audit Trail

Similar to the operating system audit trail records, Oracle Database encodes the syslog records to ensure greater security. If you have Oracle Audit Vault installed, you can use its Syslog Collector to extract and transfer syslog audit records to centralized Oracle Audit Vault server.

What Does the Syslog Audit Trail Look Like?

Example 9-5 shows how the syslog audit trail can appear. (For this example, the text has been reformatted for easier readability. In reality, the text is all on one line.) As with other Oracle Database audit trails, the brackets indicate the length of the value that was audited. For syslog audit trails, the text from (and including) LENGTH: is Oracle Database audit record. The prepended text (the date and Oracle Audit [10085] line) is added by the syslog utility.

May 14 23:40:15 shobeen 
Oracle Audit[10085]: 
LENGTH : '171' 
ACTION :[18] 'select * from aud$' 
DATABASE USER:[1] '/' 
PRIVILEGE :[6] 'SYSDBA' 
CLIENT USER:[7] 'laurelh' 
CLIENT TERMINAL:[6] 'pts/12' 
STATUS:[1] '0' 
DBID:[9] '562317007' 

Configuring Syslog Auditing

To enable syslog auditing, follow these steps:

1. Assign the value of OS to the AUDIT_TRAIL initialization parameter, as described in "Enabling or Disabling the Standard Audit Trail".

   For example:

   ALTER SYSTEM SET AUDIT_TRAIL=OS SCOPE=SPFILE;
   

2. Manually set the AUDIT_SYSLOG_LEVEL parameter to the initialization parameter file, initsid.ora.

   Set the AUDIT_SYSLOG_LEVEL parameter to specify a facility and priority in the format AUDIT_SYSLOG_LEVEL=facility.priority.

   • facility: Describes the part of the operating system that is logging the message. Accepted values are user, local0–local7, syslog, daemon, kern, mail, auth, lpr, news, uucp, and cron.

     The local0–local7 values are predefined tags that enable you to sort the syslog message into categories. These categories can be log files or other destinations that the syslog utility can access. To find more information about these types of tags, refer to the syslog utility MAN page.

   • priority: Defines the severity of the message. Accepted values are notice, info, debug, warning, err, crit, alert, and emerg.

   The syslog daemon compares the value assigned to the facility argument of the AUDIT_SYSLOG_LEVEL parameter with the syslog.conf file to determine where to log information.

   For example, the following statement identifies the facility as local1 with a priority level of warning:

   AUDIT_SYSLOG_LEVEL=local1.warning
   

   See Oracle Database Reference for more information about AUDIT_SYSLOG_LEVEL.

3. Log in to the computer that contains the syslog configuration file, /etc/syslog.conf, with the superuser (root) privilege.

4. Add the audit file destination to the syslog configuration file syslog.conf.

   For example, assuming you had set the AUDIT_SYSLOG_LEVEL to local1.warning, enter the following:

   local1.warning /var/log/audit.log
   

   This setting logs all warning messages to the /var/log/audit.log file.

5. Restart the syslog logger:

   $/etc/rc.d/init.d/syslog restart
   

   Now, all audit records will be captured in the file /var/log/audit.log through the syslog daemon.

6. Restart the database instance:

   CONNECT SYS / AS SYSOPER
   Enter password: password
   
   SHUTDOWN IMMEDIATE
   STARTUP
   

Enabling Standard Auditing with the AUDIT SQL Statement

To configure the standard auditing option, use the AUDIT SQL statement.

Table 9-4 lists the categories in which you can use the AUDIT statement.

Auditing Statement Executions: Successful, Unsuccessful, or Both

For statement, privilege, and schema object auditing, Oracle Database permits the selective auditing of successful executions of statements, unsuccessful attempts to execute statements, or both. This enables you to monitor actions even if the audited statements do not complete successfully. Monitoring unsuccessful SQL statement can expose users who are snooping or acting maliciously, though most unsuccessful SQL statements are neither.

This method of auditing is also useful in that it reduces the audit trail, helping you to focus on specific actions. This can aid in maintaining good database performance.

The options are as follows:

• WHENEVER SUCCESSFUL clause: This clause audits only successful executions of the audited statement.

• WHENEVER NOT SUCCESSFUL clause: This clause audits only unsuccessful executions of the audited statement.

  Auditing an unsuccessful statement execution generates an audit report only if a valid SQL statement is issued but fails, because it lacks proper authorization or references a nonexistent schema object. Statements that fail to execute because they were not valid cannot be audited.

  For example, an enabled privilege auditing option set to audit unsuccessful statement executions audits statements that use the target system privilege but failed for other reasons. One example is when a CREATE TABLE auditing condition is set, but some CREATE TABLE statements fail due to insufficient quota for the specified tablespace.

• Omitting WHENEVER SUCCESSFUL or WHENEVER NOT SUCCESSFUL: If you omit these clauses, then Oracle Database audits both successful and unsuccessful executions of the audited statement.

For example:

AUDIT CREATE TABLE BY ACCESS WHENEVER NOT SUCCESSFUL;

How Standard Audit Records Are Generated

Oracle Database generates an audit record for each execution of an audited statement or operation, as follows:

• Each time the SQL statement for which auditing was configured is executed. This also includes the execution of the statements within PL/SQL procedures.

• Each time the privilege for which auditing was configured is used

• Each time the object for which auditing was configured is operated upon

How Do Cursors Affect Standard Auditing?

For each execution of an auditable operation within a cursor, Oracle Database inserts one audit record into the audit trail. Events that cause cursors to be reused include the following:

• An application, such as Oracle Forms, holding a cursor open for reuse

• Subsequent execution of a cursor using new bind variables

• Statements executed within PL/SQL loops where the PL/SQL engine optimizes the statements to reuse a single cursor

Auditing is not affected by whether or not a cursor is shared. Each user creates her or his own audit trail records on first execution of the cursor.

Benefits of Using the BY ACCESS Clause in the AUDIT Statement

By default, Oracle Database writes a new audit record for every audited event, using the BY ACCESS clause functionality. To use this functionality, either include BY ACCESS in the AUDIT statement, or if you want, you can omit it because it is the default. (As of Oracle Database 11g Release 2 (11.2.0.2), the BY ACCESS clause is the default setting.)

Oracle recommends that you audit BY ACCESS and not BY SESSION in your AUDIT statements. The benefits of using the BY ACCESS clause in the AUDIT statement are as follows:

• The audit records generated through the BY ACCESS audit option have more information, such as execution status (return code), date and time of execution, the privileges used, the objects accessed, the SQL text itself and its bind values. In addition, the BY ACCESS audit option captures the SCN for each execution and this can help flashback queries.

• Oracle Database records separately each execution of a SQL statement, the use of a privilege, and access to the audited object. Given that the values for the return code, timestamp, SQL text recorded are accurate for each execution, this can help you find how many times the action was performed.

• The BY ACCESS audit records have separate LOGON and LOGOFF entries, each with fine-grained timestamps.

For example:

AUDIT SELECT TABLE BY ACCESS;

In this scenario:

• The user jward connects to the database and issues five SELECT statements against the table named departments and then disconnects from the database.

• The user swilliams connects to the database and issues three SELECT statements against the departments table and then disconnects from the database.

The audit trail contains eight records, one recorded for each SELECT statement.

Auditing Actions Performed by Specific Users

Statement and privilege audit options can audit statements issued by any user or statements issued by a specific list of users. By focusing on specific users, you can minimize the number of audit records generated.

Example 9-6 shows how to audit statements by users scott and blake when they query or update a table or view.

AUDIT SELECT TABLE, UPDATE TABLE BY scott, blake BY ACCESS;

See Oracle Database SQL Language Reference for additional information about auditing by user.

Removing the Audit Option with the NOAUDIT SQL Statement

The NOAUDIT statement removes the audit option. Use it to reset statement and privilege audit options, and object audit options. A NOAUDIT statement that sets statement and privilege audit options can include the BY user clause to specify a list of users to limit the scope of the statement and privilege audit options.

You can use the NOAUDIT statement to disable an audit option selectively using the WHENEVER clause. If the clause is not specified, then the auditing option is disabled entirely, for both successful and unsuccessful cases.

The NOAUDIT statement does not support the BY ACCESS clause. You can remove audit options, no matter how they were turned on, by using an appropriate NOAUDIT statement.

About SQL Statement Auditing

SQL statement auditing is the selective auditing of related groups of SQL statements regarding a particular type of database structure or schema object, but not a specifically named structure or schema object.

Types of SQL Statements That Are Audited

The statements that you can audit are in the following categories:

• DDL statements. For example, AUDIT TABLE audits all CREATE and DROP TABLE statements

• DML statements. For example, AUDIT SELECT TABLE audits all SELECT ... FROM TABLE/VIEW statements, regardless of the table or view

Statement auditing can be broad or focused, for example, by auditing the activities of all database users or of only a select list of activities.

Configuring SQL Statement Auditing

Use the AUDIT statement to configure SQL statement auditing. You must have the AUDIT SYSTEM system privilege before you can enable auditing. Typically, only the security administrator is granted this system privilege.

Example 9-7 shows how to audit the SELECT TABLE SQL statement.

AUDIT SELECT TABLE BY ACCESS;

If you plan to audit all SQL statements, individual user connections, or references to non-existent objects, follow these guidelines:

• Auditing all SQL statements for individual users. You can use the ALL STATEMENTS clause to audit only the top-level SQL statements. The behavior of this audit option is different from other statement audit options. If the SQL statement is issued from inside a PL/SQL procedure, then the ALL STATEMENTS audit option does not audit it. This audit option does not affect any other AUDIT options that you may have already set.

  For example, to audit all successful statements issued by users jward and jsmith, enter the following:

  AUDIT ALL STATEMENTS BY jward, jsmith BY ACCESS WHENEVER SUCCESSFUL;
  

• Auditing all the SQL statement shortcut activities performed by individual users. You can use the ALL clause to audit all the SQL statement shortcuts listed in Table 13-1 and Table 13-2 in Oracle Database SQL Language Reference.

  For example:

  AUDIT ALL BY jward BY ACCESS;
  

• Auditing all SQL statements for the current session, regardless of user. You can use the IN SESSION CURRENT clause for ALL STATEMENTS audit option to audit top-level SQL statements in the lifetime of the user session. You cannot use the IN SESSION CURRENT clause for a specific user. You cannot use the NOAUDIT statement to cancel it, but the auditing lasts only as long as the user session lasts. When the user ends the session, the auditing ends.

  For example, to audit all unsuccessful statements in any current user session:

  AUDIT ALL STATEMENTS IN SESSION CURRENT BY ACCESS WHENEVER NOT SUCCESSFUL;
  

  You can use the AUDIT ALL STATEMENTS audit option with the IN SESSION CURRENT clause in a database logon trigger. The database logon trigger can use SYS_CONTEXT function to configure this auditing only under certain conditions, such as the time a user logs in between 6:30 p.m. to 9:00 a.m. This would enable you to capture SQL statements performed by users who log in to the database during non-work hours.

  This type of auditing is useful to increase the collection of audit activity when you suspect this connection may not be secure or could pose an internal threat. For example, by using a database logon trigger, you can query contents of the connection context using the SYS_CONTEXT function.

  The logon trigger functionality can establish that this connection should be audited more fully. Issue the following SQL command:

  AUDIT ALL STATEMENTS IN SESSION CURRENT;
  

  This type of auditing remains in effect until this session is terminated.

• Auditing login and logoff connections and disconnections. The AUDIT SESSION statement generates an independent audit record for every login and logoff event. This enables you to audit all successful and unsuccessful connections to and disconnections from the database, regardless of user.

  For example:

  AUDIT SESSION BY ACCESS;
  

  You can set this option selectively for individual users also, as in the following example:

  AUDIT SESSION BY jward, jsmith BY ACCESS;
  

• Auditing statements that fail because an object does not exist. The NOT EXISTS option of the AUDIT statement specifies auditing of all SQL statements that fail because the target object does not exist.

  For example:

  AUDIT NOT EXISTS;
  

See Oracle Database SQL Language Reference for detailed information about the AUDIT SQL statement.

Removing SQL Statement Auditing

To remove SQL statement auditing, use the use the NOAUDIT SQL statement. (Privilege auditing will still be enabled.) You must have the AUDIT SYSTEM system privilege before you can remove SQL statement auditing. If you have configured the AUDIT ALL STATEMENTS option, then issuing the NOAUDIT AUDIT STATEMENTS statement does not affect other audit options you may have set. If you included the IN SESSION CURRENT clause in the AUDIT statement, you cannot remove this AUDIT statement using the NOAUDIT statement. (The audit setting discontinues when the user's session ends.)

Example 9-8 shows examples of using the NOAUDIT statement to remove auditing.

NOAUDIT session;
NOAUDIT session BY preston, sebastian;
NOAUDIT DELETE ANY TABLE;
NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE;

Example 9-9 shows how to remove all statement auditing by using the NOAUDIT statement.

NOAUDIT ALL STATEMENTS;

See Oracle Database SQL Language Reference for detailed information about the NOAUDIT statement.

About Privilege Auditing

Privilege auditing audits statements that use a system privilege, such as SELECT ANY TABLE. In this kind of auditing, SQL statements that require the audited privilege to succeed are recorded.

Types of Privileges That Can Be Audited

You can audit the use of any system privilege. Similar to statement auditing, privilege auditing audits the activities of all database users or only a specified list.

If you set similar audit options for both statement and privilege auditing, then only a single audit record is generated. For example, if the statement clause TABLE and the system privilege CREATE TABLE are both audited, then only a single audit record is generated each time a table is created.

Privilege auditing does not occur if the action is already permitted by the existing owner and object privileges. Privilege auditing is triggered only if the privileges are insufficient, that is, only if what makes the action possible is a system privilege. For example, suppose that user SCOTT has been granted the SELECT ANY TABLE privilege and the SELECT ANY TABLE is being audited. If SCOTT selects his own table (for example, SCOTT.EMP), then the SELECT ANY TABLE privilege is not used. Because he performed the SELECT statement within his own schema, no audit record is generated. On the other hand, if SCOTT selects from another schema (for example, the HR.EMPLOYEES table), then an audit record is generated. Because SCOTT selected a table outside his own schema, he needed to use the SELECT ANY TABLE privilege.

Privilege auditing is more focused than statement auditing, because each privilege auditing option audits only specific types of statements, not a related list of statements. For example, the statement auditing clause, TABLE, audits CREATE TABLE, ALTER TABLE, and DROP TABLE statements. However, the privilege auditing option, CREATE TABLE, audits only CREATE TABLE statements, because only the CREATE TABLE statement requires the CREATE TABLE privilege.

See the listing of system privileges in the GRANT SQL statement section of Oracle Database SQL Language Reference.

Configuring Privilege Auditing

Privilege audit options are the same as their corresponding system privileges. For example, the option to audit use of the DELETE ANY TABLE privilege is DELETE ANY TABLE.

Example 9-10 shows how to audit the DELETE ANY TABLE privilege.

AUDIT DELETE ANY TABLE BY ACCESS WHENEVER NOT SUCCESSFUL;

To audit all successful and unsuccessful uses of the DELETE ANY TABLE system privilege, enter the following statement:

AUDIT DELETE ANY TABLE BY ACCESS;

Example 9-11 shows how to audit all unsuccessful SELECT, INSERT, and DELETE statements on all tables and unsuccessful uses of the EXECUTE PROCEDURE system privilege, by all database users, and by individual audited statement.

AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE
      BY ACCESS
      WHENEVER NOT SUCCESSFUL;

Removing Privilege Auditing

The following statement removes all privilege audit options:

NOAUDIT ALL PRIVILEGES;

This example disables the audit settings from Example 9-11:

NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE;

To disable privilege auditing options, you must have the AUDIT SYSTEM system privilege. Usually, only the security administrator is granted this system privilege.

About Schema Object Auditing

Schema object auditing monitors actions performed on the audited schema objects, such as tables or views. Object auditing applies to all users but is limited to the audited object only. Users can use the AUDIT and NOAUDIT statements on objects in their own schemas.

Types of Schema Objects That Can Be Audited

You can audit statements that refer to tables, views, sequences, standalone stored procedures or functions, and packages, but not individual procedures within packages. (See "Auditing Functions, Procedures, Packages, and Triggers" for more information about auditing these types of objects.)

You cannot directly audit statements that reference clusters, database links, indexes, or synonyms. However, you can indirectly audit access to these schema objects, by auditing the operations that affect the base table.

When you audit a schema object, the auditing applies to all users of the database. You cannot set these options for a specific list of users. You can set default schema object audit options for all auditable schema objects.

Using Standard Auditing with Editioned Objects

When an editioned object has an audit policy, then it applies in all editions in which the object is visible. When an editioned object is actualized, any audit policies that are attached to it are newly attached to the new actual occurrence. When you newly apply an audit policy to an inherited editioned object, this action will actualize it.

You can find the editions in which audited objects appear by querying the OBJECT_NAME and OBJ_EDITION_NAME columns in the DBA_COMMON_AUDIT_TRAIL and V$XML_AUDIT_TRAIL data dictionary views.

Schema Object Audit Options for Views, Procedures, and Other Elements

The definitions for views and procedures (including stored functions, packages, and triggers) reference underlying schema objects. Because of this dependency, some unique characteristics apply to auditing views and procedures, such as the likelihood of generating multiple audit records.

Views and procedures are subject to the enabled audit options on the base schema objects, including the default audit options. These options also apply to the resulting SQL statements.

Consider the following series of SQL statements:

AUDIT SELECT ON HR.EMPLOYEES BY ACCESS; 
 
CREATE VIEW employees_departments AS 
  SELECT employee_id, last_name, department_id
    FROM employees, departments
    WHERE employees.department_id = departments.department_id;
 
AUDIT SELECT ON employees_departments BY ACCESS; 

SELECT * FROM employees_departments; 

As a result of the query on the employees_departments view, two audit records are generated: one for the query on the employees_departments view and one for the query on the base table employees (indirectly through the employees_departments view). The query on the base table departments does not generate an audit record because the SELECT audit option for this table is not enabled. All audit records pertain to the user that queried the employees_departments view.

In the given example, if the AUDIT SELECT ON HR.EMPLOYEES; statement is omitted, then using the employees_departments view does not generate an audit record for the EMPLOYEES table.

Configuring Schema Object Auditing

You can use the AUDIT statement to configure object auditing in the current edition. Oracle Database SQL Language Reference lists valid object audit options for AUDIT and the schema object types for which each option is available.

A user can set any object audit option for the objects contained in his or her schema. The AUDIT ANY system privilege is required to set an object audit option for an object contained in another user schema or to set the default object auditing option. Usually, only the security administrator is granted the AUDIT ANY privilege.

Figure 9-2 shows how to audit all successful and unsuccessful DELETE statements on the laurel.emp table.

AUDIT DELETE ON laurel.emp BY ACCESS;

Example 9-14 shows how to audit all successful SELECT, INSERT, and DELETE statements on the dept table owned by user jward.

AUDIT SELECT, INSERT, DELETE
     ON jward.dept
     BY ACCESS
     WHENEVER SUCCESSFUL;

Example 9-15 shows how you can use the ON DEFAULT clause to apply to any new objects (tables, views, and sequences) that are created after you set the AUDIT statement. In this example, new objects that are created in the future will be audited for all unsuccessful SELECT statements:

AUDIT SELECT
     ON DEFAULT
     BY ACCESS
     WHENEVER NOT SUCCESSFUL;

Example 9-16 shows how to audit the execution of PL/SQL procedure or function.

AUDIT EXECUTE ON sec_mgr.auth_orders BY ACCESS;

Removing Object Auditing

Use the NOAUDIT statement to remove object auditing. The following statements turn off the corresponding auditing options:

NOAUDIT DELETE
   ON emp;
NOAUDIT SELECT, INSERT, DELETE
   ON jward.dept;

To remove all object audit options on the emp table, enter the following statement:

NOAUDIT ALL ON emp;

To remove all default object audit options, enter the following statement:

NOAUDIT ALL ON DEFAULT;

All schema objects that are created before this NOAUDIT statement is issued continue to use the default object audit options in effect at the time of their creation, unless overridden by an explicit NOAUDIT statement after their creation.

To remove object audit options for a specific object, you must be the owner of the schema object. To remove the object audit options of an object in the schema of another user or to remove default object audit options, you must have the AUDIT ANY system privilege. A user with privileges to remove object audit options of an object can override the options set by any user.

Setting Audit Options for Objects That May Be Created in the Future

You can create audit settings for objects that do not exist yet, such as the insertion and deletion of tables to be created. There are two approaches that you can take. One approach is to use the statement audit options in the AUDIT statement. For example, to audit all inserts on future tables, enter the following SQL statement:

AUDIT INSERT TABLE BY ACCESS;

The second approach is to invoke the AUDIT statement using the ON DEFAULT clause. For example:

AUDIT ALL ON DEFAULT BY ACCESS;

This statement audits by default all subsequent object creation statements. The ON keyword specifies object auditing. The ON DEFAULT clause configures auditing for subsequently created objects that are affected by the following statements:

To restrict ON DEFAULT to a specific action, enter a statement similar to the following:

AUDIT ALTER, DELETE ON DEFAULT BY ACCESS;

For more information about the audit options and the ON DEFAULT clause of the AUDIT SQL statement, see Oracle Database SQL Language Reference. To find objects audited by default, query the ALL_DEF_AUDIT_OPTS data dictionary view.

About Directory Object Auditing

You can audit directory objects. For example, suppose you create a directory object that contains a preprocessor program that the ORACLE_LOADER access driver will use. You can audit anyone who runs this program within this directory object.

Configuring Directory Object Auditing

Use the AUDIT statement to enable object auditing. Example 9-17 shows how to audit the EXECUTE privilege on the directory object my_exec.

AUDIT EXECUTE ON DIRECTORY my_exec BY ACCESS;

Removing Directory Object Auditing

Use the NOAUDIT statement to disable directory object auditing. For example:

NOAUDIT EXECUTE ON DIRECTORY my_exec;

About Auditing Functions, Procedures, Packages, and Triggers

You can audit functions, procedures, PL/SQL packages, and triggers. The areas that you can audit are as follows:

• You can individually audit standalone functions, standalone procedures, and PL/SQL packages.

• If you audit a PL/SQL package, Oracle Database audits all functions and procedures within the package.

• If you enable auditing for all executions, Oracle Database audits all triggers in the database, as well as all the functions and procedures within PL/SQL packages.

• You cannot audit individual functions or procedures within a PL/SQL package.

If you want to audit functions that are associated with Oracle Virtual Private database policies, note the following:

• Dynamic policies: Oracle Database evaluates the policy function twice, once during SQL statement parsing and again during execution. As a result, two audit records are generated for each evaluation.

• Static policies: Oracle Database evaluates the policy function once and then caches it in the SGA. As a result, only one audit record is generated.

• Context-sensitive policies: Oracle Database executes the policy function once, during statement parsing. As a result, only one audit record is generated.

Configuring the Auditing of Functions, Procedures, Packages, and Triggers

Example 9-18 shows how to audit the execution of any function, procedure, package, or trigger, by any user in the database.

AUDIT EXECUTE PROCEDURE BY ACCESS;

Example 9-19 shows how to audit user psmith's successful and unsuccessful executions of functions, procedures, packages, and triggers.

AUDIT EXECUTE PROCEDURE BY psmith BY ACCESS;

Example 9-20 shows how to audit a standalone procedure entitled check_work that is in the sales_data schema. This idea applies to standalone functions as well.

AUDIT EXECUTE ON sales_data.check_work BY ACCESS WHENEVER SUCCESSFUL;

Removing the Auditing of Functions, Procedures, Packages, and Triggers

Use the NOAUDIT statement to remove the auditing of functions, procedures, and triggers. For example:

NOAUDIT EXECUTE PROCEDURE;

NOAUDIT EXECUTE PROCEDURE BY psmith;

NOAUDIT EXECUTE ON sales_data.checkwork;

About Network Auditing

You can use the AUDIT statement to audit unexpected errors in network protocol or internal errors in the network layer. Network auditing captures errors that occur during communication with the client on the network. These are errors thrown by the SQL*Net driver. There can be several causes of network errors. For example, an internal event set by a database engineer for testing purposes can cause a network error. Other causes include conflicting configuration settings for encryption, such as the network not finding the information required to create or process expected encryption. The errors that network auditing uncovers (such as ACTION 122 Network Error) are not connection failures: network auditing is only concerned with data as it travels across the network.

The audit record for network auditing lists the authentication type and SQL*Net address of the client (if available) in the COMMENT_TEXT field of the audit record, using the following format:

Authenticated by: authentication_type; Client Address: SQLNetAddress_of_client

The Client Address: SQLNetAddress_of_client portion only appears if this information is available.

Table 9-5 shows how to remedy four common error conditions.

Configuring Network Auditing

To configure network auditing, use the AUDIT statement. For example:

AUDIT NETWORK BY ACCESS;

Removing Network Auditing

To remove network auditing:

NOAUDIT NETWORK;

About the DBMS_FGA PL/SQL Package

To manage a fine-grained audit policy, you use the DBMS_FGA PL/SQL package. This package enables you to add all combinations of SELECT, INSERT, UPDATE, and DELETE statements to one policy. You also can audit MERGE statements, by auditing the underlying actions of INSERT and UPDATE. To audit MERGE statements, configure fine-grained access on the INSERT and UPDATE statements. Only one record is generated for each policy for successful MERGE operations. To administer fine-grained audit policies, you must have the EXECUTE privilege on the DBMS_FGA package.

The audit policy is bound to the table for which you created it. This simplifies the management of audit policies because the policy only must be changed once in the database, not in each application. In addition, no matter how a user connects to the database—from an application, a Web interface, or through SQL*Plus or Oracle SQL Developer—Oracle Database records any actions that affect the policy.

If any rows returned from a query match the audit condition that you define, then Oracle Database inserts an audit entry into the fine-grained audit trail. This entry excludes all the information that is reported in the regular audit trail. In other words, only one row of audit information is inserted into the audit trail for every fine-grained audit policy that evaluates to true.

For detailed information about the syntax of the DBMS_FGA package, see Oracle Database PL/SQL Packages and Types Reference. See also Oracle Database Advanced Application Developer's Guide.

Creating a Fine-Grained Audit Policy

To create a fine-grained audit policy, use the DBMS_FGA.ADD_POLICY procedure. This procedure creates an audit policy using the supplied predicate as the audit condition. Oracle Database executes the policy predicate with the privileges of the user who created the policy. The maximum number of fine-grained policies on any table or view object is 256. Oracle Database stores the policy in the data dictionary table, but you can create the policy on any table or view that is not in the SYS schema.

After you create the fine-grained audit policy, it does not reside in any specific schema, although the definition for the policy is stored in the SYS.FGA$ data dictionary table.

You cannot modify a fine-grained audit policy after you have created it. If you need to modify the policy, drop it and then recreate it.

Be aware that if a table column has a fine-grained audit policy, you cannot encrypt or decrypt this column (by using the UPDATE statement). To do so raises an ORA-28133: full table access is restricted by fine-grained security error. If you want to update the column, first temporarily disable the fine-grained audit policy and then encrypt or decrypt the column. Afterwards, re-enable the fine-grained audit policy. See "Disabling and Enabling a Fine-Grained Audit Policy" for more information.

The syntax for the ADD_POLICY procedure is:

DBMS_FGA.ADD_POLICY(
   object_schema      VARCHAR2, 
   object_name        VARCHAR2, 
   policy_name        VARCHAR2, 
   audit_condition    VARCHAR2, 
   audit_column       VARCHAR2, 
   handler_schema     VARCHAR2, 
   handler_module     VARCHAR2, 
   enable             BOOLEAN, 
   statement_types    VARCHAR2,
   audit_trail        BINARY_INTEGER IN DEFAULT,
   audit_column_opts  BINARY_INTEGER IN DEFAULT);

In this specification:

• object_schema: Specifies the schema of the object to be audited. (If NULL, the current log-on user schema is assumed.)

• object_name: Specifies the name of the object to be audited.

• policy_name: Specifies the name of the policy to be created. Ensure that this name is unique.

• audit_condition: Specifies a Boolean condition in a row. NULL is allowed and acts as TRUE. See "Auditing Specific Columns and Rows" for more information. If you specify NULL or no audit condition, then any action on a table with that policy creates an audit record, whether or not rows are returned

• audit_column: Specifies one or more columns to audit, including hidden columns. If set to NULL or omitted, all columns are audited. These can include Oracle Label Security hidden columns or object type columns. The default, NULL, causes audit if any column is accessed or affected.

• handler_schema: If an alert is used to trigger a response when the policy is violated, specifies the name of the schema that contains the event handler. The default, NULL, uses the current schema. See also "Tutorial: Adding an Email Alert to a Fine-Grained Audit Policy".

• handler_module: Specifies the name of the event handler. Include the package the event handler is in. This function is invoked only after the first row that matches the audit condition in the query is processed.

  Follow these guidelines:

  • Do not create recursive fine-grained audit handlers. For example, suppose you create a handler that executes an INSERT statement on the HR.EMPLOYEES table. The policy that is associated with this handler is for INSERT statements (as set by the statement_types parameter). When the policy is used, the handler executes recursively until the system has run out of memory. This can raise the error ORA-1000: maximum open cursors exceeded or ORA-00036: maximum number of recursive SQL levels (50) exceeded.

  • Do not issue the DBMS_FGA.ENABLE_POLICY or DBMS_FGA.DISABLE_POLICY statement from a policy handler. Doing so can raise the ORA-28144: Failed to execute fine-grained audit handler error.

• enable: Enables or disables the policy using true or false. If omitted, the policy is enabled. The default is TRUE.

• statement_types: Specifies the SQL statements to be audited: INSERT, UPDATE, DELETE, or SELECT only.

• audit_trail: Specifies the destination (DB or XML) of fine-grained audit records. Also specifies whether to populate LSQLTEXT and LSQLBIND in FGA_LOG$. However, be aware that sensitive data, such as credit card information, can be recorded in clear text. See "Auditing Sensitive Information" for how you can handle this scenario.

  If you set the audit_trail parameter to XML, then the XML files are written to the directory specified by the AUDIT_FILE_DEST initialization parameter.

  For read-only databases, Oracle Database writes the fine-grained audit trail to XML files, regardless of the audit_trail setting.

• audit_column_opts: If you specify more than one column in the audit_column parameter, then this parameter determines whether to audit all or specific columns. See "Auditing Specific Columns and Rows" for more information.

See Oracle Database PL/SQL Packages and Types Reference for additional details about the ADD_POLICY syntax.

Example 9-21 shows how to audit statements INSERT, UPDATE, DELETE, and SELECT on table HR.EMPLOYEES. Note that this example omits the audit_column_opts parameter, because it is not a mandatory parameter.

BEGIN
  DBMS_FGA.ADD_POLICY(
   object_schema      => 'HR',
   object_name        => 'EMPLOYEES',
   policy_name        => 'chk_hr_employees',
   enable             =>  TRUE,
   statement_types    => 'INSERT, UPDATE, SELECT, DELETE',
   audit_trail        =>  DBMS_FGA.DB);
END;
/

At this point, if you query the DBA_AUDIT_POLICIES view, you will find the new policy listed:

SELECT POLICY_NAME FROM DBA_AUDIT_POLICIES;

POLICY_NAME
-------------------------------
CHK_HR_EMPLOYEES

Afterwards, any of the following SQL statements log an audit event record.

SELECT COUNT(*) FROM HR.EMPLOYEES WHERE COMMISSION_PCT = 20 AND SALARY > 4500;

SELECT SALARY FROM HR.EMPLOYEES WHERE DEPARTMENT_ID = 50;

DELETE FROM HR.EMPLOYEES WHERE SALARY > 1000000;

Auditing Specific Columns and Rows

You can fine-tune the audit behavior by targeting a specific column, referred to as a relevant column, to be audited if a condition is met. To accomplish this, you use the audit_column parameter to specify one or more sensitive columns. In addition, you can audit data in specific rows by using the audit_condition parameter to define a Boolean condition.

Example 9-21 performs an audit if anyone in Department 50 tries to access the salary and commission_pct columns.

audit_condition    => 'DEPARTMENT_ID = 50', 
audit_column       => 'SALARY,COMMISSION_PCT,'

As you can see, this feature is enormously beneficial. It not only enables you to pinpoint particularly important types of data to audit, but it provides increased protection for columns that contain sensitive data, such as Social Security numbers, salaries, patient diagnoses, and so on.

If the audit_column lists more than one column, you can use the audit_column_opts parameter to specify whether a statement is audited when the query references any column specified in the audit_column parameter or only when all columns are referenced. For example:

audit_column_opts   => DBMS_FGA.ANY_COLUMNS,

audit_column_opts   => DBMS_FGA.ALL_COLUMNS,

If you do not specify a relevant column, then auditing applies to all columns.

For more information about the audit_condition, audit_column, and audit_column_opts parameters in the DBMS_FGA.ADD_POLICY procedure, see Oracle Database PL/SQL Packages and Types Reference. See also the usage notes for the ADD_POLICY procedure in that section.

Disabling and Enabling a Fine-Grained Audit Policy

You can disable a fine-grained audit policy by using the DBMS_FGA.DISABLE_POLICY procedure. The syntax for DISABLE_POLICY is:

DBMS_FGA.DISABLE_POLICY(
   object_schema  VARCHAR2, 
   object_name    VARCHAR2, 
   policy_name    VARCHAR2 ); 

Example 9-22 shows how to disable the fine-grained audit policy created in Example 9-21.

DBMS_FGA.DISABLE_POLICY(
  object_schema        => 'HR',
  object_name          => 'EMPLOYEES',
  policy_name          => 'chk_hr_employees');
/

For detailed information about the DISABLE_POLICY syntax, see Oracle Database PL/SQL Packages and Types Reference.

Example 9-23 show how to reenable the chk_hr_emp policy by using the DBMS_FGA.ENABLE_POLICY procedure:

DBMS_FGA.ENABLE_POLICY(
  object_schema        => 'HR',
  object_name          => 'EMPLOYEES',
  policy_name          => 'chk_hr_employees',
  enable               => TRUE);
/

For detailed information about the ENABLE_POLICY syntax, see Oracle Database PL/SQL Packages and Types Reference.

Dropping a Fine-Grained Audit Policy

Oracle Database automatically drops the audit policy if you remove the object specified in the object_name parameter of the DBMS_FGA.ADD_POLICY procedure, or if you drop the user who created the audit policy.

Example 9-24 shows how to drop a fine-grained audit policy manually by using the DBMS_FGA.DROP_POLICY procedure.

DBMS_FGA.DROP_POLICY(
  object_schema      => 'HR',
  object_name        => 'EMPLOYEES',
  policy_name        => 'chk_hr_employees');

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the DROP_POLICY syntax.

About This Tutorial

You can add an email alert to a fine-grained audit policy that goes into effect when a user (or an intruder) violates the policy. To accomplish this, you first must create a procedure that generates the alert, and then use the following DBMS_FGA.ADD_POLICY parameters to call this function when someone violates this policy:

• handler_schema: The schema in which the handler event is stored

• handler_module: The name of the event handler

The alert can come in any form that best suits your environment: an email or pager notification, updates to a particular file or table, and so on. Creating alerts also helps to meet certain compliance regulations, such as California Senate Bill 1386. In this tutorial, you will create an email alert.

In this tutorial, you create an email alert that notifies a security administrator that a Human Resources representative is trying to select or modify salary information in the HR.EMPLOYEES table. The representative is permitted to make changes to this table, but to meet compliance regulations, we want to create a record of all salary selections and modifications to the table.

Step 1: Install and Configure the UTL_MAIL PL/SQL Package

1. Log on as user SYS with the SYSDBA privilege.

   sqlplus sys as sysdba
   Enter password: password
   

2. Install the UTL_MAIL package.

   @$ORACLE_HOME/rdbms/admin/utlmail.sql
   @$ORACLE_HOME/rdbms/admin/prvtmail.plb
   

   The UTL_MAIL package enables you to manage email. See Oracle Database PL/SQL Packages and Types Reference for more information about UTL_MAIL.

   Be aware that currently, the UTL_MAIL PL/SQL package does not support SSL servers.

3. Check the current value of the SMTP_OUT_SERVER initialization parameter, and make a note of this value so that you can restore it when you complete this tutorial.

   For example:

   SHOW PARAMETER SMTP_OUT_SERVER
   

   If the SMTP_OUT_SERVER parameter has already been set, then output similar to the following appears:

   NAME                    TYPE              VALUE
   ----------------------- ----------------- ----------------------------------
   SMTP_OUT_SERVER         string            some_imap_server.example.com
   

4. Issue the following ALTER SYSTEM statement:

   ALTER SYSTEM SET SMTP_OUT_SERVER="imap_mail_server.example.com";
   

   Replace imap_mail_server with the name of your SMTP server, which you can find in the account settings in your email tool. Enclose these settings in quotation marks. For example:

   ALTER SYSTEM SET SMTP_OUT_SERVER="my_imap_server.example.com"
   

5. Connect as SYS using the SYSOPER privilege and then restart the database.

   CONNECT SYS/AS SYSOPER
   Enter password: password
   
   SHUTDOWN IMMEDIATE
   STARTUP
   

6. Ensure that the SMTP_OUT_SERVER parameter setting is correct.

   CONNECT SYS/AS SYSDBA
   Enter password: password
   
   SHOW PARAMETER SMTP_OUT_SERVER
   

   Output similar to the following appears:

   NAME                    TYPE              VALUE
   ----------------------- ----------------- ----------------------------------
   SMTP_OUT_SERVER         string            my_imap_server.example.com
   

Step 2: Create User Accounts

1. Ensure that you are connected as SYS using the SYSDBA privilege, and then create the sysadmin_fga account, who will create the fine-grained audit policy.

   For example:

   CONNECT SYS/AS SYSDBA
   Enter password: password
   
   GRANT CREATE SESSION, DBA TO sysadmin_fga IDENTIFIED BY password;
   GRANT EXECUTE ON DBMS_FGA TO sysadmin_fga;
   GRANT CREATE PROCEDURE, DROP ANY PROCEDURE TO sysadmin_fga;
   GRANT EXECUTE ON UTL_TCP TO sysadmin_fga;
   GRANT EXECUTE ON UTL_SMTP TO sysadmin_fga;
   GRANT EXECUTE ON UTL_MAIL TO sysadmin_fga;
   GRANT EXECUTE ON DBMS_NETWORK_ACL_ADMIN TO sysadmin_fga;
   

   Replace password with a password that is secure. See "Minimum Requirements for Passwords" for more information.

   The UTL_TCP, UTL_SMTP, UTL_MAIL, and DBMS_NETWORK_ACL_ADMIN PL/SQL packages are used by the email security alert that you create.

2. Connect as user SYSTEM.

   CONNECT SYSTEM
   Enter password: password
   

3. Ensure that the HR schema account is unlocked and has a password. If necessary, unlock HR and grant this user a password.

   SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = 'HR';
   

   If the DBA_USERS view lists user HR as locked and expired, then enter the following statement to unlock the HR account and create a new password:

   ALTER USER HR ACCOUNT UNLOCK IDENTIFIED BY password;
   

   Enter a password that is secure. For greater security, do not give the HR account the same password from previous releases of Oracle Database. "Minimum Requirements for Passwords" for the minimum requirements for creating passwords.

4. Create a user account for Susan Mavris, who is an HR representative, and then grant this user access to the HR.EMPLOYEES table.

   GRANT CREATE SESSION TO smavris IDENTIFIED BY password;
   GRANT SELECT, INSERT, UPDATE, DELETE ON HR.EMPLOYEES TO SMAVRIS; 
   

Step 3: Configure an Access Control List File for Network Services

Before you can use PL/SQL network utility packages such as UTL_MAIL, you must configure an access control list (ACL) file that enables fine-grained access to external network services. For detailed information about this topic, see "Managing Fine-Grained Access in PL/SQL Packages and Types".

To configure an access control list for the email alert:

1. Connect to SQL*Plus as user sysadmin_fga.

   CONNECT sysadmin_fga
   Enter password: password
   

2. Create the following access control list and its privilege definitions.

   BEGIN
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
     acl          => 'email_server_permissions.xml', 
     description  => 'Enables network permissions for the email server',
     principal    => 'SYSADMIN_FGA',
     is_grant     => TRUE, 
     privilege    => 'connect');
   END;
   /
   

   Ensure that you enter your exact user name for the principal setting, in upper-case letters. For this tutorial, enter SYSADMIN_FGA for the name of the principal.

3. Assign the access control list to the outgoing SMTP network host for your email server.

   BEGIN
    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
     acl         => 'email_server_permissions.xml',
     host        => 'SMTP_OUT_SERVER_setting', 
     lower_port  => port); 
   END;
   /
   

   In this example:

   • SMTP_OUT_SERVER_setting: Enter the SMTP_OUT_SERVER setting that you set for the SMTP_OUT_SERVER parameter in "Step 1: Install and Configure the UTL_MAIL PL/SQL Package". This setting should match exactly the setting that your email tool specifies for its outgoing server.

   • port: Enter the port number that your email tool specifies for its outgoing server. Typically, this setting is 25. Enter this value for the lower_port setting. (Currently, the UTL_MAIL package does not support SSL. If your email server is an SSL server, then enter 25 for the port number, even if the email server uses a different port number.)

Step 4: Create the Email Security Alert PL/SQL Procedure

As user sysadmin_fga, create the following procedure. (You can copy and paste this text by positioning the cursor at the start of CREATE OR REPLACE in the first line.)

1
2
3
4
5
6
7
8
9
10
11
12

CREATE OR REPLACE PROCEDURE email_alert (sch varchar2, tab varchar2, pol varchar2)
AS
msg varchar2(20000) := 'HR.EMPLOYEES table violation. The time is: ';
BEGIN
  msg := msg||TO_CHAR(SYSDATE, 'Day DD MON, YYYY HH24:MI:SS'); 
UTL_MAIL.SEND (
    sender      => 'youremail@example.com',
    recipients  => 'recipientemail@example.com',
    subject     => 'Table modification on HR.EMPLOYEES',
    message     => msg); 
END email_alert;
/

In this example:

• Lines 1 and 2: In the CREATE PROCEDURE statement, you must include a signature that describes the schema name (sch), table name (tab), and the name of the audit procedure (pol) that you will define in audit policy in the next step.

• Lines 9 and 10: Replace youremail@example.com with your email address, and recipientemail@example.com with the email address of the person you want to receive the notification.

Step 5: Create and Test the Fine-Grained Audit Policy Settings

1. As user sysadmin_fga, create the chk_hr_emp policy fine-grained audit policy as follows.

   BEGIN
    DBMS_FGA.ADD_POLICY (
     object_schema      =>  'HR',
     object_name        =>  'EMPLOYEES',
     policy_name        =>  'CHK_HR_EMP',
     audit_column       =>  'SALARY', 
     handler_schema     =>  'SYSADMIN_FGA',
     handler_module     =>  'EMAIL_ALERT',
     enable             =>   TRUE,
     statement_types    =>  'SELECT, UPDATE',
     audit_trail        =>   DBMS_FGA.DB + DBMS_FGA.EXTENDED); 
   END;
   /
   

2. Commit the changes you have made to the database.

   COMMIT;
   

3. Test the settings that you have created so far.

   EXEC email_alert ('hr', 'employees', 'chk_hr_emp');
   

   SQL*Plus should display a PL/SQL procedure successfully completed message, and in a moment, depending on the speed of your email server, you should receive the email alert.

   If you receive an ORA-24247: network access denied by access control list (ACL) error followed by ORA-06512: at stringline string errors, then check the settings in the access control list file.

Step 6: Test the Alert

1. Connect to SQL*Plus as user smavris, check your salary, and give yourself a nice raise.

   CONNECT smavris
   Enter password: password
   
   SELECT SALARY FROM HR.EMPLOYEES WHERE LAST_NAME = 'Mavris';
   
   SALARY
   -----------
   6500
   
   UPDATE HR.EMPLOYEES SET SALARY = 13000 WHERE LAST_NAME = 'Mavris';
   

2. Now select from a column other than SALARY in the HR.EMPLOYEES table.

   SELECT FIRST_NAME, LAST_NAME FROM HR.EMPLOYEES WHERE LAST_NAME = 'Raphaely';
   

   The following output should appear:

   FIRST_NAME           LAST_NAME
   -------------------- --------------------
   Den                  Raphaely
   

   By now, depending on the speed of you email server, you (or your recipient) should have received an email with the subject header Table modification on HR.EMPLOYEES notifying you of the tampering of the HR.EMPLOYEES table.

3. As user sysadmin_fga, query the DBA_FGA_AUDIT_TRAIL data dictionary view, which contains the Susan Mavris's audited activities.

   CONNECT sysadmin_fga
   Enter password: password
   
   col dbuid format a10
   col lsqltext format a66
   col ntimestamp# format a15
   
   SELECT DBUID, LSQLTEXT, NTIMESTAMP# FROM SYS.FGA_LOG$ WHERE POLICYNAME='CHK_HR_EMP';
   

   Output similar to the following appears:

   DBUID      LSQLTEXT
   ---------- ------------------------------------------------------------------
   NTIMESTAMP#
   --------------------------------------------------------------------------
   SMAVRIS    SELECT SALARY FROM HR.EMPLOYEES WHERE LAST_NAME = 'Mavris'
   23-JUN-09 03.48.59.111000 PM
   
   SMAVRIS    UPDATE HR.EMPLOYEES SET SALARY = 13000 WHERE LAST_NAME = 'Mavris'
   23-JUN-09 03.49.07.330000 PM
   

   The audit trail captures the two SQL statements that Susan Mavris ran that affected the SALARY column in the HR.EMPLOYEES table. The third statement she ran, in which she asked about Den Raphaely, was not recorded because it was not affected by the audit policy. This is because Oracle Database executes the audit function as an autonomous transaction, committing only the actions of the handler_module setting and not any user transaction. The function has no effect on any user SQL transaction.

Step 7: Remove the Components for This Tutorial

1. Connect to SQL*Plus as user SYSTEM privilege, and then drop users sysadmin_fga (including the objects in the sysadmin_fga schema) and smavris.

   CONNECT SYSTEM
   Enter password: password
   
   DROP USER sysadmin_fga CASCADE;
   DROP USER smavris;
   

2. Connect as user HR and remove the loftiness of Susan Mavris's salary.

   CONNECT HR
   Enter password: password
   
   UPDATE HR.EMPLOYEES SET SALARY = 6500 WHERE LAST_NAME = 'Mavris';
   

3. If you want, lock and expire HR, unless other users want to use this account:

   ALTER USER HR PASSWORD EXPIRE ACCOUNT LOCK;
   

4. Connect as user SYS with the SYSDBA privilege, and drop the email_server_permissions.xml access control list.

   BEGIN
      DBMS_NETWORK_ACL_ADMIN.DROP_ACL(
        acl => 'email_server_permissions.xml');
   END;
   /
   

   Access control lists reside in the SYS schema, not the schema of the user who created them.

5. Issue the following ALTER SYSTEM statement to restore the SMTP_OUT_SERVER parameter to the previous value, from Step 4 under "Step 1: Install and Configure the UTL_MAIL PL/SQL Package":

   ALTER SYSTEM SET SMTP_OUT_SERVER="previous_value";
   

   Enclose this setting in quotation marks. For example:

   ALTER SYSTEM SET SMTP_OUT_SERVER="some_imap_server.example.com"
   

6. Restart the database instance.

   SHUTDOWN
   
   STARTUP
   

About This Tutorial

This tutorial shows how to create a fine-grained audit policy that audits a nondatabase user's actions, based on the identity set in the client identifier.

Step 1: Create the User Account and Ensure the User HR Is Active

1. Log on as user SYS with the SYSDBA privilege.

   sqlplus SYS AS SYSDBA
   Enter password: password
   

2. Create the sysadmin_fga account, who will create the fine-grained audit policy.

   GRANT CREATE SESSION, DBA TO sysadmin_fga IDENTIFIED BY password;
   GRANT SELECT ON OE.ORDERS TO sysadmin_fga;
   GRANT EXECUTE ON DBMS_FGA TO sysadmin_fga;
   GRANT SELECT ON SYS.FGA_LOG$ TO sysadmin_fga;
   

   Replace password with a password that is secure. See "Minimum Requirements for Passwords" for more information.

3. The sample user OE will also be used in this tutorial, so query the DBA_USERS data dictionary view to ensure that OE is not locked or expired.

   SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = 'OE';
   

   If the DBA_USERS view lists user OE as locked and expired, log in as user SYSTEM and then enter the following statement to unlock the OE account and create a new password:

   ALTER USER OE ACCOUNT UNLOCK IDENTIFIED BY password;
   

   Enter a password that is secure. For greater security, do not give the OE account the same password from previous releases of Oracle Database. "Minimum Requirements for Passwords" for the minimum requirements for creating passwords.

Step 2: Create the Fine-Grained Audit Policy

1. Connect to SQL*Plus as user sysadmin_fga.

   CONNECT sysadmin_fga
   Enter password: password
   

2. Create the following policy:

   BEGIN
    DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA => 'OE',
      OBJECT_NAME                     => 'ORDERS',
      POLICY_NAME                     => 'ORDERS_FGA_POL',
      AUDIT_CONDITION                 => 'SYS_CONTEXT(''USERENV'', ''CLIENT_IDENTIFIER'') = ''Robert''',
      HANDLER_SCHEMA                  => NULL,
      HANDLER_MODULE                  => NULL,
      ENABLE                          => True,
      STATEMENT_TYPES                 => 'INSERT,UPDATE,DELETE,SELECT',
      AUDIT_TRAIL                     => DBMS_FGA.DB + DBMS_FGA.EXTENDED,
      AUDIT_COLUMN_OPTS               => DBMS_FGA.ANY_COLUMNS);
   END;
   /
   

   In this example, the AUDIT_CONDITION parameter assumes the nondatabase user is named Robert. The policy will monitor any INSERT, UPDATE, DELETE, and SELECT statements Robert will attempt.

Step 3: Test the Policy

1. Connect as user OE and select from the OE.ORDERS table.

   CONNECT OE
   Enter password: password
   
   SELECT COUNT(*) FROM ORDERS;
   

   The following output appears:

   COUNT(*)
   ----------
          105
   

2. Connect as user sysadmin_fga and then check if any audit records were generated.

   CONNECT sysadmin_fga
   Enter password: password
   
   SELECT DBUID, LSQLTEXT FROM SYS.FGA_LOG$ WHERE POLICYNAME='ORDERS_FGA_POL';
   

   The following output appears:

   no rows selected
   

   Because no nondatabase users were logged in to query the OE.ORDERS table, the audit trail is empty.

3. Reconnect as user OE, set the client identifier to Robert, and then reselect from the OE.ORDERS table.

   CONNECT OE
   Enter password: password
   
   EXEC DBMS_SESSION.SET_IDENTIFIER('Robert');
   
   SELECT COUNT(*) FROM ORDERS;
   

   The following output should appear:

   COUNT(*)
   ----------
          105
   

4. Reconnect as user sysadmin_fga and then check the audit trail again.

   CONNECT sysadmin_fga
   Enter password: password
   
   SELECT DBUID, LSQLTEXT FROM SYS.FGA_LOG$ WHERE POLICYNAME='ORDERS_FGA_POL';
   

   This time, because Robert has made his appearance and queried the OE.ORDERS table, the audit trail captures his actions:

   DBUID            LSQLTEXT
   ---------------- ----------------------------
   OE               SELECT COUNT(*) FROM ORDERS;
   

Step 4: Remove the Components for This Tutorial

1. Connect to SQL*Plus as user SYSTEM, and then drop user sysadmin_fga (including the objects in the sysadmin_fga schema).

   CONNECT SYSTEM
   Enter password: password
   
   DROP USER sysadmin_fga CASCADE;
   

2. If you want, lock and expire OE, unless other users want to use this account:

   ALTER USER OE PASSWORD EXPIRE ACCOUNT LOCK;
   

Database Audit Trail Contents

The database audit trail is a pair of tables, AUD$ (for standard auditing) and FGA_LOG$ (for fine-grained auditing), in the SYS schema of each Oracle Database data dictionary. It records both standard and fine-grained audit activities. Several data dictionary views can help you use the information in this table. "Finding Information About Audited Activities" lists all the auditing-related views.

The database audit trail record contains different types of information, depending on the events audited and the auditing options set. For example, if you have set the AUDIT_TRAIL initialization parameter to DB, EXTENDED or XML, EXTENDED, then the SQL_BIND and SQL_TEXT columns show any SQL bind variables used for a SQL statement and SQL text that triggered the audit, respectively. For full details about the contents of these views, refer to Oracle Database Reference. However, be aware that the format and columns of the DBA_AUDIT_TRAIL view may change across Oracle Database releases.

If the database destination for audit records becomes full or unavailable, and, therefore, unable to accept new records, then an audited action cannot complete. Instead, Oracle Database generates an error message and does not audit the action. You can control the size of the audit trail to make it more manageable. (In fact, Oracle strongly recommends that you do so.) See "Controlling the Size of the Database Audit Trail" for more information. See also "Keeping Audited Information Manageable".

The audit trail does not store information about any data values that might be involved in the audited statement. For example, old and new data values of updated rows are not stored when an UPDATE statement is audited. However, you can perform this specialized type of auditing by using fine-grained auditing methods.

You can use the Flashback Query feature to show the old and new values of the updated rows, subject to any auditing policy presently in force. The current policies are enforced even if the flashback is to an old query that was originally subject to a different policy. Current business access rules always apply.

Controlling the Size of the Database Audit Trail

If the database audit trail is full and no more audit records can be inserted, then underlying statement cannot complete successfully until you purge the audit trail. Oracle Database issues errors to all users who issue statements that cause the audit. Therefore, you must control the growth and size of the audit trail.

When auditing is enabled and audit records are being generated, the audit trail increases according to two factors:

• The number of audit options turned on

• The frequency of execution of audited statements

To control the growth of the audit trail, you can use the following methods:

• Enable and disable database auditing. If it is enabled, then audit records are generated and stored in the audit trail. If it is disabled, then audit records are not generated. (Remember that some activities are always audited.)

• Be selective about the audit options that are turned on. If more selective auditing is performed, then useless or unnecessary audit information is not generated and stored in the audit trail. You can use fine-grained auditing to selectively audit only certain conditions.

• Tightly control the ability to perform object auditing. You can accomplish this in the following ways:

  • A security administrator owns all objects and never grants the AUDIT ANY system privilege to any other user. Alternatively, all schema objects can belong to a schema for which the corresponding user does not have CREATE SESSION privilege.

  • All objects are contained in schemas that do not correspond to real database users (that is, the CREATE SESSION privilege is not granted to the corresponding user). The security administrator is the only user granted the AUDIT ANY system privilege.

  In both scenarios, a security administrator controls entirely object auditing.

The maximum size of the database audit trail tables (AUD$ and FGA_LOG$) is determined by the default storage parameters of the SYSTEM tablespace, in which it is stored by default. If you are concerned that a too-large database audit trail will affect the SYSTEM table performance, then consider moving the database audit trail tables to a different tablespace.

Moving the Database Audit Trail to a Different Tablespace

By default, the SYSTEM tablespace stores the database audit trail SYS.AUD$ and SYS.FGA_LOG$ tables. You can change this default location to another tablespace, such as the SYSAUX tablespace or a user-created tablespace. You may want to move the database audit trail tables to a different tablespace if the SYSTEM tablespace is too busy. Another reason for moving these audit trail tables to a different tablespace is if you plan to purge them by using the DBMS_AUDIT_MGMT PL/SQL package procedures.

Be aware that moving the database audit trail tables to a different tablespace can take a long time, depending on the amount of audit data in the audit tables, so you may want to do this during a time when database activity is slow.

To move the database audit trail from SYSTEM to a different tablespace:

1. Log in to SQL*Plus as an administrator who has the EXECUTE privilege on the DBMS_AUDIT_MGMT PL/SQL package.

   For more information about the DBMS_AUDIT_MGMT PL/SQL package, see Oracle Database PL/SQL Packages and Types Reference.

2. Check the tablespace to which you want to move the database audit trail tables.

   You may need to optimize and allocate more space to this tablespace, including the SYSAUX auxiliary tablespace. For more information, see Oracle Database Performance Tuning Guide.

3. Run the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION PL/SQL procedure to specify the name of the destination tablespace.

   For example:

   BEGIN
    DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
     AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, 
     AUDIT_TRAIL_LOCATION_VALUE  => 'AUD_AUX');
   END;
   

   In this example:

   • AUDIT_TRAIL_TYPE: Refers to the database audit trail type. Enter one of the following values:

     • DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail table, AUD$.

     • DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail table, FGA_LOG$.

     • DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trail tables.

   • AUDIT_TRAIL_LOCATION_VALUE: Specifies the destination tablespace. This example specifies a tablespace named AUD_AUX.

Auditing the Database Audit Trail

At times an application must give the SYS.AUD$ system table access to regular users (non-SYSDBA users). For example, an audit report generator needs access to AUD$ table to generate daily reports on possible violations. Also, many installations have a distinct auditor role to achieve separation of duty.

In this case, be aware that DML statements such as INSERT, UPDATE, MERGE, and DELETE are always audited and recorded in the SYS.AUD$ table. You can check these activities by querying the DBA_AUDIT_TRAIL and DBA_COMMON_AUDIT_TRAIL views.

If a user has SELECT, UPDATE, INSERT, and DELETE privileges on SYS.AUD$ and executes a SELECT operation, then the audit trail will have a record of that operation. That is, SYS.AUD$ will have a row identifying the SELECT action on itself, as for example row 1.

If a user later tries to delete this row from SYS.AUD$, then the DELETE operation succeeds, because the user has the privilege to perform this action. However, this DELETE action on SYS.AUD$ is also recorded in the audit trail. Setting up this type of auditing acts as a safety feature, potentially revealing unusual or unauthorized actions.

Archiving the Database Audit Trail

You should periodically archive and then purge the audit trail to prevent it from growing too large. Archiving and purging both frees audit trail space and facilitates the purging of the database audit trail. See "Purging Audit Trail Records" for different ways of purging the audit trail records.

You can create an archive of the database audit trail by using one of the following methods:

• Oracle Audit Vault. You install Oracle Audit Vault separately from Oracle Database. For more information, see Oracle Audit Vault Administrator's Guide.

• Oracle Data Warehouse. Oracle Data Warehouse is automatically installed with Oracle Database. For more information, see Oracle Warehouse Builder Installation and Administration Guide.

After you complete the archive, you can purge the database audit trail contents. See "Purging Audit Trail Records" for more information.

To archive standard and fine-grained audit records, you can copy the relevant records to a normal database table. For example:

INSERT INTO table SELECT ... FROM SYS.AUD$ ...;
INSERT INTO table SELECT ... FROM SYS.FGA_LOG$ ...; 

If the Operating System Audit Trail Becomes Full

Be aware that an operating system audit trail or file system, including the Windows Event Log, can become full, and therefore, unable to accept new records, including audit records from Oracle Database. In this case, Oracle Database cancels and rolls back the operation being performed, including operations that normally are always audited. (See "Activities That Are Always Audited for All Platforms".) If the operating system audit trail becomes full, then set the AUDIT_TRAIL parameter to use database audit trail (such as DB or DB, EXTENDED). This prevents the audited actions from completing if their audit records cannot be stored. You should periodically archive and purge the operating system audit file to prevent these types of failures.

If you plan to use operating system auditing, then ensure that the operating system audit trail or the file system does not fill completely. Most operating systems provide administrators with sufficient information and warning to ensure this does not occur. If you configure auditing to use the database audit trail, you can prevent this potential loss of audit information. Oracle Database prevents audited events from occurring if the audit trail is unable to accept the database audit record for the statement.

Periodically archive and then purge the operating system audit trail. See "Archiving the Operating System Audit Trail" and "Purging Audit Trail Records"for more information.

Setting the Size of the Operating System Audit Trail

To control the size of the operating system audit trail, set the DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE property by using the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY PL/SQL procedure. Remember that you must have the EXECUTE privilege for the DBMS_AUDIT_MGMT PL/SQL package before you can use it. When the operating system file meets the size limitation you set, Oracle Database stops adding records to the current file and then creates a new operating system file for the subsequent records. For more information about the DBMS_AUDIT_MGMT PL/SQL package, see Oracle Database PL/SQL Packages and Types Reference.

If you set both the DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE and the DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE (described in "Setting the Age of the Operating System Audit Trail") properties, then Oracle Database performs the action based the property value limit that is met first.

For example:

BEGIN
  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
   AUDIT_TRAIL_TYPE            =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   AUDIT_TRAIL_PROPERTY        =>  DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE,
   AUDIT_TRAIL_PROPERTY_VALUE  =>  102400);
END;
/

In this example:

• AUDIT_TRAIL_TYPE: Specifies the operating system audit trail. Enter one of the following values:

  • DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS: Operating system audit trail files with the .aud extension. (This setting does not apply to Windows Event Log entries. Nor does it apply to syslog audit records, when the AUDIT_SYSLOG_LEVEL initialization parameter is set.)

  • DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML: XML audit trail files.

  • DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES: Both operating system and XML audit trail files.

• AUDIT_TRAIL_PROPERTY: Specifies the DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE property, which sets the maximum size. To find the status of the current property settings, query the PARAMETER_NAME and PARAMETER_VALUE columns of the DBA_AUDIT_MGMT_CONFIG_PARAMS data dictionary view.

• AUDIT_TRAIL_PROPERTY_VALUE: Sets the maximum size to 102400 kilobytes, that is, 10 megabytes. The default setting is 10,000 kilobytes (approximately 10 megabytes). Do not exceed 2 gigabytes.

Clearing the DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE Setting

To clear the maximum file size setting, use the DBMS_AUDIT_MGMT.CLEAR_AUDIT_TRAIL_PROPERTY procedure.

For example:

BEGIN
  DBMS_AUDIT_MGMT.CLEAR_AUDIT_TRAIL_PROPERTY(
   AUDIT_TRAIL_TYPE        =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   AUDIT_TRAIL_PROPERTY    =>  DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE,
   USE_DEFAULT_VALUES      =>  TRUE );
END;
/

In this example:

• AUDIT_TRAIL_TYPE: Specifies the operating system audit trail. Enter one of the AUDIT_TRAIL_TYPE values described in "Setting the Size of the Operating System Audit Trail".

• AUDIT_TRAIL_PROPERTY: Specifies the DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE property. You can query the DBA_AUDIT_MGMT_CONFIG_PARAMS data dictionary view to find the current status of this property.

• USE_DEFAULT_VALUES: Enter one of the following values:

  • TRUE: Clears the current value and uses the default value, 10,000 kilobytes, instead.

  • FALSE: Oracle Database does not use a default maximum size for the operating system or XML file growth. The files will continue to grow without limitation unless you configure the DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE property. The default setting is FALSE.

Setting the Age of the Operating System Audit Trail

To control the age of the operating system audit trail, use the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY PL/SQL procedure. Remember that you must have the EXECUTE privilege for the DBMS_AUDIT_MGMT PL/SQL package before you can use it. When the operating system file meets the age limitation you set, Oracle Database stops adding records to the current file and then creates a new operating system file for the subsequent records. For more information about the DBMS_AUDIT_MGMT PL/SQL package, see Oracle Database PL/SQL Packages and Types Reference.

If you set both the DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE and the DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE (described in "Setting the Size of the Operating System Audit Trail") properties, then Oracle Database controls the growth of the Audit file based on the property value limit that is met first.

For example:

BEGIN
  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
   AUDIT_TRAIL_TYPE            =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   AUDIT_TRAIL_PROPERTY        =>  DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE,
   AUDIT_TRAIL_PROPERTY_VALUE  =>  10 );
END;
/

In this example:

• AUDIT_TRAIL_TYPE: Specifies the operating system audit trail. Enter one of the following values:

  • DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS: Operating system audit trail files with the .aud extension. (This setting does not apply to Windows Event Log entries. Nor does it apply to syslog audit records, when the AUDIT_SYSLOG_LEVEL initialization parameter is set.)

  • DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML: XML audit trail files.

  • DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES: Both operating system and XML audit trail files.

• AUDIT_TRAIL_PROPERTY: Specifies the DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE property to set the maximum age. To find the status of the current property setting, query the DBA_AUDIT_MGMT_CONFIG_PARAMS data dictionary view.

• AUDIT_TRAIL_PROPERTY_VALUE: Sets the maximum age to 10 days. Enter a value between 1 and 495. The default age is 5 days.

Clearing the DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE Setting

To clear the maximum file age setting, use the DBMS_AUDIT_MGMT.CLEAR_AUDIT_TRAIL_PROPERTY procedure.

For example:

BEGIN
  DBMS_AUDIT_MGMT.CLEAR_AUDIT_TRAIL_PROPERTY(
   AUDIT_TRAIL_TYPE        =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   AUDIT_TRAIL_PROPERTY    =>  DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE,
   USE_DEFAULT_VALUES      =>  TRUE );
END;
/

In this example:

• AUDIT_TRAIL_TYPE: Specifies operating system audit trail. Enter one of the AUDIT_TRAIL_TYPE values listed in "Setting the Age of the Operating System Audit Trail".

• AUDIT_TRAIL_PROPERTY: Specifies the DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE property. Query the PARAMETER_NAME and PARAMETER_VALUE columns of the DBA_AUDIT_MGMT_CONFIG_PARAMS data dictionary view to find the current status of this property.

• USE_DEFAULT_VALUES: Specify one of the following values:

  • TRUE: Clears the current value and uses the default value, 5 days, instead.

  • FALSE: Oracle Database does not use a default maximum age for the operating system or XML file growth. In this case, the files will continue to age without limitation unless you configure the DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE property. The default setting is FALSE.

Archiving the Operating System Audit Trail

You should periodically archive the operating system audit trail. Use your platform-specific operating system tools to create an archive of the operating system audit files.

Use the following methods to archive the operating system audit files:

• Use Oracle Audit Vault. You install Oracle Audit Vault separately from Oracle Database. For more information, see Oracle Audit Vault Administrator's Guide.

• Create tape or disc backups. You can create a compressed file of the audit files, and then store it on tapes or discs. Consult your operating system documentation for more information.

Afterwards, you should purge (delete) the operating system audit records both to free audit trail space and to facilitate audit trail management. See "Purging Audit Trail Records" for different ways that you can use to purge the operating system audit trail records.

Step 1: If Necessary, Tune Online and Archive Redo Log Sizes

The purge process may generate additional redo logs. Before you run this process, you may need to tune online and archive redo log sizes to accommodate the additional records generated during the audit table purge process. For more information about tuning log files, see Oracle Database Performance Tuning Guide and Oracle Database Administrator's Guide.

Step 2: Plan a Timestamp and Archive Strategy

You must record the timestamp of the database and operating system audit records before you can archive them. You can check the timestamp date by querying the DBA_AUDIT_MGMT_LAST_ARCH_TS data dictionary view. Later on, when the purge takes place, Oracle Database purges only the audit trail records that were created before the date of this timestamp. See "Step 4: Optionally, Set an Archive Timestamp for Audit Records" for more information.

After you have timestamped the records, you are ready to archive them. See the following sections for more information:

• "Archiving the Database Audit Trail"

• "Archiving the Operating System Audit Trail"

Step 3: Initialize the Audit Trail Cleanup Operation

Before you can purge the audit trail by using the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL PL/SQL procedure, you must initialize the audit trail for the cleanup operation. For the database audit trail, if you have not moved the database audit trail tables (SYS.AUD$ and SYS.FGA_LOG$) from the SYSTEM tablespace to another tablespace, this process moves these tables to the SYSAUX tablespace or to the tablespace that you specified in "Moving the Database Audit Trail to a Different Tablespace". Be aware that moving these tables takes a while, so you may want to schedule the initialization process during time when the database is not busy.

To initialize the audit trail cleanup operation:

1. Log in to SQL*Plus as an administrative user who has the EXECUTE privilege on the DBMS_AUDIT_MGMT PL/SQL package.

2. If you have not done so already, initialize the audit trail cleanup operation by running the DBMS_AUDIT_MGMT.INIT_CLEANUP procedure. (You only need to perform this step once.

   You can check if the audit trail has been initialized for cleanup by running the DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED function. See "Verifying That the Audit Trail Is Initialized for Cleanup".)

   For example:

   BEGIN
    DBMS_AUDIT_MGMT.INIT_CLEANUP(
     AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
     DEFAULT_CLEANUP_INTERVAL    => 12 );
   END;
   /
   

   In this specification:

   • AUDIT_TRAIL_TYPE: Enter one of the following values:

     • DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail table, AUD$.

     • DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail table, FGA_LOG$.

     • DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trail tables.

     • DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS: Operating system audit trail files with the .aud extension. (This setting does not apply to Windows Event Log entries.)

     • DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML: XML Operating system audit trail files.

     • DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES: Both operating system and XML audit trail files.

     • DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL: All audit trail records, that is, both database audit trail and operating system audit trail types.

   • DEFAULT_CLEANUP_INTERVAL: Specify the desired default hourly purge interval (for example, 12 for every 12 hours). The DBMS_AUDIT_MGMT procedures use this value to determine how to purge audit records. The timing begins when you run the DBMS_AUDIT_MGMT.INIT_CLEANUP procedure. To update this value later, set the DBMS_AUDIT_MGMT.CLEAN_UP_INTERVAL property of the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY procedure.

     The DEFAULT_CLEANUP_INTERVAL setting must indicate the frequency in which DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL is called. If you are uncertain about the frequency, set it to an approximate value. You can change this value later on by using the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY procedure.

Step 4: Optionally, Set an Archive Timestamp for Audit Records

If you want to delete all of the audit trail, you can bypass this step.

You can set a timestamp when the last audit record was archived. Setting an archive timestamp provides a hint to the cleanup infrastructure that the cleanup operation will be invoked every 6 hours.

For the database audit trail, you must set the timestamp after you have initialized the audit trail cleanup operation. To find the last archive timestamps for the audit trail, you can query the DBA_AUDIT_MGMT_LAST_ARCH_TS data dictionary view. After you set the timestamp, all audit records in the audit trail that indicate a time earlier than that timestamp are purged when you run the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL PL/SQL procedure. If you want to clear the archive timestamp setting, see "Clearing the Archive Timestamp Setting".

For the operating system audit trail, remember that you cannot delete individual audit records in the operating system (including XML) audit files. Instead, Oracle Database removes the entire file that contains the timestamped records.

If you are using Oracle Real Application Clusters (Oracle RAC), then use Network Time Protocol (NTP) to synchronize the time on each computer where you have installed an Oracle Database instance. For example, suppose you set the time for one Oracle RAC instance node at 11:00:00 a.m. and then set the next Oracle RAC instance node at 11:00:05. As a result, the two nodes have inconsistent times. You can use Network Time Protocol (NTP) to synchronize the times for these Oracle RAC instance nodes.

To set the timestamp, use the DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP PL/SQL procedure.

For example:

BEGIN
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
   AUDIT_TRAIL_TYPE     =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   LAST_ARCHIVE_TIME    =>  '2009-05-28 06:30:00.00'   
   RAC_INSTANCE_NUMBER  =>  0 );
END;
/

In this example:

• AUDIT_TRAIL_TYPE: Enter one of the following settings:

  • DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Specified the standard audit trail table, AUD$.

  • DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Specifies the fine-grained audit trail table, FGA_LOG$.

  • DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS: Operating system audit trail files with the .aud extension. (This setting does not apply to Windows Event Log entries.)

  • DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML: Specifies XML audit trail files.

• LAST_ARCHIVE_TIME: Enter the timestamp in YYYY-MM-DD HH:MI:SS.FF UTC (Coordinated Universal Time) format for AUDIT_TRAIL_DB_AUD and AUDIT_TRAIL_FGA_STD (standard and fine-grained audit trails), and in the Local Time Zone for AUDIT_TRAIL_OS and AUDIT_TRAIL_XML (operating system and XML audit trails).

• RAC_INSTANCE_NUMBER: Specifies the instance number for an Oracle RAC installation. If you specified the DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD or DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD audit trail types, you can omit the RAC_INSTANCE_NUMBER argument. This is because there is only one AUD$ and FGA_LOG$ table, even for an Oracle RAC installation. The default is 0, which is used for single-instance database installations.

Typically, after you set the timestamp, you can use the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL PL/SQL procedure to remove the audit records that were created before the timestamp date.

Step 5: Create and Schedule the Purge Job

Create and schedule the purge job by running the DBMS_AUDIT_MGMT.CREATE_PURGE_JOB PL/SQL procedure.

For example:

BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
   AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, 
   AUDIT_TRAIL_PURGE_INTERVAL  => 12,
   AUDIT_TRAIL_PURGE_NAME      => 'Standard_Audit_Trail_PJ',
   USE_LAST_ARCH_TIMESTAMP     => TRUE );
END;
/

In this example:

• AUDIT_TRAIL_TYPE: Enter one of the following values:

  • DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail table, AUD$

  • DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail table, FGA_LOG$

  • DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trail tables

  • DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS: Operating system audit trail files with the .aud extension. (This setting does not apply to Windows Event Log entries.)

  • DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML: XML audit trail files

  • DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES: Both operating system and XML audit trail files

  • DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL: All audit trail records, that is, both database audit trail and operating system audit trail types

• AUDIT_TRAIL_PURGE_INTERVAL: Specify the hourly interval for this purge job to run. The timing begins when you run the DBMS_AUDIT_MGMT.CREATE_PURGE_JOB procedure, in this case, 12 hours after you run this procedure. Later on, if you want to update this value, run the DBMS_AUDIT_MGMT.SET_PURGE_JOB_INTERVAL procedure.

• USE_LAST_ARCH_TIMESTAMP: Enter either of the following settings:

  • TRUE: Deletes audit records created before the last archive timestamp. To check the last recorded timestamp, query the LAST_ARCHIVE_TS column of the DBA_AUDIT_MGMT_LAST_ARCH_TS data dictionary view. The default value is TRUE. Oracle recommends that you set USE_LAST_ARCH_TIMESTAMP to TRUE.

  • FALSE: Deletes all audit records without considering last archive timestamp. Be careful about using this setting, in case you inadvertently delete audit records that should have been deleted.

Step 6: Optionally, Configure the Audit Trail Records to be Deleted in Batches

By default, the DBMS_AUDIT_MGMT package procedures delete the database and operating system audit trail records in batches of 10000 database audit records, or 1000 operating system audit files. You can set this batch size to a different value if you want. Later on, when Oracle Database runs the purge job, it deletes each batch, rather than all records together. If the audit trail is very large (and they can grow quite large), deleting the records in batches facilitates the purge operation.

To find the current batch setting, you can query the PARAMETER_NAME and PARAMETER_VALUE columns of the DBA_AUDIT_MGMT_CONFIG_PARAMS data dictionary view. To set the batch size, use the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY procedure. If you later want to clear this setting, see "Clearing the Database Audit Trail Batch Size".

For example:

BEGIN
 DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
  AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  AUDIT_TRAIL_PROPERTY        => DBMS_AUDIT_MGMT.DB_DELETE_BATCH_SIZE,
  AUDIT_TRAIL_PROPERTY_VALUE  => 100000);
END;
/

In this example:

• AUDIT_TRAIL_TYPE: Specifies the audit trail type, which in this case is the database system audit trail. Enter one of the following values:

  • DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail table, AUD$.

  • DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail table, FGA_LOG$.

  • DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS: Operating system audit files.

  • DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML: XML audit files.

• AUDIT_TRAIL_PROPERTY: Uses the DBMS_AUDIT_MGMT.DB_DELETE_BATCH_SIZE property to indicate the database audit trail batch size setting. If you want to batch the operating system audit trail, then use the FILE_DELETE_BATCH_SIZE property.

• AUDIT_TRAIL_PROPERTY_VALUE: Sets the number of audit record files to be 100,000 for each batch. Enter a value between 100 and 1000000. To determine this number, consider the total number of records being purged, and the time interval in which the purge operation is performed. The default is 10000 for the database audit trail and 1000 for the operating system audit trail records.

Verifying That the Audit Trail Is Initialized for Cleanup

You can check if the audit trail has been initialized for cleanup by running the DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED function. If the audit trail has been initialized, then this function returns TRUE. If it is not, it returns FALSE.

For example:

SET SERVEROUTPUT ON
BEGIN
 IF 
   DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD)
 THEN
   DBMS_OUTPUT.PUT_LINE('AUD$ is initialized for cleanup');
 ELSE
   DBMS_OUTPUT.PUT_LINE('AUD$ is not initialized for cleanup.');
 END IF;
END;
/

This example verifies that the database standard audit trail has been initialized and returns a message indicating its status. To select a setting for a different audit trail, choose from the AUDIT_TRAIL_TYPE settings described in "Step 3: Initialize the Audit Trail Cleanup Operation".

Setting the Default Audit Trail Purge Interval for Any Audit Trail Type

You can set a default purge operation interval, in hours, that must pass before the next purge operation takes place for a specified audit trail type.

For example:

BEGIN
 DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
  AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  AUDIT_TRAIL_PROPERTY        => DBMS_AUDIT_MGMT.CLEAN_UP_INTERVAL,
  AUDIT_TRAIL_PROPERTY_VALUE  => 24 );
END;
/

In this example:

• AUDIT_TRAIL_TYPE: Specifies the audit trail type, which in this case is the database standard audit trail. Choose from the following settings:

  • DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail table, AUD$

  • DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail table, FGA_LOG$

  • DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trail tables

  • DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS: Operating system audit trail files with the .aud extension. (This setting does not apply to Windows Event Log entries.)

  • DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML: XML Operating system audit trail files

  • DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES: Both operating system and XML audit trail files

  • DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL: All audit trail records, that is, both database audit trail and operating system audit trail types

  You can set a default interval for multiple audit trail types, so long as they do not conflict. For example, you can set individual intervals for the DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD and DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD properties, but not for the DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD property.

• AUDIT_TRAIL_PROPERTY: Sets the DBMS_AUDIT_MGMT.CLEAN_UP_INTERVAL property to indicate the purge operation interval setting. To find the current property settings, query the PARAMETER_NAME and PARAMETER_VALUE columns of the DBA_AUDIT_MGMT_CONFIG_PARAMS data dictionary view. The timing begins when you set the DBMS_AUDIT_MGMT.CLEAN_UP_INTERVAL property.

• AUDIT_TRAIL_PROPERTY_VALUE: Updates the default hourly interval set by the DBMS_AUDIT_MGMT.INIT_CLEANUP procedure. Enter a value between 1 and 999.

Cancelling the Initialization Cleanup Settings

You can cancel the DBMS_AUDIT_MGMT.INIT_CLEANUP settings, that is, the default cleanup interval, by invoking the DBMS_AUDIT_MGMT.DEINIT_CLEANUP procedure.

For example, to cancel all purge settings for the standard audit trail:

BEGIN
 DBMS_AUDIT_MGMT.DEINIT_CLEANUP(
  AUDIT_TRAIL_TYPE  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD);
END;
/

In this example:

• AUDIT_TRAIL_TYPE: Enter one of the AUDIT_TRAIL_TYPE settings listed in "Step 3: Initialize the Audit Trail Cleanup Operation".

Enabling or Disabling an Audit Trail Purge Job

To enable or disable an audit trail purge job, use the DBMS_AUDIT_MGMT.SET_PURGE_JOB_STATUS PL/SQL procedure.

For example:

BEGIN
 DBMS_AUDIT_MGMT.SET_PURGE_JOB_STATUS(
  AUDIT_TRAIL_PURGE_NAME      => 'OS_Audit_Trail_PJ',
  AUDIT_TRAIL_STATUS_VALUE    => DBMS_AUDIT_MGMT.PURGE_JOB_ENABLE);
END;
/

In this example:

• AUDIT_TRAIL_PURGE_NAME: Specifies a purge job called OS_Audit_Trail_PJ. To find existing purge jobs, query the JOB_NAME and JOB_STATUS columns of the DBA_AUDIT_MGMT_CLEANUP_JOBS data dictionary view.

• AUDIT_TRAIL_STATUS_VALUE: Enter one of the following properties:

  • DBMS_AUDIT_MGMT.PURGE_JOB_ENABLE: Enables the specified purge job.

  • DBMS_AUDIT_MGMT.PURGE_JOB_DISABLE: Disables the specified purge job.

Setting the Default Audit Trail Purge Job Interval for a Specified Purge Job

You can set a default purge operation interval, in hours, that must pass before the next purge job operation takes place. The interval setting that is used in the DBMS_AUDIT_MGMT.CREATE_PURGE_JOB procedure takes precedence over this setting.

For example:

BEGIN
 DBMS_AUDIT_MGMT.SET_PURGE_JOB_INTERVAL(
  AUDIT_TRAIL_PURGE_NAME       => 'OS_Audit_Trail_PJ',
  AUDIT_TRAIL_INTERVAL_VALUE   => 24 );
END;
/

In this example:

• AUDIT_TRAIL_PURGE_NAME: Specifies the name of the audit trail purge job. To find a list of existing purge jobs, query the JOB_NAME and JOB_STATUS columns of the DBA_AUDIT_MGMT_CLEANUP_JOBS data dictionary view.

• AUDIT_TRAIL_INTERVAL_VALUE: Updates the default hourly interval set by the DBMS_AUDIT_MGMT.CREATE_PURGE_JOB procedure. Enter a value between 1 and 999. The timing begins when you run the purge job.

Deleting an Audit Trail Purge Job

To delete an audit trail purge job, use the DBMS_AUDIT_MGMT.DROP_PURGE_JOB PL/SQL procedure. To find existing purge jobs, query the JOB_NAME and JOB_STATUS columns of the DBA_AUDIT_MGMT_CLEANUP_JOBS data dictionary view.

For example:

BEGIN
 DBMS_AUDIT_MGMT.DROP_PURGE_JOB(
  AUDIT_TRAIL_PURGE_NAME  => 'FGA_Audit_Trail_PJ');
END;
/

In this example:

• AUDIT_TRAIL_PURGE_NAME: Specifies a purge job called FGA_Audit_Trail_PJ.

Clearing the Archive Timestamp Setting

To clear the archive timestamp setting, use the DBMS_AUDIT_MGMT.CLEAR_LAST_ARCHIVE_TIMESTAMP PL/SQL procedure.

For example:

BEGIN
  DBMS_AUDIT_MGMT.CLEAR_LAST_ARCHIVE_TIMESTAMP(
   AUDIT_TRAIL_TYPE     =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML',
   RAC_INSTANCE_NUMBER  =>  1 );
END;
/

In this example:

• RAC_INSTANCE_NUMBER: If the AUDIT_TRAIL_TYPE property is set to DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS or DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML, then you cannot set RAC_INSTANCE_NUMBER to 0. You can omit this setting or specify 1 to indicate an instance number.

  You can omit the RAC_INSTANCE_NUMBER setting when AUDIT_TRAIL_TYPE is DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD or DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, or if the database is not an Oracle RAC database. Otherwise, specify the correct instance number. You can find the instance number by issuing the SHOW PARAMETER INSTANCE_NUMBER command in SQL*Plus.

Clearing the Database Audit Trail Batch Size

To clear the batch size setting, use the DBMS_AUDIT_MGMT.CLEAR_AUDIT_TRAIL_PROPERTY procedure.

For example:

BEGIN
  DBMS_AUDIT_MGMT.CLEAR_AUDIT_TRAIL_PROPERTY(
   AUDIT_TRAIL_TYPE        =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   AUDIT_TRAIL_PROPERTY    =>  DBMS_AUDIT_MGMT.DB_DELETE_BATCH_SIZE,
   USE_DEFAULT_VALUES      =>  TRUE );
END;
/

In this example:

• AUDIT_TRAIL_TYPE: Specifies the audit trail type, which in this case is the database system audit trail. Enter one of the AUDIT_TRAIL_TYPE values listed in "Step 6: Optionally, Configure the Audit Trail Records to be Deleted in Batches".

• AUDIT_TRAIL_PROPERTY: Specifies the DB_DELETE_BATCH_SIZE property. Query the DBA_AUDIT_MGMT_CONFIG_PARAMS data dictionary view to find the current status of this property.

• USE_DEFAULT_VALUES: Is set to TRUE, which clears the current audit record batch size and uses the default value, 10000, instead.

Listing Active Statement Audit Options

The following query returns all the statement audit options that are set:

SELECT * FROM DBA_STMT_AUDIT_OPTS;

Output similar to the following appears:

USER_NAME               AUDIT_OPTION         SUCCESS         FAILURE
--------------------    -------------------  ----------      ---------
JWARD                   DROP ANY CLUSTER     BY ACCESS       BY ACCESS
SWILLIAMS               DEBUG PROCEDURE      BY ACCESS       BY ACCESS
MSEDLAK                 ALTER RESOURCE COST  BY ACCESS       BY ACCESS

Listing Active Privilege Audit Options

The following query returns all the privilege audit options that are set:

SELECT * FROM DBA_PRIV_AUDIT_OPTS;

Output similar to the following appears:

USER_NAME           PRIVILEGE            SUCCESS      FAILURE
------------------- -------------------- ---------    ----------
PSMITH              BY ACCESS            BY ACCESS

Listing Active Object Audit Options for Specific Objects

The following query returns all audit options set for any objects with names that start with the characters emp and that are contained in the schema of laurel:

SELECT * FROM DBA_OBJ_AUDIT_OPTS
    WHERE OWNER = 'LAUREL' AND OBJECT_NAME LIKE 'EMP%';

Output similar to the following appears:

OWNER   OBJECT_NAME OBJECT_TY ALT AUD COM DEL GRA IND INS LOC ...
-----   ----------- --------- --- --- --- --- --- --- --- --- ...
LAUREL EMP         TABLE     S/S -/- -/- A/- -/- S/S -/- -/- ...
LAUREL EMPLOYEE    VIEW      -/- -/- -/- A/- -/- S/S -/- -/- ...

The view returns information about all the audit options for the specified object. The information in the view is interpreted as follows:

• A dash (-) indicates that the audit option is not set.

• The S character indicates that the audit option is set BY SESSION.

• The A character indicates that the audit option is set BY ACCESS.

• Each audit option has two possible settings, WHENEVER SUCCESSFUL and WHENEVER NOT SUCCESSFUL, separated by a slash (/). For example, the DELETE audit option for laurel.emp is set BY ACCESS for successful DELETE statements and not set at all for unsuccessful DELETE statements.

Listing Default Object Audit Options

The following query returns all default object audit options:

SELECT * FROM ALL_DEF_AUDIT_OPTS;

Output similar to the following appears:

ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA
--- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
S/S -/- -/- -/- -/- S/S -/- -/- S/S -/- -/- -/- -/- /-  -/-

Notice that the view returns information similar to the USER_OBJ_AUDIT_OPTS and DBA_OBJ_AUDIT_OPTS views (refer to previous example).

Listing Audit Records

The following query lists audit records generated for all objects in the database:

SELECT * FROM DBA_AUDIT_OBJECT;

Listing Audit Records for the AUDIT SESSION Option

The following query lists audit information corresponding to the AUDIT SESSION statement audit option:

SELECT USERNAME, LOGOFF_TIME, LOGOFF_LREAD, LOGOFF_PREAD,
    LOGOFF_LWRITE, LOGOFF_DLOCK
    FROM DBA_AUDIT_SESSION;

Output similar to the following appears:

USERNAME   LOGOFF_TI LOGOFF_LRE LOGOFF_PRE LOGOFF_LWR LOGOFF_DLO
---------- --------- ---------- ---------- ---------- ----------
JWARD      02-AUG-91         53          2         24          0 
SWILLIAMS  02-AUG-91       3337        256        630          0