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 top-level SQL statement directly issued 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.
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.
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.