10.6 Types of Auditing There are several different forms of auditing that can be enabled. Each form covers a different area of interest within the database: The general command syntax used to enable any form of auditing is shown in Figure 10.1. Figure 10.1. General command syntax  10.6.1 Statement-Level Auditing Statement-level auditing falls into two categories: Data Definition Language (DDL) statements and Data Manipulation Language (DML) statements. This type of auditing can be very broad or very specific. The statement-level audits are based on the type of SQL statement presented. An example of a statement-level audit would be to audit any action performed on tables, such as CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE, etc. Another example of statement-level auditing would be to audit specific actions of one user on a session-by-session basis. 10.6.1.1 Enabling and viewing statement-level auditing To view what statement-level auditing has been enabled in a database, you can issue the statement: SQL> AUDIT SESSION BY mary; Audit succeeded. SQL> SELECT user_name, audit_option, success, failure 2 FROM sys.dba_stmt_audit_opts; USER_NAME AUDIT_OPTION SUCCESS FAILURE ----------- ---------------------------------------- ---------- ---------- MARY CREATE SESSION BY ACCESS BY ACCESS In this example, we see that mary is being audited "by session." Whenever mary creates or fails to create a session, an entry will be placed in the audit trail. 10.6.1.2 Connect and disconnect auditing One major area of concern in any computer system is whether someone is attempting to gain access to the database without authorization to do so. As we've mentioned previously, when an Oracle database is created, the default password for system is MANAGER and the default password for sys is CHANGE_ON_INSTALL . On some databases, default demonstration areas are created automatically with easily guessed passwords and very high privileges. An unknowledgeable DBA may neglect to change default passwords or not realize that the demonstration accounts can and should be deleted from a production system. Alternatively, a user may attempt to "guess" his way into a system. Auditing is a way of helping you detect when someone is trying to break into your database. If a policy has been established that uses a very specific username convention and that convention is widely known, guessing usernames may be a very easy task. For example, suppose your company's policy is to have a six-character username using a person's first initial and as many characters of the last name as will yield a total of six characters . The employee whose name is Mary Janes will have the username mjanes . Any person who knows an employee well enough to know personal facts about that employee, and who knows your username convention, may be able to guess his way into your system based on this knowledge. They'll know Mary's username immediately. And if they know that Mary Janes loves fishing , they might try to guess her password by using names of fish or types of lures. The difficulty of the task of hacking into a system is reduced when the username is already known. If you, as the DBA, have enabled auditing to watch for failures of a user to connect to the database, the hacker's attempts to guess the correct password for an account will be recorded as unsuccessful logons . You will be able to identify an unauthorized person's attempts to gain access to your system. In looking through the list of command types that can be audited, note that command number 100 is "logon" and command 101 is " logoff ." The action of attempting to connect to the database or disconnect from it can be audited by collecting data on the values "connect" and "disconnect." If you wanted to capture the number of times the database was accessed but the attempt to log on failed, the command you would issue would be: SQL> AUDIT CONNECT WHENEVER UNSUCCESSFUL; Your summary table for this audit might contain a count, by 24- hour period, of all of the failed attempts to connect to your database. A low number of failures to connect would be considered normal since not everyone successfully types his password every time they connect to a system. A higher than normal number of failures might indicate that someone was trying to break into the database by guessing account names and passwords. 10.6.2 Privilege Auditing Recently, there was a news article about some teenagers who had successfully broken into a government system and managed to give themselves privileges on that system. Fortunately for the government agency involved, the teens did not do any overt damage to the system. They just wanted to prove they could compromise the system. There have also been numerous articles over time about employees who, while doing development work with higher than normal privileges enabled, have left "back doors" into systems so that they could re-enter the system after it had been "secured." One such story involved a contractor who, when he did not receive payment for his services after repeatedly billing the company, re-entered the company's system, using the privileged account which he had left behind, and deleted a substantial amount of vital data. According to news reports about the incident, the loss of data to that company and the amount of time it will take them to recover the data far outweighs the amount of salary owed to the contractor. Criminal charges have been filed against the contractor, but that won't replace the data that was lost. How could this loss have been prevented? If you take the initiative to audit who has been granted specific privileges on your system, a developer will have a much harder time building "hidden" access to your database. The audit plan you create should include both a definition of what privileges within the database will be audited and a statement of the interval at which the database will be reviewed to determine exactly which privileges have been assigned to which users and whether anyone has changed those privileges. 10.6.2.1 Enabling audit by privileges You can enable auditing on specific privileges via the AUDIT command. The monitoring of who holds which privileges is a manual or automated task which is coded by creating SQL-generated SQL code something like the following: SELECT grantee, privilege FROM dba_sys_privs WHERE GRANTEE NOT IN ('SYS','SYSTEM','CONNECT','RESOURCE','DBA','MDSYS', 'RECOVERY_CATALOG_OWNER','IMP_FULL_DATABASE', 'EXP_FULL_DATABASE') ORDER BY grantee / In this SELECT statement, the search has been narrowed on the users that will be returned. The WHERE clause has eliminated some usernames that are already known privileged users in the system. On a Windows NT database running Oracle8 version 8.0.4, the query produced the following results: GRANTEE PRIVILEGE ------------------------------ ---------------------------------------- DBSNMP CREATE PUBLIC SYNONYM DBSNMP UNLIMITED TABLESPACE DEMO UNLIMITED TABLESPACE ORACLE UNLIMITED TABLESPACE ORACLE_SECURITY_SERVICE CREATE SESSION ORACLE_SECURITY_SERVICE CREATE SYNONYM ORACLE_SECURITY_SERVICE_ADMIN CREATE ANY SYNONYM ORACLE_SECURITY_SERVICE_ADMIN CREATE PROCEDURE ORACLE_SECURITY_SERVICE_ADMIN CREATE SEQUENCE ORACLE_SECURITY_SERVICE_ADMIN CREATE SESSION ORACLE_SECURITY_SERVICE_ADMIN CREATE TABLE ORACLE_SECURITY_SERVICE_ADMIN CREATE TRIGGER ORACLE_SECURITY_SERVICE_ADMIN CREATE VIEW ORACLE_SECURITY_SERVICE_ADMIN DROP ANY SYNONYM ORDSYS CREATE LIBRARY ORDSYS GRANT ANY PRIVILEGE ORDSYS UNLIMITED TABLESPACE RECOV UNLIMITED TABLESPACE ROGER UNLIMITED TABLESPACE SCOTT ALTER SESSION SCOTT CREATE TABLE SCOTT UNLIMITED TABLESPACE SNMPAGENT ANALYZE ANY 23 rows selected. To enable auditing of a system privilege, identify the privilege, then issue the command to audit that privilege. The basic syntax used to enable auditing of a system privilege was shown earlier under "Types of Auditing." The command syntax shown below is presented in a slightly different form so we can further examine this code: AUDIT <statement_opt or system_priv> BY <user> BY <SESSION or ACCESS> WHENEVER <SUCCESSFUL or UNSUCCESSFUL>; Only the AUDIT <statement_opt or system_priv> portion is required. The other clauses: BY user BY SESSION BY ACCESS WHENEVER SUCCESSFUL WHENEVER UNSUCCESSFUL are optional and enable the audit to be performed in a narrower fashion. If you are concerned about who might be creating tables in the database, you want to audit the use of the CREATE TABLE command as follows : SQL> AUDIT create table 2 WHENEVER SUCCESSFUL; Audit succeeded. After the general privilege for a period of time (i.e., all occurrences of "create table"), you might use a narrower audit scope. For example, you might issue a command like: SQL> AUDIT create table 2 BY mary 3 BY SESSION 4 WHENEVER SUCCESSFUL; to capture only the tables mary created. 10.6.3 Object-Level Auditing Auditing can also be used to aid in tuning a database. An easy way to determine how many times a table was accessed is to enable general auditing on all tables for a brief period of time and then generate a report showing the number of times each table was accessed. You would quickly be able to determine which tables were being used most heavily. 10.6.3.1 Enabling audit by object To audit all table accesses for all tables, first issue the statement: AUDIT select table, update table, delete table; The command, in this form, tells the audit utility to audit all SELECT, UPDATE, and DELETE actions on all tables. If you want to audit only the SELECT actions on all tables, issue the statement: AUDIT select table; The summary table for the command to audit all SELECT, INSERT, and DELETE accesses includes the sum of the table accesses by date. A narrower audit might be tied to how many times a specific user accessed tables. Of particular interest might be an audit of how many tables were deleted from the system. 10.6.3.2 Capturing "before" data As we mentioned earlier, when table auditing is enabled, the entire table is audited. You can audit for inserts to a table, updates to a table, deletions of rows in a table, or the viewing of information contained in the table. Auditing individual columns of a table becomes more problematic , and auditing specific data changes becomes very difficult. In the next chapter, we will present an audit trail application, written by Christopher Hamilton, that will show you a way to audit individual columns of a table. In some applications, the ability to capture pieces of data before any changes have occurred may be of interest. Let's say you have an application from which you can change employee vacation and sick leave accrual. If 100 employees' amounts of vacation available were changed in error, either intentionally or accidentally , having the values that originally appeared in the "vacation available" column could help you correct the error more rapidly without having to perform extra computations . The auditing capabilities delivered with the Oracle RDBMS do not include an easy way to accomplish tracking the "before image" appearance of the data. However, there are ways to accomplish this task. One effective approach is to create a separate audit table for the area of interest and create a "before update" trigger to capture the appearance of the data prior to allowing the change to occur. For example, let's say you have a table with many columns of employee information. One of these columns contains salary data, which should not change very often. Company policy states that no employee should have access to modify salary information unless that employee is a member of the timekeeping_supervisor group . In our payroll application, we want to be able to track exactly who has accessed the employee table and made any modifications to the salary column. We want to capture not only the value of the salary before the change occurred but who made the change and on what date. We have created views of the commonly- viewed columns of the employee table and have ensured that no direct access to the table can be accomplished unless the salary data is to be changed. Enabling auditing to track updates on the table will show us any changes to the table but won't tell us what the salary column looked like prior to a change being made. A new table, sal_chg, could be created with five columns: old_salary, new_salary, user_changed, mod_user, and mod_date. A "before update" trigger could be created, which would fire each time a salary value was modified. The trigger would insert the values for the current salary, the new salary value, the employee whose salary was being changed, the employee who was making the change, and the date on which the change was made. In this way, if a salary was changed whether maliciously or in error a complete record of the transaction would be captured and an audit path enabled. The audit trail application shown in the next chapter will show you more fully how to implement this form of auditing. Although implementing this approach is easy, use it with caution because it may have an impact on both data storage and performance. Each time a trigger fires, there is a minor amount of time used to write the information to the secondary table. If many tables are audited in this manner, you may observe a slowing down of the entire system. As with any other form of auditing, use this approach sparingly and with great forethought and care. 10.6.3.3 Capturing "after" data As with capturing "before" values of interest, capturing modifications to data after they have occurred is difficult using normal Oracle auditing features. The fact that the data is present and observable does not give us any ability to tell exactly what progression of changes have occurred against the data. There is an internal time-stamp Oracle maintains on each row of data in the database, but not on each individual value within the row. If it is critical to know exactly what values have changed, when, and by whom, the use of secondary tables and triggers may, again, provide a possible solution. 10.6.4 Auditing Shortcuts Oracle provides the ability to audit groups of privileges by using the shortcuts CONNECT, RESOURCE, and DBA (the Oracle-supplied default role values), described in Chapter 5. If you need to audit the group of privileges associated with one of the default roles, you will find it easier to enable auditing on CONNECT than on each individual privilege within this role. Rather than enabling auditing on CREATE SESSION, you could enable auditing on CONNECT. If you enable auditing on RESOURCE, the privileges that would be audited are: - ALTER SESSION
- CREATE CLUSTER
- CREATE DATABASE LINK
- CREATE PROCEDURE
- CREATE ROLLBACK SEGMENT
- CREATE SEQUENCE
- CREATE SYNONYM
- CREATE TABLE
- CREATE TABLESPACE
- CREATE VIEW
If you enable auditing on DBA, the privileges associated with "SYSTEM GRANT" and the following privileges would be included: - AUDIT SYSTEM
- CREATE PUBLIC DATABASE LINK
- CREATE PUBLIC SYNONYM
- CREATE ROLE
- CREATE USER
There are also shortcuts to audit "ALL" and "ALL PRIVILEGES." However, the overhead of enabling auditing on privileges by using these shortcuts may be high because of the volume of audit information you will be gathering in the SYS$AUD table. Give careful consideration before you enable auditing using any of these shortcuts, and be sure you have enough space in the tablespace in which your SYS$AUD table is stored. | 
