Parameter	Description	Default Value
`object_schema`	The schema of the object to be a udited. `(`If NULL, the current effective user schema is assumed.`)`	NULL
< p class="TB">`object_name`	The name of the object to be audited.	-
`policy_name`	< /a> The unique name of the policy.	-
`audit_condition`	A condition in a row that indicates a monitoring condition. NULL is a llowed and acts as TRUE.	NULL
`audit_column`	The columns to be checked for access. These can include hidden columns. The default, `NUL L`, causes audit if any column is accessed or affected.	NULL< /p>
`handler_sc hema`	The schema that contains the event handler. The default , NULL, causes the current schema to be used.	NULL
`handler_module` < /td>	The function name of the event handler; includes the package name if ne cessary. This is fired only after the first row that matches the audit condition is processed in the query. If the procedure fails wi th exception, the user SQL statement will fail as well.	NULL
`enable`	Enables the policy if TRUE, which is the default.	TRUE
`statement_types`	Th e SQL statement types to which this policy is applicable: insert, update, delete, or select only.	SELECT
`audit_trail`	Whether to populate `LSQLTEXT` and `LSQLBIND` in fga_log$.	DB_EXT ENDED
`audi t_column_opts`	Establishes whether a statement is audited whe n the query references any column specified in the audit_column parameter or only when all such columns are referenced.	ANY_COLUMNS

Parameter	Description
`object _schema`	The schema of the object to be audited. `(If NULL, the current effective user schema is assumed.)`
< p class="TB">The name of the object to be audited.
`policy_name`	The uni que name of the policy.

~~Parameter~~	Description
`object_schema`	The schema of the object to be audited. `(`If NULL, the current effective user schema is assumed.)
`object_name`	The name of the object to be audited.
`policy_name`< /p>	The unique name of the policy.
`enable`	Defaults to `TRUE` to enable the policy.

Parameter	Description
`obje ct_schema`	The schema of the object to be audited. `(If NULL, the current effective user schema is assumed.)`
`object_name`	The name of the object to be audited.
< a name="1011947"> `policy_name`	The u nique name of the policy.

11
Configuring and Administering Auditing

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

The types of auditing ava ilable in Oracle systems were described in Chapter 8, "Database Auditing: Security Considerations ".

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

Actions Audited by Default

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

Connections to the instance with administrator privileges
< a name="1007563">

An audit record is generated that lists the operating system user connecting to Oracle as SYSOPER or SYSDBA. This provides for accountability of users with administrative privileges. Full auditing fo r these users can be enabled as explained in "Auditing Administrative Users".
Database startup

An audit record is generated that lists the operating system user starting the instance, the user's terminal identifier, the date and time stamp. This data is stored in the operating system audit trail because the database audit trail is not available until afte r startup has successfully completed.
Database shutdown

An audit record is generated that lists the operating system user shutting down the instance, the user's terminal identifier, and the date and time stamp.

Guidelines for Auditing

Oracle Database 10g gives you the option of sending audit records to the database audit trail or your operating system's audit trail, when the operating system is capable of receiving them. 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 is root 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 f lexibility to implement an auditing scheme that suits your specific business needs.

This se ction describes guidelines for auditing and contains the following topics:

Keep Audited Information Manageable

Although auditing is relatively inexpensive, limit the number of audited events as much as possible. Doing so minim izes the performance impact on the execution of audited statements and the size of the audit trail, making it easier to analyze and u nderstand.

Use the following general guidelines when devising an auditing strategy:

Evaluate your purpose for auditing.

After you have a clear understanding of the reasons for auditing, you can devise an appropriate auditing strategy and av oid 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 ha ve 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 clutterin g the meaningful information and consuming valuable space in the SYSTEM tablespace. Balance your need to gather sufficie nt security information with your ability to store and process it.

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

Auditing Normal Data base 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 informa tion 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 monit or 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, leg al, and company-policy concerns.

Auditing Suspicious Database Activity

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

Audit generally, 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. The refore, audit options must be set more generally at first. Once preliminary audit information is recorded and analyzed, the general a udit options should be turned off and more specific audit options enabled. This process should continue until enough evidence is gath ered to make concrete 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.
< /a>

See Also:
"Protecting the Standard Audit Trail"

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 sett ing 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 conta ins the audit trail, and not to SYS.AUD$ (also viewable as DBA_AUDIT_TRAIL).

In Windows, for example, audit records are written as events to the Event Viewer log file.
For Solaris, if the AUDIT_FILE_DEST parameter is not specified, the default location is $ORACLE_HOME/rdbms/audit.
For other operating systems, see 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 s
alary SET base=1000 WHERE name='myname';

When SYS auditing is ena bled, both the ALTER SYSTEM and UPDATE statements are displayed in the operating system audit file as follo ws:

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
A
CTION: '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 TE
RMINAL: pts/2
STATUS: 0

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

< /a>

Using Triggers

You can often use triggers to record additional customized information that is not au tomatically included in audit records, thereby customizing your own audit conditions and record contents. For example, you could defi ne a trigger on the EMP table to generate an audit record whenever an employee's salary is increased by more than 10 per cent. 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_sala
ries
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 th em 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, un certain. 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 Oracle's 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. See Fine-Grained Auditing .

