Skip Headers
Oracle® Database Security Guide
10g Release 2 (10.2)

Part Number B14266-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

12 Configuring and Administering Auditing

Auditing is always about accountability, and is frequently done to protect and preserve privacy for the information stored in databases. Concern about privacy policies and practices has been rising steadily with the ubiquitous use of databases in businesses and on the Internet. Oracle Database provides a depth of auditing that readily enables system administrators to implement enhanced protections, early detection of suspicious activities, and finely-tuned security responses.

The types of auditing available in Oracle Database are described in Chapter 8, "Database Auditing: Security Considerations".

The present chapter explains how to choose the types of auditing you need, how to manage that auditing, and how to use the information gained, in the following sections:

12.1 Actions Audited by Default

Regardless of whether database auditing is enabled, Oracle Database always audits certain database-related operations and writes them to the operating system audit file. This fact is called mandatory auditing, and it includes the following operations:

12.2 Guidelines for Auditing

Oracle Database 10g enables you to send audit records to the database audit trail or the operating system audit trail, when the operating system is capable of receiving them. The database audit records can also be written to operating system files in XML format. The audit trail for database administrators, for example, is typically written to a secure location in the operating system. Writing audit trails to the operating system provides a way for a separate auditor who has root privileges on the operating system to hold all DBAs (who don't have root access) accountable for their actions. These options, added to the broad selection of audit options and customizable triggers or stored procedures, give you the flexibility to implement an auditing scheme that suits your specific business needs.

This section describes guidelines for auditing and contains the following topics:

12.2.1 Keeping Audited Information Manageable

Although auditing is relatively inexpensive, limit the number of audited events as far as possible. Doing so minimizes the performance impact on the execution of audited statements and the size of the audit trail, making it easier to analyze and understand.

Use the following general guidelines when devising an auditing strategy:

  • Evaluate the purpose for auditing.

    After you have a clear understanding of the reasons for auditing, you can devise an appropriate auditing strategy and avoid unnecessary auditing.

    For example, suppose you are auditing to investigate suspicious database activity. This information by itself is not specific enough. What types of suspicious database activity do you suspect or have you noticed? A more focused auditing purpose might be to audit unauthorized deletions from arbitrary tables in the database. This purpose narrows the type of action being audited and the type of object being affected by the suspicious activity.

  • Audit knowledgeably.

    Audit the minimum number of statements, users, or objects required to get the targeted information. This prevents unnecessary audit information from cluttering the meaningful information and consuming valuable space in the SYSTEM tablespace. Balance your need to gather sufficient security information with your ability to store and process it.

    For example, if you are auditing to gather information about database activity, then determine exactly what types of activities you want to track, audit only the activities of interest, and audit only for the amount of time necessary to gather the information that you desire. As another example, do not audit objects if you are only interested in logical I/O information for each session.

12.2.2 Auditing Normal Database Activity

When your purpose for auditing is to gather historical information about particular database activities, use the following guidelines:

  • Audit only pertinent actions.

    To avoid cluttering meaningful information with useless audit records and reduce the amount of audit trail administration, only audit the targeted database activities.

  • Archive audit records and purge the audit trail.

    After you have collected the required information, archive the audit records of interest and purge the audit trail of this information.

  • Privacy considerations

    Privacy regulations often lead to additional business privacy policies. Most privacy laws require businesses to monitor access to personally identifiable information (PII), and such monitoring is implemented by auditing. A business-level privacy policy should address all relevant aspects of data access and user accountability, including technical, legal, and company-policy concerns.

12.2.3 Auditing Suspicious Database Activity

When you audit to monitor suspicious database activity, use the following guidelines:

  • First audit generally, and then specifically.

    When starting to audit for suspicious database activity, it is common that not much information is available to target specific users or schema objects. Therefore, audit options must be set more generally at first. Once preliminary audit information is recorded and analyzed, the general audit options should be turned off and more specific audit options enabled. This process should continue until enough evidence is gathered to draw conclusions about the origin of the suspicious database activity.

  • Protect the audit trail.

    When auditing for suspicious database activity, protect the audit trail so that audit information cannot be added, changed, or deleted without being audited.

12.2.4 Auditing Administrative Users

Sessions for users who connect as SYS can be fully audited, including all users connecting as SYSDBA or SYSOPER. Use the AUDIT_SYS_OPERATIONS initialization parameter to specify whether such users are to be audited. For example, the following setting specifies that SYS is to be audited:

AUDIT_SYS_OPERATIONS = TRUE

The default value, FALSE, disables SYS auditing.

All audit records for SYS are written to the operating system file that contains the audit trail, and not to SYS.AUD$ (also viewable as DBA_AUDIT_TRAIL).

  • In Windows, for example, when AUDIT_TRAIL=OS, audit records are written as events to the Event Viewer log file. If either XML or XML,EXTENDED is specified, then audit records are written in the XML format.

    Notes:

    DB_UNIQUE_NAME is defined as the globally unique name for the database in Oracle Database Reference. Refer to this book for other details and naming rules.

    The adump directory is the first default location used if the AUDIT_FILE_DEST init.ora parameter is not set or does not point to a valid directory. If writing to that first default location fails, then the $ORACLE_HOME/rdbms/audit directory is used as the backup default destination. If that attempt fails, then the audited operation fails and a message is written to the alert log.

    When AUDIT_TRAIL=OS, audit filenames will continue to be of the following form:

    <short_form_of_process_name>_<processid>.aud
    
    

    For example, the short process name ora is used for dedicated server processes, and the names s001, s002, and so on are used for shared server processes.

    When AUDIT_TRAIL=XML, the same audit filenames have the extension xml instead of aud.

  • If the AUDIT_FILE_DEST parameter is not specified, then the default location is $ORACLE_BASE/admin/$DB_UNIQUE_NAME/adump in Solaris and $ORACLE_BASE\admin\$DB_UNIQUE_NAME\adump in Windows.

  • For other operating systems, refer to their audit trail documentation.

All SYS-issued SQL statements are audited indiscriminately and regardless of the setting of the AUDIT_TRAIL initialization parameter.

Consider the following SYS session:

CONNECT / AS SYSDBA;
ALTER SYSTEM FLUSH SHARED_POOL;
UPDATE salary SET base=1000 WHERE name='myname';

When SYS auditing is enabled, both the ALTER SYSTEM and UPDATE statements are displayed in the operating system audit file as follows:

Thu Jan 24 12:58:00 2002
ACTION: 'CONNECT'
DATABASE USER: '/'
OSPRIV: SYSDBA
CLIENT USER: jeff
CLIENT TERMINAL: pts/2
STATUS: 0

Thu Jan 24 12:58:00 2002
ACTION: 'alter system flush shared_pool'
DATABASE USER: ''
OSPRIV: SYSDBA
CLIENT USER: jeff
CLIENT TERMINAL: pts/2
STATUS: 0

Thu Jan 24 12:58:00 2002
ACTION: 'update salary set base=1000 where name='myname''
DATABASE USER: ''
OSPRIV: SYSDBA
CLIENT USER: jeff
CLIENT TERMINAL: pts/2
STATUS: 0

Because of the superuser privileges available to users who connect as SYSDBA, Oracle recommends that DBAs rarely use this connection and only when necessary. Normal day to day maintenance activity can usually be done by DBAs, who are regular database users with the DBA role, or by means of a DBA role (for example, mydba or jr_dba) that your organization customizes.

12.2.5 Using Triggers

You can often use triggers to record additional customized information that is not automatically included in audit records, thereby customizing your own audit conditions and record contents. For example, you could define a trigger on the EMP table to generate an audit record whenever the salary of an employee is increased by more than 10 percent. You can include selected information, such as the values of SALARY before and after it was changed:

CREATE TRIGGER audit_emp_salaries
AFTER INSERT OR DELETE OR UPDATE ON employee_salaries
for each row
begin
if (:new.salary> :old.salary * 1.10)
      then
      insert into emp_salary_audit values (
      :employee_no,
      :old.salary,
      :new.salary,
      user,
      sysdate);
      endif;
end;

Furthermore, you can use event triggers to enable auditing options for specific users on login, and disable them upon logoff.

However, while Oracle triggers can readily monitor DML actions such as INSERT, UPDATE, and DELETE, monitoring on SELECT can be costly and, in some cases, uncertain. Triggers do not enable businesses to capture the statement executed as well as the result set from a query. They also do not enable users to define their own alert action in addition to simply inserting an audit record into the audit trail.

For these capabilities, use fine-grained auditing, which provides an extensible auditing mechanism supporting definition of key conditions for granular audit as well as an event handler to actively alert administrators to misuse of data access rights. Refer to Fine-Grained Auditing.

12.2.6 Deciding Whether to Use the Database or Operating System Audit Trail

The data dictionary of every Oracle database has a table named SYS.AUD$, commonly referred to as the database audit trail. Depending on configuration choices, this table can reside in different schema, such as the traditional SYS schema in the SYSTEM tablespace. For example, when Oracle Label Security is active, the AUD$ table is moved to the SYSTEM schema, with SYS.AUD$ becoming a synonym to SYSTEM.AUD$. The database audit trail is viewable as DBA_AUDIT_TRAIL, and its entries arise from auditing database statements, privileges, or schema objects.

You can optionally choose to store the database audit information in an operating system file. If auditing facility of your operating system writes audit records to a file that Oracle can also write to, then you can direct the database audit entries to this file. For example, the Windows operating system allows Oracle to write audit records as events to the Application Event Log, viewable by the Event Viewer.

Consider the advantages and disadvantages of using either the database or operating system audit trail to store database audit records.

Using the database audit trail offers the following advantages:

  • You can view selected portions of the audit trail with the predefined audit trail views of the data dictionary, such as DBA_AUDIT_TRAIL.

  • You can use Oracle tools (such as Oracle Reports) or third-party tools to generate audit reports.

Using the operating system audit trail offers the following advantages:

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

  • If init.ora specifies AUDIT_TRAIL=XML, then audit records are written to the operating system as XML files. A new dynamic view, V$XML_AUDIT_TRAIL, makes such XML audit records available to DBAs through SQL query, providing enhanced usability. Querying this view causes all XML files (all files with a .xml extension) in the AUDIT_FILE_DEST directory to be parsed and presented in relational table format.

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

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

See Also:

  • Your operating-system-specific documentation for information about its auditing capabilities.

  • Audit Trail Views

12.3 What Information Is Contained in the Audit Trail?

Oracle Database can write records to either the database audit trail, an operating system file, or both. This section describes what information the audit trail contains.

12.3.1 Database Audit Trail Contents

The database audit trail is a single table named SYS.AUD$ in the SYS schema of each Oracle Database data dictionary. Several predefined views are provided to help you use the information in this table, such as DBA_AUDIT_TRAIL.

Audit trail records can contain different types of information, depending on the events audited and the auditing options set. The partial list in the following section shows columns that always appear in the audit trail: if the data they represent is available, then that data populates the corresponding column. (For certain columns, this list has the column name as it displays in the audit record, shown here inside parentheses.) The operating system audit trail has only those columns marked Yes in the corresponding column.

Table 12-1 Audit Trail Record Data

Data Populated in Database Audit Trail In Operating System Audit Trail?
(*) Bind values used for the SQL statement, if any Footnote 1
(*) SQL text (the SQL text that triggered the auditing) Footnote 1
Completion code of the operation Yes
Database user name (DATABASE USER) Yes
Date and time stamp in UTC (Coordinated Universal Time) format No
Distinguished name Yes
Global User unique ID No
Instance number No
Name of the schema object accessed Yes
Operating system login user name (CLIENT USER) Yes
Operation performed or attempted (ACTION) Yes
Process number Footnote 2
Proxy Session audit ID No
SCN (system change number) for the SQL statement No
Session identifier Yes
System privileges used (PRIVILEGE) Yes
Terminal identifier Yes
Transaction ID No

Footnote 1: Asterisked (*) columns in Table 12-1 appear in the audit records only if your database initialization file, init.ora, specifies AUDIT_TRAIL=DB,EXTENDED or AUDIT_TRAIL=XML,EXTENDED. Also, for an array, the values recorded are only the last set of bind values.

Footnote 2: Process number is populated as ProcessId on Unix systems. In Windows systems, the label is ProcessId:ThreadId (or ProcessId if it is not running as a thread).

Note:

If the AUDIT_TRAIL parameter in init.ora is set to XML or XML,EXTENDED, then standard audit records are sent to OS files in XML format. Because XML is a standard document format, many utilities are available to parse and analyze such XML data.

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, it causes an error message and is not done. In most cases, using an operating system log as the audit trail destination allows such an action to complete.

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, this specialized type of auditing can be performed using fine-grained auditing methods.

The DBA_COMMON_AUDIT_TRAIL view combines standard and fine-grained audit log records.

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.

See Also:

Note:

To read from FLASHBACK_TRANSACTION_TABLE or V$LOGMNR_CONTENTS, the following system privilege is required: SELECT ANY TRANSACTION.

12.3.2 Audit Information Stored in an Operating System File

The operating system file that contains the audit trail can include any of the following data:

  • Audit records generated by the operating system

  • Database audit trail records

  • Database actions that are always audited

  • Audit records for administrative users (SYS)

Audit trail records written to an operating system audit trail may contain encoded information, but this information can be decoded using data dictionary tables and error messages as follows:

Encoded Information How to Decode
Action code Describes the operation performed or attempted, using codes listed in the AUDIT_ACTIONS data dictionary table, with their descriptions.
Privileges used Describes any system privileges used to perform the operation, using codes listed in the SYSTEM_PRIVILEGE_MAP table, with their descriptions.
Completion code Describes the result of the attempted operation, using codes listed in Oracle Database Error Messages, with their descriptions. Successful operations return a value of zero and unsuccessful operations return an Oracle error code corresponding to the reason the operation was unsuccessful.

12.4 Managing the Standard Audit Trail

This section describes various aspects of managing standard audit trail information, and contains the following topics:

12.4.1 Enabling and Disabling Standard Auditing

Any authorized database user can set statement, privilege, and object auditing options at any time. However, Oracle Database does not generate audit information for the standard database audit trail unless database auditing is enabled. The security administrator is normally responsible for controlling auditing.

This section discusses the initialization parameters that enable and disable standard auditing.

Note:

  • The initialization parameters AUDIT_SYS_OPERATIONS and AUDIT_TRAIL affecting standard auditing are static. Static means that if you change their values, you must shut down and restart your database for the new values to take effect.

  • The AUDIT_FILE_DEST initialization parameter can be changed with ALTER SYSTEM SET AUDIT_FILE_DEST = <dir> DEFERRED, meaning the new destination will be effective for all subsequent sessions.

12.4.1.1 Setting the AUDIT_TRAIL Initialization Parameter

Database auditing is enabled and disabled by the AUDIT_TRAIL initialization parameter in the database initialization parameter file, init.ora. The parameter can be set to the following values:

Parameter Value Meaning
DB Enables database auditing and directs all audit records to the database audit trail (SYS.AUD$), except for records that are always written to the operating system audit trail
XML All elements of the AuditRecord node except Sql_Text and Sql_Bind will be printed to the operating system XML audit file.
DB,EXTENDED Does all actions of AUDIT_TRAIL=DB and also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, wherever possible. (These two columns are populated only when this parameter is specified.)
XML,EXTENDED Does all actions of AUDIT_TRAIL=XML and also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, wherever possible. (These columns are populated only when this parameter is specified.)
OS Enables database auditing and directs all audit records to an operating system file
NONE Disables standard auditing (This value is the default.)

Note that changes altering what objects are audited do not require restarting the database. Restart is only required if a universal change is made, such as turning on or off all auditing.

Note:

You do not need to set AUDIT_TRAIL to enable either fine-grained auditing or SYS auditing. For fine-grained auditing, you simply add and remove FGA policies as you see fit, applying them to the specific operations or objects you want to monitor. You can use the AUDIT_SYS_OPERATIONS parameter to enable and disable SYS auditing.

12.4.1.2 Specifying a Directory for the Operating System Auditing Trail

The AUDIT_FILE_DEST initialization parameter specifies an operating system directory into which the audit trail is written when either AUDIT_TRAIL=OS or AUDIT_TRAIL=XML is specified. Mandatory auditing information also goes into that directory, as do audit records for user SYS if the AUDIT_SYS_OPERATIONS initialization parameter is specified. AUDIT_FILE_DEST can be changed with ALTER SYSTEM SET AUDIT_FILE_DEST = <dir> DEFERRED, meaning the new destination will be effective for all subsequent sessions.

If the AUDIT_FILE_DEST parameter is not specified, then the default location on Solaris is $ORACLE_BASE/admin/$DB_UNIQUE_NAME/adump, and on Windows, $ORACLE_BASE\admin\$DB_UNIQUE_NAME\adump.

Notes:

  • If your operating system supports an audit trail, then its location is operating-system-specific. For example, when the init.ora file contains AUDIT_TRAIL=OS, Windows operating systems write audit records as events to the application event log.

  • When the init.ora file contains AUDIT_TRAIL=XML (or XML,EXTENDED), audit records are written to XML-formatted operating system files. Setting the AUDIT_FILE_DEST parameter on Windows causes the XML-format audit records to be stored in the directory specified by the parameter.

12.4.1.3 Specifying the Syslog Level

To enable syslog auditing, you assign a value of OS to the AUDIT_TRAIL initialization parameter, as described in "Setting the AUDIT_TRAIL Initialization Parameter". You must also manually add the AUDIT_SYSLOG_LEVEL parameter to the database's initialization parameter file, init.ora. You assign to the AUDIT_SYSLOG_LEVEL parameter a facility and priority in the format AUDIT_SYSLOG_LEVEL=facility.priority. The facility argument describes the part of the operating system that is logging the message while the priority argument defines the severity of the message. The syslog daemon compares the value assigned to the facility argument of the AUDIT_SYSLOG_LEVEL parameter with the syslog.conf file in order 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

Caution:

You should have a strong understanding of how to work with syslog before enabling syslog auditing.

12.4.2 Standard Auditing in a Multitier Environment

In a multitier environment, Oracle preserves the identity of the client through all tiers, which enables auditing of actions taken on behalf of the client. To do such auditing, you use the BY proxy clause in your AUDIT statement.

This clause allows to do the following:

  • Audit SQL statements issued by the specified proxy on its own behalf

  • Audit statements executed on behalf of a specified user or users

  • Audit all statements executed on behalf of any user

The following example audits SELECT TABLE statements issued on behalf of client jackson by the proxy application server appserve.

AUDIT SELECT TABLE
    BY appserve ON BEHALF OF jackson; 

See Also:

Oracle Database Concepts andOracle Database Application Developer's Guide - Fundamentals for more information on proxies and multitier applications

12.4.3 Enabling Standard Auditing Options

You specify one of the four standard auditing options using the AUDIT statement:

Level Effect
Statement Causes auditing of specific SQL statements or groups of statements that affect a particular type of database object. For example, AUDIT TABLE audits the CREATE TABLE, TRUNCATE TABLE, COMMENT ON TABLE, and DELETE [FROM] TABLE statements.
Privilege Audits SQL statements that are authorized by the specified system privilege. For example, AUDIT CREATE ANY TRIGGER audits statements issued using the CREATE ANY TRIGGER system privilege.
Object Audits specific statements on specific objects, such as ALTER TABLE on the emp table.
Network Audits unexpected errors in network protocol or internal errors in the network layer.

To use the AUDIT statement to set statement and privilege options, you must have the AUDIT SYSTEM privilege. To use it to set object audit options, you must own the object to be audited or have the AUDIT ANY privilege.

Audit statements that set statement and privilege audit options can include a BY clause to specify a list of users or application proxies to limit the scope of the statement and privilege audit options.

When setting auditing options, you can also specify the following conditions for auditing:

  • BY SESSION/BY ACCESS

    BY SESSION causes Oracle Database to write a single record for all SQL statements of the same type issued in the same session. BY ACCESS causes Oracle to write one record for each access.

    Note:

    If AUDIT_TRAIL=OS or AUDIT_TRAIL=XML, then multiple records may still be written to the audit trail when BY SESSION is specified. Multiple records occur because while Oracle Database can write to the operating system file, the database cannot read it to detect that an audit entry already exists for the action.
  • WHENEVER SUCCESSFUL/WHENEVER NOT SUCCESSFUL

    WHENEVER SUCCESSFUL chooses auditing only for statements that succeed. WHENEVER NOT SUCCESSFUL chooses auditing only for statements that fail or result in errors.

The implications of your choice of auditing options and the specification of AUDIT statement clauses are discussed in subsequent sections.

A new database session picks up auditing options from the data dictionary when the session is created. These auditing options remain in force for the duration of the database connection. Setting new system or object auditing options causes all subsequent database sessions to use these options. Existing sessions continue using the audit options in place at session creation.

Caution:

The AUDIT statement only specifies auditing options, it does not enable auditing as a whole. To turn auditing on and control whether Oracle Database generates audit records based on the audit options currently set, set the initialization parameter AUDIT_TRAIL as described in "Enabling and Disabling Standard Auditing".

See Also:

Oracle Database SQL Reference for a complete description of the AUDIT statement

12.4.3.1 Enabling Statement Auditing

Valid statement audit options that can be included in AUDIT and NOAUDIT statements are listed in the Oracle Database SQL Reference.

Two special cases of statement auditing are discussed in the following sections.

12.4.3.1.1 Auditing Connections and Disconnections

The SESSION statement option is unique because it does not generate an audit record when a particular type of statement is issued. This option generates a single audit record for each session created by connections to an instance. An audit record is inserted into the audit trail at connect time and updated at disconnect time. Cumulative information about a session is stored in a single audit record that corresponds to the session. This record can include connection time, disconnection time, and logical and physical I/O processed, among other information.

To audit all successful and unsuccessful connections to and disconnections from the database, regardless of user, BY SESSION (the default and only value for this option), enter the following statement:

AUDIT SESSION;

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

AUDIT SESSION
BY jeff, lori;
12.4.3.1.2 Auditing Statements That Fail Because an Object Does Not Exist

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

12.4.3.2 Enabling Privilege Auditing

Privilege audit options exactly match the corresponding system privileges. For example, the option to audit use of the DELETE ANY TABLE privilege is DELETE ANY TABLE. To turn this option on, you use a statement similar to the following example:

AUDIT DELETE ANY TABLE
    BY ACCESS
    WHENEVER NOT SUCCESSFUL;

Oracle Database system privileges are listed in the Oracle Database SQL Reference.

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

AUDIT DELETE ANY TABLE;

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, issue the following statement:

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

The AUDIT SYSTEM system privilege is required to set any statement or privilege audit option. Normally, the security administrator is the only user granted this system privilege.

12.4.3.3 Enabling Object Auditing

The Oracle Database SQL Reference lists valid object audit options and the schema object types for which each option is available.

A user can set any object audit option for the objects contained in the schema of the user. 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. Normally, the security administrator is the only user granted the AUDIT ANY privilege.

To audit all successful and unsuccessful DELETE statements on the jeff.emp table, BY SESSION (the default value), enter the following statement:

AUDIT DELETE ON jeff.emp;

To audit all successful SELECT, INSERT, and DELETE statements on the dept table owned by user jward, BY ACCESS, enter the following statement:

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

To set the default object auditing options to audit all unsuccessful SELECT statements, BY SESSION (the default), enter the following statement:

AUDIT SELECT
     ON DEFAULT
     WHENEVER NOT SUCCESSFUL;

12.4.3.4 Enabling Network Auditing

Valid statement audit options that can be included in AUDIT and NOAUDIT statements are listed in the Oracle Database SQL Reference.

The errors that network auditing uncovers (such as ACTION 122 Network Error in AUDIT_ACTIONS) are not connect failures, but rather can have several possible causes. One such possible cause could be an internal event set by an Oracle engineer purely for testing purposes. Other causes include conflicting configuration settings for encryption, such as the network not finding the information required to create or process expected encryption. Table 12-2 shows four such conditions.

Table 12-2 Auditable Network Error Conditions

Error Cause Action
TNS-02507

Encryption algorithm not installed

After picking an algorithm, the server was unable to find an index for it in its table of algorithms. This should be impossible because the algorithm was chosen (indirectly) from that list. Not normally visible to the user. For further details, turn on tracing and rerun the operation. If error persists, then contact Oracle Support Services.
TNS-12648

Encryption or data integrity algorithm list empty

An Oracle Advanced Security list-of-algorithms parameter was empty. Change the list to contain the name of at least one installed algorithm, or remove the list entirely if every installed algorithm is acceptable.
TNS-12649

Unknown encryption or data integrity algorithm

An Oracle Advanced Security list-of-algorithms parameter included an algorithm name that was not recognized. Remove that algorithm name, correct it if it was misspelled, or install the driver for the missing algorithm.
TNS-12650

No common encryption or data integrity algorithm

The client and server have no algorithm in common for either encryption or data integrity or both. Choose sets of algorithms that overlap. In other words, add one of the client algorithm choices to the server list or add one of the server list choices to the client algorithm.

12.4.4 Disabling Standard Audit Options

The NOAUDIT statement turns off the various audit options of Oracle Database 10g. 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 or BY proxy option to specify a list of users to limit the scope of the statement and privilege audit options.

You can use a 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 BY SESSION/BY ACCESS option pair is not supported by the NOAUDIT statement. Audit options, no matter how they were turned on, are turned off by an appropriate NOAUDIT statement.

Caution:

The NOAUDIT statement only specifies auditing options. It does not disable auditing as a whole. To turn auditing off and stop Oracle Database from generating audit records, set the initialization parameter AUDIT_TRAIL in the database's initialization parameter file as described in "Enabling and Disabling Standard Auditing".

See Also:

Oracle Database SQL Reference for a complete syntax listing of the NOAUDIT statement

12.4.4.1 Turning Off Statement and Privilege Auditing

The following statements turn off the corresponding audit options:

NOAUDIT session;
NOAUDIT session BY jeff, lori;
NOAUDIT DELETE ANY TABLE;
NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE,
    EXECUTE PROCEDURE;

The following statement turns off all statement audit options:

NOAUDIT ALL;

The following statement turns off all privilege audit options:

NOAUDIT ALL PRIVILEGES;

To disable statement or privilege auditing options, you must have the AUDIT SYSTEM system privilege.

12.4.4.2 Turning Off Object Auditing

The following statements turn off the corresponding auditing options:

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

Furthermore, to turn off all object audit options on the emp table, enter the following statement:

NOAUDIT ALL
   ON emp;

To turn off 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 disable object audit options for a specific object, you must be the owner of the schema object. To disable the object audit options of an object in another user's schema or to disable default object audit options, you must have the AUDIT ANY system privilege. A user with privileges to disable object audit options of an object can override the options set by any user.

12.4.4.3 Turning Off Network Auditing

The following statement turns off network auditing:

NOAUDIT NETWORK;

Recording of db link usage and login type stops.

12.4.5 Controlling the Growth and Size of the Standard Audit Trail

If the audit trail is full and no more audit records can be inserted, then audited statements cannot be successfully executed until the audit trail is purged. Warnings are returned to all users that issue audited statements. Therefore, the security administrator must control the growth and size of the audit trail.

When auditing is enabled and audit records are being generated, the audit trail grows 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.

  • Be very 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.

  • Tightly control the ability to perform object auditing. This can be done in two different ways:

    • A security administrator owns all objects and the AUDIT ANY system privilege is never granted 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) and the security administrator is the only user granted the AUDIT ANY system privilege.

    In both scenarios, object auditing is controlled entirely by the security administrator.

