Standard Database Auditing

From a native database perspective, auditing comes in four flavors: mandatory, standard, Fine-Grained, and administrator (SYS) auditing.

Mandatory Auditing

The database always records three important things: database startup, database shutdown, and users authenticated with the SYSDBA or SYSOPER roles. For the database startup, the audit record also indicates whether standard auditing has been enabled. This allows one to determine if an administrator has disabled auditing (setting the AUDIT_TRAIL value to none or FALSE) and is now restarting the database.

These audit records have to be stored on the operating system because the database isn’t available (it’s being started or stopped). The actual location varies depending on OS platform—for example, on Windows, the records are written to the Event Logs; on Linux, the audits are generally found in the $ORACLE_HOME/rdbms/audit directory.

Auditing SYS

As of Oracle9i Database Release 2, auditing actions performed by users authenticated as SYSDBA or SYSOPER are also supported. The audit records are again written to OS files. This is important for two reasons. First, these users have the most significant privileges in the database, such as the ability to see and modify all data, change passwords, log in to any schema, and drop schemas. As such, it’s generally advisable to monitor their actions.

Second, they control the database auditing. If they want to disable it, they have the privileges to do so. They also have the privileges to delete the audit records. Therefore, auditing to the database is useless since the user would be able to modify or delete the audit records. When auditing on SYS, it’s important to remember that you shouldn’t allow the database user access to the operating system directories where the audits will be written, or else you will suffer from the same challenge as auditing to the database. To enable audits for the SYS user, you have to set the AUDIT_SYS_OPERATIONS initialization parameter to TRUE:

system@KNOX10g> ALTER SYSTEM SET audit_sys_operations=TRUE     2    SCOPE=SPFILE; System altered.

This change is written to the initialization file (init.ora), and the database has to be rebooted for the change to take effect. Trying to change this parameter at runtime results in the error: “ORA-02095: specified initialization parameter cannot be modified”. This is a security feature. If the database could be modified at runtime without booting, the SYS user could turn off auditing, do something bad, and then re-enable auditing. By forcing a reboot, you know that you’ll have captured both the disabling of the auditing as well as the reboot.

After restarting the database, all successful actions performed by the SYS user will be audited. To illustrate this point, set the parameter to TRUE. Then authenticate as SYSDBA and change the SYS user’s password. In Figure 8-1, the Microsoft Event Viewer shows how the event has been audited.

Figure 8-1: Auditing SYS actions ensures accountability for the most privileged database users.

Enabling Standard Auditing

When most people think of database auditing, they think of standard auditing. Before you can successfully utilize the database standard auditing, you have to enable it by setting the AUDIT_TRAIL initialization parmeter. Note this is a distinctly separate parameter from AUDIT_SYS_OPERATIONS, which just audits the actions of SYS. The AUDIT_TRAIL parameter allows standard database auditing to occur. By default, this parameter is also set to FALSE.

To enable auditing, you can set the AUDIT_TRAIL parameter to several values. Setting the value to “OS” will enable the database audit records to be written to the operating system (OS). As with auditing on SYS, this is a good idea if you’re concerned with someone modifying or deleting the audit records that might otherwise be contained within the database. However, auditing to the OS can make it difficult to run reports because the data is in a text file.

To enable auditing for records stored in the database (records are stored in the SYS.AUD$ table), you have to set the AUDIT_TRAIL parameter to either “DB” or to “TRUE”. New in Oracle Database 10g is the ability to set auditing to “DB_EXTENDED”. DB_EXTENDED also enables database auditing but captures additional information in the audit record. The SQL text and any potential bind variables that actually caused the audit to occur, as well as some other useful information, will be captured. You see this in the following example:

system@KNOX10g> ALTER SYSTEM SET audit_trail=db_extended SCOPE=SPFILE; System altered.

For the same security precautions just discussed, the database has to be rebooted before the changes will take effect. As a reboot of the database is required, it’s best to set the AUDIT_TRAIL parameter and reboot so once you decide what you really want to audit, you’ll be able to do it without needing to restart the database. A security best practice is to set the AUDIT_TRAIL value to DB_EXTENDED on database creation. By default the value is not set.