Decide Whether to Use th e Database or Operating System Audit Trail

The data dictionary of ev ery Oracle database has a table named SYS.AUD$, commonly referred to as the database audit trail, and viewable as DBA_AUDIT_TRAIL. This table is designed to store entries auditing database statements, privilege s, or schema objects.

You can optionally choose to store the database audit information to an operating system file. If your operating system has an audit trail that stores audit records generated by the operating system aud iting facility, and Oracle is allowed to write to it, you can choose to 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 Viewe r.

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

Using the database audit trail offers the f ollowing advantages:

You can view selected portions of the aud it 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.

Alternatively, your operating system audit trail may allow you to consolidate audit recor ds from multiple sources including Oracle and other applications. Therefore, examining system activity might be more efficient becaus e all audit records are in one place. Another advantage to this approach is achieving a separation of duty between a DBA and an audit or.

See Also:

Your operating syste m specific documentation for information about its auditing capabilities.

Audit Trail Views

What Information is Contained in the Audit Trail?

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

Database Audit Trail Contents

Audit Information Stored in an Operating System File< /li>

Dat abase Audit Trail Contents

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

Audit trai l 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, that data popu lates the corresponding column. (For certain columns, this list has the column name as it displays in the audit record, shown here in side parentheses.) Certain audit columns (marked with an * in the following list) appear only if you have specified AUDIT_TRAIL=DB_EX TENDED in the database initialization file, init.ora. The operating system audit trail has only those columns marked (os).

Operating system login user name (CLIENT USER) (os)
Database user name (DATABASE USER)
Session identifier (os)

Terminal identifier (os)

Name of the schema object accessed (os)

Operation performed or attempted (ACTION) (os)

Completion c ode of the operation (os)

Date and time stamp in UTC (Coordinated Universal Ti me) format

System privileges used (PRIVILEGE) (os)

Proxy Session's auditid

Global User u nique id

Distinguished name (os)

Instance number

Process number

TransactionId

SCN (system change number) for the SQL stateme nt

(*) SQL text (the SQL text that triggered the auditing)

(*) Bind values used for the SQL statement, if any

If the database destination for audit records becomes full or unavailable and therefore unable to accept new records, an audited ac tion cannot complete. Instead, it causes an error message and is not done. In some cases, an operating system log allows such an acti on 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.

There is a new audit trail view that combines standard and fine-grained audit log records, named DBA_CO MMON_AUDIT_TRAIL.

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 a n old query that was originally subject to a different policy. Current business access rules always apply.

See Also:

"Fine-Grained Auditing" for more information about methods of fine-grained auditing

"Flas hback Queries" in Oracle Database Administrator's Guide

Audit Information Stored in an Operating System File

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

Audit records generated by the operating system

Database audit trail records

Database actions that are alw ays audited

Audit records for administrative users (SYS)

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

Describes the operation performed or attempted. The AUDIT_ACTIONS data dictionary table contains a list of these codes and their descriptions.

Encoded Information How to Decode

Action code

Privileges used
< /a>
Describes any system privileges used to perform the operation. The SYSTEM_PRIVILEGE_MAP table lists a ll of these codes and their descriptions.

Completion code

