 |
|
Using Privilege Auditing
Oracle Tips by Burleson
|
Privilege auditing involves the tracking of
the SQL statements issued by users who have been granted the right to
execute that statement through a system privilege. In order to enable
or disable auditing on SQL statements, you must have the AUDIT SYSTEM
privilege. Privilege audit options match the corresponding system
privileges. For example, in order to audit the DELETE ANY TABLE system
privilege, you would issue the following command:
AUDIT DELETE
ANY TABLE
BY ACCESS
WHENEVER SUCCESSFUL;
Using Object Auditing
Object auditing involves the tracking of the
specific SQL DML statements issued against objects by database users.
In order to enable or disable auditing on objects, you must have the
AUDIT ANY system privilege or own the object. The following objects
can be audited: tables, views, sequences, packages, standalone stored
procedures and standalone functions, directories, libraries, and
types. Since views and procedures may contain statements that
reference multiple objects, they may generate multiple records in the
audit trail. You cannot limit object auditing with the BY USER clause;
all users will be audited for the object.
Managing the Audit Trail
Audit records are written when the statement
is executed. Even if a statement or transaction is rolled back, the
audit trail record remains. Auditing BY SESSION will write only one
record to the audit trail. Auditing BY ACCESS will write multiple
records, one for each time the action is executed.
The audit trail grows according to the
following factors:
* The number of audit options enabled
* The number of audited actions issued
You need to control the growth of the audit
trail with the following methods:
* Enable and disable auditing options.
* Be selective in deciding which auditing to
turn on.
* Control who can perform auditing.
In order to control the auditing of objects,
Oracle recommends that all objects be owned by a separate schema that
does not correspond to an actual user and that is not granted CONNECT
SESSION. This will prevent anyone from connecting as the owner of
these objects and turning on auditing for the objects. The AUDIT ANY
system privilege should not be granted to anyone except the security
administrator. An alternate method is to have all the objects owned by
the security administrator.
If the audit trail record cannot be written,
the audited action will not be executed and errors will be generated.
If connections are being audited and the audit trail table (SYS.AUD$)
is full, users will not be allowed to log in to the database. The DBA
can CONNECT INTERNAL using Server Manager in either GUI or
command-line mode and clean out the audit trail.
You should develop an auditing strategy by
evaluating the purpose of the auditing and be conservative in the
auditing performed. When auditing is being set to investigate possible
problems, begin with general auditing and then narrow it down to
specific actions. If the purpose of the audit is to provide a record
of historical activity, remember to archive (to another table or an
export file) and purge data on a regular basis. You can selectively
insert records to another table if only a subset needs to be
maintained.
Take these recommended steps to truncate the
audit trail:
1. Copy any subset of
records you may need to keep into a temporary table, or export the
entire SYS.AUD$ table.
2. CONNECT INTERNAL.
3. Truncate the SYS.AUD$
table.
4. Insert records from the
temporary table back into SYS.AUD$.
To protect the audit trail, AUDIT insert,
update, and delete on SYS.AUD$ BY ACCESS (or in whichever user you
have created the AUD$ table) so that records cannot be added, changed,
or deleted without that action being audited. Even if a user is able
to delete records from SYS.AUD$, he or she will not be able to delete
the records generated by his or her actions without creating
additional audit records. In addition, users should not be granted
DELETE ANY TABLE system privilege. Always protect the audit trail (SYS.AUD$).
The maximum size allowed for an audit trail
written to the database is determined at the time the database is
created. By default, the size reflects the system tablespace default
values. The sql.bsq script, which is executed when the database is
created, sets the size of the SYS.AUD$ table. It is very important
that the audit trail be cleaned up regularly. You should export the
data and truncate the SYS.AUD$ table on a regular basis.
If you want to implement processes such as
notification when a user logs on, checking of passwords, automated
invalidation of passwords, and you don’t have Oracle8 or later, you
will want to move the AUD$ table out of the SYS user so that triggers
can be created against it. By creating an ON INSERT trigger that
monitors for specific event codes in the AUD$ table, you can then use
that trigger to implement specific database actions.
Follow these steps to create the AUD$ table in
a different user:
1. Turn off auditing.
2. Export the current
records in the AUD$ table.
3. Drop the SYS.AUD$ table.
4. Create the new AUD$ table
either under SYS in a different tablespace or as a different user.
5. If step 4 created an AUD$
table in a different user, grant all on that AUD$ to SYS user.
6. If step 4 created an AUD$
table in a different user, create a private synonym, AUD$, pointing to
that user’s AUD$ table in the SYS user.
Viewing Audit Trail Information
On most operating systems, special views on
SYS.AUD$ are created when the catalog.sql script (which calls several
scripts, including cataudit.sql) is executed. For other operating
systems, you can create these views by executing the script
cataudit.sql. To remove the audit views, you can execute the script
catnoaud.sql.
The following notations are used in the audit
trail views:
<NULL>. Audit is not set.
S. Audit by SESSION.
A. Audit by ACCESS.
/. Separates the two settings.
The first setting is for WHENEVER SUCCESSFUL; the second setting is
for WHENEVER NOT SUCCESSFUL.
Examples:
<NULL>/<NULL>. No auditing.
A/<NULL>. Auditing BY ACCESS WHENEVER
SUCCESSFUL.
<NULL>/A. Auditing BY ACCESS WHENEVER
NOT SUCCESSFUL.
S/<NULL>. Auditing BY SESSION WHENEVER
SUCCESSFUL.
<NULL>/S. Auditing BY SESSION WHENEVER
NOT SUCCESSFUL.
A/S. Auditing BY ACCESS WHENEVER
SUCCESSFUL and BY SESSION WHENEVER NOT SUCCESSFUL.
S/A. Auditing BY SESSION WHENEVER
SUCCESSFUL and BY ACCESS WHENEVER NOT SUCCESSFUL.
Several views contain a column called
ses_actions. The ses_actions column is a summary for the actions
included in that entry. The ses_actions column is an 11-character
code, where the letter S means success, F means failure, B means both,
and none indicates not audited. The character codes appear in
alphabetical order:
Alter, Audit, Comment, Delete, Grant, Index,
Insert, Lock, Rename, Select, Update
The audit views fall into several categories:
general information on auditing performed, statement audit
information, privilege audit information, and object audit
information.
There are four general auditing views:
AUDIT_ACTIONS. Maps audit trail action
types to codes.
STMT_AUDIT_OPTION_MAP. Maps auditing
option types to codes.
DBA_AUDIT_TRAIL. All audit records in
the system.
USER_AUDIT_TRAIL. Audit trail entries
relevant to the user.
For statement auditing, the following views
are useful:
DBA_STMT_AUDIT_OPTS. Information on
current system-auditing options across the system and by user.
DBA_AUDIT_STATEMENT. Audit entries for
statements with the GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM
commands.
USER_AUDIT_STATEMENT. Audit entries for
statements issued by the user.
DBA_AUDIT_SESSION. Audit entries for
connections and disconnections.
USER_AUDIT_SESSION. Audit entries for
connections and disconnections for that user.
DBA_AUDIT_EXISTS. Audit entries created
by the AUDIT EXISTS command.
For privilege auditing, the
DBA_PRIV_AUDIT_OPTS view shows the privilege option audit entries.
There is one entry for each audited privilege. For object auditing,
the relevant views are:
DBA_OBJ_AUDIT_OPTS. Auditing options
set for all tables and views.
USER_OBJ_AUDIT_OPTS. Auditing options
for the owner’s tables and views (corresponds to the
DBA_OBJ_AUDIT_OPTS view).
ALL_DEF_AUDIT_OPTS. Default audit
options for objects being created.
DBA_AUDIT_OBJECT. Audit records for all
objects in the system.
USER_AUDIT_OBJECT. Audit trail records
for statements concerning objects owned by that user (corresponds to
the DBA_AUDIT_OBJECT view).
Auditing by Database Triggers
Enabling auditing options may not always be
sufficient to evaluate suspicious activity within your database. When
you enable auditing, Oracle places records in the SYS.AUD$ table in
accordance with the auditing options that you have specified. One
limitation to this type of auditing is that SYS.AUD$ does not provide
you with value-based information. You need to write triggers to record
the before and after values on a per-row basis.
Auditing with Oracle supports DML and DDL
statements on objects and structures. Triggers support DML statements
issued against objects, and can be used to record the actual values
before and after the statement.
In some facilities, audit commands are
considered security audit utilities, while triggers are referred to as
financial auditing. This is because triggers can provide a method to
track actual changes to values in a table. Although, similar to the
AUDIT command, you can use triggers to record information, you
should customize your auditing by using triggers only when you need
more detailed audit information.
AFTER triggers are normally used to avoid
unnecessary statement generation for actions that fail due to
integrity constraints. AFTER triggers are executed only after all
integrity constraints have been checked. AFTER ROW triggers provide
value-based auditing for each row of the tables and support the use of
“reason codes.” A reason for the statement or transaction, along with
the user, sysdate, and old and new values, can be inserted into
another table for auditing purposes.
Oracle auditing can be used for successful and
unsuccessful actions, as well as connections, disconnections, and
session I/O activities. With auditing, you can decide if the actions
should be BY ACCESS or BY SESSION. Triggers can only audit successful
actions against the table on which they are created. If auditing is
being performed using a trigger, any rollback or unsuccessful action
will not be recorded.
Auditing provides an easy, error-free method
to tracking, with all the audit records stored in one place. Triggers
are more difficult to create and maintain.
See
Code Depot for Full Scripts
 |
This is an excerpt
from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
|