Auditing By User, Privilege, and Object

The database allows for a very robust auditing environment:

• You can audit on objects such as tables and views. For example, every time someone accesses the APP.USER_DATA table an audit will be recorded.

• You can audit procedure executions.

• You can audit when someone exercises a system privilege, such as disabling a trigger or using the SELECT ANY TABLE privilege.

• You can restrict your audits to specific users.

• You can audit for successful actions, unsuccessful actions, or both.

• With all of the preceding, you can audit every time someone performs the action, or audit only once per session regardless of the number of times they perform the action within the session.

The point is that this extensive capability allows you to focus your auditing to precisely the things that are of interest. This fidelity is what makes the auditing a real asset.

Targeted Auditing

As mentioned previously, auditing can only be done successfully once you have a clear idea of why you are auditing and what you are auditing. If you audit too much, you could suffer performance hits, and more importantly, generate excessive and potentially useless records. Culling through thousands of audit records is generally ineffective, especially when most of the audits were done for users that were performing the tasks they were assigned. The greater the fidelity in the auditing capability, the better your chances are of focusing the auditing to just the right level on just the right things.

Auditing can help you identify gaps in your security policies. From the audit records, you may notice that authorized users don’t have the necessary privileges, or on the other extreme that they are over privileged. You may even identify contradictions within your security policy. For example, it’s typical to have one requirement to support database backups that allows a user to gain access to the entire database. Another requirement based on “need to know” may say that users are only allowed access to certain records based on their affiliation. These two requirements may conflict.

This highlights an important point to auditing. In cases where administrators require super privileges, auditing may be the only thing you can do to ensure privileges are not abused and misused. This is exactly why the database supports audits for SYS.

Auditing Best Practices

In addition to targeted auditing, there are a couple of occasions worth constant consideration.

Audit Connections

Auditing when users log on and log off of the database is a good thing to do. It’s a matter of common sense. You should also know who has been in your database(s). If something bad happens to the database and you know about when the bad thing happened, it’s invaluable to be able to find out who was working in the database when the incident happened. Acknowledging the fact that most database outages are human induced and not a software or hardware failure, auditing connections will help narrow the field of possible suspects.

However, there are two things that have to be done to ensure this type of auditing is effective. Most importantly, you have to be able to distinguish between users. Auditing on applications that conceal the user’s identity may be pointless—after all, you really can’t distinguish the “who,” only that it was a person running the application. For applications with connection pools, this type of auditing may be ineffective.

Second, and this applies to all auditing, you have to do something with the records. With connections, this point is particularly acute. Because the users will be connecting all the time, unless you have a tremendous amount of disk space, you’ll have to archive and delete the old records.

To enable auditing for logons and logoffs, simply audit the user’s connection. Again, you can do this at a user level or for all users. First, check to ensure that auditing is enabled, and then audit all connections for all users:

system@KNOX10g> show parameter audit_trail     NAME                                 TYPE        VALUE   ------------------------------------ ----------- ------------   audit_trail                          string      DB_EXTENDED system@KNOX10g> AUDIT SESSION; Audit succeeded.

The check for the value of the AUDIT_TRAIL parameter is a good habit. This is because the “audit session;” statement will succeed even if the database auditing is disabled (AUDIT_TRAIL = FALSE). You might think you are auditing, but in reality you aren’t.

To complete our example, log on and log off as the user SCOTT (not shown). Upon logon, the database writes an entry to the audit trail:

system@KNOX10g> BEGIN     2    FOR rec IN     3      (SELECT username,   4              action_name,   5              TO_CHAR (TIMESTAMP, 'Mon-DD HH24:MI')   6                                              LOGON,   7              TO_CHAR (logoff_time,   8                       'Mon-DD HH24:MI') LOGOFF,   9              priv_used,  10              comment_text    11         FROM dba_audit_trail)  12    LOOP    13      DBMS_OUTPUT.put_line (   'User:      '  14                            || rec.username);  15      DBMS_OUTPUT.put_line (   'Action:    '  16                            || rec.action_name);  17      DBMS_OUTPUT.put_line (   'Logon:     '  18                            || rec.LOGON);  19      DBMS_OUTPUT.put_line (   'Logoff:    '  20                            || rec.LOGOFF);  21      DBMS_OUTPUT.put_line (   'Priv Used: '  22                            || rec.priv_used);  23      DBMS_OUTPUT.put_line (   'Comments:  '  24                            || rec.comment_text);  25      DBMS_OUTPUT.put_line    26        ('--------------- End of Record ---------------');  27    END LOOP;  28  END;  29  /   User:      SCOTT   Action:    LOGON   Logon:     Mar-24 15:50   Logoff: Priv Used: CREATE SESSION   Comments:  Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.100)(PORT=3445)) --------------- End of Record ---------------     PL/SQL procedure successfully completed. 

Note the useful information the database automatically places in the COMMENT_TEXT field. The logoff time is NULL because the user in this example is still logged in. Once the user has disconnected, the database updates the audit entry. The ACTION is changed to LOGOFF, and the actual logoff time is indicated as well:

system@KNOX10g> /   User:      SCOTT   Action:    LOGOFF   Logon:     Mar-24 15:50   Logoff:    Mar-24 15:53 Priv Used: CREATE SESSION   Comments:  Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.100)(PORT=3445)) --------------- End of Record ---------------     PL/SQL procedure successfully completed.

This simple ability to track who was connected and when is valuable and may be the only records you have prior to something happening.

Audit Whenever Unsuccessful

Auditing unsuccessful actions represents the database’s ability to detect the burglar rattling the windows and checking the doors. You should know when someone is banging on a locked door. This is especially important with truly sensitive data or with data that can be used to derive sensitive information, such as privacy-related data, encryption keys, passwords, and user preferences.

Let’s show this in action. You’ll enable auditing on a table, but your auditing policy will only audit when users try to access the object but don’t have the proper privileges. This may be an indication that the user is trying to gain unauthorized access to our sensitive data.

This will audit by access and not by session. This means for every SQL statement that touches the table (or rather, tries to touch the table), an audit record will be generated. If you audited by session, there would be only one record for each session regardless of the number of actual SQL statements that accessed the table.

sec_mgr@KNOX10g> CREATE TABLE t AS SELECT * FROM DUAL; Table created. sec_mgr@KNOX10g> -- audit selects on T for failures sec_mgr@KNOX10g> AUDIT SELECT ON t   2    BY ACCESS WHENEVER NOT SUCCESSFUL; Audit succeeded.

When an unauthorized user tries to access this table the action will be audited.

scott@KNOX10g> SELECT *   2    FROM sec_mgr.t;   FROM sec_mgr.t                * ERROR at line 2: ORA-00942: table or view does not exist

Checking the audit trail, you see this failed attempt to access the table:

sec_mgr@KNOX10g> BEGIN     2    FOR rec IN (SELECT audit_type,   3                       db_user,   4                       object_schema,   5                       object_name,   6                       extended_timestamp,   7                       sql_text    8                  FROM dba_common_audit_trail)   9    LOOP    10      DBMS_OUTPUT.put_line (   'Audit Type: '  11                            || rec.audit_type);  12      DBMS_OUTPUT.put_line (   'Who:        '  13                            || rec.db_user);  14      DBMS_OUTPUT.put_line (   'What:       '  15                            || rec.object_schema   16                            || '.'  17                            || rec.object_name);  18      DBMS_OUTPUT.put_line (   'When:       '  19                            || rec.extended_timestamp);  20      DBMS_OUTPUT.put_line (   'How:        '  21                            || rec.sql_text);  22      DBMS_OUTPUT.put_line   23        ('--------------- End of Record ---------------');  24    END LOOP;  25  END;  26  /   Audit Type: Standard Audit   Who:        SCOTT   What:       SEC_MGR.T   When:       24-MAR-04 04.11.10.350000 PM -05:00   How:        SELECT * FROM sec_mgr.t   --------------- End of Record ---------------

The audit type of “Standard Audit” is used because this new view integrates both the standard audit records and fine-grained audit records.

Tracking Database Use

You can also use auditing to check or verify how the database is being used. For example, you may wonder whether a table, schema, or procedures are still being used. Enabling auditing will indicate if the object(s) is still in use.

