Audit Methods

Auditing takes many forms in today’s applications; this section explores popular ways and the benefits and drawbacks of each. It’ll discuss application server logs, application auditing, and trigger auditing, and then will conclude with Oracle’s standard auditing and fine-grained auditing.

Note that these auditing techniques aren’t mutually exclusive. They can and should, as necessary, be combined to build complementary layers of defense within the auditing realm.

You will see that each possesses certain advantages and disadvantages. A composite of several auditing techniques will almost surely be the configuration you’ll need.

Application Server Logs

Application server access logs and all associated log files for the application server and web server are often considered a basic form of auditing. These files vary in the amount of information they contain. In the general sense, they will list the resources that have been accessed, when and how the access occurred, and the result by way of a status code—for example, success, failure, and unknown.

The logs are very useful. The records contained in the log files are often direct indicators of the actions the user performed. For example, an update posted from a web page would have a distinct URL signature. As such, the user (or rather the user’s IP address) can be audited as having invoked some program.

Application server logs are very useful in determining suspicious behavior. Denial of service (DoS) attacks may be evident. Many administrators actually use the logs to track a user’s behavior as they navigate a website. This is similar to studying the shopping patterns of customers in department stores.

The challenge with using the application log files is that the information is indirect. It’s only useful when combined with other data that links IP addresses to users and the URLs with actual programs. For this reason, application auditing is usually performed in addition to gathering server log files because it can directly audit who is acting on what.

Application Auditing

One of the most frequently used auditing techniques is application auditing, which is the built-in auditing services that are sometimes an actual part of a larger application. Regardless of the implementation language, application auditing is a natural to use, because it can meet most auditing requirements. It can achieve this lofty goal because the auditing is manually programmed into the application. As such, it’s considered as extensible as the application and the developer’s ability will allow.

Many people are quite familiar with application auditing. This technique is often seen when the developers don’t understand or can’t take advantage of the database auditing. Application auditing may also be the choice when the application wishes to remain database agnostic.

As users perform actions, the application code selectively audits. Various aspects of auditing are generally seen. User logins, data manipulations, and administration tasks can be easily audited. In mature applications, the auditing has been implemented as a service. The business objects within the application call different auditing services and different times to record different actions.

Application Audit Example

Consider an example procedure implemented in PL/SQL. The program could be invoked from an application running either within or outside the database. It may even be called from an application written in another language running outside the database.

This program will be explicitly called by the application at the appropriate time, which will vary from application to application. The example will invoke this auditing when the user performs an update to the SAL column of our table. First, create a copy of the SCOTT.EMP table:

scott@KNOX10g> CREATE TABLE emp_copy AS SELECT * FROM emp; Table created.

Next, create the audit table, which is intended to serve as the audit table for all data manipulation on the EMP_COPY table:

scott@KNOX10g> CREATE TABLE aud_emp (   2    username    VARCHAR2(30),   3    action      VARCHAR2(6),   4    empno       NUMBER(4),   5    column_name VARCHAR2(255),   6    call_stack  VARCHAR2(4000),   7    client_id   VARCHAR2(255),   8    old_value   VARCHAR2(10),   9    new_value   VARCHAR2(10),  10    action_date DATE DEFAULT SYSDATE  11  )  12  / Table created.

The table will capture identifying information about the user performing the action, the action being performed (insert, update, or delete), new and old values, and what, if any, column is referenced. In the case of a delete, just say “ALL” for the column name.

Next, a procedure is created to perform the updates. This code could be embedded into an existing application’s code or called from an existing application’s code. You can also create a procedure that displays the formatted output of the data in the audit table as follows:

scott@KNOX10g> CREATE OR REPLACE PROCEDURE audit_emp (   2    p_username     IN  VARCHAR2,   3    p_action       IN  VARCHAR2,   4    p_empno        IN  NUMBER,   5    p_column_name  IN  VARCHAR2,   6    p_old_value    IN  VARCHAR2,   7    p_new_value    IN  VARCHAR2)   8  AS   9  BEGIN  10  -- check data format and length  11  -- not shown here  12    INSERT INTO aud_emp  13                (username,  14                 action,  15                 empno,  16                 column_name,  17                 call_stack,  18                 client_id,  19                 old_value,  20                 new_value,  21                 action_date)  22         VALUES (p_username,  23                 p_action,  24                 p_empno,  25                 p_column_name,  26                 DBMS_UTILITY.format_call_stack,  27                 SYS_CONTEXT ('userenv',  28                              'client_identifier'),  29                 p_old_value,  30                 p_new_value,  31                 SYSDATE);  32  END;  33  / Procedure created. scott@KNOX10g> -- create procedure to display audit trail records scott@KNOX10g> CREATE OR REPLACE PROCEDURE show_aud_emp   2  AS   3  BEGIN   4    FOR rec IN (SELECT   *   5                    FROM aud_emp   6                ORDER BY action_date DESC)   7    LOOP   8      DBMS_OUTPUT.put_line (   'User:       '   9                            || rec.username);  10      DBMS_OUTPUT.put_line (   'Client ID:  '  11                            || rec.client_id);  12      DBMS_OUTPUT.put_line (   'Action:     '  13                            || rec.action);  14      DBMS_OUTPUT.put_line (   'Empno:      '  15                            || rec.empno);  16      DBMS_OUTPUT.put_line (   'Column:     '  17                            || rec.column_name);  18      DBMS_OUTPUT.put_line (   'Old Value:  '  19                            || rec.old_value);  20      DBMS_OUTPUT.put_line (   'New Value:  '  21                            || rec.new_value);  22      DBMS_OUTPUT.put_line (   'Date:       '  23                            || TO_CHAR  24                                (rec.action_date,  25                                 'Mon-DD-YY HH24:MI'));  26      DBMS_OUTPUT.put_line  27                   ('------------------------------');  28    END LOOP;  29  END;  30  / Procedure created.

The code is simple as written. It’s a good idea, however, to check the data types and data lengths prior to inserting (not done here, for brevity).

In this example, the auditing occurs by invoking the AUDIT_EMP procedure from another program when the user performs the actual update.

This popular design uses definer rights to help restrict access to actual database tables. Instead of allowing users to directly manipulate data, they have to invoke a procedure that performs the data manipulation task on the user’s behalf. Inside the procedure, there may be some auditing code that captures various aspects of the operation. Perhaps the old and new values are captured, the user, and the time.

scott@KNOX10g> CREATE OR REPLACE PROCEDURE update_sal (   2    p_empno   IN  NUMBER,   3    p_salary  IN  NUMBER)   4  AS   5    l_old_sal  VARCHAR2 (10);   6  BEGIN   7    SELECT     sal   8          INTO l_old_sal   9          FROM emp_copy  10         WHERE empno = p_empno  11    FOR UPDATE;  12    UPDATE emp_copy  13       SET sal = p_salary  14     WHERE empno = p_empno;  15    audit_emp  16      (p_username       => USER,  17       p_action         => 'UPDATE',  18       p_empno          => p_empno,  19       p_column_name    => 'SAL',  20       p_old_value      => l_old_sal,  21       p_new_value      => p_salary);  22  END;  23  / Procedure created.

For example, you want the user BLAKE to perform updates, but you don’t need to grant him update on the table directly. Simply grant him execute on the preceding UPDATE_SAL procedure. Do this in addition to granting him the ability to query the EMP_COPY table:

scott@KNOX10g> GRANT EXECUTE ON update_sal TO blake; Grant succeeded. scott@KNOX10g> GRANT SELECT ON emp_copy TO blake; Grant succeeded.

Now, as user BLAKE, you’ll execute the procedure. Note that BLAKE has no idea the procedure is auditing the update. Our audit table has a column for the Client Identifier, which can be set to any meaningful value. By capturing this or any other application context in the audit table, you can obtain more information about the end user’s context. Note the security caveats of using the Client Identifier described in Chapter 6.

The application could be setting the value explicitly. As an alternative shown next, a database logon trigger could have set the Client Identifier value transparently. Here’s an example trigger that sets the Client Identifier to the user’s connected IP Address:

sec_mgr@KNOX10g> CREATE OR REPLACE TRIGGER set_ip_in_id   2    AFTER LOGON ON DATABASE   3  BEGIN   4    DBMS_SESSION.set_identifier   5                         (SYS_CONTEXT ('userenv',   6                                       'ip_address'));   7  END;   8  / Trigger created. 

To test your audit, connect as BLAKE, query to check the original data values, and then execute your update procedure:

blake@KNOX10g> SELECT empno, sal   2    FROM scott.emp_copy   3   WHERE ename = 'BLAKE';      EMPNO        SAL ---------- ----------       7698       2850 blake@KNOX10g> EXEC scott.update_sal(p_empno=>7698, p_salary=>3000); PL/SQL procedure successfully completed. blake@KNOX10g> COMMIT ; Commit complete. blake@KNOX10g> SELECT empno, sal   2    FROM scott.emp_copy   3   WHERE ename = 'BLAKE';      EMPNO        SAL ---------- ----------       7698       3000

Connecting back as SCOTT, you see the audit data:

scott@KNOX10g> EXEC show_aud_emp   User:       BLAKE   Client ID:  192.168.0.100   Action:     UPDATE   Empno:      7698 Column:     SAL   Old Value:  2850   New Value:  3000   Date:       Mar-24-04 13:34 ------------------------------

The call stack was also preserved for you. The call stack shows (reading from the bottom up) an anonymous PL/SQL block called the SCOTT.UPDATE_SAL procedure, which then called the SCOTT.AUDIT_EMP procedure:

scott@KNOX10g> COL call_stack format a50   scott@KNOX10g> COL username format a10   scott@KNOX10g> SELECT username, call_stack     2    FROM aud_emp; USERNAME   CALL_STACK   ---------- --------------------------------------------------   BLAKE      ----- PL/SQL Call Stack -----                object      line  object                handle    number  name              692097F4         1  anonymous block               694CAF18        12  procedure SCOTT.AUDIT_EMP                6945FAEC        15  procedure SCOTT.UPDATE_SAL                 693CEA5C         1  anonymous block

Benefits

One of the greatest benefits of application auditing is that it’s inherently extensible. As security and auditing requirements evolve, application auditing can often be modified to meet these new and ever-changing requirements.

Not only can application auditing support many requirements, but also it controls how the auditing is done. This has benefits because applications in the application server may elect to audit to a file on the midtier or audit to a separate database, which would protect the audit records from the administrators of the production database. The auditing implementation can be based on anything. The previous example is only one possible method. Database tables are excellent for auditing because they provide the structure that facilitates the reporting that makes auditing useful. It’s generally simple to create SQL reports on the audit data. Many questions such as, “What has the user SCOTT accessed in the last three days?” can be easily answered when the audit records are stored in database tables.

Another major motivator for application auditing is that all aspects of the application can be audited, not only the data access. If the application interfaces with multiple databases, a few flat files, and a web service, all of that can be audited in a consistent way.

Application auditing may also be done to help ensure database independence. To do this effectively, a service layer would be implemented that would separate the auditing interface calls from the actual audit implementation. While this may seem noble at first, the reality is that to get the most use of your investment, your applications should be exploiting as much database technology as possible. Why reinvent the wheel?

Finally, application auditing requires no knowledge of database auditing. Even if knowledge isn’t the issue, the database auditing may provide little value if the application architecture doesn’t support it. Consider an application that doesn’t propagate the user’s identity to the database. Database auditing wouldn’t add much value, at least not for user-level auditing.

Drawbacks

After all those benefits, you might be tempted to rush right out and build in application auditing. Before you do, consider some of the following issues.

First, the programmatic nature of application auditing can be a drawback as well as a benefit. The audit code is just that—code. It’s therefore subject to all the challenges that plague code, such as logic errors, bugs, and the tremendous cost of maintaining the code over time.

From the security angle, the real drawback occurs if the application is bypassed. If a user conducts a direct update on the table, the application auditing will not be done because the application has been circumvented. Applications, especially applications facing large communities of users, will be targeted and possibly hacked. As such, all the security, including the auditing, may be overthrown or at the very least, be in jeopardy.

This hints at another challenge in application auditing. The application has to know that it’s supposed to call the auditing programs. One possible way to enforce this is to have the audit program set a signal value in a user-defined application context. You could then create a row-level security policy using views or Virtual Private Database (VPD) that checks for this signal. If you don’t understand this now, don’t worry—we’ll revisit row-level security implemented by views and VPD in 11. The point is, without a way to enforce the auditing, the auditing may not occur.

Trigger Auditing

Within the database, a very popular technique for auditing is to utilize database triggers. DML triggers will be explored: Oracle supports triggers for inserts, updates, and deletes. Oracle doesn’t support SELECT triggers, but similar functionality can be achieved using fine-grained auditing—details on how to do this are in the “Fine-Grained Auditing” section. Trigger auditing provides transparency, allowing you to enable auditing without requiring application modifications. Applications don’t have to be aware of the trigger auditing.

Trigger Audit Example

Auditing via triggers usually consists of writing to an auxiliary auditing table. Generally, the new and old data, along with some other useful information, is captured. Create the trigger to call the AUDIT_EMP procedure defined previously:

scott@KNOX10g> CREATE OR REPLACE TRIGGER update_emp_sal_trig   2    BEFORE UPDATE OF sal   3    ON emp_copy   4    FOR EACH ROW   5  DECLARE   6  BEGIN   7    audit_emp (p_username       => USER,   8               p_action         => 'UPDATE',   9               p_empno          => :OLD.empno,  10               p_column_name    => 'SAL',  11               p_old_value      => TO_CHAR (:OLD.sal),  12               p_new_value      => TO_CHAR (:NEW.sal));  13  END;  14  / Trigger created.

To test, perform an update on the table as the user BLAKE. For the update, BLAKE requires the update privileges on the table.

scott@KNOX10g> GRANT UPDATE(sal) ON emp_copy TO blake; Grant succeeded.

BLAKE now performs a direct update giving everyone in department 20 a 10 percent raise.

blake@KNOX10g> UPDATE scott.emp_copy   2     SET sal = sal * 1.1   3   WHERE deptno = 20; 5 rows updated. blake@KNOX10g> COMMIT ; Commit complete.

Returning to SCOTT to view the audit data, you see the updates that occurred for each record:

scott@KNOX10g> EXEC show_aud_emp; User:       BLAKE   Client ID:  192.168.0.100   Action:     UPDATE   Empno:      7369 Column:     SAL   Old Value:  800   New Value:  880   Date:       Mar-24-04 14:23 ------------------------------   User:       BLAKE   Client ID:  192.168.0.100   Action:     UPDATE   Empno:      7566 Column:     SAL   Old Value:  2975 New Value:  3272.5 Date:       Mar-24-04 14:23 ------------------------------   User:       BLAKE   Client ID:  192.168.0.100   Action:     UPDATE   Empno:      7788 Column:     SAL   Old Value:  3000   New Value:  3300   Date:       Mar-24-04 14:23 ------------------------------   User:       BLAKE   Client ID:  192.168.0.100   Action:     UPDATE   Empno:      7902   Column:     SAL   Old Value:  3000   New Value:  3300   Date:       Mar-24-04 14:23 ------------------------------   User:       BLAKE   Client ID:  192.168.0.100   Action:     UPDATE   Empno:      7876 Column:     SAL Old Value:  1100   New Value:  1210   Date:       Mar-24-04 14:23 ------------------------------   User:       BLAKE   Client ID:  192.168.0.100   Action:     UPDATE   Empno:      7698 Column:     SAL   Old Value:  2850   New Value:  3000   Date:       Mar-24-04 13:34 ------------------------------     PL/SQL procedure successfully completed.

The last record is the original record that was generated in the previous section. The trigger fires five times—once for each row because the trigger fires for each row; you could easily audit at the statement level by making the trigger fire once per statement.

Finally, you can see that the preceding audit records were initiated by the trigger by viewing the call stack:

scott@KNOX10g> SELECT DISTINCT call_stack     2             FROM aud_emp; CALL_STACK   --------------------------------------------------   ----- PL/SQL Call Stack -----     object      line  object     handle    number  name   692097F4         1  anonymous block   694CAF18        12  procedure SCOTT.AUDIT_EMP   691DECC0         3  SCOTT.UPDATE_EMP_SAL_TRIG ----- PL/SQL Call Stack -----     object      line  object     handle    number  name   692097F4         1  anonymous block   694CAF18        12  procedure SCOTT.AUDIT_EMP   6945FAEC        15  procedure SCOTT.UPDATE_SAL   693CEA5C         1  anonymous block

Benefits

One major benefit to trigger auditing is that the auditing can be transparent to the application. If you have purchased an application in which the code can’t be modified, then trigger auditing may provide a robust mechanism for adding or augmenting what is already provided. The triggers also can be enabled only when specific columns are being manipulated, as seen in the previous example. The trigger can operate for each row or for each statement. This allows selectivity in auditing and reduces the number of unnecessary audit records. The trigger auditing will also be invoked for all applications regardless of language; that is, no matter how the user interacts with the data, the trigger will audit. This consistency is important.

Note 

As with the application auditing, trigger auditing is programmed. From the benefits angle, this gives you many of the extensibility virtues that were discussed earlier.

Drawbacks

Triggers, while effective, aren’t guaranteed. They don’t fire for certain actions, such as TRUNCATE statements.

Triggers don’t allow applications to pass additional parameters. They are constrained to the table columns. Outside of the new and old values of the data, the only other information the trigger can use are application contexts and environmental data, such as the user’s name and connecting IP address.

Also, just like application auditing, triggers have to be created and defined for every object. Calling procedures from within triggers can help if the procedures can be shared across several triggers.

Autonomous Transactions and Auditing

As you may have noticed in the AUDIT_EMP procedure, the transaction wasn’t committed. Note what happens then if the user doesn’t commit the transaction. First, the audit trail is truncated:

scott@KNOX10g> TRUNCATE TABLE aud_emp; Table truncated.

Now, the BLAKE user will check the SAL values, issue an update, and write down the results. Once he has the information he wants, he issues a rollback:

blake@KNOX10g> SELECT SUM (sal)   2    FROM scott.emp_copy   3   WHERE deptno = 20;   SUM(SAL) ----------    11962.5 blake@KNOX10g> UPDATE scott.emp_copy   2     SET sal = sal * 1.1   3   WHERE deptno = 20; 5 rows updated. blake@KNOX10g> SELECT SUM (sal)   2    FROM scott.emp_copy   3   WHERE deptno = 20;   SUM(SAL) ----------   13158.75 blake@KNOX10g> ROLLBACK ; Rollback complete.

When the audit trail is queried, there is no audit data! Its as if the update never happened or the audit data has been erased:

scott@KNOX10g> EXEC show_aud_emp; PL/SQL procedure successfully completed.

That’s because the updates never did happen. At least, the updates never happened from a database transactional perspective. The audit was part of the same transaction, so the rollback removed the audit entries. You know the updates did happen and not only that, but the user was able to see the results of the updates. The rollback performed as it was supposed to.

You may try to solve this issue by immediately placing a commit statement in the procedure, but you can’t place a commit in the trigger or you’ll receive a runtime “ORA-04092: can’t COMMIT in a trigger” error.

Ensuring the audit data isn’t erased on rollback is important for auditing in cases where you wish to capture actions being performed even if the actions are later “unperformed.” In fact, the Oracle database auditing works precisely on this principle. Rollbacks don’t erase the entries from the audit trails. To do this within the database, you can simply utilize autonomous transactions. These transactions are independent of the other transaction that the user session has created.

You can modify both your database trigger and your stored procedure to run as autonomous transactions. Once done, the updates will be audited even if the user issues a rollback. For the procedure, add a PRAGMA in the variables section and add a commit. This commit affects only our autonomous transaction:

scott@KNOX10g> CREATE OR REPLACE PROCEDURE audit_emp (   2    p_username     IN  VARCHAR2,   3    p_action       IN  VARCHAR2,   4    p_empno        IN  NUMBER,   5    p_column_name  IN  VARCHAR2,   6    p_old_value    IN  VARCHAR2,   7    p_new_value    IN  VARCHAR2)   8  AS     9    PRAGMA AUTONOMOUS_TRANSACTION;  10  BEGIN   11    INSERT INTO aud_emp    12                (username,  13                 action,  14                 empno,  15                 column_name,  16                 call_stack,  17                 client_id,  18                 old_value,  19                 new_value,  20                 action_date)  21         VALUES (p_username,  22                 p_action,  23                 p_empno,  24                 p_column_name,  25                 DBMS_UTILITY.format_call_stack,  26                 SYS_CONTEXT ('userenv',  27                              'client_identifier'),  28                 p_old_value,  29                 p_new_value,  30                 SYSDATE);  31    COMMIT;  32  END;  33  /     Procedure created.

Because both the trigger and the UPDATE_SAL procedure call this procedure, all updates will be audited, regardless of whether there is a commit or rollback issued. To test this, Blake issues the same update:

blake@KNOX10g> UPDATE scott.emp_copy     2     SET sal = sal * 1.1      3   WHERE deptno = 20; 5 rows updated. blake@KNOX10g> ROLLBACK ; Rollback complete.

User SCOTT can count the audits, and he’ll see all five records even though the rollback removed the actual updates. The autonomous transaction-enabled AUDIT_EMP procedure preserved the audit records:

scott@KNOX10g> SELECT COUNT (*)   2    FROM aud_emp;   COUNT(*) ----------        5