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:
Enabling Systemwide Auditing: AUDIT_TRAIL Initialization Parameter
Creating and Dropping an Audit Trail View for Oracle Label Security
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.
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
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.
See Also:
For information about enabling and disabling systemwide auditing, setting audit options, and managing the audit trail, refer to Oracle Database Administrator's GuideFor information about editing initialization parameters, refer to Oracle Database Reference
For details about systemwide AUDIT and NOAUDIT functioning, see Oracle Database SQL Reference
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.
Enabling Oracle Label Security Auditing with SA_AUDIT_ADMIN.AUDIT
Disabling Oracle Label Security Auditing with SA_AUDIT_ADMIN.NOAUDIT
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 |
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.
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.
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 |
-/- |
See Also:
Chapter 11 of the Oracle Database Security GuideThis section describes procedures available to manage policy label auditing:
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 |
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 |
This section contains these topics:
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.
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.This section contains these topics:
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.
See Also:
For more information about using triggers for auditing, see Oracle Database ConceptsConsider 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.