091 - 10.3 How Auditing Works


Oracle Security
By William Heney, Marlene Theriault
Table of Contents
Chapter 10.  Developing an Audit Plan

10.3 How Auditing Works

By default, from Oracle version 7.X on, the following sequence of events occurs when the database is created:

  1. CATALOG.SQL is run and calls several other scripts.

  2. CATAUDIT.SQL is run as one of the scripts called from CATALOG.SQL .

  3. The auditing views are created.

  4. A public synonym is created for each of the auditing views.

  5. 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.

10.3.1 The Auditing Views

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)

Auditing View



Description table for audit trail action type codes.


Single-row view indicating the default auditing options remark for newly-created objects. This view has an ALL member only, since the default is system-wide and applies to all accessible objects.


These views indicate what kind of audit trail entries (none, session-level, or access-level) are generated by the success or failure of each possible operation on a table or view (e.g., SELECT, ALTER). All information will be visible to the DBA.


These views indicate what kind of audit trail entries (none, session-level, or access-level) are generated by the success or failure of each possible operation on a table or view (e.g., SELECT, ALTER). Only the user's own information will be visible to him.


This view is only accessible to DBAs. One row is kept for each system auditing option set system-wide, or for a particular user.


One row is kept for each system privilege auditing option set system-wide, or for a particular user.


The raw audit trail of all audit trail records in the system. Some columns are only filled in by certain statements. This view is accessible only to DBAs.


The raw audit trail of all information related to the user or the objects owned by the user. Some columns are only filled in by certain statements. This view is created by selecting from the DBA_AUDIT_TRAIL view and restricting the rows.


All audit trail records concerning CONNECT and DISCONNECT, based on DBA_AUDIT_TRAIL.


All audit trail records concerning CONNECT and DISCONNECT, based on USER_AUDIT_TRAIL.


All audit trail records concerning the following statements: GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM. Based on DBA_AUDIT_TRAIL.


Same as the DBA version, except that it is based on USER_AUDIT_TRAIL.


Audit trail records for statements concerning objects, such as table, cluster, view, index, sequence, [public] database link, [public] synonym, procedure, trigger, rollback segment, tablespace, role, or user. The audit trail records for AUDIT/NOAUDIT and GRANT/REVOKE operations on these objects can be seen through the DBA_AUDIT_STATEMENT view.


Same as DBA_AUDIT_OBJECT, except that it is based on the USER_AUDIT_TRAIL.


Only DBAs can see audit information about objects that do not exist. Lists audit trail entries produced by AUDIT EXISTS/NOT EXISTS. This is all audit trail entries with return codes of 942, 943, 959, 1418, 1432, 1434, 1435, 1534, 1917, 1918, 1919, 2019, 2024, and 2289, and for Trusted Oracle 1, 951, 955, 957, 1430, 1433, 1452, 1471, 1535, 1543, 1758, 1920, 1921, 1922, 2239, 2264, 2266, 2273, 2292, 2297, 2378, 2379, 2382, 4081, 12006, and 12325. This view is accessible to DBAs only.

If a DML statement such as UPDATE is performed and then rolled back, the audit entry may also be removed from the audit table.

10.3.2 The DBA as a Clairvoyant

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.

10.3.3 Available Audit Actions

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.

10.3.4 Auditing Options

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:


The default auditing options and their abbreviations are:



































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.

10.3.5 From the DICTIONARY View

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.)

10.3.6 Views Related to SYS.AUD$

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 What's stored in SYS.AUD$?

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. Creating a summary table

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.

10.3.7 Eliminating the Audit Views

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:

  • You are supporting a system where disk space is at a premium.

  • Performance is a major issue on your system and you want to ensure auditing can't be enabled accidentally .


Oracle Security
Oracle Security Handbook : Implement a Sound Security Plan in Your Oracle Environment
ISBN: 0072133252
EAN: 2147483647
Year: 1998
Pages: 154

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net