Describes the resul t of the attempted operation. Successful operations return a value of zero; unsuccessful operations return the Oracle error code desc ribing why the operation was unsuccessful. These codes are listed in Oracle Database Error Messages.
Managing the Standard Audit Trail This section describes various aspects of managing standard audit trail information, and contains the following topics: Enabling and Disabling Standard Auditing Standard Auditing in a Multitier Environment Setting Standard Auditing Options Turning Off Standard Audit Options Controlling the Growth and Size of the Standard Audit Trail Protecting the Standard Audit Trail Auditing the Standard Audit Trail Enabling and Disabling Standard Auditing Any authorized database user can set statement, privilege, and object auditing options at any time, but Oracl e does not generate audit information for the standard database audit trail unless database auditing is enabled. The security adminis trator is normally responsible for controlling auditing. This section discusses the initial ization parameters that enable and disable standard auditing. Note: th e initialization parameters AUDIT_SYS_OPERATIONS and AUDIT_TRAIL affecting standard auditing are static. "S tatic" 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 S ystem set AUDIT_FILE_DEST = <dir> DEFERRED", meaning the new destination will be effective for all subsequent sessions. Setting the AUDIT_TRAIL Initialization Parameter Database auditing is enabled and disabled by the AUDIT_TRAIL initialization parameter in the database's initialization p arameter file. 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 t hat are always written to the operating system audit trail DB_EXTENDED Does a ll 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 columns are the ones referred to as the additional eight, 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 that alter what objects are audited do not r equire restarting the database, which is only required if a universal change is made, such as turning on or off al l auditing. Note: You do not need to set AUDIT_TRAIL to enable either fine-grained auditing or SYS auditing. For fin e-grained auditing, you simply add and remove FGA policies as you see fit, applying them to the specific operations or objects you wa nt to monitor. For SYS auditing, you just set the SYS audit parameter for SYS audit. See the section titled Fine-Grained Auditing later in this chapter. Setting the AUDIT _FILE_DEST Initialization Parameter The AUDIT_FILE_DEST initialization parameter specifies an operating system directory into which the audit trail is written when AUDIT_TRAIL=OS is specified. It is also the location to which mandatory auditing information is written and, if so specified by the AUDIT_ SYS_OPERATIONS initialization parameter, audit records for user SYS. 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 sess ions. If the AUDIT_FILE_DEST parameter is not specified, the default location on Solaris is $ORACLE_HOME/rdbms/audit. In Windows, the default location to wh ich audit records are written is the Event Viewer log file. < strong class="NH">Notes: If your operating system supports an audit trail, then its location is operating system specific. For example, the Windows operating sys tems writes audit records as events to the application event log. You can view and manage these events using Event Viewer. You are no t allowed to specify the AUDIT_FILE_DEST initialization parameter for Windows platforms. For more information, see Oracle Database Platform Guide for Windows. Some operating systems always log an audit record for instance connection and dat abase startup to the default location $ORACLE_HOME/rdbms/audit regardless of the setting for AUDIT_FILE_DEST. This log action occurs because the parameter setting is not known until the database is mounted. < /div> Standar d Auditing in a Multitier Environment In a multitier environment, Or acle preserves the identity of the client through all tiers, which enables auditing of actions taken on behalf of the client. To do s uch auditing, you use the BY proxy clause in your AUDIT statement. This clause allows you a few options. You can: 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 sta tements executed on behalf of any user The following example audits SELECT TABL E 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 and < em class="Italic">Oracle Database Application Developer's Guide - Fu ndamentals for more information on proxies and multitier applications Setting Standard Auditing Options You specify one of the three standard auditing options using the AU DIT 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, TRUNCA TE TABLE, COMMENT ON TABLE, and DELETE [FROM] TABLE statements. Privilege Audits SQL statements that are authorized by the specified system privilege. For Example, AUDIT C REATE 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 To use the AUDIT statement to set statement and privilege options, you must have the AUDIT SYSTEM privilege. To use it to set objec t 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 u sers 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 to write a single record for all SQL statements of the same type issued in the same session. BY AC CESS causes Oracle to write one record for each access. Note: If you are using an operating system file for the au dit trail (AUDIT_TRAIL=OS), multiple records may still be written to the audit trail when BY SESSION is spe cified. This occurs because while Oracle can write to the operating system file, it is unable to read it to detect that it has alread y written an audit entry for the action. WHENEVER SUCCESSFUL/WHENEVER NOT SUCCESSFUL WHENEVER SUCCES SFUL chooses auditing only for statements that succeed. WHENEVER NOT SUCCESSFUL chooses auditing only for stateme nts that fail or result in errors. The implications of your choice of auditing op tion and specification of AUDIT statement clauses is discussed in subsequent sections. A new database session picks up auditing options from the data dictionary when the session is created. These auditing option s remain in force for the duration of the database connection. Setting new system or object auditing options causes all subsequent da tabase 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 generates audit records based on the audit options currently set, set the initialization parameter A UDIT_TRAIL as described in "Enabling and Disabling Standard Auditing". See Also: Oracle Database SQL Reference for a complete description of the AUDIT statement Specifying Statement Auditing Valid statement audit options t hat 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. Auditing Connections and Disconnections Auditing Statements That F ail Because an Object Does Not Exist The NOT EXISTS sta tement option specifies auditing of all SQL statements that fail because the target object does not exist. Specifying 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's system privileges are listed in the Oracle Database SQL Reference. To audit all successful and unsucces sful uses of the DELETE ANY TABLE system privilege, enter the following statement: AUDIT DELETE ANY TABLE; To audit all unsuccessful SELE CT, 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 t he only user granted this system privilege. Specifying 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 user's own schema. The AUDIT ANY system privilege is r equired to set an object audit option for an object contained in another user's 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 fol lowing 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 SESSIO N (the default), enter the following statement: AUDIT SELECT ON DEFAULT WHENEVER NOT SUCCESSFUL; Turning Off 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 pr oxy 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< /code> clause. If the clause is not specified, the auditing option is disabled entirely, for both successful and unsuccessful cases.< /p> 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 appropriat e NOAUDIT statement. Caution: The NOAUDIT statement only specifies auditing options; it does no t disable auditing as a whole. To turn auditing off and stop Oracle from generating audit records, set the initialization parameter < code>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 sy ntax listing of the NOAUDIT statement 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 TAB LE; 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. Turning Off Object Auditing The following statements tur n off the corresponding auditing options: NOAUDIT DELETE ON emp; < a name="1006791">NOAUDIT SELECT, INSERT, DELETE ON jward.dept; Furthermore, to turn off all object audit options on the emp table, enter the following state ment: NOAUDIT ALL ON emp; To turn off all default object audit options, enter the following statement: NOAUDIT ALL ON DEFAULT; Al l schema objects created before this NOAUDIT statement is issued continue to use the default object audit options in eff ect 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 d isable the object audit options of an object in another user's schema or to disable default object audit options, you must have the < code>AUDIT ANY system privilege. A user with privileges to disable object audit options of an object can override the options set by any user. Controlling the Growth and Size of the Standard Audit Trail If the audit trail becomes completely full and no more audit records can be inserted, audited statements cannot be successfully execu ted until the audit trail is purged. Warnings are returned to all users that issue audited statements. Therefore, the security admini strator must control the growth and size of the audit trail. When auditing is enabled and a udit records are being generated, the audit trail grows according to two factors: The number of audit options turned on The frequency of exe cution of audited statements To control the growth of the audit trail, you can use th e following methods: Enable and disable database auditing. If it is enabled, audit records are generated and stored in the audit trail; if it is disabled, audit records are not generated. Be very selective about the audit options that are turned on. If more selective audit ing is performed, 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 two different ways: A security administrator owns all objects and the AUDIT ANY s ystem privilege is never granted to any other user. Alternatively, all schema objects can belong to a schema for which the correspond ing user does not have CREATE SESSION privilege. All objects ar e contained in schemas that do not correspond to real database users (that is, the CREATE SESSION privilege is not grant ed 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 administrato r. 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: Your operating system specific Oracle documentation for more information about managing the operating system audit trail when you a re directing audit records to that location 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$; Alternat ively, to delete all audit records from the audit trail generated as a result of auditing the table emp, enter the follo wing statement: DELETE FROM SYS.AUD$ WHERE obj$name='EMP'; Note: All deletes from the audit trail are audited without exception: see this chapter's sections en titled 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 completely full and connections are being audited (that is, if the SESSION option is set), 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 a udited) and make space available in the audit trail. See Also: Oracle Database Utilities for information about exporting table s Archiving Audit Trail Information If audit trail in formation must be archived for historical purposes, the security administrator can copy the relevant records to a normal database tab le (for example, using INSERT INTO table SELECT ... FROM SYS.AUD$ ...) or export the audit trail table to an operating system file. 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 stil l exist. If the database audit trail has many extents allocated for it, but many of them ar e not being used, the space allocated to the database audit trail can be reduced by following these steps: If you want to save information currently in the audit trail, copy it to another database table or export it using the EXPORT utility. Connect as a user with administrator privileges. Truncate SY S.AUD$ using the TRUNCATE statement. Reload archive d audit trail records generated from 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$ i s the only SYS object that should ever be directly modified. Protecting the Standard Audit Trail When auditing for suspicious database activity, protect the integrity o f 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. Auditing the Standa rd Audit Trail If an application needs to give SYS.AUD$ access to re gular 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: Conn ect sys/passw as SYSDBA. 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.< /p> Then if a regular user has select, update, insert and delete privileges on SYS.AUD$ and executes a SELECT operation, 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 t ries to DELETE this row1 from SYS.AUD$, the DELETE will succeed, since the user has the privilege to perform this action. However, th is DELETE action on SYS.AUD$ is also recorded in the audit trail. Setting up this type of a uditing 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 Example of Auditing Table SYS.AUD$. Viewing Database Audit Trail Information The database audit tr ail (SYS.AUD$) is a single table in each Oracle database's data dictionary. Several predefined views are available to pr esent auditing information from this table in a meaningful way. If you decide not to use auditing, you can later delete these views. The following subsections show you what's in these views, how to use them, and how to delete them: Audit Trail Views Using Audit Trail Views to Investigate Suspicious Activities Deleting the Audit Trail Views Audit Trail Views The following views are created upon installation: View Description< /th> STMT_AUDIT_OPTION_MAP Contains information about auditing o ption 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< /p> DBA_STMT_A UDIT_OPTS Describes current system auditing options across th e system and by user DBA_PRIV_AUDIT_OPTS Describes current system privi leges being audited across the system and by user DBA_OBJ_AUDIT_OPTS USER_OBJ_AUDIT_OPTS Describes auditing options on all objects. USER view describes auditing options on all objects owned by the current user. DBA_AUDIT_TRAIL USER_AUDIT_TRAIL Lists all audit trail entries. USER view shows audit trail entries relating to current user. DBA_AUDIT_O BJECT USER_AUDIT_OBJECT Contains audit trail records for all objects in the system. 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. USER view lists all audit trail records concerning connections and disconnections for the current user. DBA_AUDIT_STATEMENT US ER_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 < div align="center"> See Also: < em class="Italic">Oracle Database Reference for more detail ed descriptions of the Oracle provided predefined views Using Audit Trail Views to Investigate Suspicious Activities This section offers examples that demonstrate how to examine and inte rpret 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 being altered without authorization. A high number of deadlocks are occurring, most likely because of users acquiring exclusive table locks . Rows are arbitrarily being deleted from the emp table in jeff's schema. You suspect the users jward and swilliams of several of these detrimental actions. To enable your investigation, you issue the f ollowing statements (in order): AUDIT ALTER, INDEX, RENAME ON DEFAULT BY SESSION; CREATE VIEW jeff.employee AS SELECT * FROM jeff.emp; AUDIT SESSION BY jw ard, swilliams; AUDIT ALTER USER; AUDIT LOCK TABLE BY ACCESS WHENEVER SUCCESSFUL; AUDIT DELETE ON jeff.emp BY ACCESS WHENEVER SUCCESSFUL; The following statement s are subsequently issued by the user jward: ALTER USER tsmith QUOTA 0 ON user s; DROP USER djones; The following statemen ts are subsequently issued by the user swilliams: LOCK TABLE jeff.emp IN EXCLU SIVE MODE; DELETE FROM jeff.emp WHERE mgr = 7698; ALTER TABLE jeff.emp ALLOCATE EXTENT (S IZE 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 investigat ion that can be viewed using the audit trail views in the data dictionary: Listing Active Statement Audit Options Listing Active Privilege Audit Options Listing Active Object Audit Options for Specific Objects Listing Default Object Audit Options Listing Audit Records Listing Audit Records for the AUDIT SESSION Option 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 ACC ESS NOT SET Notice that the view reveals the statement audi t options set, whether they are set for success or failure (or both), and whether they are set for BY SESSION or B Y ACCESS. Listing Active Privilege Audit Options The following query r eturns 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 Listing Active Object Audit Options for Specific Objects The following query returns all audit options set for any objects whose name starts with the characters emp and which are contained in jeff's schema: SELECT * FROM DBA_OBJ_AUDIT_OPTS WHERE OW NER = 'JEFF' AND OBJECT_NAME LIKE 'EMP%'; OWNER OBJECT_NAME OBJECT_TY ALT AUD COM DEL GR A IND INS LOC ... ----- ----------- --------- --- --- --- --- --- --- --- --- ... JEFF EM P TABLE S/S -/- -/- A/- -/- S/S -/- -/- ... JEFF EMPLOYEE VIEW -/- -/- -/- A/- -/- S/S -/- -/- ... Notice that the view returns information about all the au dit options for the specified object. The information in the view is interpreted as follows: The character "-" indicates that the audit option is not set. The character "S" indicates that the audit option is set, BY SESSION. The character "A" indicates that the audit option is set, BY ACCESS. Each audit option has two possible settings, WHENEVER SUCCESSFUL and WHENEVER NOT SUCCESSF UL, separated by "/". 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. 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 --- --- --- --- --- --- --- --- --- --- --- --- --- --- S/S -/- -/- -/- -/- S/S -/- -/- S/S -/- -/- -/- -/- /- Notice that th e view returns information similar to the USER_OBJ_AUDIT_OPTS and DBA_OBJ_AUDIT_OPTS views (see previous ex ample). L isting Audit Records The following query lists audit records generat ed by statement and object audit options: SELECT * FROM DBA_AUDIT_OBJECT; Listing Audit Records for the AUDIT SESSION Option The following query lists audit informa tion corresponding to the AUDIT SESSION statement audit option: SELECT USERNAM E, 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 Deleting the Audit Trail Views If you disable auditing and no longer need the audit trail views, delete them by connecting to the database as SYS and running the script file CA TNOAUD.SQL. The name and location of the CATNOAUD.SQL script are operating system dependent. Example of Auditing Table SYS .AUD$ The code in this section illustrates the auditing of changes m ade 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> gran t 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. < em class="Italic">SQL> select count(*) from emp COUNT(*) ---------- 0 1 row selected. SQL> SQL> select statementid,entryid,userna me,action_name,returncode,owner, 2 obj_name,substr(priv_used,1,8) priv, SES_ACTIONS < /em>3 from dba_audit_trail 4 order by sessio nid,entryid; STATEMENTID ENTRYID USERNAME ACTION_NAME RETURNCODE OWNER OBJ_NA ----------- ---------- ---------- ----------------- ---------- ---------- ------ < /a>PRIV SES_ACTIONS -------- ------------------- 8 1 JEFF SEL ECT 0 SYS AUD$ 1 row selected. SQL> SQL> update sys.au d$ 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 -------- ------------------- < a name="1010176"> 8 1 0 SELECT 0 SYS AUD$ 9 2 0 SELECT 0 SYS AUD$ < /a> 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; STATE MENTID 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. < a name="1010217"> SQL> SQL> spool o ff 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 be substantially expanded by using triggers, providing additional customized information . However, there is no mechanism to specify audit conditions so as to minimize unhelpful au dits, and reconstructing events from access logs often fails to prove access rights were violated. Oracle's 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-grai ned 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 the DBA_FGA_AUDIT_TRAIL view, and also in the DBA_COMMON_AUDIT_TRAIL view, which combines standard and fine-grained audit log records. See Also: To add, drop, enable, or disable policies, you use the package described later in this chapter: The DBMS_FGA Package Policies in Fine-Grained Auditing Policies you establish with fine-grained auditing can monitor data access based on content. Using policies, you can establish what columns and conditions you want audit records for. Your conditions can incl ude limiting the audit to specific types of DML statements used in connection with the columns you specify. You can also provide the name of the routine you want called when an audit event occurs, to notify or alert administrators or to handle errors or anomalies. For example, most companies logically want to limit access to the specifications for a produ ct under development, or its test results, and prefer that salary information remain private. Auditors want enough detail to be able to determine what data was accessed. Knowing only that SELECT privilege was used by a specific user on a particular tabl e is not specific enough to provide accountability. A central tax authority has similar pri vacy concerns, needing to track access to tax returns so that employees don't snoop. Similarly, a government agency needs detailed tr acking of access to its database of informants. Such agencies also need enough detail to determine what data was accessed, not simply that the SELECT privilege was used by JEFF on the TAXPAYERS or INFORMANTS table. Advantag es of Fine-Grained Auditing over Triggers Fine-grained auditing meet s these needs by providing functionality (and efficiency) beyond what triggers can do. 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. An FGA policy, on the other hand, does not incur this cost for every row. Instead, it audits only once for every policy. S pecifically, it audits when a specified relevant column occurs in a specified type of DML statement, either being changed by the stat ement or being in its selection criteria. This combination of criteria uncovers users who hope their information gathering will be ma sked because they only use the selection criteria of a DML statement. Triggers also cannot monitor the activity of another "instead-o f" trigger on the same object, while fine-grained auditing supports tables and views. Extensible Interface Using Event Handler Functions Organizations can thus use fine-grained auditing to define policies speci fying the data access conditions that are to trigger audit events. These policies can use flexible event handlers that notify adminis trators 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 are 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 fol lows: PROCEDURE <fname> ( object_schema VARCHAR2, object_name VARCHAR2, policy_ name VARCHAR2 ) AS ... where fname is the name of the p rocedure, object_schema is the name of the schema of the table audited, object_name is the name of the tabl e to be audited, and policy_name is the name of the policy being enforced. Functions and Relevant Columns in Fine-Grained Auditin g For additional flexibility in implementation, organizations can em ploy a user-defined function to determine the policy condition, and identify an audit column (called a relevant co lumn) 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 u nnecessary 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 informatio n alone is not meaningful unless an HR clerk also selects the corresponding employee name. You can, however, specify that auditing oc cur only when all relevant columns are referenced. If more than one relevant audit column i s specified, Oracle 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 a udit policy on the target object using the functions in the DBMS_FGA package. See also: The DBMS_FGA Package (the next major section) 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 username, SQL text, bind variable, policy name, sessi on 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. NULL Audit Conditions< /font> To guarantee auditing of the specified actions ("statement_types") a ffecting 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 reliabl y 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 act ions on a table with this policy. The audit function is execut ed 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 a ction on a table with that policy causes an audit record to be created, whether or not rows are returned. Defining FGA Policies The administrator uses the DBMS_FGA.ADD_POLICY interface to define ea ch 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< /code> 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 affe cting a particular column is audited. More than one column can be included as relevant columns in a single FGA policy. Examples inclu de privacy-relevant columns, such as those containing social security numbers, salaries, patient diagnoses, and so on. If no relevant column is specified, auditing applies to all columns. That is, auditing occurs whenever any specified statem ent type affects any column, unless you specify in the policy that auditing is to occur only when all relevant columns are referenced . An Adde d 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, whe never policy conditions are met for a returning row, the query is audited. Later, Oracle can execute a user-defined audit event handl er, if specified in the policy, using autonomous transactions to process the event. Fine-gr ained 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 which 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 > 10000 000; 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 */ CRE ATE 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', audi t_condition => 'dept = ''SALES'' ', audit_column => 'salary', handler_schema => 'sec', handler_module => 'log_id', enable => TRUE); Note: Since the words "schema" and "table" are reserved words, they cannot be used as variables without some alteration, such as appe nding "1" as is done here. What happens when these commands ar e issued? After the fetch of the first interested row, the event is recorded, and the audit function SEC.LOG_ID is execu ted. The audit event record generated is stored in DBA_FGA_AUDIT_TRAIL (fga_log$), which has reserved columns (such as < code>SQL_TEXT and SQL_BIND) for recording SQL text, policy name, and other information. The query's SQLBIND and SQLTEXT are recorded in the LSQLTEXT and LSQLBIND columns of fga_log$ only if the policy specified audit_trail = DBMS_FGA.DB_EXTENDED. Note:: Fine-grained auditing is supported only with cos t-based optimization. For queries using rule-based optimization, audit will check before applying row filtering, which could result i n an unnecessary audit event trigger. See Also: Oracle Database Application Develope r's Guide - Fundamentals for information about using fine-grained auditing The DBMS_FGA chapter in PL/SQL Packages and Types Reference Note: Policies currently in force on an object involved in a flash back query are applied to the data returned from the specified flashback snapshot (based on time or SCN). < /div> The DBM S_FGA Package The DBMS_FGA package provides fine-graine d security functions. Execute privilege on DBMS_FGA is needed for administering audit policies. Because the audit functi on can potentially capture all user environment and application context values, policy administration should be executable by privile ged users only. This feature is available for only cost-based optimization. The rule-based optimizer may generate unnecessary audit records since audit monitoring can occur before row filtering. For both the rule-based optim izer and the cost-based optimizer, you can refer to DBA_FGA_AUDIT_TRAIL to analyze the SQL text and corresponding bind v ariables that are issued. The procedures for this package are described in the following su bsections: ADD_POLICY Procedure DROP_POLICY Procedure ENABLE_POLICY Procedure DISABLE_POLICY Procedure The syntax, parameters, and usage notes accompanying each procedure description also discuss the defa ults and restrictions that apply to it. ADD_POLICY Procedure This procedure c reates an audit policy using the supplied predicate as the audit condition. The maximum number of FGA policies on any table or view o bject is 256. Syntax DBMS_FGA.ADD_POLICY( object_schem a VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2, audit_condition VARCHAR2, audit_column VARCHAR2, handler_schema VAR CHAR2, handler_module VARCHAR2, enable BOOLEAN, statement_types VARCHAR2, audit_trail BINARY_INTEGER IN DEFAULT, audit_column_ opts BINARY_INTEGER IN DEFAULT); Parameters Table 11-1 ADD_POLICY Procedure Parameters < tr class="Formal" align="left" valign="top"> Parameter Description Default Value object_schema The schema of the object to be a udited. (If NULL, the current effective user schema is assumed.) NULL < p class="TB">object_name The name of the object to be audited. - policy_name < /a> The unique name of the policy. - audit_condition A condition in a row that indicates a monitoring condition. NULL is a llowed and acts as TRUE. NULL audit_column The columns to be checked for access. These can include hidden columns. The default, NUL L, causes audit if any column is accessed or affected. NULL< /p> handler_sc hema The schema that contains the event handler. The default , NULL, causes the current schema to be used. NULL handler_module< /td> The function name of the event handler; includes the package name if ne cessary. This is fired only after the first row that matches the audit condition is processed in the query. If the procedure fails wi th exception, the user SQL statement will fail as well. NULL enable Enables the policy if TRUE, which is the default. TRUE statement_types Th e SQL statement types to which this policy is applicable: insert, update, delete, or select only. SELECT audit_trail Whether to populate LSQLTEXT and LSQLBIND in fga_log$. DB_EXT ENDED audi t_column_opts Establishes whether a statement is audited whe n the query references any column specified in the audit_column parameter or only when all such columns are referenced. ANY_COLUMNS Usa ge Notes 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, statemen t_types=> 'INSERT, UPDATE'); An FGA policy should not be applied to out-of-line columns such as LOB columns. 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 omi tted), which is interpreted as TRUE, but it cannot contain the following elements: Subqueries or sequences Any direct use of SYSDA TE, UID, USER or USERENV functions. However, a user-defined function and other SQL functions can use these functions to return the de sired information. Any use of the pseudocolumns LEVEL, PRIOR, or ROWNUM.< /ul> Specifying an audit condition of "1=1" to force auditing of all specified statements ("st atement_types") affecting the specified column ("audit_column") is no longer needed to achieve this purpose. NULL will c ause audit even if no rows were processed, so that all actions on a table with this policy are audited. If object_schema is NULL, the current effective user schema is assumed. The audit function (handler_module) is an alerting mechanism for the administrator. The required interface fo r such a function is as follows: PROCEDURE <fname> ( object_schema VARCHAR2, object_nam e 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. 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 rows being returned satisfy that policy's audit_condition. In other words, whenever any number of rows being returned satisfy an audit condition defined on the table, a single audit record will be generated for each s uch policy. If a table with an FGA policy defined on it receives a Fast Path i nsert or a vectored update, the hint is automatically disabled before any such operations. Disabling the hint allows auditing to occu r according to the policy's terms. (One example of a Fast Path insert is the statement INSERT-WITH-APPEND-hint.) The audit_trail parameter specifies whether to record the query's Sql Text and Sql Bi nd variable information in the FGA audit trail (fga_log$) columns named LSQLTEXT and LSQLBIND: To populate, set to DBMS_FGA.DB_EXTENDED (the default) To leave unpopulated, set to DBMS_FGA.DB. The audit_trail parameter appears in the ALL_AUDIT_POLICIES view. The audit_column_opts parameter establishes whether a statement is audited when the query references any column specified in the audit_column parameter (audit_column_opts = DBMS_FGA.A NY_COLUMNS), or only when all such columns are referenced (audit_column _opts = DBMS_FGA.ALL_COLUMNS). The default is DBMS_FGA.ANY_COLUMN S. The ALL_AUDIT_POLICIES view also shows audit_column_opts. DROP_POLICY Procedure This procedure drops an audit policy. Syntax DBMS_FGA.DROP_POLICY( object_schema VARCHAR2, o bject_name VARCHAR2, policy_name VARCHAR2 ); Parameters Table 11-2 DROP_POLICY Procedure Param eters object_name Parameter Description object _schema The schema of the object to be audited. (If NULL, the current effective user schema is assumed.) < p class="TB">The name of the object to be audited. policy_name The uni que name of the policy. Usage Notes The DBMS_ FGA procedures cause current DML transactions, if any, to commit before the operation. However, the procedures do not cause a commit first if 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, the current effective user schema is assumed.) E NABLE_POLICY Procedure This procedure enables an audit policy. < a name="1011887"> Syntax< /h4> DBMS_FGA.ENABLE_POLICY( object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2, enable BOOLEAN); Parameters Table 11-3 ENABLE_POLICY Procedure Parameters < /tr> Parameter Description object_schema The schema of the object to be audited. (If NULL, the current effective user schema is assumed.) object_name The name of the object to be audited. policy_name< /p> The unique name of the policy. enable Defaults to TRUE to enable the policy. DISABLE_POLICY Procedure This procedure disables an audit policy. Syntax DBMS_FGA.DISABLE_POLICY( object_schema VARCHAR2, obj ect_name VARCHAR2, policy_name VARCHAR2 ); Parameters Table 11-4 DISABLE_POLICY Procedure Par ameters Parameter Description obje ct_schema The schema of the object to be audited. (If NULL, the current effective user schema is assumed.) object_name The name of the object to be audited. < a name="1011947"> policy_name The u nique name of the policy. The default value for object_schema is NULL. (If NULL, the current effective user schema is assumed.) Previous Next< /a> Copyright © 2003 Oracle Corporation All Rights Rese rved. Home Book List Contents Index Master Index Feedback

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`, `TRUNCA TE TABLE`, `COMMENT ON TABLE`, and `DELETE [FROM] TABLE` statements.
Privilege	Audits SQL statements that are authorized by the specified system privilege. For Example, `AUDIT C REATE 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

Encoded Information	How to Decode
Action code
Privileges used	< /a> Describes any system privileges used to perform the operation. The `SYSTEM_PRIVILEGE_MAP` table lists a ll of these codes and their descriptions.
Completion code	Describes the resul t of the attempted operation. Successful operations return a value of zero; unsuccessful operations return the Oracle error code desc ribing why the operation was unsuccessful. These codes are listed in Oracle Database Error Messages.

Parameter Value	Meaning
`DB`	Enables database auditing and directs all audit records to the database audit trail (SYS.AUD$), except for records t hat are always written to the operating system audit trail
DB_EXTENDED	Does a ll 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 columns are the ones referred to as the additional eight, 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 .)

View	Description < /th>
STMT_AUDIT_OPTION_MAP	Contains information about auditing o ption 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< /p>
DBA_STMT_A UDIT_OPTS	Describes current system auditing options across th e system and by user
DBA_PRIV_AUDIT_OPTS	Describes current system privi leges being audited across the system and by user
DBA_OBJ_AUDIT_OPTS USER_OBJ_AUDIT_OPTS	Describes auditing options on all objects. `USER` view describes auditing options on all objects owned by the current user.
DBA_AUDIT_TRAIL USER_AUDIT_TRAIL	Lists all audit trail entries. `USER` view shows audit trail entries relating to current user.
DBA_AUDIT_O BJECT USER_AUDIT_OBJECT	Contains audit trail records for all objects in the system. `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`. `USER` view lists all audit trail records concerning connections and disconnections for the current user.
DBA_AUDIT_STATEMENT US ER_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