The maximum size of the database audit trail (SYS.AUD$ table) is determined by the default storage parameters of the SYSTEM tablespace, in which it is stored.

See Also:

Operating-system-specific Oracle documentation for more information about managing the operating system audit trail when directing audit records to that location

12.4.5.1 Purging Audit Records from the Audit Trail

After auditing is enabled for some time, the security administrator may want to delete records from the database audit trail both to free audit trail space and to facilitate audit trail management.

For example, to delete all audit records from the audit trail, enter the following statement:

DELETE FROM SYS.AUD$;

Alternatively, to delete all audit records from the audit trail generated as a result of auditing the table emp, enter the following statement:

DELETE FROM SYS.AUD$
     WHERE obj$name='EMP';

Note:

All deletes from the audit trail are audited without exception. Refer to "Auditing the Standard Audit Trail" and "Auditing Administrative Users".

Only the user SYS, a user who has the DELETE ANY TABLE privilege, or a user to whom SYS has granted DELETE privilege on SYS.AUD$ can delete records from the database audit trail.

Note:

If the audit trail is full and connections are being audited (that is, if the SESSION option is set), then typical users cannot connect to the database because the associated audit record for the connection cannot be inserted into the audit trail. In this case, the security administrator must connect as SYS (operations by SYS are not audited) and make space available in the audit trail.

