11 Auditing Under Oracle Label Security

The Oracle Database 10g Release 2 (10.2) audit facility lets you hold database users accountable for the operations they perform. It can track specific database objects, operations, users, and privileges. Oracle Label Security supplements this by tracking use of its own administrative operations and policy privileges. It provides the SA_AUDIT_ADMIN package to set and change the policy auditing options.

This chapter explains how to use Oracle Label Security auditing. It contains these topics:

Overview of Oracle Label Security Auditing
Enabling Systemwide Auditing: AUDIT_TRAIL Initialization Parameter
Creating and Dropping an Audit Trail View for Oracle Label Security
Oracle Label Security Auditing Tips

11.1 Overview of Oracle Label Security Auditing

Oracle Label Security auditing supplements standard Oracle Database auditing by tracking use of its own administrative operations and policy privileges. You can use either the SA_AUDIT_ADMIN package or Oracle Policy Manager to set and change the auditing options for an Oracle Label Security policy.

When you create a new policy, a label column for that policy is added to the database audit trail. The label column is created regardless of whether auditing is enabled or disabled, and independent of whether database auditing or operating system auditing is used. Whenever a record is written to the audit table, each policy provides a label for that record to indicate the session label. The administrator can create audit views to display these labels. Note that in the audit table, the label does not control access to the row, instead it only records the sensitivity of the row.

The auditing options that you specify apply only to subsequent sessions, not to the current session. You can specify audit options even if auditing is disabled. No overhead is created by making only these specifications. When you do enable Oracle Label Security auditing, the options come into effect, and overhead is created beyond that created by standard Oracle Database auditing.

Note that Oracle Label Security does not provide labels for audit data written to the operating system audit trial. All Oracle Label Security audit records are written directly to the database audit trail, even if operating system auditing is enabled. If auditing is disabled, then no Oracle Label Security audit records are generated.

11.2 Enabling Systemwide Auditing: AUDIT_TRAIL Initialization Parameter

For Oracle Label Security to generate audit records, you must first enable systemwide auditing by setting the Oracle Database AUDIT_TRAIL initialization parameter in the database's parameter file. The parameter can be set to one of the following values:

Table 11-1 AUDIT_TRAIL Parameter Settings

Setting	Explanation
DB	Enables database auditing and directs all audit records to the database audit trail. This approach is recommended by Oracle. Note that even with AUDIT_TRAIL set to DB, some records are always sent to the operating system audit trail. These include STARTUP and SHUTDOWN statements, as well as CONNECT AS SYSOPER or SYSDBA.
DB_EXTENDED	Does all actions of AUDIT_TRAIL=DB and also populates the SqlBind and SqlText CLOB-type columns of the AUD$ table.
OS	Enables operating system auditing. This directs most of your Oracle Database audit records to the operating system, rather than to the database; the records will not contain Oracle Label Security labels. By contrast, any Oracle Label Security auditing will go to the database, with labels. If you set AUDIT_TRAIL to OS, then the Oracle Label Security-specific audit records are written to the database audit trail and the other Oracle Database audit records are written to the operating system audit trail (with no policy column in the operating system data).
NONE	Disables auditing. This is the default.

After you have edited the parameter file, restart the database instance to enable or disable database auditing as specified.

Set the AUDIT_TRAIL parameter before you set audit options. If you do not set this parameter, then you are still able to set audit options. However, audit records are not written to the database until the parameter is set and the database instance is restarted.

11.3 Enabling Oracle Label Security Auditing with SA_AUDIT_ADMIN

After you have enabled systemwide auditing, you can use SA_AUDIT_ADMIN procedures to enable or disable Oracle Label Security auditing. To use Oracle Label Security auditing, you must have the policy_type role.

Auditing Options for Oracle Label Security
Enabling Oracle Label Security Auditing with SA_AUDIT_ADMIN.AUDIT
Disabling Oracle Label Security Auditing with SA_AUDIT_ADMIN.NOAUDIT
Examining Audit Options with the DBA_SA_AUDIT_OPTIONS View

11.3.1 Auditing Options for Oracle Label Security

The AUDIT and NOAUDIT options are as follows:

Table 11-2 Auditing Options for Oracle Label Security

Option	Description
APPLY	Audits application of specified Oracle Label Security policies to tables and schemas
REMOVE	Audits removal of specified Oracle Label Security policies from tables and schemas
SET	Audits the setting of user authorizations, and user and program privileges
PRIVILEGES	Audits use of all policy-specific privileges

11.3.2 Enabling Oracle Label Security Auditing with SA_AUDIT_ADMIN.AUDIT

Use the AUDIT procedure to enable policy-specific auditing.

Syntax:

PROCEDURE AUDIT (
     policy_name     IN VARCHAR2,
     users           IN VARCHAR2 DEFAULT NULL,
     option          IN VARCHAR2 DEFAULT NULL,
     type            IN VARCHAR2 DEFAULT NULL,
     success         IN VARCHAR2 DEFAULT NULL);

Parameter	Description	Default Behavior
policy_name	Required. Specifies the name of an existing policy. Auditing of each policy is independent of all others.	None
users	Optional. A comma-delimited list of user names to audit. If not specified, then all users are audited.	All users
option	Optional. A comma-delimited list of options to be audited. Refer to . If not specified, then all default options (that is, options not including privileges) are audited. Audit options for privileged operations should be set explicitly by specifying the PRIVILEGES option, which sets audit options for all privileges.	All options
type	Optional. BY ACCESS or BY SESSION. If not specified, then audit records are written by session.	BY SESSION
success	Optional. SUCCESSFUL or NOT SUCCESSFUL. If not specified, then audit is written for both.	SUCCESSFUL and NOT SUCCESSFUL

If the administrator does not specify any audit options, then all options except the privilege-related ones are audited. Auditing of privileges must be specified explicitly. For example, if the administrator enters

SA_AUDIT_ADMIN.AUDIT ('HR');

then default auditing options are set for the HR policy. When the administrator enables auditing, it will be performed on all users by session, whether successful or not.

When you set auditing parameters and options, the new values apply only to subsequent sessions, not to the current session.

Consider also a case in which one AUDIT call (with no users specified) enables auditing for APPLY operations for all users, and then a second call enables auditing of REMOVE operations for a specific user. For example:

SA_AUDIT_ADMIN.AUDIT ('HR', NULL, 'APPLY');
SA_AUDIT_ADMIN.AUDIT ('HR', 'SCOTT', 'REMOVE');

In this case, SCOTT is audited for both APPLY and REMOVE operations.

11.3.3 Disabling Oracle Label Security Auditing with SA_AUDIT_ADMIN.NOAUDIT

To disable policy-specific auditing, use the SA_AUDIT_ADMIN.NOAUDIT procedure.

Syntax:

PROCEDURE NOAUDIT (
     policy_name     IN VARCHAR2,
     users           IN VARCHAR2 DEFAULT NULL,
     option          IN VARCHAR2 DEFAULT NULL);

Parameter	Description	Default Behavior
policy_name	Required. Specifies the name of an existing policy.	None
users	Optional. A comma-delimited list of user names to audit. If not specified, then auditing is disabled for all users.	All users
option	Optional. A comma-delimited list of options to be disabled.Refer to . If not specified, then all default options are disabled. Privileges must be disabled explicitly.	All options

You can disable auditing for all enabled options, or only for a subset of enabled options. All auditing for the specified options is disabled for all specified users (or all users, if the users parameter is NULL). For example, the following statement disables auditing of the APPLY and REMOVE operations for users John, Mary, and Scott:

SA_AUDIT_ADMIN.NOAUDIT ('HR', 'JOHN, MARY, SCOTT', 'APPLY, REMOVE');

Consider also a case in which one AUDIT call enables auditing for a specific user, and a second call (with no user specified) enables auditing for all users. For example:

SA_AUDIT_ADMIN.AUDIT ('HR', 'SCOTT');
SA_AUDIT_ADMIN.AUDIT ('HR');

In this case, a subsequent call to NOAUDIT with no users specified (such as the following)

SA_AUDIT_ADMIN.NOAUDIT ('HR');

does not reverse the auditing that was set for SCOTT explicitly in the first call. So, auditing continues to be performed on SCOTT. In this way, even if NOAUDIT is set for all users, Oracle Label Security still audits any users for whom auditing was explicitly set.

Auditing of privileged operations must be specified explicitly. If you run NOAUDIT with no options, the Oracle Label Security will nonetheless continue to audit privileged operations. For example, if auditing is enabled and you enter

SA_AUDIT_ADMIN.NOAUDIT ('HR');

then auditing will continue to be performed on the privileged operations (such as WRITEDOWN).

NOAUDIT parameters and options that you set apply only to subsequent sessions, not to current sessions.

If you try to enable an audit option that has already been set, or if you try to disable an audit option that has not been set, then Oracle Label Security processes the statement without indicating an error. An attempt to specify an invalid option results in an error message.

11.3.4 Examining Audit Options with the DBA_SA_AUDIT_OPTIONS View

This section describes the view that displays the Oracle Label Security auditing options and privileges.

The DBA_SA_AUDIT_OPTIONS view contains the following columns:

Table 11-3 Columns in the DBA_SA_AUDIT_OPTIONS View

Name	Null?	Type
POLICY_NAME	NOT NULL	VARCHAR2(30)
USER_NAME	NOT NULL	VARCHAR2(30)
APY		VARCHAR2(3)
REM		VARCHAR2(3)
SET_		VARCHAR2(3)
PRV		VARCHAR2(30

Output is similar to the following:

Table 11-4 DBA_SA_AUDIT_OPTIONS Sample Output

POLICY_NAME	USER_NAME	APY	REM	SET	PRV
HR	SCOTT	-/-	-/-	-/-	A/A
HR	LBACSYS	S/S	S/S	S/S	-/-

11.4 Managing Policy Label Auditing

This section describes procedures available to manage policy label auditing:

Policy Label Auditing with SA_AUDIT_ADMIN.AUDIT_LABEL
Disabling Policy Label Auditing with SA_AUDIT_ADMIN.NOAUDIT_LABEL
Finding Label Audit Status with AUDIT_LABEL_ENABLED

11.4.1 Policy Label Auditing with SA_AUDIT_ADMIN.AUDIT_LABEL

Use the AUDIT_LABEL procedure to record policy labels during auditing. It causes the user's session label to be stored in the audit table.

Syntax:

PROCEDURE AUDIT_LABEL (
     policy_name     IN VARCHAR2);

Parameter	Description	Default
policy_name	Required. Specifies the name of an existing policy.	None

11.4.2 Disabling Policy Label Auditing with SA_AUDIT_ADMIN.NOAUDIT_LABEL

Use the NOAUDIT_LABEL procedure to disable auditing of policy labels.

Syntax:

PROCEDURE NOAUDIT_LABEL (
     policy_name     IN VARCHAR2);

Parameter	Description	Default
policy_name	Required. Specifies the name of an existing policy.	None

11.4.3 Finding Label Audit Status with AUDIT_LABEL_ENABLED

Use the AUDIT_LABEL_ENABLED function to show whether labels are being recorded in audit records for the policy.

Syntax:

FUNCTION AUDIT_LABEL_ENABLED (policy_name IN VARCHAR2)
     RETURN boolean;

11.5 Creating and Dropping an Audit Trail View for Oracle Label Security

This section contains these topics:

Creating a View with SA_AUDIT_ADMIN.CREATE_VIEW
Dropping a View with SA_AUDIT_ADMIN.DROP_VIEW

11.5.1 Creating a View with SA_AUDIT_ADMIN.CREATE_VIEW

The CREATE_VIEW procedure creates an audit trail view named DBA_policyname_AUDIT_TRAIL, which contains the specified policy's label column as well as all the entries in the audit trail written on behalf of this policy. If the view name exceeds the database limit of 30 characters, then the user can optionally specify a shorter view name.

Syntax (either of two):

A one-parameter procedure:
```
PROCEDURE CREATE_VIEW (
     policy_name     IN VARCHAR2);
```
where policy_name specifies the name of an existing policy.

or
A two-parameter procedure:
```
PROCEDURE CREATE_VIEW (
     policy_name     IN VARCHAR2,
     view_name       IN VARCHAR2    DEFAULT NULL);
```
where policy_name specifies the name of an existing policy and view_name is an optional parameter, maximum 14 characters, specifying the desired view name.

11.5.2 Dropping a View with SA_AUDIT_ADMIN.DROP_VIEW

The DROP_VIEW procedure drops the audit trail view for the specified policy.

Syntax (either of two):

A one-parameter procedure:
```
PROCEDURE DROP_VIEW (
     policy_name     IN VARCHAR2);
```
where policy_name specifies the name of an existing policy.

or
A two-parameter procedure:
```
PROCEDURE DROP_VIEW (
     policy_name     IN VARCHAR2,
     view_name       IN VARCHAR2    DEFAULT NULL);
```
where policy_name specifies the name of an existing policy and view_name is an optional parameter, maximum 14 characters, specifying an existing view's name .

Note:

When sa_audit_admin.create_view was used to create a pre-10i audit view, that view did not show the timestamp field for the audit records in 10i. Oracle Label Security recommends that all pre-10i Oracle Label Security audit views be dropped and re-created, by using sa_audit_admin.drop_view and sa_audit_admin.create_view.

11.6 Oracle Label Security Auditing Tips

This section contains these topics:

Strategy for Setting SA_AUDIT_ADMIN Options
Auditing Privileged Operations

11.6.1 Strategy for Setting SA_AUDIT_ADMIN Options

Before setting any audit options, you must devise an auditing strategy that monitors events of interest, without recording extraneous events. You should periodically review this strategy, because applications, user base, configurations, and other external factors can change.

The Oracle Label Security options, and those provided by the Oracle Database audit facility, might not directly address all of your specific or application-dependent auditing requirements. However, through use of database triggers, you can audit specific events and record specific information that you cannot audit and record using the more generic audit facility.

11.6.2 Auditing Privileged Operations

Consider auditing any operations that require Oracle Label Security privileges. Because these privileges perform sensitive operations, and because their abuse could jeopardize security, you should closely monitor their dissemination and use.