| Oracle® Database Security Guide 10g Release 2 (10.2) Part Number B14266-02 |
|
|
View PDF |
| Oracle® Database Security Guide 10g Release 2 (10.2) Part Number B14266-02 |
|
|
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 12 provides detailed information and guidelines on configuring auditing parameters and administering auditing actions and results.
The present chapter describes the different types of auditing, what it involves, and the resulting audit trails and records.
Auditing is typically used to:
Enable future accountability for current actions taken in a particular schema, table, or row, or affecting specific content
Deter users (or others) from inappropriate actions based on that accountability
Investigate suspicious activity
For example, if some user is deleting data from tables, then the security administrator might decide to audit all connections to the database and all successful and unsuccessful deletions of rows from all tables in the database.
Notify an auditor that an unauthorized user is manipulating or deleting data and that the user has more privileges than expected which can lead to reassessing user authorizations
Monitor and gather data about specific database activities
For example, the database administrator can gather statistics about which tables are being updated, how many logical I/Os are performed, or how many concurrent users connect at peak times.
Detect problems with an authorization or access control implementation
For example, you can create audit policies that you expect will never generate an audit record because the data is protected in other ways. However, if these policies do generate audit records, then you will know the other security controls are not properly implemented.
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 the following:
Successful statement executions, unsuccessful statement executions, or both
Statement executions once in each user session or once every time the statement is executed
Activities of all users or of a specific user
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.
Table 8-1 Auditing Types and Descriptions
The following subsections explain the records and timing of the different audit trails:
Audit records include information about 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 12, "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 consists of a single table named SYS.AUD$ in the SYS schema of the data dictionary of each Oracle database. 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 Table 8-2 shows columns that always appear in the audit trail. If the data they represent is available, then that data populates the corresponding column. (For certain columns, this list shows the column name displayed in the audit record in parentheses.)
Table 8-2 Columns Shown in the Database Audit Trail (DBA_AUDIT_TRAIL)
| Column Description (Name) | Also Appears in the Operating System Audit Trail? |
|---|---|
|
Operating system login user name ( |
Yes. |
|
Database user name ( |
Yes. |
|
Session identifier |
Yes. |
|
Terminal identifier |
Yes. |
|
Name of the schema object accessed |
Yes. |
|
Operation performed or attempted ( |
Yes. |
|
Completion code of the operation |
Yes. |
|
Date and time stamp in UTC (Coordinated Universal Time) format |
Date and time yes, but not in UTC format. |
|
System privileges used ( |
Yes. |
|
Proxy Session audit ID |
No. |
|
Global User unique ID |
No. |
|
Distinguished name |
Yes. |
|
Instance number |
No. |
|
Process number |
No. |
|
Transaction ID |
No. |
|
SCN (system change number) for the SQL statement |
No. |
|
SQL text that triggered the auditing ( |
No. |
|
Bind values used for the SQL statement, if any ( |
No. |
Notes:
SQLBIND and SQLTEXT are not populated unless you specify AUDIT_TRAIL=DB,EXTENDED in the database initialization file, init.ora. This is because CLOBs are comparatively expensive to populate.If the database destination for audit records becomes full or is unavailable, and is therefore unable to accept new records, then 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 Database 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 Database. If not, then 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.
If init.ora specifies AUDIT_TRAIL=XML, then audit records are written to the operating system as XML files. A new dynamic view, V$XML_AUDIT_TRAIL, makes such XML audit records available to DBAs through a SQL query, providing enhanced usability. Querying this view causes all XML files (all files with a .xml extension) in the AUDIT_FILE_DEST directory to be parsed and presented in relational table format. Because XML is a standard document format, many utilities are available to parse and analyze such XML data.
See Also:
Operating-system-specific Oracle documentation, to see if this feature has been implemented on your operating systemHowever, 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 Database 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 Database server prevents audited events from occurring if the audit trail is unable to accept the database audit record for the statement.
One potential security vulnerability for an operating system audit trail is that a privileged user, such as a DBA, can modify or delete audit records. In order to minimize this risk, you can use a syslog audit trail. Syslog is a standard protocol on UNIX-based systems for logging information from different components of a network. Applications call the syslog() function to log information to the syslog daemon, which then determines where to log the information. You can configure syslog to log information to a file name syslog.conf, to the console, or to a remote, dedicated log host. You can also configure syslog to alert a specified set of users when information is logged.
Because applications, such as an Oracle process, use the syslog() function to log information to the syslog daemon, a privileged user does not need to have permissions to the file system where messages are logged. For this reason, audit records stored using a syslog audit trail can be more secure than audit records stored using an operating system audit trail. In addition to restricting permissions to a file system for a privileged user, for a syslog audit trail to be secure, neither privileged users nor the Oracle process should have root access to the system where the audit records are written.
Note:
The security vulnerability that is exposed with an operating system audit trail is not an issue on Windows operating systems, because audit records cannot by modified directly. Instead, audit records on Windows operating systems are stored and monitored through Event Viewer.The operating system and syslog audit trails are encoded, but are decoded in data dictionary files and error messages. The following fields are included:
Action code describes the operation performed or attempted. The AUDIT_ACTIONS data dictionary table contains a list of these codes and their descriptions.
Privileges used describes any system privileges used to perform the operation. The SYSTEM_PRIVILEGE_MAP table lists all of these codes and their descriptions.
Completion code describes the result of the attempted operation. Successful operations return a value of zero, and unsuccessful operations return the Oracle error code describing why the operation was unsuccessful.
See Also:
Table 8-2, "Columns Shown in the Database Audit Trail (DBA_AUDIT_TRAIL)", which also indicates the columns that appear in the operating system audit trail
Oracle Database Administrator's Guide for instructions for creating and using predefined views
Oracle Database Error Messages for a list of completion codes
Some database-related actions are always recorded into the operating system and syslog audit trails regardless of whether database auditing is enabled. The fact that these records are always created is sometimes referred to as mandatory auditing. The following actions are recorded:
At instance startup, an audit record is generated that includes the operating system user starting the instance, the terminal identifier of the user, and the date and time stamp. This information is recorded into the operating system or syslog audit trails, because the database audit trail is not available until after startup has successfully completed.
At instance shutdown, an audit record is generated that details the operating system user shutting down the instance, the terminal identifier of the user, and the date and time stamp.
During connections made with administrator privileges, an audit record is generated that details the operating system user connecting to Oracle Database with administrator privileges. This record provides accountability regarding users connected with administrator privileges.
On operating systems that do not make an audit trail accessible to Oracle Database, 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:
Operating-system-specific Oracle documentation for more information about the operating system and syslog audit trailStandard auditing for the entire database is either enabled or disabled by the security administrator. If it is disabled, then 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 the statement.
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 transaction being committed. That is, even if a user transaction is rolled back, the audit trail record remains committed.
Statement and privilege audit options in effect at the time a database user connects to the database remain in effect for the duration of the session. Setting or changing statement or privilege audit options in a session does not take effect in that session. The modified statement or privilege audit options take effect only when the current session ends and a new session is created.
In contrast, changes to schema object audit options become effective for current sessions immediately.
Note:
Operations by theSYS user and by users connected through SYSDBA or SYSOPER can be fully audited with the AUDIT_SYS_OPERATIONS initialization parameter. Every successful SQL statement from SYS is audited. This specialized form of auditing audits all actions performed by every user with the SYSDBA privilege and writes only to an operating system location. It is not dependent on the standard auditing parameter, AUDIT_TRAIL. Storing these records in a location separate from the usual database audit trail in the SYS schema provides for greater auditing security.See Also:
Oracle Database Administrator's Guide for instructions on enabling and disabling auditing
"SQL, PL/SQL, and Java" in Oracle Database Concepts for information about the different phases of SQL statement processing and shared SQL
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 the following categories:
DDL statements: As an example, AUDIT TABLE audits all CREATE and DROP TABLE statements
DML statements: As an example, AUDIT SELECT TABLE audits all SELECT ... FROM TABLE/VIEW statements, regardless of the table or view
Statement auditing can be broad or focused, for example, by auditing the activities of all database users or of only a select list.
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, then only a single audit record is generated. For example, if the statement clause TABLE and the system privilege CREATE TABLE are both audited, then only a single audit record is generated each time a table is created.
Thus privilege auditing does not occur if the action is already permitted by the existing owner and schema object privileges. Privilege auditing is 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 indirectly audit access to these schema objects, 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 objectsThe 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 lists auditing actions that were not available before Oracle Database.
Oracle Database lets you focus statement, privilege, and schema object auditing in three areas, as discussed in the following subsections:
Auditing Statement Executions: Successful, Unsuccessful, or Both
Number of Audit Records from Multiple Executions of a Statement
For statement, privilege, and schema object auditing, Oracle Database 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 fail to execute because they were not valid cannot be audited.
For example, an enabled privilege auditing option set to audit unsuccessful statement executions audits statements that use the target system privilege but 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 able to audit 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 able to audit both successful and unsuccessful executions of the audited statement.
If an audited statement is issued multiple times in a single user session, then 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, then 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:
All statement audit options that audit DDL statements
All privilege audit options that audit DDL statements
For all other audit options, BY SESSION is used by default.
This section provides detailed examples of using each clause, in the following subsections:
See Also:
Oracle Database SQL ReferenceFor 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 Oracle Database.
BY SESSION Example 1
Assume the following:
The 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.
BY SESSION Example 2
Alternatively, assume the following:
The 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.
Note:
If you use theBY SESSION clause when directing audit records to the operating system audit trail, then Oracle Database generates and stores an audit record each time an access is made. Therefore, in this auditing configuration, BY SESSION is equivalent to BY ACCESS.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:
An application, such as Oracle Forms, holding a cursor open for reuse
Subsequent execution of a cursor using new bind variables
Statements executed within PL/SQL loops where the PL/SQL engine optimizes the statements to reuse a single cursor
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:
The 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 departments table 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.
Audit by User Example
To audit statements by the users
SCOTT and BLAKE that query or update a table or view, use the following statements:
AUDIT SELECT TABLE, UPDATE TABLE
BY scott, blake;
See Also:
Oracle Database SQL Reference for more information about auditing by userIn 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 middle-tier application. To do so, use the BY proxy clause in your AUDIT statement.
This clause allows you a few options. You can:
Audit SQL statements issued by the specific proxy on its own behalf
Audit statements executed on behalf of a specified user or users
Audit all statements executed on behalf of any user
The middle tier can also set the user client identity in a database session, enabling audit of end-user actions through the mid-tier application. The end-user 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 Database triggers can potentially monitor DML actions such as INSERT, UPDATE, and DELETE, monitoring SELECT statements can be costly. In some cases, a trigger may audit too much, and 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.
FGA 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 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, then 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 created are 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.