12.4.5.2 Archiving Audit Trail Information

If audit trail information must be archived for historical purposes, then the security administrator can copy the relevant records to a normal database table (for example, using INSERT INTO table SELECT ... FROM SYS.AUD$ ...) or export the audit trail table to an operating system file.

See Also:

Oracle Database Utilities for information about exporting tables

12.4.5.3 Reducing the Size of the Audit Trail

As with any database table, after records are deleted from the database audit trail, the extents allocated for this table still exist.

If the database audit trail has many extents allocated for it, but many of them are not being used, then the space allocated to the database audit trail can be reduced by following these steps:

  1. If you want to save information currently in the audit trail, then copy it to another database table or export it by using the EXPORT utility.

  2. Connect as a user with administrator privileges.

  3. Truncate SYS.AUD$ using the TRUNCATE statement.

  4. Reload archived audit trail records generated in Step 1.

The new version of SYS.AUD$ is allocated only as many extents as are necessary to contain current audit trail records.

Note:

SYS.AUD$ is the only SYS object that should ever be directly modified.

12.4.6 Protecting the Standard Audit Trail

When auditing for suspicious database activity, protect the integrity of the audit trail's records to guarantee the accuracy and completeness of the auditing information.

Audit records generated as a result of object audit options set for the SYS.AUD$ table can only be deleted from the audit trail by someone connected with administrator privileges, which itself has protection against unauthorized use.