11 Configuring and Administering Auditing

Actions Audited by Default

Guidelines for Auditing

Keep Audited Information Manageable

Auditing Normal Data base Activity

Auditing Suspicious Database Activity

Auditing Administrative Users

Using Triggers

Decide Whether to Use th e Database or Operating System Audit Trail

What Information is Contained in the Audit Trail?

Dat abase Audit Trail Contents

Audit Information Stored in an Operating System File

Managing the Standard Audit Trail

Enabling and Disabling Standard Auditing

Setting the AUDIT_TRAIL Initialization Parameter

Setting the AUDIT _FILE_DEST Initialization Parameter

Standar d Auditing in a Multitier Environment

Setting Standard Auditing Options

Specifying Statement Auditing

Auditing Connections and Disconnections

Auditing Statements That F ail Because an Object Does Not Exist

Specifying Privilege Auditing

Specifying Object Auditing

Turning Off Standard Audit Options

Turning Off Statement and Privilege Auditing

Turning Off Object Auditing

Controlling the Growth and Size of the Standard Audit Trail

Purging Audit Records from the Audit Trail

Archiving Audit Trail Information

Reducing the Size of the Audit Trail

Protecting the Standard Audit Trail

Auditing the Standa rd Audit Trail

Viewing Database Audit Trail Information

Audit Trail Views

Listing Active Privilege Audit Options

Listing Active Object Audit Options for Specific Objects

L isting Audit Records

Listing Audit Records for the AUDIT SESSION Option

Deleting the Audit Trail Views

Example of Auditing Table SYS .AUD$

Fine-Grained Auditing

Policies in Fine-Grained Auditing

Advantag es of Fine-Grained Auditing over Triggers

Extensible Interface Using Event Handler Functions

Functions and Relevant Columns in Fine-Grained Auditin g

Audit Records in Fine-Grained Auditing

NULL Audit Conditions< /font>

Defining FGA Policies

An Adde d Benefit to Fine-Grained Auditing

The DBM S_FGA Package

ADD_POLICY Procedure

Syntax

Parameters

Table 11-1 ADD_POLICY Procedure Parameters

Usa ge Notes

DROP_POLICY Procedure

Syntax

Parameters

Table 11-2 DROP_POLICY Procedure Param eters

Usage Notes

E NABLE_POLICY Procedure

Syntax< /h4> DBMS_FGA.ENABLE_POLICY( object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2, enable BOOLEAN);

Parameters

Table 11-3 ENABLE_POLICY Procedure Parameters

DISABLE_POLICY Procedure

Syntax

Parameters

Table 11-4 DISABLE_POLICY Procedure Par ameters

11
Configuring and Administering Auditing

Syntax< /h4>
DBMS_FGA.ENABLE_POLICY( object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2, enable BOOLEAN);