By default, from Oracle version 7.X on, the following sequence of events occurs when the database is created:
CATALOG.SQLis run and calls several other scripts.CATAUDIT.SQLis run as one of the scripts called fromCATALOG.SQL.The auditing views are created.
A public synonym is created for each of the auditing views.
Public access is granted to enable SELECT on each of the auditing views.
Thus, from the point in time when the database is created, the
ability for anyone to audit activities in the database exists. In the
case of USER_ audit views, as opposed to DBA_ audit views, the user
will only be permitted to view information in his own area and not be
able to see information in another user’s schema. The
CATAUDIT.SQL
script can be found in the
$ORACLE_HOME/RDMBS80/ADMIN directory on most
platforms. On an OpenVMS system, all of the “CAT” scripts
(Oracle-delivered scripts with the first three letters of the script
name “CAT”) can be found in the
ORA_ROOT:[RDBMS] directory.
The
auditing views require few resources prior to being activated.
However, since public is granted access to them, be
sure to protect them from tampering. Running the
CATAUDIT.SQL script will create the auditing
views shown in Table 10.1. Only the access to the
DBA_ views for auditing is granted to the SELECT_CATALOG_ROLE.
Table 10-1. Oracle Standard Auditing Views (from CATAUDIT.SQL)
In the documentation of the last view, DBA_AUDIT_EXISTS, the Oracle documentation supplied in the script does not mean to imply that the DBA is really clairvoyant! The statement “Only DBAs can see audit trail information about objects that do not exist” is in reference to Oracle’s error code return when a query requests an object the user does not have the privilege to access. For example, an error with a code of 942 is generated when a request is made for a table whose table name is not registered within the data dictionary. Sometimes, 942 errors don’t indicate any actual wrongdoing. There are occasions when a user or developer may just have “fat fingered” or mistyped a table name and received this error as a result. The error tells the user that the “table or view does not exist.” However, an employee who is repeatedly receiving this error may be attempting to gain information from the database about areas with which he should not have the ability to interact. If you suspect that someone is fishing around in the database trying to guess the names of tables, you might enable auditing to track occurrences of 942 errors (among others). In this way, you might be able to spot someone who is steadily receiving this error. Once you have confirmed that someone is attempting to obtain information about the tables, you can take further action as required.
In an Oracle 8.0.4 database,
there are 121 separate types of auditing that can be performed. Once
the CATAUDIT.SQL script has been run, you can do
a SELECT on AUDIT_ACTIONS to display the complete list of audit
actions you can perform. The list contains the following:
SQL> SELECT * FROM audit_actions;
ACTION NAME
--------- ---------------------------
0 UNKNOWN
1 CREATE TABLE
2 INSERT
3 SELECT
4 CREATE CLUSTER
5 ALTER CLUSTER
6 UPDATE
7 DELETE
8 DROP CLUSTER
9 CREATE INDEX
10 DROP INDEX
11 ALTER INDEX
12 DROP TABLE
13 CREATE SEQUENCE
14 ALTER SEQUENCE
15 ALTER TABLE
16 DROP SEQUENCE
17 GRANT OBJECT
18 REVOKE OBJECT
19 CREATE SYNONYM
20 DROP SYNONYM
21 CREATE VIEW
22 DROP VIEW
23 VALIDATE INDEX
24 CREATE PROCEDURE
25 ALTER PROCEDURE
26 LOCK
27 NO-OP
28 RENAME
29 COMMENT
30 AUDIT OBJECT
31 NOAUDIT OBJECT
32 CREATE DATABASE LINK
33 DROP DATABASE LINK
34 CREATE DATABASE
35 ALTER DATABASE
36 CREATE ROLLBACK SEG
37 ALTER ROLLBACK SEG
38 DROP ROLLBACK SEG
39 CREATE TABLESPACE
40 ALTER TABLESPACE
41 DROP TABLESPACE
42 ALTER SESSION
43 ALTER USER
44 COMMIT
45 ROLLBACK
46 SAVEPOINT
47 PL/SQL EXECUTE
48 SET TRANSACTION
49 ALTER SYSTEM
50 EXPLAIN
51 CREATE USER
52 CREATE ROLE
53 DROP USER
54 DROP ROLE
55 SET ROLE
56 CREATE SCHEMA
57 CREATE CONTROL FILE
59 CREATE TRIGGER
60 ALTER TRIGGER
61 DROP TRIGGER
62 ANALYZE TABLE
63 ANALYZE INDEX
64 ANALYZE CLUSTER
65 CREATE PROFILE
66 DROP PROFILE
67 ALTER PROFILE
68 DROP PROCEDURE
70 ALTER RESOURCE COST
71 CREATE SNAPSHOT LOG
72 ALTER SNAPSHOT LOG
73 DROP SNAPSHOT LOG
74 CREATE SNAPSHOT
75 ALTER SNAPSHOT
76 DROP SNAPSHOT
77 CREATE TYPE
78 DROP TYPE
79 ALTER ROLE
80 ALTER TYPE
81 CREATE TYPE BODY
82 ALTER TYPE BODY
83 DROP TYPE BODY
84 DROP LIBRARY
85 TRUNCATE TABLE
86 TRUNCATE CLUSTER
91 CREATE FUNCTION
92 ALTER FUNCTION
93 DROP FUNCTION
94 CREATE PACKAGE
95 ALTER PACKAGE
96 DROP PACKAGE
97 CREATE PACKAGE BODY
98 ALTER PACKAGE BODY
99 DROP PACKAGE BODY
100 LOGON
101 LOGOFF
102 LOGOFF BY CLEANUP
103 SESSION REC
104 SYSTEM AUDIT
105 SYSTEM NOAUDIT
106 AUDIT DEFAULT
107 NOAUDIT DEFAULT
108 SYSTEM GRANT
109 SYSTEM REVOKE
110 CREATE PUBLIC SYNONYM
111 DROP PUBLIC SYNONYM
112 CREATE PUBLIC DATABASE LINK
113 DROP PUBLIC DATABASE LINK
114 GRANT ROLE
115 REVOKE ROLE
116 EXECUTE PROCEDURE
117 USER COMMENT
118 ENABLE TRIGGER
119 DISABLE TRIGGER
120 ENABLE ALL TRIGGERS
121 DISABLE ALL TRIGGERS
122 NETWORK ERROR
123 EXECUTE TYPE
157 CREATE DIRECTORY
158 DROP DIRECTORY
159 CREATE LIBRARY
121 rows selected.The number and type of auditing actions you can enable may vary from version to version of the RDBMS. The numbers listed in the ACTION column are used internally by Oracle to reference the actions rather than reference the character NAME translation. Oracle views that contain numbers generally have sequence gaps to provide space for options to be added in later releases. Keep in mind that when you enable auditing on an action, you are just asking Oracle to report each time that action has occurred.
The default auditing options that can be taken against an object are normally activated with the clause WHENEVER SUCCESSFUL or WHENEVER UNSUCCESSFUL. The command to enable auditing when the user ralph successfully modifies a value in a table would look like this:
AUDIT UPDATE BY ralph BY SESSION WHENEVER SUCCESSFUL;
The default auditing options and their abbreviations are:
|
Option |
Abbreviation |
|---|---|
|
ALTER |
ALT |
|
AUDIT |
AUD |
|
COMMENT |
COM |
|
CREATE |
CRE |
|
DELETE |
DEL |
|
EXECUTE |
EXE |
|
GRANT |
GRA |
|
INDEX |
IND |
|
INSERT |
INS |
|
LOCK |
LOC |
|
READ |
REA |
|
REFERENCE |
REF |
|
RENAME |
REN |
|
SELECT |
SEL |
|
UPDATE |
UPD |
|
WRITE |
WRI |
For the USER_OBJ_AUDIT_OPTS and DBA_OBJ_AUDIT_OPTS views (see Table 10.1 for a summary of these views), the values in each column of the view from the ALT (ALTER) column through the UPD (UPDATE) column are three-character strings that represent different levels of detail. The other columns in this view are OWNER, OBJECT_NAME, and OBJECT_TYPE. For every object a user owns, there will be an entry in the USER_OBJ_AUDIT_OPTS view and the DBA_OBJ_AUDIT_OPTS view. A table owner can enable auditing to capture each time someone removes data from his table.
Since you can enable auditing by each time a user accesses an object or, alternatively, by each user session, the levels of detail are Access (A), Session (S), or None (-). The values appear in sequences like “A/S”, “A/-”, etc. The value before the slash indicates the auditing level if the action was successful. The value after the slash indicates the auditing level if the action was not successful. The form of display was chosen to enable the listing of all the forms of audit on one line. This is also the reason for the columns having three-letter names. The view may be formatted for readability but the values (once you get used to what the information is presenting) eventually become very readable. A sample output of one line from the DBA_OBJ_AUDIT_OPTS view minus the OBJECT_NAME and OBJECT_TYPE columns — without any auditing enabled — would look like the following.
ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-
There are no action levels displayed in this example, so there is a dash (-) on either side of each slash in the pairs. The object types of interest in this view are TABLE, VIEW, SEQUENCE, PROCEDURE, TYPE, and DIRECTORY.
Earlier in this chapter, we looked
at the documentation stored in the actual
CATAUD.SQL script to see what the auditing views are and
to gain a brief description of each view. A much easier way to see
the auditing view descriptions is to do a SELECT from the view
DICTIONARY and just look for entries that contain the word
“AUDIT.” In an version 8.0.3 database, you will see the
following entries:
SQL> COLUMN table_name FORMAT a25
SQL> COLUMN comments FORMAT a45 WORD
SQL> SELECT table_name, comments
2 FROM dictionary
3 WHERE table_name LIKE '%_AUDIT%';
TABLE_NAME COMMENTS
------------------------- ---------------------------------------------
ALL_DEF_AUDIT_OPTS Auditing options for newly created objects
DBA_AUDIT_EXISTS Lists audit trail entries produced by AUDIT
NOT EXISTS and AUDIT EXISTS
DBA_AUDIT_OBJECT Audit trail records for statements concerning
objects, specifically: table, cluster, view,
index, sequence, [public] database link,
[public] synonym, procedure, trigger, rollback
segment, tablespace, role, user
DBA_AUDIT_SESSION All audit trail records concerning CONNECT and
DISCONNECT
DBA_AUDIT_STATEMENT Audit trail records concerning grant,
revoke, audit, noaudit and alter system
DBA_AUDIT_TRAIL All audit trail entries
DBA_OBJ_AUDIT_OPTS Auditing options for all tables and views
DBA_PRIV_AUDIT_OPTS Describes current system privileges being
audited across the system and by user
DBA_STMT_AUDIT_OPTS Describes current system auditing options
across the system and by user
USER_AUDIT_OBJECT Audit trail records for statements concerning
objects, specifically: table, cluster, view,
index, sequence, [public] database link,
[public] synonym, procedure, trigger, rollback
segment, tablespace, role, user
USER_AUDIT_SESSION All audit trail records concerning CONNECT and
DISCONNECT
USER_AUDIT_STATEMENT Audit trail records concerning grant, revoke,
audit, noaudit and alter system
USER_AUDIT_TRAIL Audit trail entries relevant to the user
USER_OBJ_AUDIT_OPTS Auditing options for user's own tables and
views
14 rows selected.There were actually 20 rows returned from the query, but 6 of the rows pertain to replication internal auditing, so we eliminated them from the output since they do not pertain to this discussion. (Some minor massaging of the output was also done for readability.)
All the audit information is stored to one table, SYS.AUD$. The view used most often to gain data about the results of audits is the DBA_AUDIT_TRAIL view. Because this view is used most often, we examine it more closely here. We are not presenting the actual SYS.AUD$ table, because you will generally not be dealing with this table directly.
The DBA_AUDIT_TRAIL view contains the following columns:
SQL> DESCRIBE dba_audit_trail Name Null? Type ------------------------------- -------- ---- OS_USERNAME VARCHAR2(255) USERNAME VARCHAR2(30) USERHOST VARCHAR2(128) TERMINAL VARCHAR2(255) TIMESTAMP NOT NULL DATE OWNER VARCHAR2(30) OBJ_NAME VARCHAR2(128) ACTION NOT NULL NUMBER ACTION_NAME VARCHAR2(27) NEW_OWNER VARCHAR2(30) NEW_NAME VARCHAR2(128) OBJ_PRIVILEGE VARCHAR2(16) SYS_PRIVILEGE VARCHAR2(40) ADMIN_OPTION VARCHAR2(1) GRANTEE VARCHAR2(30) AUDIT_OPTION VARCHAR2(40) SES_ACTIONS VARCHAR2(19) LOGOFF_TIME DATE LOGOFF_LREAD NUMBER LOGOFF_PREAD NUMBER LOGOFF_LWRITE NUMBER LOGOFF_DLOCK VARCHAR2(40) COMMENT_TEXT VARCHAR2(4000) SESSIONID NOT NULL NUMBER ENTRYID NOT NULL NUMBER STATEMENTID NOT NULL NUMBER RETURNCODE NOT NULL NUMBER PRIV_USED VARCHAR2(40) OBJECT_LABEL RAW MLSLABEL SESSION_LABEL RAW MLSLABEL
Of the columns within the SYS.AUD$ table (provided the information is meaningful for the auditing action), there will always be information filled in about the user’s name, the session identifier, the terminal identifier, the name of the schema object accessed, the operation that was performed or attempted, the completion code of the operation, the date and timestamp, the system privilege used, and, for Trusted Oracle, the user session label and the schema object accessed label.
For each action being audited, there may be a large volume of information generated. For this reason, you might decide to create a summary table to separate and/or summarize the information of interest from the total volume of information collected.
In order to easily view the audit information you have collected, you may want to create a table of data that summarizes the information of interest. By creating a smaller table and populating it with a summary of information, you accomplish two very important things:
You enable removal of information from the underlying SYS.AUD$ audit table to conserve storage space.
You gain easier access to your trending data.
In other words, if you are interested in collecting the total number of failed logons to your database over a two-week period of time, you might create a table that contains only two columns: the day’s date and the total count of unsuccessful logons for that day. On an automated basis, you could populate the summary table for failed logins and delete the associated rows from the SYS.AUD$ table.
You should be very careful in choosing the columns of information you want to summarize since you may find it difficult to change the summary data later on. Let’s go back to the example we just used with two summary columns for the day’s date and the total count of unsuccessful logins. Suppose you’ve spent several weeks summarizing data and you now delete the day’s values from the SYS.AUD$ table. You may realize later that you really wanted to retain the usernames of the accounts on which the connection attempt had failed in order to evaluate whether there was a pattern to the unsuccessful login attempts. But the data already summarized and deleted would be gone. Thus, your ability to perform trending analysis would be delayed while you attempted to accumulate more data. The current summary table would either need to be modified, or a new summary table would need to be created to fulfill the newly-identified requirement to capture account names. New code would need to be written to populate the new table.
Although changing audit requirements and revising auditing plans is sometimes necessary to help you perform the most effective auditing of a system, the more thought you put into the original plan, the better chance you will have of capturing effective data from the start.
Of the information stored in the audit trail, the data you decide to summarize will vary based on what you were trying to find out by auditing. If you were looking for the number of times a specific user connected to the database, your summary table would probably contain two columns — one for the user’s name and one for the total count of accesses by day. If you are auditing to find out several different pieces of information, you might have one large summary table or several small ones. Either way, the summary table(s) should be kept in a different tablespace from the system tablespace to help avoid fragmentation of the system tablespace. Growth of the summary table(s) should be monitored to ensure that they remain manageable and that you don’t run out of space in the tablespace.
If you choose not to audit anything and want to eliminate the
auditing views from the database system, you can run the script
CATNOAUD.SQL
while connected as sys to
drop the views and synonyms for the auditing metadata areas. The
CATNOAUD.SQL script can be found in the
$ORACLE-HOME/RDBMS/ADMIN directory on most
systems. Why would you want to remove the audit views from a
database?
Two reasons come to mind: