Oracle9i Database Concepts Release 1 (9.0.1) Part Number A88856-02 |
|
This chapter discusses the auditing feature of Oracle. It includes:
Auditing is the monitoring and recording of selected user database actions. Auditing is normally used to:
This section outlines the features of the Oracle auditing mechanism.
Oracle supports three general types of auditing:
Oracle allows audit options to be focused or broad. You can audit:
Audit records include information such as the operation that was audited, the user performing the operation, and the date and time of the operation. Audit records can be stored in either a data dictionary table, called the database audit trail, or an operating system audit trail.
The database audit trail is a single table named FGA_LOG$
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.
The audit trail records can contain different types of information, depending on the events audited and the auditing options set. The following information is always included in each audit trail record, if the information is meaningful to the particular audit action:
The operating system audit trail is encoded and not readable, but it is decoded in data dictionary files and error messages as follows:
See Also:
|
This section explains the mechanisms used by the Oracle auditing features.
The recording of audit information can be enabled or disabled. This functionality allows any authorized database user to set audit options at any time but reserves control of recording audit information for the security administrator.
When auditing is enabled in the database, an audit record is generated during the execute phase of statement execution.
SQL statements inside PL/SQL program units are individually audited, as necessary, when the program unit is executed.
The generation and insertion of an audit trail record is independent of a user's transaction. Therefore, even if a user's transaction is rolled back, the audit trail record remains committed.
See Also:
|
Regardless of whether database auditing is enabled, Oracle always records some database-related actions into the operating system audit trail:
On operating systems that do not make an audit trail accessible to Oracle, these audit trail records are placed in an Oracle audit trail file in the same directory as background process trace files.
Statement and privilege audit options in effect at the time a database user connects to the database remain in effect for the duration of the session. A session does not see the effects of statement or privilege audit options being set or changed. The modified statement or privilege audit options take effect only when the current session is ended and a new session is created. In contrast, changes to schema object audit options become effective for current sessions immediately.
Auditing is site autonomous. An instance audits only the statements issued by directly connected users. A local Oracle node cannot audit actions that take place in a remote database. Because remote connections are established through the user account of a database link, the remote Oracle node audits the statements issued through the database link's connection.
Oracle allows audit trail records to be directed to an operating system audit trail if the operating system makes such an audit trail available to Oracle. On some other operating systems, these audit records are written to a file outside the database, with a format similar to other Oracle trace files.
Oracle allows certain actions that are always audited to continue, even when the operating system audit trail (or the operating system file containing audit records) is unable to record the audit record. The usual cause of this is that the operating system audit trail or the file system is full and unable to accept new records.
System administrators configuring operating system auditing should ensure that the audit trail or the file system does not fill completely. Most operating systems provide administrators with sufficient information and warning to ensure this does not occur. Note, however, that configuring auditing to use the database audit trail removes this vulnerability, because the Oracle server prevents audited events from occurring if the audit trail is unable to accept the database audit record for the statement.
Statement auditing is the selective auditing of related groups of statements that fall into two categories:
AUDIT TABLE
audits all CREATE
and DROP TABLE
statements)
AUDIT SELECT TABLE
audits all SELECT ... FROM TABLE/VIEW
statements, regardless of the table or view)
Statement auditing can be broad or focused, auditing the activities of all database users or the activities of only a select list of database users.
Privilege auditing is the selective auditing of the statements allowed using a system privilege. For example, auditing of the SELECT ANY TABLE
system privilege audits users' statements that are executed using the SELECT ANY TABLE
system privilege. You can audit the use of any system privilege.
In all cases of privilege auditing, owner privileges and schema object privileges are checked before system privileges. If the owner and schema object privileges suffice to permit the action, the action is not audited.
If similar statement and privilege audit options are both set, only a single audit record is generated. For example, if the statement clause TABLE
and the system privilege CREATE TABLE
are both audited, only a single audit record is generated each time a table is created.
Privilege auditing is more focused than statement auditing because each option audits only specific types of statements, not a related list of statements. For example, the statement auditing clause TABLE
audits CREATE TABLE, ALTER TABLE,
and DROP TABLE
statements, while the privilege auditing option CREATE TABLE
audits only CREATE TABLE
statements. This is because only the CREATE TABLE
statement requires the CREATE TABLE
privilege.
Like statement auditing, privilege auditing can audit the activities of all database users or the activities of a select list of database users.
Schema object auditing is the selective auditing of specific DML statements (including queries) and GRANT
and REVOKE
statements for specific schema objects. Schema object auditing audits the operations permitted by schema object privileges, such as SELECT
or DELETE
statements on a given table, as well as the GRANT
and REVOKE
statements that control those privileges.
You can audit statements that reference tables, views, sequences, standalone stored procedures and functions, and packages. Procedures in packages cannot be audited individually.
Statements that reference clusters, database links, indexes, or synonyms are not audited directly. However, you can audit access to these schema objects indirectly by auditing the operations that affect the base table.
Schema object audit options are always set for all users of the database. These options cannot be set for a specific list of users. You can set default schema object audit options for all auditable schema objects.
Views and procedures (including stored functions, packages, and triggers) reference underlying schema objects in their definition. Therefore, auditing with respect to views and procedures has several unique characteristics. Multiple audit records can be generated as the result of using a view or a procedure: The use of the view or procedure is subject to enabled audit options, and the SQL statements issued as a result of using the view or procedure are subject to the enabled audit options of the base schema objects (including default audit options).
Consider the following series of SQL statements:
AUDIT SELECT ON emp; CREATE VIEW emp_dept AS SELECT empno, ename, dname FROM emp, dept WHERE emp.deptno = dept.deptno; AUDIT SELECT ON emp_dept; SELECT * FROM emp_dept;
As a result of the query on EMP_DEPT,
two audit records are generated: one for the query on the EMP_DEPT
view and one for the query on the base table EMP
(indirectly through the EMP_DEPT
view). The query on the base table DEPT
does not generate an audit record because the SELECT
audit option for this table is not enabled. All audit records pertain to the user that queried the EMP_DEPT
view.
The audit options for a view or procedure are determined when the view or procedure is first used and placed in the shared pool. These audit options remain set until the view or procedure is flushed from, and subsequently replaced in, the shared pool. Auditing a schema object invalidates that schema object in the cache and causes it to be reloaded. Any changes to the audit options of base schema objects are not observed by views and procedures in the shared pool.
Continuing with the previous example, if auditing of SELECT
statements is turned off for the EMP
table, use of the EMP_DEPT
view no longer generates an audit record for the EMP
table.
Oracle9i, Release 1 (9.0.1), provides fine-grained auditing, which allows the monitoring of data access based on content. More importantly, the monitoring does not depend on how it is done. A built-in audit mechanism in the database prevents users from by-passing the audit. Oracle DBMS has already provided triggers capability for potentially monitoring DML actions such as INSERT/UPDATE/DELETE.
However, monitoring on SELECT
is costly and may not work for certain cases. In addition, users may want to define their own alert action in addition to just inserting an audit record into the audit trail. This feature provides an extensible interface to audit SELECT
statements on tables and views.
A PLSQL package, DBMS_FGA,
administers these value-based audit policies. Using DBMS_FGA,
the security administrator creates an audit policy on the target table. If any of the rows returned from a query block matches the audit condition (these rows are referred to as interested rows), an audit event entry, including username, SQL text, bind variable, policy name, session id, time stamp, and other attributes, is inserted into the audit trail. As part of the extensibility framework, administrators can also optionally define an appropriate event handler, an audit event handler, to process the event; for example, the audit event handler could send an alert page to the administrator.
For example, to audit SELECT
statements on table hr.emp
to monitor any query that accesses the salary
column of the employee records that belong to the sales
department, the administrator can issue the following SQL statement to set up the auditing:
DBMS_FGA.ADD_POLICY( object_schema => 'hr', object_name => 'emp', policy_name => 'chk_hr_emp', audit_condition => 'dept = ''SALES'' ', audit_column => 'salary');
After the auditing is set up, 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; or SELECT salary FROM hr.emp WHERE dept = 'SALES';
All the relevant information has been supplied, along with a trigger-like mechanism; administrators need only define what to record and how to process the audit event. For example, suppose the following command has been issued:
/* create audit event handler */ CREATE PROCEDURE sec.log_id (schema varchar2, table varchar2, policy varchar2) AS luser varchar2(30); sql varchar2(2000); time date; BEGIN UTIL_ALERT_PAGER(schema, table, policy); -- 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', event_handler_module => 'log_id', enable => TRUE);
After the fetch of the first interested row, the event is recorded, and the audit function sec.log_id
is fired. The audit event record that is generated gets stored in the new format of fga_log$,
which has reserved columns for recording SQL text, bind variables and policy name.
Oracle allows you to focus statement, privilege, and schema object auditing in three areas:
BY SESSION
and BY ACCESS
auditing
For statement, privilege, and schema object auditing, Oracle allows the selective auditing of successful executions of statements, unsuccessful attempts to execute statements, or both. Therefore, you can monitor actions even if the audited statements do not complete successfully.
You can audit an unsuccessful statement execution only if a valid SQL statement is issued but fails because of lack of proper authorization or because it references a nonexistent schema object. Statements that failed to execute because they simply were not valid cannot be audited. For example, an enabled privilege auditing option set to audit unsuccessful statement executions audits statements that use the target system privilege but have failed for other reasons (such as when CREATE TABLE
is set but a CREATE TABLE
statement fails due to lack of quota for the specified tablespace).
Using either form of the AUDIT
statement, you can include:
WHENEVER SUCCESSFUL
clause, to audit only successful executions of the audited statement
WHENEVER NOT SUCCESSFUL
clause, to audit only unsuccessful executions of the audited statement
Most auditing options can be set to indicate how audit records should be generated if the audited statement is issued multiple times in a single user session. This section describes the distinction between the BY SESSION
and BY ACCESS
clauses of the AUDIT
statement.
For any type of audit (schema object, statement, or privilege), BY SESSION
inserts only one audit record in the audit trail, for each user and schema object, during the session that includes an audited action.
A session is the time between when a user connects to and disconnects from an Oracle database.
Assume the following:
SELECT TABLE
statement auditing option is set BY SESSION.
JWARD
connects to the database and issues five SELECT
statements against the table named DEPT
and then disconnects from the database.
SWILLIAMS
connects to the database and issues three SELECT
statements against the table EMP
and then disconnects from the database.
In this case, the audit trail contains two audit records for the eight SELECT
statements-- one for each session that issued a SELECT
statement.
Alternatively, assume the following:
SELECT TABLE
statement auditing option is set BY SESSION.
JWARD
connects to the database and issues five SELECT
statements against the table named DEPT,
and three SELECT
statements against the table EMP,
and then disconnects from the database.
In this case, the audit trail contains two records--one for each schema object against which the user issued a SELECT
statement in a session.
Setting audit BY ACCESS
inserts one audit record into the audit trail for each execution of an auditable operation within a cursor. Events that cause cursors to be reused include the following:
Note that auditing is not affected by whether a cursor is shared. Each user creates her or his own audit trail records on first execution of the cursor.
For example, assume that:
SELECT TABLE
statement auditing option is set BY ACCESS.
JWARD
connects to the database and issues five SELECT
statements against the table named DEPT
and then disconnects from the database.
SWILLIAMS
connects to the database and issues three SELECT
statements against the table DEPT
and then disconnects from the database.
The single audit trail contains eight records for the eight SELECT
statements.
The AUDIT
statement allows you to specify either BY SESSION
or BY ACCESS.
However, several audit options can be set only BY ACCESS,
including:
For all other audit options, BY SESSION
is used by default.
Statement and privilege audit options can audit statements issued by any user or statements issued by a specific list of users. By focusing on specific users, you can minimize the number of audit records generated.
To audit statements by the users SCOTT
and BLAKE
that query or update a table or view, issue the following statements:
AUDIT SELECT TABLE, UPDATE TABLE BY scott, blake;
In a multitier environment, Oracle preserves the identity of a client through all tiers. This enables auditing of actions taken on behalf of the client. To do so, use the BY proxy clause in your AUDIT
statement.
This clause allows you a few options. You can:
The middle tier can set the light-weight user identity in a database session so that it will show up in audit trail. You use OCI or PL/SQL to set the client identifier.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|