12.4.7 Auditing the Standard Audit Trail

If an application needs to give SYS.AUD$ access to regular users (non-SYSDBA users), then such access needs to be audited.

To do so, you turn on the relevant auditing options for SYS.AUD$, which work a little differently because they are auditing actions on the audit trail(aud$) itself:

  1. CONNECT sys/passw AS SYSDBA

  2. Issue the following command:

    AUDIT SELECT, INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;
    
    

Please note that this command will AUDIT actions performed by non-SYSDBA users only.

If a regular 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 say row1.

If a user later tries to DELETE this row1 from SYS.AUD$, then the DELETE will succeed, 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. A logfile for an illustrative test case appears at the end of this chapter, at The SYS.AUD$ Auditing Table: Example.

Note:

DELETE, INSERT, UPDATE, and MERGE operations on SYS.AUD$ table are always audited, and such audit records are not allowed to be deleted.

12.5 Viewing Database Audit Trail Information

The database audit trail (SYS.AUD$) is a single table in each Oracle database data dictionary. Several predefined views are available to present auditing information from this table in a meaningful way. If you decide not to use auditing, then you can later delete these views. The following subsections show you what is in these views, how to use them, and how to delete them:

12.5.1 Audit Trail Views

The following views are created upon installation:

View Description
STMT_AUDIT_OPTION_MAP
Contains information about auditing option type codes. Created by the SQL.BSQ script at CREATE DATABASE time.
AUDIT_ACTIONS
Contains descriptions for audit trail action type codes.
ALL_DEF_AUDIT_OPTS
Contains default object-auditing options that will be applied when objects are created.
DBA_STMT_AUDIT_OPTS
Describes current system auditing options across the system and by user.
DBA_PRIV_AUDIT_OPTS
Describes current system privileges being audited across the system and by user.
DBA_OBJ_AUDIT_OPTS
USER_OBJ_AUDIT_OPTS
Describes auditing options on all objects. The USER view describes auditing options on all objects owned by the current user.
DBA_AUDIT_TRAIL
USER_AUDIT_TRAIL
Lists all audit trail entries. The USER view shows audit trail entries relating to current user.
DBA_AUDIT_OBJECT
USER_AUDIT_OBJECT
Contains audit trail records for all objects in the system. The USER view lists audit trail records for statements concerning objects that are accessible to the current user.
DBA_AUDIT_SESSION
USER_AUDIT_SESSION 
Lists all audit trail records concerning CONNECT and DISCONNECT. The USER view lists all audit trail records concerning connections and disconnections for the current user.
DBA_AUDIT_STATEMENT
USER_AUDIT_STATEMENT
Lists audit trail records concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements throughout the database, or for the USER view, issued by the user.
DBA_AUDIT_EXISTS
Lists audit trail entries produced BY AUDIT NOT EXISTS.
DBA_AUDIT_POLICIES
Shows all the auditing policies on the system.
DBA_FGA_AUDIT_TRAIL
Lists audit trail records for value-based auditing.
DBA_COMMON_AUDIT_TRAIL
Combines standard and fine-grained audit log records, and includes SYS and mandatory audit records written in XML format.