Also recall that removing unused schemas is a security best practice. Before you drop the schema, it’s best to audit access to objects or connections to the schema to determine if the schema really is unused.

Determining the Audit Status

Evaluating the current audit status is important especially when performance becomes questionable. It can also be useful for proving compliance with a security policy. When you want to inspect the audit status on your objects, you can query the DBA_OBJ_AUDIT_OPTS or the USER_OBJ_AUDIT_OPTS view. The views show the audit status of every object even if auditing isn’t enabled. You should therefore form your query to target the specific object or a specific statement of interest:

sec_mgr@KNOX10g> COL "select option" format a13 sec_mgr@KNOX10g> SELECT sel "select option"   2    FROM user_obj_audit_opts   3   WHERE object_name = 'T'; select option ------------- -/A

The format is a bit cryptic, but it’s easy to use once you know the code. The USER_OBJ_AUDIT_OPTS view lists all the options that can be performed on an object. In this query, you’re just looking at the select option. There are two values represented for each option. The first field, represented by a hyphen in the above output, indicates if auditing should occur when the user is successful in performing the action. The second field, an “A” in the output above, indicates whether auditing will occur for unsuccessful actions. A hyphen or a blank means no auditing will occur. An “A” means auditing will occur for every access, and an “S” means auditing will occur once for each session.

If you enable auditing by session on the table, you can verify the previous rules:

 sec_mgr@KNOX10g> AUDIT SELECT ON t BY SESSION WHENEVER SUCCESSFUL; Audit succeeded. sec_mgr@KNOX10g> SELECT sel "select option"   2    FROM user_obj_audit_opts   3   WHERE object_name = 'T'; select option ------------- S/A

The DBA_STMT_AUDIT_OPTS view shows system wide auditing. The auditing that was enabled to track user logons can be viewed by this query:

sec_mgr@KNOX10g> COL user_name format a10 sec_mgr@KNOX10g> COL proxy_name format a10 sec_mgr@KNOX10g> COL audit_option format a20 sec_mgr@KNOX10g> SELECT * FROM dba_stmt_audit_opts; USER_NAME  PROXY_NAME AUDIT_OPTION         SUCCESS    FAILURE ---------- ---------- -------------------- ---------- ----------                       CREATE SESSION       BY ACCESS  BY ACCESS

The last essential view is DBA_PRIV_AUDIT_OPTS, which allows you to check the auditing status of system privileges. For example, if you enable auditing for users exercising the SELECT ANY TABLE privilege, you can check the status as follows:

sec_mgr@KNOX10g> -- Audit the SELECT ANY TABLE privilege sec_mgr@KNOX10g> AUDIT SELECT ANY TABLE; Audit succeeded. sec_mgr@KNOX10g> COL privilege format a20 sec_mgr@KNOX10g> -- Show privilege auditing sec_mgr@KNOX10g> SELECT * FROM dba_priv_audit_opts; USER_NAME  PROXY_NAME PRIVILEGE            SUCCESS    FAILURE ---------- ---------- -------------------- ---------- ----------                       SELECT ANY TABLE     BY SESSION BY SESSION

Extending the Audit Data with Client Identifiers

A similar statement made earlier regarding preventive security measures also holds true with auditing: The more information the database has, the better the security. With auditing, you can augment the audit trails using Client Identifiers. If the user’s identity is already known to the database, the Client Identifier should be seeded with other useful information such as the user’s IP address, the application they are running, how they authenticated, and anything and everything that can be used to describe the user’s operating context. Please see the section “Securing the Client Identifier” in Chapter 6.

As illustrated previously, logon triggers can be used to effectively set meaningful information about the user in the Client Identifier.

The code presented earlier can be modified to set the user’s IP address and the program they are using and storing the result in the Client Identifier. The module name can be spoofed by the user but it does often indicate correctly the name of the application being used such as SQL*Plus, MS Excel, or T.O.A.D. Note that changing the program’s executable name changes the program and not the module:

sec_mgr@KNOX10g> CREATE OR REPLACE TRIGGER set_default_client_info     2    AFTER LOGON ON DATABASE     3  DECLARE     4    l_module  v$session.module%TYPE;   5  BEGIN     6    SELECT UPPER (module)   7      INTO l_module      8      FROM v$process a, v$session b     9     WHERE a.addr = b.paddr    10       AND b.audsid = USERENV ('sessionid');  11    DBMS_SESSION.set_identifier    12                        (   SYS_CONTEXT    13                                       ('userenv',  14                                        'ip_address')  15                         || ':'  16                         || l_module);  17  END;  18  /     Trigger created.

This code could be extended to capture the authentication mode, the network protocol, or any other valuable piece of information you want to extract from the user session environment.

Now enable auditing on the SCOTT.EMP object. By not specifying when you want to audit, you’ll be auditing for both successful and unsuccessful actions as well as auditing by access so you can capture a record for every SQL statement:

sec_mgr@KNOX10g> AUDIT SELECT ON scott.emp BY ACCESS; Audit succeeded.

For testing, delete the existing records from the audit table:

sys@KNOX10g> DELETE FROM aud$;

Next log on as SYSTEM and issue a query on the table:

system@KNOX10g> SELECT ename, sal     2    FROM scott.emp    3   WHERE sal < (SELECT sal    4                  FROM scott.emp     5                 WHERE ename = 'SCOTT')  6     AND deptno = (SELECT deptno     7                     FROM scott.emp     8                    WHERE ename = 'SCOTT'); ENAME             SAL   ---------- ----------   SMITH             800   JONES            2975   ADAMS            1100

Now, as SCOTT, issue two more queries:

scott@KNOX10g> SELECT SUM (sal) FROM scott.emp;   SUM(SAL) ----------            29025     scott@KNOX10g> SELECT ename FROM scott.emp     2   WHERE 1 = 2; no rows selected 

Checking the audit records, you see there are five records in the audit trail: the two from SCOTT and three that were generated from the single statement issued by SYSTEM. You’ll create a procedure to group the records together so you actually see only three results. This ability to correlate records is also a new capability enabled by the DB_EXTENDED value of the AUDIT_TRAIL parameter.

sec_mgr@KNOX10g> SELECT COUNT (*)   2    FROM dba_common_audit_trail;   COUNT(*) ----------                 5     sec_mgr@KNOX10g> CREATE OR REPLACE PROCEDURE show_aud     2  AS     3  BEGIN     4    FOR rec IN (SELECT   db_user,   5                         client_id,   6                         object_schema,   7                         object_name,   8                         extended_timestamp,   9                         sql_text,  10                         statementid    11                    FROM dba_common_audit_trail    12                GROUP BY db_user,  13                         statementid,  14                         sql_text,  15                         object_schema,  16                         object_name,  17                         client_id,  18                         extended_timestamp    19                ORDER BY extended_timestamp ASC)  20    LOOP   21      DBMS_OUTPUT.put_line ('Who:  ' || rec.db_user);  22      DBMS_OUTPUT.put_line (   'What: '  23                            || rec.object_schema    24                            || '.'  25                            || rec.object_name);  26      DBMS_OUTPUT.put_line (   'Where: '  27                            || rec.client_id);  28      DBMS_OUTPUT.put_line    29                   (   'When:  '  30                    || TO_CHAR    31                            (rec.extended_timestamp,  32                             'Mon-DD HH24:MI'));  33      DBMS_OUTPUT.put_line ('How:   '  34                            || rec.sql_text);  35      DBMS_OUTPUT.put_line    36        ('-------------- End of Record --------------');  37    END LOOP;  38  END;  39  /     Procedure created. sec_mgr@KNOX10g> EXEC show_aud   Who:  SCOTT   What: SCOTT.EMP   Where: 192.168.0.100:SQLPLUS.EXE   When:  Mar-24 16:28 How:   SELECT ename FROM scott.emp   WHERE 1 = 2 -------------- End of Record --------------   Who:  SCOTT   What: SCOTT.EMP   Where: 192.168.0.100   When:  Mar-24 16:28 How:   SELECT SUM (sal) FROM scott.emp   -------------- End of Record --------------   Who:  SYSTEM  What: SCOTT.EMP   Where: 192.168.0.100:SQLPLUS.EXE   When:  Mar-24 16:28 How:   SELECT ename, sal     FROM scott.emp    WHERE sal < (SELECT sal                   FROM scott.emp    WHERE ename = 'SCOTT')    AND deptno = (SELECT deptno                      FROM scott.emp    WHERE ename = 'SCOTT') -------------- End of Record --------------     PL/SQL procedure successfully completed.

