10.3 How Auditing Works By default, from Oracle version 7.X on, the following sequence of events occurs when the database is created: -
CATALOG.SQL is run and calls several other scripts. -
CATAUDIT.SQL is run as one of the scripts called from CATALOG.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. 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 | | AUDIT_ACTIONS | Description table for audit trail action type codes. | | ALL_DEF-AUDIT_OPTS | 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. | | DBA_OBJ_AUDIT_OPTS | 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. | | USER_OBJ_AUDIT_OPTS | 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. | | DBA_STMT_AUDIT_OPTS | This view is only accessible to DBAs. One row is kept for each system auditing option set system-wide, or for a particular user. | | DBA_PRIV_AUDIT_OPTS | One row is kept for each system privilege auditing option set system-wide, or for a particular user. | | DBA_AUDIT_TRAIL | 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. | | USER_AUDIT_TRAIL | 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. | | DBA_AUDIT_SESSION | All audit trail records concerning CONNECT and DISCONNECT, based on DBA_AUDIT_TRAIL. | | USER_AUDIT_SESSION | All audit trail records concerning CONNECT and DISCONNECT, based on USER_AUDIT_TRAIL. | | DBA_AUDIT_STATEMENT | All audit trail records concerning the following statements: GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM. Based on DBA_AUDIT_TRAIL. | | USER_AUDIT_STATEMENT | Same as the DBA version, except that it is based on USER_AUDIT_TRAIL. | | DBA_AUDIT_OBJECT | 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. | | USER_AUDIT_OBJECT | Same as DBA_AUDIT_OBJECT, except that it is based on the USER_AUDIT_TRAIL. | | DBA_AUDIT_EXISTS | 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: 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. 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 10.3.6.1 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. 10.3.6.2 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: 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: |