See Also:

Oracle Database Reference for detailed descriptions of the predefined views in Oracle Database

12.5.2 Using Audit Trail Views to Investigate Suspicious Activities

This section offers examples that demonstrate how to examine and interpret the information in the audit trail. Consider the following situation.

You would like to audit the database for the following suspicious activities:

  • Passwords, tablespace settings, and quotas for some database users are altered without authorization.

  • A high number of deadlocks occur, most likely because of users acquiring exclusive table locks.

  • Rows are arbitrarily deleted from the emp table in jeff's schema.

You suspect the users jward and swilliams of several of these detrimental actions.

To investigate, you issue the following statements (in the order specified):

AUDIT ALTER, INDEX, RENAME ON DEFAULT
    BY SESSION;
CREATE VIEW jeff.employee AS SELECT * FROM jeff.emp;
AUDIT SESSION BY jward, swilliams;
AUDIT ALTER USER;
AUDIT LOCK TABLE
    BY ACCESS
    WHENEVER SUCCESSFUL;
AUDIT DELETE ON jeff.emp
    BY ACCESS
    WHENEVER SUCCESSFUL;

The following statements are subsequently issued by the user jward:

ALTER USER tsmith QUOTA 0 ON users;
DROP USER djones;

The following statements are subsequently issued by the user swilliams:

LOCK TABLE jeff.emp IN EXCLUSIVE MODE;
DELETE FROM jeff.emp WHERE mgr = 7698;
ALTER TABLE jeff.emp ALLOCATE EXTENT (SIZE 100K);
CREATE INDEX jeff.ename_index ON jeff.emp (ename);
CREATE PROCEDURE jeff.fire_employee (empid NUMBER) AS
  BEGIN
    DELETE FROM jeff.emp WHERE empno = empid;
  END;
/

EXECUTE jeff.fire_employee(7902);

The following sections display the information relevant to your investigation that can be viewed using the audit trail views in the data dictionary:

12.5.2.1 Listing Active Statement Audit Options

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

SELECT * FROM DBA_STMT_AUDIT_OPTS;

USER_NAME               AUDIT_OPTION         SUCCESS         FAILURE
--------------------    -------------------  ----------      ---------
JWARD                   SESSION              BY SESSION      BY SESSION
SWILLIAMS               SESSION              BY SESSION      BY SESSION
                        LOCK TABLE           BY ACCESS       NOT SET

Notice that the view reveals the statement audit options set, whether they are set for success or failure (or both), and whether they are set for BY SESSION or BY ACCESS.

12.5.2.2 Listing Active Privilege Audit Options

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

SELECT * FROM DBA_PRIV_AUDIT_OPTS;

USER_NAME           PRIVILEGE            SUCCESS      FAILURE
------------------- -------------------- ---------   ----------
ALTER USER          BY SESSION           BY SESSION

12.5.2.3 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 jeff's schema:

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

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

Notice that 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 jeff.emp is set BY ACCESS for successful delete statements and not set at all for unsuccessful delete statements.

12.5.2.4 Listing Default Object Audit Options

The following query returns all default object audit options:

SELECT * FROM ALL_DEF_AUDIT_OPTS;

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).

12.5.2.5 Listing Audit Records

The following query lists audit records generated by statement and object audit options:

SELECT * FROM DBA_AUDIT_OBJECT;

12.5.2.6 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;

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 

12.5.3 Deleting the Audit Trail Views

If you disable auditing and no longer need the audit trail views, then delete them by connecting to the database as SYS and running the script file CATNOAUD.SQL. The name and location of the CATNOAUD.SQL script are operating-system-dependent.

12.5.4 The SYS.AUD$ Auditing Table: Example

The code in this section illustrates the auditing of changes made to SYS.AUD$.

SQL> @t
SQL> 
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL> 
SQL> column username format a10
SQL> column owner  format a10
SQL> column obj_name format a6
SQL> column action_name format a17
SQL> SET ECHO ON
SQL> 
SQL> connect sys/newdbapassword as sysdba
Connected.
SQL> grant select, insert, update, delete on sys.aud$ to jeff;

Grant succeeded.

SQL> grant select on dba_audit_trail to jeff;

Grant succeeded.

SQL> audit select, update, delete on sys.aud$ by access;

Audit succeeded.

SQL> truncate table sys.aud$;

Table truncated.

SQL> 
SQL> connect jeff/wolf
Connected.
SQL> select count(*) from emp

  COUNT(*)
----------
         0

1 row selected.

SQL> 
SQL> select statementid,entryid,username,action_name,returncode,owner,
  2  obj_name,substr(priv_used,1,8) priv,  SES_ACTIONS
  3  from dba_audit_trail
  4  order by sessionid,entryid;

STATEMENTID    ENTRYID USERNAME   ACTION_NAME       RETURNCODE OWNER      OBJ_NA
----------- ---------- ---------- ----------------- ---------- ---------- ------
PRIV     SES_ACTIONS
-------- -------------------
          8          1 JEFF      SELECT                     0 SYS        AUD$


1 row selected.

SQL> 
SQL> update sys.aud$ set userid = 0;

2 rows updated.

SQL> select statementid,entryid,username,action_name,returncode,owner,
  2  obj_name,substr(priv_used,1,8) priv,  SES_ACTIONS
  3  from dba_audit_trail
  4  order by sessionid,entryid;

STATEMENTID    ENTRYID USERNAME   ACTION_NAME       RETURNCODE OWNER      OBJ_NA
----------- ---------- ---------- ----------------- ---------- ---------- ------
PRIV     SES_ACTIONS
-------- -------------------
          8          1 0          SELECT                     0 SYS        AUD$

          9          2 0          SELECT                     0 SYS        AUD$

         10          3 JEFF       UPDATE                     0 SYS        AUD$

3 rows selected.

SQL> 
SQL> delete from sys.aud$;

3 rows deleted.

SQL> select statementid,entryid,username,action_name,returncode,owner,
  2  obj_name,substr(priv_used,1,8) priv,  SES_ACTIONS
  3  from dba_audit_trail
  4  order by sessionid,entryid;