The correlation of records was done by the grouping by statementIDs. For other DML types, you also can correlate by transactionID. This will link together all the inserts, updates, and deletes that were part of the same transaction, which allows us to support transaction level auditing in addition to the statement (task) level auditing.

Tip 

To disable auditing (useful if you are following along), you have to issue the NOAUDIT command: NOAUDIT SELECT ON scott.emp;.

Peformance Test

No matter what you read, auditing has to be (at least theoretically) slower than not auditing because you are doing more work. A quick test should always be performed in coordination with your auditing policy. In the preceding example, you can run a loop of queries on the table with and without auditing enabled to determine how much the auditing operations will add to your execution time.

Here are the results—your mileage may vary. First, without auditing, query a table 100,000 times in just over six seconds:

scott@KNOX10g> BEGIN   2    FOR rec IN 1 .. 100000   3    LOOP   4      FOR irec IN (SELECT ename   5                     FROM scott.emp)   6      LOOP   7        NULL;   8      END LOOP;   9    END LOOP;  10  END;  11  / PL/SQL procedure successfully completed. Elapsed: 00:00:06.33

Auditing can be disabled at the system level via the AUDIT_TRAIL parameter and at the object level via audit policies. The time to execute the preceding was approximately the same, regardless of how the auditing was disabled.

Next, the same 100,000 queries are executed. When auditing by access, this will insert 100,000 records into the audit table. This naturally is where the slow-down occurs:

scott@KNOX10g> / PL/SQL procedure successfully completed. Elapsed: 00:23:06.22

You might expect something like this. There are a lot of records being inserted into a table. The point is that there is no magic with auditing. That insert process is going to take time.

There are a couple of important conclusions that should be obvious from this. First, this test is contrived and doesn’t represent any real-world application. It was done intentionally to get the drastic results seen. You have to test based on your true expected load and access methods. Querying in the loop shows how fast the database can process serial requests from the same user. It doesn’t indicate how it will work concurrently with mulitple users.

Second, this shows how careful consideration has to be given to auditing. One hundred thousand audit records are a lot of records. This might not be the right level of auditing for our specific data usage. You might decide to change the audit from “access” to “sesssion,” or you might want to audit only on failed access attempts. Both would limit the number of records and increase performance. The answer is dependent on why you are auditing and what you hope to get from the audit records. One thing the preceding example illustrates is that SCOTT is querying this table many times in a short period of time using the same SQL. Perhaps he is executing a program that is (accidentally) continuously looping.

Caveats

Standard auditing has a few drawbacks. The auditing fidelity, while very good to a point, may not be adequate for some requirements. For example, if you want to capture audits on specific columns or when specific conditions arise, then standard auditing would be ineffective.

Another issue is one that is arguably not part of the auditing domain but nonetheless still frequently brought up. The audits don’t indicate what the user actually saw as a result of the query. None of the implementations of auditing in this chapter have that ability by themselves. However, Oracle is aware of this, and the audit records do capture the time of the audit and the SCN (system change number). Either of these values allows an administrator to execute the Flashback ability of the database. Once the Flashback has occurred, you can rerun the query the user ran logged in as the user and see what the user saw when they issued the query. Note if you issue an ALTER SESSION SET CURRENT_SCHEMA=<AuditedUser> you will only be resolving objects as the user; their privileges and context values aren’t included, so the results from the Flashback query are very likely to be erroneous.

There are limitations to the longevity of the Flashback data. An alternative for those that are concerned with this issue is to use an Oracle solution called Selective Audit. Selective Audit is an Oracle Consulting supported solution that puts Oracle’s Standard Auditing on steroids. Search on the term “Selective Audit” on www.oracle.com for more information.