| Oracle® Database Security Guide 10g Release 1 (10.1) Part Number B10773-01 |
|
|
View PDF |
| Oracle® Database Security Guide 10g Release 1 (10.1) Part Number B10773-01 |
|
|
View PDF |
Auditing is the monitoring and recording of selected user database actions. It can be based on individual actions, such as the type of SQL statement executed, or on combinations of factors that can include user name, application, time, and so on. Security policies can trigger auditing when specified elements in an Oracle database are accessed or altered, including the contents within a specified object.
An overview of database auditing appears in Chapter 6.
Chapter 11 provides detailed information and guidelines on configuring auditing parameters and administering auditing actions and results.
The present chapter describes the different types and focuses of auditing and the resulting audit trails and records.
Auditing is normally used to:
This chapter describes the types of auditing available in Oracle systems, in the following sections:
Oracle allows audit options to be focused or broad, enabling you to audit:
Table 8-1 describes the different Oracle auditing mechanisms. Each entry in the first column is a link to a more extensive discussion of that particular method.
The following subsections explain the records and timing of the different audit trails:
Audit records include such information 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 in operating system files, called an operating system audit trail.
| See Also:
The complete contents of these audit trails is described in Chapter 11, "Configuring and Administering Auditing", in the section entitled What Information is Contained in the Audit Trail? |
The two general types of auditing are standard auditing, which is based on privileges, schemas, objects, and statements, and fine-grained auditing. Standard audit records can be written either to DBA_AUDIT_TRAIL (the sys.aud$ table) or to the operating system. Fine-grained audit records are written to DBA_FGA_AUDIT_TRAIL (the sys.fga_log$ table) and the DBA_COMMON_AUDIT_TRAIL view, which combines standard and fine-grained audit log records.
The following subsections describe these trails and records:
The database audit trail is a single table 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 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, that data populates the corresponding column. (For certain columns, this list shows the column name displayed in the audit record, here inside parentheses.)
If the database destination for audit records becomes full or unavailable and therefore unable to accept new records, an audited action cannot complete. Instead, it causes an error message and is not done. In some cases, an operating system log allows such an action to complete.
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. If not, audit records are written to a file outside the database. The target directory varies by platform: on the Solaris platform, it is $ORACLE_HOME/rdbms/audit, but for other platforms you must check the platform documentation to learn the correct target directory. In Windows, the information is accessed through Event Viewer.
| See Also:
Your operating system specific Oracle documentation, to see if this feature has been implemented on your operating system |
An operating system audit trail or file system can become full and therefore unable to accept new records, including audit records directed to the operating system. In this circumstance, Oracle still allows certain actions that are always audited to continue, even though the audit record cannot be stored because the operating system destination is full. Using a database audit trail prevents audited actions from completing if their audit records cannot be stored.
System administrators configuring operating system auditing should ensure that the operating system audit trail or the file system does not fill completely. Most operating systems provide administrators with sufficient information and warning to enable them to ensure this does not occur.
Note, however, that configuring auditing to use the database audit trail removes this potential loss of audit information. The Oracle server prevents audited events from occurring if the audit trail is unable to accept the database audit record for the statement.
The operating system audit trail is encoded, but it is decoded in data dictionary files and error messages.
AUDIT_ACTIONS data dictionary table contains a list of these codes and their descriptions.SYSTEM_PRIVILEGE_MAP table lists all of these codes and their descriptions.See Also:
|
Some database-related actions are always recorded into the operating system audit trail regardless of whether database auditing is enabled. The fact that these records are always created is sometimes referred to as mandatory auditing:
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, and in a similar format.
| See Also:
Your operating system specific Oracle documentation for more information about the operating system audit trail |
Standard auditing for the entire database is either enabled or disabled by the security administrator. If it is disabled, no audit records are created.
|
Note: Fine-grained auditing uses audit policies applied to individual objects. Therefore, standard audit settings that are on or off for the entire database do not affect fine-grained auditing. |
If database auditing is enabled by the security administrator, then individual audit options become effective. These audit options can be set by any authorized database user for database objects he owns.
When auditing is enabled in the database and an action set to be audited occurs, 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 being committed. That is, even if a user's transaction is rolled back, the audit trail record remains committed.
Statement and privilege audit options in effect at the time a database user connects to the database remain in effect for the duration of the session. Setting or changing statement or privilege audit options in a session does not cause effects in that session. 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.
See Also:
|
Statement auditing is the selective auditing of related groups of statements regarding a particular type of database structure or schema object, but not a specifically named structure or schema object. These statements 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 of only a select list.
Privilege auditing audits statements that use a system privilege, such as SELECT ANY TABLE. For example, when AUDIT SELECT ANY TABLE is in force, all statements issued by users with the SELECT ANY TABLE privilege are audited.
You can audit the use of any system privilege. Like statement auditing, privilege auditing can audit the activities of all database users or of only a specified list.
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.
Thus privilege auditing does not occur if the action is already permitted by the existing owner and schema object privileges. Privilege auditing triggered only if they are insufficient, that is, only if what makes the action possible is a system privilege.
Privilege auditing is more focused than statement auditing because each privilege auditing option audits only specific types of statements, not a related list of statements. For example, the statement auditing clause TABLE audits CREATE TABLE, ALTER TABLE, and DROP TABLE statements. However, the privilege auditing option CREATE TABLE audits only CREATE TABLE statements, because only the CREATE TABLE statement requires the CREATE TABLE privilege.
Schema object auditing can audit all SELECT and DML statements permitted by schema object privileges, such as SELECT or DELETE statements on a given table. The GRANT and REVOKE statements that control those privileges are also audited.
You can audit statements that reference tables, views, sequences, standalone stored procedures or functions, and packages, but not individual procedures within packages. Further discussion appears in the next section, entitled Schema Object Audit Options for Views, Procedures, and Other Elements.
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.
| See Also:
Oracle Database SQL Reference for information about auditable schema objects |
The definitions for views and procedures (including stored functions, packages, and triggers) reference underlying schema objects. Because of this dependency, some unique characteristics apply to auditing views and procedures, such as the likelihood of generating multiple audit records.
Views and procedures are subject to the enabled audit options on the base schema objects, including the default audit options. These options apply to the resulting SQL statements as well.
Consider the following series of SQL statements:
AUDIT SELECT ON employees; CREATE VIEW employees_departments AS SELECT employee_id, last_name, department_id FROM employees, departments WHERE employees.department_id = departments.department_id; AUDIT SELECT ON employees_departments; SELECT * FROM employees_departments;
As a result of the query on employees_departments, two audit records are generated: one for the query on the employees_departments view and one for the query on the base table employees (indirectly through the employees_departments view). The query on the base table departments does not generate an audit record because the SELECT audit option for this table is not enabled. All audit records pertain to the user that queried the employees_departments view.
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.
In the given example, if the "AUDIT SELECT ON employees;" statement is omitted, then using the employees_departments view will not generate an audit record for the employees table.
Table 8-3, " Auditing Actions Newly Enabled by Oracle Database 10g" lists auditing actions that were not available before Oracle Database.
Oracle lets you focus statement, privilege, and schema object auditing in three areas, as discussed in the following subsections:
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. Monitoring unsuccessful SQL can expose users who are snooping or acting maliciously, though of course most unsuccessful SQL is neither.
Auditing an unsuccessful statement execution provides a report only if a valid SQL statement is issued but fails because it lacks proper authorization or 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. One example is when a CREATE TABLE auditing condition is set, but some CREATE TABLE statements fail due to lack of quota for the specified tablespace.
When your audit statement includes the WHENEVER SUCCESSFUL clause, you will be auditing only successful executions of the audited statement.
When your audit statement includes the WHENEVER NOT SUCCESSFUL clause, you will be auditing only unsuccessful executions of the audited statement.
When your audit statement includes neither of the preceding two clauses, you will be auditing both successful and unsuccessful executions of the audited statement.
If an audited statement is issued multiple times in a single user session, your audit trail can have one or more related records. The controlling clause BY ACCESS causes each execution of an auditable operation within a cursor to generate a separate audit record. If you use the BY SESSION clause instead, your audit trail will contain a single audit record for each session, for each user and schema object. Only one audit record results, no matter how often the statement occurs in that session.
However, several audit options can be set only BY ACCESS:
For all other audit options, BY SESSION is used by default.
This section provides detailed examples of using each clause, in the following subsections:
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 a 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 departments and then disconnects from the database.SWILLIAMS connects to the database and issues three SELECT statements against the table employees 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 departments, and three SELECT statements against the table employees, 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 departments and then disconnects from the database.SWILLIAMS connects to the database and issues three SELECT statements against the table departments and then disconnects from the database.The single audit trail contains eight records for the eight SELECT statements.
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;
| See Also:
Oracle Database SQL Reference for more information about auditing by user |
In a multitier environment, Oracle can preserve the identity of a client through all tiers. Thus, you can audit actions taken on behalf of the client by a mid-tier application. 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 also set the user's client identity in a database session, enabling audit of end-user actions through the mid-tier application. The end-user's client identity then shows up in the audit trail.
Fine-Grained Auditing (FGA) enables you to monitor data access based on content. A built-in audit mechanism in the database prevents users from bypassing the audit.
While Oracle triggers can potentially monitor DML actions such as INSERT, UPDATE, and DELETE, monitoring on SELECT can be costly. In some cases, a trigger may audit too much; in others, its effectiveness or completeness may be uncertain. Triggers also do not enable users to define their own alert action in response to a triggered audit, beyond simply inserting an audit record into the audit trail.
Fine-Grained Auditing provides an extensible interface for creating policies to audit SELECT and DML statements on tables and views. The DBMS_FGA package administers these value-based audit policies. Using DBMS_FGA, the security administrator creates an audit policy on the target object. If any rows returned from a query match the audit condition, then an audit event entry is inserted into the fine-grained audit trail. This entry includes all the information reported in the regular audit trail: see the Audit Records and the Audit Trails section. Only one row of audit information is inserted into the audit trail for every FGA policy that evaluates to TRUE. The extensibility framework in FGA also enables administrators optionally to define an appropriate audit event handler to process the event, for example by sending an alert page to the administrator.
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.
FGA policies associated with a table or view may also specify relevant columns, so that any specified statement type affecting a relevant column is audited. If no relevant column is specified, auditing applies to all columns. That is, auditing occurs whenever any specified statement type affects any column, independent of whether any rows are returned or not.
The relevant-column capability enables you to hone in on particularly important types of data to audit. Examples include privacy-relevant columns, such as those containing social security numbers, salaries, patient diagnoses, and so on. You could combine the fine-grained audit records to surface queries that had addressed both name and social security number, a potential violation of privacy security laws.
One added benefit is that the audit records being created are more clearly relevant, because they relate to specific data of interest or concern. Another benefit is that fewer total audit records need be generated, because each is now more specific and useful than what could be tracked in earlier releases.
|
 Copyright © 2003 Oracle Corporation All Rights Reserved. |
|