STATEMENTID    ENTRYID USERNAME   ACTION_NAME       RETURNCODE OWNER      OBJ_NA
----------- ---------- ---------- ----------------- ---------- ---------- ------
PRIV     SES_ACTIONS
-------- -------------------
         10          3 JEFF      UPDATE                     0 SYS        AUD$

         12          5 JEFF      DELETE                     0 SYS        AUD$

2 rows selected.

SQL> 
SQL> connect sys/newdbapassword as sysdba
Connected.
SQL> noaudit insert, select, update, delete on sys.aud$;

Noaudit succeeded.

SQL> 
SQL> spool off

12.6 Fine-Grained Auditing

As described earlier in this chapter and in Chapter 8, standard Oracle auditing is highly configurable. Its audit trail provides a fixed set of facts that monitor privileges, object access, or (optionally) SQL usage, including information about the environment or query results. The scope of standard auditing can also be substantially expanded by using triggers, and providing additional customized information.

However, two auditing goals are not directly addressed by any mechanism in standard auditing: minimizing unhelpful audits, and proving that access rights were violated. Access logs, while helpful in reconstructing events, can often be inconclusive.

Fine-grained auditing addresses these needs, taking you beyond standard auditing and enabling you to minimize false or unhelpful audits by specifying more detailed audit conditions. You do not need to set AUDIT_TRAIL to enable fine-grained auditing. You simply add and remove FGA policies as you see fit, applying them to the specific operations or objects you want to monitor. A built-in audit mechanism in the database prevents users from bypassing the audit. Fine-grained auditing records are stored in SYS.FGA_LOG$ table and are accessible through the DBA_FGA_AUDIT_TRAIL view.

Note:

The DBA_COMMON_AUDIT_TRAIL view combines standard and fine-grained audit log records.

See Also:

To add, drop, enable, or disable policies, use The DBMS_FGA Package

12.6.1 Policies in Fine-Grained Auditing

Policies you establish with fine-grained auditing can monitor data access based on content. Using policies, you can specify the columns and conditions that you want audit records for. Conditions can include limiting the audit to specific types of DML statements used in connection with the columns that you specify. You can also provide the name of the routine you want called when an audit event occurs. This routine can notify or alert administrators or handle errors and anomalies.

For example, most companies logically want to limit access to the specifications or test results for a product under development, and prefer that salary information remain private. Auditors need enough detail to be able to determine the data that was accessed.

Knowing only that SELECT privilege was used by a specific user on a particular table is not specific enough to provide accountability. A central tax authority has similar privacy concerns, needing to track access to tax returns so that employees do not snoop. Similarly, government agencies that use informants need detailed tracking of access to the database containing their identities. Such agencies also need enough detail to determine the data that was accessed, and not just information that the SELECT privilege was used by JEFF on the TAXPAYERS or INFORMANTS table.

12.6.1.1 Advantages of Fine-Grained Auditing over Triggers

Fine-grained auditing meets these needs by providing functionality (and efficiency) beyond triggers. Triggers incur a PL/SQL process call for every row processed and create an audit record only when a relevant column is changed by a DML statement.

A fine-grained auditing policy, on the other hand, does not incur this cost for every row. Instead, it audits only once for every policy. Specifically, it audits when a specified relevant column occurs in a specified type of DML statement, either being changed by the statement or being in its selection criteria. This combination of criteria uncovers users who hope their information gathering will be masked because they only use the selection criteria of a DML statement. Triggers also cannot monitor the activity of another instead-of trigger on the same object, while fine-grained auditing supports tables and views.

12.6.1.2 Extensible Interface Using Event Handler Functions

Organizations can define fine-grained auditing policies to specify the data access conditions that are to trigger audit events. These policies can use flexible event handlers that notify administrators when a triggering event has occurred. For example, an organization may allow HR clerks to access employee salary information, but trigger an audit event when salaries greater than $500K are accessed. The audit policy (where SALARY > 500000) is applied to the EMPLOYEES table through an audit policy interface (DBMS_FGA, a PL/SQL package).

The audit function (handler_module) is an alerting mechanism for the administrator. The required interface for such a function is as follows:

PROCEDURE fname ( object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2 )  AS ...

Where:

  • fname is the name of the procedure

  • object_schema is the name of the schema of the table audited

  • object_name is the name of the table to be audited

  • policy_name is the name of the policy being enforced

12.6.1.3 Functions and Relevant Columns in Fine-Grained Auditing

For additional flexibility in implementation, organizations can employ a user-defined function to determine the policy condition and identify an audit column (called a relevant column) to further refine the audit policy. For example, the function could cause an audit record only when a salary greater than $250,000 is accessed.

Specifying a relevant column helps reduce the instances of false or unnecessary audit records, because the audit need only be triggered when a particular column is referenced in the query. For example, an organization may only wish to audit executive salary access when an employee name is accessed, because accessing salary information alone is not meaningful unless an HR clerk also selects the corresponding employee name. You can, however, specify that auditing occur only when all relevant columns are referenced.

If more than one relevant audit column is specified, then Oracle Database produces an audit record if the SQL statement references any of those audit columns.

The DBMS_FGA package administers these value-based audit policies. The security administrator creates an audit policy on the target object using the functions in the DBMS_FGA package.

See also:

12.6.1.4 Audit Records in Fine-Grained Auditing

If any rows returned from a query block match the audit condition, then an audit event entry is inserted into the fine-grained audit trail. This entry includes user name, SQL text, bind variable, policy name, session ID, time stamp, and other attributes. Only one row of audit information is inserted into the audit trail for every FGA policy that evaluates to true. As part of the extensibility framework, administrators can also optionally define an appropriate audit event handler to process the event, for example sending an alert page to the administrator.

12.6.1.5 NULL Audit Conditions

To guarantee auditing of the specified actions (statement_types) affecting the specified columns (audit_column), specify the audit_condition as NULL (or omit it), which is interpreted as TRUE. Only specifying NULL will guarantee auditing of the specified actions (statement_types) affecting the specified columns (audit_column). The former practice of specifying an audit condition of 1=1 to force such auditing should no longer be used and will not reliably achieve the desired result. NULL will cause audit even if no rows were processed, so that all actions on an audit_column with this policy are audited.

Note:

Using an empty string is not equivalent to NULL and will not reliably cause auditing of all actions on a table with this policy.

The audit function is executed as an autonomous transaction, committing only the actions of the handler_module and not any user transaction. This function has no effect on any user SQL transaction.

If NULL or no audit condition is specified, then any action on a table with that policy causes an audit record to be created, whether or not rows are returned.

12.6.1.6 Defining FGA Policies

The administrator uses the DBMS_FGA.ADD_POLICY interface to define each FGA policy for a table or view, identifying any combination of SELECT, UPDATE, DELETE, or INSERT statements. Oracle supports MERGE statements as well, by auditing the underlying actions of INSERT and UPDATE. To audit MERGEs, set up FGA on these INSERTs and UPDATEs. Only one record is generated for each policy for successful MERGEs.

FGA policies associated with a table or view may also specify relevant columns, so that any specified statement type affecting a particular column is audited. More than one column can be included as relevant columns in a single FGA policy. Examples include privacy-relevant columns, such as those containing social security numbers, salaries, patient diagnoses, and so on. If no relevant column is specified, then auditing applies to all columns. That is, auditing occurs whenever any specified statement type affects any column, unless you specify in the policy that auditing is to occur only when all relevant columns are referenced.

12.6.2 An Added Benefit to Fine-Grained Auditing

In general, fine-grained auditing policies are based on simple user-defined SQL predicates on table objects as conditions for selective auditing. During fetching, whenever policy conditions are met for a returning row, the query is audited. Later, Oracle Database can run a user-defined audit event handler, if specified in the policy, using autonomous transactions to process the event.

Fine-grained auditing can be implemented in user applications using the DBMS_FGA package or by using database triggers.

The following example shows how you can audit statements (INSERT, UPDATE, DELETE, and SELECT) on table hr.emp to monitor any query that accesses the salary column of the employee records that belong to sales department:

DBMS_FGA.ADD_POLICY(
object_schema => 'hr',
object_name   => 'emp',
policy_name   => 'chk_hr_emp',
audit_condition => 'dept = ''SALES'' ', 
audit_column => 'salary'
statement_types => 'insert,update,delete,select');

Then, any of the following SQL statements will cause the database to log an audit event record.

SELECT count(*) FROM hr.emp WHERE dept = 'SALES' and salary > 10000000;

SELECT salary FROM hr.emp WHERE dept = 'SALES';

DELETE from hr.emp where salary >1000000

With all the relevant information available, and a trigger-like mechanism to use, the administrator can define what to record and how to process the audit event.

Consider the following commands:

/* create audit event handler */
CREATE PROCEDURE sec.log_id (schema1 varchar2, table1 varchar2, policy1 varchar2) AS
BEGIN
UTIL_ALERT_PAGER(schema1, table1, policy1);      -- send an alert note to my pager
END;

/* add the policy */
DBMS_FGA.ADD_POLICY(
object_schema => 'hr',
object_name   => 'emp',
policy_name   => 'chk_hr_emp',
audit_condition => 'dept = ''SALES'' ', 
audit_column => 'salary',
handler_schema => 'sec',
handler_module => 'log_id',
enable               =>  TRUE);

Note:

Because schema and table are reserved words, they cannot be used as variables without some alteration, such as appending 1 as is done here.

After the first row of interest is fetched, the event is recorded, and the SEC.LOG_ID audit function is run. The audit event record generated is stored in DBA_FGA_AUDIT_TRAIL, which is fga_log$ in the SYS schema in the SYSTEM tablespace. This table has reserved columns (such as SQL_TEXT and SQL_BIND) for recording SQL text, policy name, and other information. The SQLBIND and SQLTEXT values are recorded in the LSQLTEXT and LSQLBIND columns of fga_log$ only if the policy specifies audit_trail = DBMS_FGA.DB + DBMS_FGA.EXTENDED. If the policy specifies AUDIT_TRAIL=DBMS_FGA.XML, then the audit records would be written to XML-formatted OS files.

Note:

  • Fine-grained auditing is supported only with cost-based optimization. For queries using rule-based optimization, audit will check before applying row filtering, which could result in an unnecessary audit event trigger.

  • Policies currently in force on an object involved in a flashback query are applied to the data returned from the specified flashback snapshot (based on time or SCN).

See Also:

12.7 The DBMS_FGA Package

The DBMS_FGA package provides fine-grained security functions. The execute privilege on DBMS_FGA is needed for administering audit policies. Because the audit function can potentially capture all user environment and application context values, policy administration should be executable by privileged users only.

This feature is available only for cost-based optimization. The rule-based optimizer may generate unnecessary audit records because audit monitoring can occur before row filtering. For both the rule-based optimizer and the cost-based optimizer, you can refer to DBA_FGA_AUDIT_TRAIL to analyze the SQL text and corresponding bind variables that are issued.

The procedures for this package are described in the following subsections:

The syntax, parameters, and usage notes accompanying each procedure description also discuss the defaults and restrictions that apply to it.

12.7.1 ADD_POLICY Procedure

This procedure creates an audit policy using the supplied predicate as the audit condition. The maximum number of FGA policies on any table or view object is 256.

12.7.1.1 Syntax

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);

12.7.1.2 Parameters

Table 12-3 ADD_POLICY Procedure Parameters

Parameter Description Default Value
object_schema The schema of the object to be audited. (If NULL, then the current login user schema is assumed.) NULL
object_name The name of the object to be audited. -
policy_name The unique name of the policy. -
audit_condition A condition in a row that indicates a monitoring condition. NULL is allowed and acts as TRUE. NULL
audit_column The columns to be checked for access. These can include hidden columns. The default, NULL, causes audit if any column is accessed or affected. NULL
handler_schema The schema that contains the event handler. The default, NULL, causes the current schema to be used. NULL
handler_module The function name of the event handler includes the package name if necessary. This function is called only after the first row that matches the audit condition in the query is processed. If the procedure fails with an exception, then the user SQL statement will fail as well. NULL
enable Whether the policy is to be enabled: TRUE means enable it. TRUE
statement_types The SQL statement types to which this policy is applicable: INSERT, UPDATE, DELETE, or SELECT only. SELECT
audit_trail Both where to write the fine-grained audit trail and whether or not to populate LSQLTEXT and LSQLBIND. DB+EXTENDED
audit_column_opts Whether a statement is audited when the query references any column specified in the audit_column parameter or only when all such columns are referenced. ANY_COLUMNS

12.7.1.3 Usage Notes

Usage notes are described as follows:

  • Sample command:

    DBMS_FGA.ADD_POLICY(object_schema => 'scott', object_name=>'emp', policy_name => 'mypolicy1', audit_condition => 'sal < 100', audit_column =>'comm, credit_card, expirn_date', handler_schema => NULL, handler_module => NULL, enable => TRUE, statement_types=> 'INSERT, UPDATE', audit_trail => DBMS_FGA.DB+DBMS_FGA.EXTENDED, audit_column_opts => DBMS_FGA.ALL_COLUMNS);
    
    
  • If object_schema is not specified, then the current login user schema is assumed.

  • An FGA policy should not be applied to out-of-line columns such as LOB columns.

  • Each audit policy is applied to the query individually. However, at most one audit record may be generated for each policy, no matter how many returned rows satisfy the audit_condition of the policy. In other words, whenever the number of rows returned satisfy an audit condition defined on the table, a single audit record is generated for each such policy.

  • If a table with an FGA policy defined on it receives a Fast Path insert or a vectored update, then the hint is automatically disabled before any such operations. Disabling the hint allows auditing to occur according to the policy terms. (One example of a Fast Path insert is the statement INSERT-WITH-APPEND-hint.)

  • The audit_condition must be a Boolean expression that can be evaluated using the values in the row being inserted, updated, or deleted. This condition can be NULL (or omitted), which is interpreted as TRUE, but it cannot contain the following elements:

    • Subqueries or sequences

    • Any direct use of SYSDATE, UID, USER or USERENV functions. However, a user-defined function and other SQL functions can use these functions to return the desired information.

    • Any use of the pseudocolumns LEVEL, PRIOR, or ROWNUM.

    Specifying an audit condition of 1=1 to force auditing of all specified statements (statement_types) affecting the specified column (audit_column) is no longer needed to achieve this purpose. NULL will cause audits even if no rows were processed, so that all actions on a table with this policy are audited.

  • The audit function (handler_module) is an alerting mechanism for the administrator. The required interface for such a function is as follows:

    PROCEDURE <fname> ( object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2 )  AS ...
    
    

    where fname is the name of the procedure, object_schema is the name of the schema of the table audited, object_name is the name of the table to be audited, and policy_name is the name of the policy being enforced.

  • The audit_trail parameter specifies both where the fine-grained audit trail will be written and whether the audit trail should include the associated SQL Text and SQL Bind variable information (typically in columns named LSQLTEXT and LSQLBIND):

    • If audit_trail includes XML, then fine-grained audit records are written to XML-format operating system files, irrespective of the init.ora AUDIT_TRAIL parameter. The XML-format operating system files are stored in the directory specified by an AUDIT_FILE_DEST init.ora parameter. (The default AUDIT_FILE_DEST is $ORACLE_BASE/admin/$DB_UNIQUE_NAME/adump on Unix-based systems, and $ORACLE_BASE\admin\$DB_UNIQUE_NAME\adump on Windows systems.)

    • If audit_trail includes DB instead, then the audit records are written to the SYS.FGA_LOG$ table in the database.

    • If audit_trail includes EXTENDED, then the SQL Text and SQL Bind variable information for the query are included in the audit trail.

    • For example:

      • Setting audit_trail to DBMS_FGA.DB sends the audit trail to the SYS.FGA_LOG$ table in the database and omits SQL Text and SQL Bind.

      • Setting audit_trail to DBMS_FGA.DB+EXTENDED sends the audit trail to the SYS.FGA_LOG$ table in the database and includes SQL Text and SQL Bind.

      • Setting audit_trail to DBMS_FGA.XML writes the audit trail in XML files sent to the operating system and omits SQL Text and SQL Bind.

      • Setting audit_trail to DBMS_FGA.XML+EXTENDED writes the audit trail in XML files sent to the operating system and includes SQL Text and SQL Bind.

      Note:

      When the init.ora file contains AUDIT_TRAIL=XML (or XML,EXTENDED), audit records are written to XML-formatted operating system files.

    The audit_trail parameter appears in the ALL_AUDIT_POLICIES view.

  • You can change the AUDIT_FILE_DESTparameter using the following command:

    ALTER SYSTEM SET AUDIT_FILE_DEST = '<New Directory>' DEFERRED

  • On many platforms, XML audit files are named as follows:

    <process_name>_<processId>.xml

    For example, ora_2111.xml, or s002_11.xml. On Windows, the XML audit files are named <process_name>_<ThreadId>.xml (or <process_name>_ProcessId>.xml if the process is not running as a thread).

  • The audit_column_opts parameter establishes whether a statement is audited in any of the following conditions:

    • When the query references any column specified in the audit_column parameter (audit_column_opts = DBMS_FGA.ANY_COLUMNS)

    • Only when all such columns are referenced (audit_column_opts = DBMS_FGA.ALL_COLUMNS).

      Note:

      When audit_column_opts is set to DBMS_FGA.ALL_COLUMNS, a SQL statement is audited only when all the columns mentioned in audit_column have been explicitly referenced in the statement. These columns must have been referenced in the same SQL statement or subquery. Also, all these columns must refer to a single table/view or alias. Thus if a SQL statement selects the columns from different table aliases, then the statement will not be audited.

    The default is DBMS_FGA.ANY_COLUMNS.

    The ALL_AUDIT_POLICIES view also shows audit_column_opts.

12.7.1.4 V$XML_AUDIT_TRAIL View

The new values for the audit_trail parameter (XML and XML,EXTENDED) cause fine-grained auditing records to be written to operating system files in XML format.

Audit records stored in operating system files can be more secure than database-stored audit records because access can require file permissions that DBAs do not have. Operating system storage for audit records also offers higher availability, because such records remain available even if the database is temporarily inaccessible.

A new dynamic view, V$XML_AUDIT_TRAIL, makes such audit records from XML files available to DBAs through a SQL query, providing enhanced usability. Querying this view causes all XML files (all files with an .xml extension) in the AUDIT_FILE_DEST directory to be parsed and presented in relational table format.

The DBA_COMMON_AUDIT_TRAIL view includes the contents of the V$XML_AUDIT_TRAIL dynamic view for standard and fine-grained audit records.

Because the audit XML files are stored in files with .xml extension on all platforms, the dynamic view presents audit information similarly on all platforms, using the following schema:

Table 12-4 Elements in the V$XML_AUDIT_TRAIL Dynamic View

Element Type
AUDIT_TYPE (refer to Note 1.) NUMBER
CLIENT_ID VARCHAR2(64)
COMMENT_TEXT VARCHAR2(4000)
DB_USER VARCHAR2(30)
ENTRYID NUMBER
EXT_NAME VARCHAR2(4000)
EXTENDED_TIMESTAMP (Refer to Note 1.) TIMESTAMP(6) WITH TIME ZONE
GLOBAL_UID VARCHAR2(32)
INSTANCE_NUMBER NUMBER
OBJECT_NAME VARCHAR2(128)
OBJECT_SCHEMA VARCHAR2(30)
OS_PROCESS (Refer to Note 3.) VARCHAR2(16)
OS_USER VARCHAR2(255)
POLICY_NAME VARCHAR2(30)
PROXY_SESSIONID NUMBER (Refer to Note 3.)
SCN NUMBER
SESSION_ID NUMBER
SQL_BIND (Refer to Note 2.) VARCHAR2(4000)
SQL_TEXT (Refer to Note 2.) VARCHAR2(4000)
STATEMENT_TYPE VARCHAR2(28)
STATEMENTID NUMBER
TERMINAL VARCHAR2(255)
TRANSACTIONID RAW(8)
USERHOST VARCHAR2(128)

Note 1: The AUDIT_TYPE column shows 1 for standard XML audit, 2 for fine-grained XML audit, 4 for SYS XML audit, and 8 for mandatory XML audit. Every XML audit record contains the elements Audit_Type and Extended_Timestamp, with the latter printed in UTC zone (with no time zone information). Values retrieved using V$XML_AUDIT_TRAIL view are converted to session time zone and printed.

Note 2: For SQL_TEXT and SQL_BIND element values (CLOB type columns), the dynamic view shows only the first 4000 characters. The underlying XML file may have more than 4000 characters for such SQL_TEXT and SQL_BIND values.

Note 3: OS_PROCESS on Unix systems. In Windows systems, the label is ProcessId:ThreadId ( or ProcessId, if it is not running as a thread).

Note 4: For a large numbers of XML audit files, querying V$XML_AUDIT_TRAIL is faster when they are loaded into a database table using SQL*Loader or a similar tool. XML audit files are larger than the equivalent written to OS files when AUDIT_TRAIL=OS.

Error handling is the same as when AUDIT_TRAIL=OS. If any error occurs in writing an audit record to disk, including the directory identified by AUDIT_FILE_DEST being full, then the auditing operation fails. An alert message is logged.

12.7.1.5 Examples

DBMS_FGA.ADD_POLICY (object_schema => 'scott', object_name=>'emp', policy_name => 'mypolicy1', audit_condition => 'sal < 100', audit_column =>'comm, credit_card, expirn_date', handler_schema => NULL, handler_module => NULL, enable => TRUE, statement_types=> 'INSERT, UPDATE', audit_trail => DBMS_FGA.DB+DBMS_FGA.EXTENDED, audit_column_opts => DBMS_FGA.ALL_COLUMNS); 

12.7.2 DISABLE_POLICY Procedure

This procedure disables an audit policy.

12.7.2.1 Syntax

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

12.7.2.2 Parameters

Table 12-5 DISABLE_POLICY Procedure Parameters

Parameter Description
object_schema The schema of the object to be audited (If NULL, then the current login user schema is assumed.)
object_name The name of the object to be audited
policy_name The unique name of the policy

The default value for object_schema is NULL. (If NULL, then the current login user schema is assumed.)

12.7.3 DROP_POLICY Procedure

This procedure drops an audit policy.

12.7.3.1 Syntax

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

12.7.3.2 Parameters

Table 12-6 DROP_POLICY Procedure Parameters

Parameter Description
object_schema The schema of the object to be audited. (If NULL, then the current log-on user schema is assumed.)
object_name The name of the object to be audited.
policy_name The unique name of the policy.

12.7.3.3 Usage Notes

The DBMS_FGA procedures cause current DML transactions, if any, to commit before the operation unless they are inside a DDL event trigger. With DDL transactions, the DBMS_FGA procedures are part of the DDL transaction. The default value for object_schema is NULL. (If NULL, then the current login user schema is assumed.)

12.7.4 ENABLE_POLICY Procedure

This procedure enables an audit policy.

12.7.4.1 Syntax

DBMS_FGA.ENABLE_POLICY(
   object_schema  VARCHAR2,
   object_name    VARCHAR2,
   policy_name    VARCHAR2,
   enable         BOOLEAN);

12.7.4.2 Parameters

Table 12-7 ENABLE_POLICY Procedure Parameters

Parameter Description
object_schema The schema of the object to be audited. (If NULL, then the current log-on user schema is assumed.)
object_name The name of the object to be audited.
policy_name The unique name of the policy.
enable Defaults to TRUE to enable the policy.