097 - 11.1 About the Audit Trail Application

Team-Fly    

 
Oracle Security
By William Heney, Marlene Theriault
Table of Contents
Chapter 11.  Developing a Sample Audit Application


11.1 About the Audit Trail Application

To implement the audit trail application described in this chapter, you need to create the following:

  • Three tables

  • Two sequences

  • One PL/SQL package (with two stored procedures)

  • Three database triggers per audited table

  • Three additional columns in each audited table

After that, the application runs itself.

Developers who are writing or modifying applications do not need to be concerned with the audit trail application, except when a new column is added to a table. The performance impact must be assessed on your own system, but can be minimized by selectively implementing some of the components that comprise the audit trail application. Note that you can implement only the components you want, for example:

  • Only INSERT tracking

  • Only a record of the fact that the transaction occurred (i.e., only the AUDIT_ROW table)

  • Full UPDATE and DELETE tracking

You do this by commenting out the relevant lines in the trigger code included in the application. The triggers need only be written for the specific tables and columns you want to audit. You need to determine your desired level of detail, acceptable performance impact, and business requirements, and implement the audit trail application accordingly . For more detailed information, see the "About Performance" section later in this chapter.

The approach presented is very effective and easy to implement because:

  • Each table needs triggers that are written only once.

  • The triggers call stored procedures to do the actual auditing.

  • Everything is handled by the Oracle kernel, so the audit trail cannot be bypassed during normal use except by rollback.

  • Developers can develop applications without worrying about (or even knowing about) the triggers.

  • The auditing triggers can be selectively disabled for certain functions like bulk updates or deletions.

As you did when you created an audit plan (see Chapter 10), you must first decide what you want to audit. So we can show you the full audit tracking implementation in this example, we assume here that you want full auditing tracking inserts , updates down to the column level, and deletions, including deletion recovery capability. Because the application is written in a modular manner (several separate pieces), you will find it easy to implement only the parts you decide you actually need for your own facility.

11.1.1 A Few Limitations

There are ramifications to consider when implementing any application. If you choose to implement part or all of the application we present here, you must keep the following points in mind:

  • If any of the auditing triggers fail for any reason, the SQL statement that caused the trigger to fire will also fail. This is very important to know if the application is being run in a production environment.

  • The size of the fields in the application tables particularly the username and data in the delete table must be chosen very carefully . Any data overflow will raise a VALUE_ERROR and the triggering statement will fail.

  • The trigger generation scripts do not handle LONG columns and the like when building triggers to store values.

11.1.2 Tracking Inserts

To track inserts, you need to add three columns to each table you wish to track. The columns are called rid, creator, and timestamp. Their definitions are listed in Table 11.1.

Table 11.1. Additional Table Columns for Inserts

Column Name

Definition

Default

Description

rid

NUMBER(10)

 

Record ID unique in database

creator

VARCHAR2(12)

RTRIM( user )

User that created the record

timestamp

DATE

SYSDATE

Date/time of record creation

The creator and timestamp columns can be defined as nullable if your existing tables do not have any information to populate them. However, the rid column is essential and must be backfilled after you have added it to a table.

The creator and timestamp columns are defined as defaults so they will always be automatically populated when an INSERT is performed. For the creator column, 12 characters seems to be enough for "OPS$" plus eight characters . However, you can expand or shrink this column to meet your needs. The timestamp column includes the full time, so you may want to apply a TO_CHAR mask of "DD-MON-YYYY HH:MI:SS am" to it when displaying or selecting it, or use the TRUNC( ) function if you are searching for a specific day.

11.1.3 The First Sequence Creation Script (SEQ-RID)

You also need to define a new sequence, SEQ_RID, which is used to generate record IDs when a row is inserted. The sequence is used for the rid column in all tables, so the rid is unique in the entire database. It is not a replacement for your normal primary key column (although it can serve as one if you want), and need not be indexed.

 REM This sequence is used to generate unique record IDs for each row in the REM data tables.  Recommended to fill in via before-insert trigger. You'll REM need to add the appropriate RID column to each audit table. CREATE SEQUENCE seq_rid   START WITH 1        /* starting number */   INCREMENT BY 1      /* in increments of 1 */   ORDER               /* generate them in order */   NOCYCLE             /* do not recycle numbers */   CACHE 100           /* keep lots of them in RAM for speed! */   MINVALUE 1          /* 1 is the minimum */   NOMAXVALUE          /* no practical limit on high # */ / GRANT select ON seq_rid TO public; 

11.1.4 The First Trigger Creation Script (Before-Insert)

You need to create the before-insert database trigger (below) on each table you want to audit to ensure that the rid is filled in at insert time.

 REM  Trigger to fill in the RID (Record ID) for the EMP table REM  if not supplied by the application. CREATE OR REPLACE TRIGGER emp_bi0   BEFORE INSERT       ON employee      FOR EACH ROW     WHEN (new.rid IS NULL)  BEGIN   SELECT TRUNC(seq_rid.nextval)           /* Use TRUNC() to avoid ORA-01438 errors - which you should              not get, but ORACLE doesn't seem to know that. */     INTO :new.rid     FROM dual;  END;  / 

11.1.5 Tracking Updates and Deletions

To track updates and deletions, you need to add (at a minimum) the RID column (described earlier under "Tracking Inserts") to each audited table. This provides an absolute method of uniquely identifying any record from any table. Then you need to create several objects (depending on the level of detail desired), listed in Table 11.2.

Table 11.2. Additional Table Columns for Updates and Deletes

Object Name

Object Type

Description

audit_row

Table

Holds record of transaction (UPDATE/DELETE)

audit_column

Table

Holds old/new values of updated columns

audit_delete

Table

Holds image of deleted row data

seq_rid

Sequence

Generates unique record ID (as above)

seq_audit

Sequence

Generates unique audit transaction numbers

audit_trail

PL/SQL package

Contains stored procedures to write data to the AUDIT_<table> tables

record_transaction

Stored procedure

Writes the record to the AUDIT_ROW table

column_update

Stored procedure

Writes the data to the AUDIT_COLUMN table

<tablename>_bi0

Before-insert trigger

Populates the rid column of the table

<tablename_au0

After-update trigger

Determines which columns have been changed

<tablename>_ad0

After-delete trigger

Writes the deleted data to the AUDIT_DELETE table

The three triggers are required for each audited table if you want to capture information about information that has been inserted, updated, or deleted. Substitute your table name for the <tablename> notation. The "0" on the end of the trigger name is used to avoid naming conflicts with possible existing triggers.

11.1.6 The Three Table Creation Scripts

The three tables we need to create for the audit trail application are AUDIT_ROW, AUDIT_COLUMN, and AUDIT_DELETE. The scripts used to create these tables, along with an explanation of each column, are presented in the following sections.

11.1.6.1 The AUDIT_ROW table

The AUDIT_ROW table contains information about the table that was changed, the type of change that was performed, the date and time when the change occurred, and who made the change. The timestamp is obtained by capturing the system time when the change was made, and, since Oracle stores the user's name in a variable called "user", we can obtain the information about who has made the change directly from Oracle and can store the value into the user_name column.

In Oracle8 you can make the old_value, new_value, and row_data columns up to 4000 bytes long.

 REM AUDIT_ROW - one row per transaction. CREATE TABLE audit_row   (raid        NUMBER(10)   NOT NULL,         /* Row audit ID */    table_name  VARCHAR2(30) NOT NULL,         /* Table name */    rid         NUMBER(10)   NOT NULL,         /* Record ID (from table) */    action      VARCHAR2(1)  NOT NULL,         /* Action (I, U, D) */    REM use defaults to populate these columns - less overhead    timestamp   DATE DEFAULT SYSDATE NOT NULL, /* Time of action */    user_name   VARCHAR2(12) DEFAULT RTRIM(USER) NOT NULL) /* User */    TABLESPACE audit_ts    PCTFREE 0     PCTUSED 90     STORAGE (INITIAL 100k                 NEXT 100k          MAXEXTENTS UNLIMITED          PCTINCREASE 0); 
11.1.6.2 The AUDIT_COLUMN table

The AUDIT_COLUMN table holds the record of what data was changed. The column OLD_VALUE holds the value prior to the change, and the column NEW_VALUE holds the value after the change has occurred. There is one row inserted in the table per changed column. The RAID (Row Audit ID) column is used as the foreign key link to the AUDIT_ROW table. The primary key is the CAID (Column Audit ID) column, which is also generated by the SEQ_AUDIT sequence generator.

The AUDIT_COLUMN table has the following columns:

RAID

Row Audit ID. Number identifying the transaction. Foreign key link to the AUDIT_ROW table.

CAID

Column Audit ID. Sequence-generated number (from SEQ_AUDIT) uniquely identifying each row in the table. Primary key.

COLUMN_NAME

Name of the column involved in the transaction.

OLD_VALUE

Pre-transaction data value of the column.

NEW_VALUE

Post-transaction data value of the column.

 REM AUDIT_COLUMN - one row per changed column. CREATE TABLE audit_column   (raid        NUMBER(10)   NOT NULL,   /* Row audit ID */    caid        NUMBER(10)   NOT NULL,   /* Column audit ID */    column_name VARCHAR2(30) NOT NULL  , /* Column name */     old_value   VARCHAR2(2000),          /* Old data value */    new_value   VARCHAR2(2000))          /* New data value */  TABLESPACE audit_ts    PCTFREE 0     PCTUSED 90     STORAGE (INITIAL 100k                 NEXT 100k          MAXEXTENTS UNLIMITED          PCTINCREASE 0); 
11.1.6.3 The AUDIT_DELETE table

The AUDIT_DELETE table holds the data (separated by "pipes") of a row that was deleted. The primary key and foreign key link to the AUDIT_ROW table is the RAID column. The AUDIT_DELETE columns are as follows :

RAID

Row Audit ID. Number identifying the transaction. Foreign key link to the AUDIT_ROW table.

ROW_DATA

Column data values of deleted row, separated by the pipe symbol, "".

 REM AUDIT_DELETE - one row per deleted row. CREATE TABLE audit_delete   (raid      NUMBER(10)     NOT NULL,     /* Row audit ID */     row_data  VARCHAR2(2000) NOT NULL)     /* Data from row */  TABLESPACE audit_ts    PCTFREE 0     PCTUSED 90     STORAGE (INITIAL 100k                 NEXT 100k          MAXEXTENTS UNLIMITED          PCTINCREASE 0); 

For better security, grant privileges to the audit tables only to users who need to examine them. Since the audit tables are populated by database procedures, the normal user does not need any privileges to the audit tables. The procedure executes with the security level of the schema owner.

For better performance, use appropriate INITIAL and NEXT values. PCTINCREASE should be 0, since the tables will not be updated, and a value will enable the most rows to be packed into each block. Also, do not index these tables (unless you're querying them) to avoid index update overhead on each audit row INSERT. A separate tablespace (separate disk if possible) is recommended for easy administration and optimum performance.

11.1.7 The Second Sequence Creation Script (SEQ_AUDIT)

A sequence generator is used to supply a unique, usually numeric, value for a column. In this script, a sequence generator is built to supply a unique, primary key value for the Row Audit ID (RAID) and the Column Audit ID (CAID). The SEQ_AUDIT sequence generator is called from the before-delete and after-update triggers. The same sequence generator is used for both triggers because there is no need to ensure that the RAID values are sequential. There is only a need to ensure that the RAID values are unique in the entire system.

 CREATE SEQUENCE seq_audit    START WITH 1        /* starting number */   INCREMENT BY 1      /* in increments of 1 */   ORDER               /* generate them in order */   NOCYCLE             /* do not recycle numbers */   CACHE 100           /* keep lots of them in RAM for speed! */   MINVALUE 1          /* 1 is the minimum */   NOMAXVALUE          /* no practical limit on high # */ / GRANT select ON seq_audit TO public; 

11.1.8 The Second Trigger Creation Script (After-Update)

The after-update trigger (EMP_AU0) is used to populate two separate tables: the AUDIT_ROW table and, if a change has actually occurred in the table, the AUDIT_COLUMN table. Here's the way the second trigger works.

The trigger fires after the update has occurred, but before it is committed to the database. First it calls the procedure audit_trail.RECORD_TRANSACTION to record that this transaction occurred and set up a master audit record in the AUDIT_ROW table. Then it compares the pre- and post-update values of the columns to each other (old.column versus new.column). If they differ , the procedure audit_trail.COLUMN_UPDATE is called to record the data into the AUDIT_COLUMN table. Remember to NVL the comparisons, because a null is never equal to (or not equal to) a null, which will create false audit records. The dates are compared to a standard value of 9595 AD. [2] One record is written for each column changed in the UPDATE statement.

[2] Surely this is all we'll need.

You don't need to audit all of the columns; simply name the ones you want in the AFTER UPDATE OF clause in the trigger definition. If you add columns to the table, add them to this clause and to the comparison clauses, or re-run the generation script ( gen_au.sql ).

Unfortunately, you cannot reliably use the PL/SQL "IF UPDATING column_name THEN..." structure to test old versus new values. It works for SQL*Plus, but SQL*Forms, Oracle Forms, and other development tools construct an UPDATE statement containing every column in the block, whether it has actually changed or not, which creates scads of false audit records and incurs enormous overhead. Trust me, I learned this the hard way! In Oracle Forms 4.x and above, however, you can select whether the block should pass all columns or only changed columns to the database.

 REM AFTER UPDATE trigger to write a record of the UPDATE transaction and  REM the pre- and post-change values of the affected data.  The sample  REM trigger is for the EMPLOYEE table. CREATE OR REPLACE TRIGGER emp_au0  AFTER UPDATE OF        empno, ename, job, mgr, hiredate, sal, comm, deptno, rid     ON employee    FOR EACH ROW DECLARE   raid NUMBER(10); BEGIN -- Get next RAID (Row Audit ID).   SELECT seq_audit.nextval      INTO raid      FROM dual; -- Record transaction into the AUDIT_ROW table.   audit_trail.record_transaction     (raid, 'EMP', :old.rid, 'U'); -- Test columns to see what is updated and write to AUDIT_COLUMN. -- You must use the NVL function, otherwise old nulls will not -- compare properly to new nulls, and results will be unpredicable.   IF NVL(:old.empno,0) != NVL(:new.empno,0) THEN     audit_trail.column_update (raid, 'EMPNO', :old.empno, :new.empno);   END IF;   IF NVL(:old.ename,' ') != NVL(:new.ename,' ') THEN     audit_trail.column_update (raid, 'ENAME', :old.ename, :new.ename);   END IF;   IF NVL(:old.job,' ') != NVL(:new.job,' ') THEN     audit_trail.column_update (raid, 'JOB', :old.job, :new.job);   END IF;   IF NVL(:old.mgr,0) != NVL(:new.mgr,0) THEN     audit_trail.column_update (raid, 'MGR', :old.mgr, :new.mgr);   END IF;   IF NVL(TO_CHAR(:old.hiredate,'dd-mon-yyyy'),'31-dec-9595') !=       NVL(TO_CHAR(:new.hiredate,'dd-mon-yyyy'),'31-dec-9595') THEN     audit_trail.column_update (raid, 'HIREDATE',       TO_CHAR(:old.hiredate,'DD-MON-YYYY'),        TO_CHAR(:new.hiredate,'DD-MON-YYYY');   END IF;   IF NVL(:old.sal,0) != NVL(:new.sal,0) THEN     audit_trail.column_update (raid, 'SAL', :old.sal, :new.sal);   END IF;   IF NVL(:old.comm,0) != NVL(:new.comm,0) THEN     audit_trail.column_update (raid, 'COMM', :old.comm, :new.comm);   END IF;   IF NVL(:old.rid,0) != NVL(:new.rid,0) THEN     audit_trail.column_update (raid, 'RID', :old.rid, :new.rid);   END IF; END; / 

11.1.9 The Third Trigger Creation Script (After-Delete)

The after-delete trigger (EMP_AD0) fires after the deletion has occurred, but before it is committed to the database. First, it calls the procedure audit_trail.RECORD_TRANSACTION to record that this transaction has occurred and to set up a master audit record in the AUDIT_ROW table. Then it concatenates the data from each (desired) column together into a variable, and writes a record containing that data into the AUDIT_DELETE table. If a future "undelete" is desired, you can print out the record and reconstruct the data, or write a script to do it.

 REM This script creates a trigger to track deletions made to the EMP table. CREATE OR REPLACE TRIGGER emp_ad0   AFTER DELETE   ON emp   FOR EACH ROW DECLARE    raid NUMBER(10);   deleted_data VARCHAR2(2000); BEGIN -- Get the next RAID (Row Audit ID).   SELECT seq_audit.nextval      INTO raid      FROM dual; -- Record transaction in audit_row table and write data to  -- audit_delete table.   audit_trail.record_transaction     (raid, 'EMP', :old.rid, 'D');    deleted_data :=    TO_CHAR(:old.empno)  ''    :old.ename  ''    :old.job  ''    TO_CHAR(:old.mgr)  ''    TO_CHAR(:old.hiredate,'DD-MON-YYYY')  ''    TO_CHAR(:old.sal)  ''    TO_CHAR(:old.comm)  ''    TO_CHAR(:old.deptno)  ''    TO_CHAR(:old.rid)  ''    :old.creator  ''     TO_CHAR(:old.timestamp,'DD-MON-YYYY')  '';   INSERT INTO audit_delete    VALUES (raid, deleted_data);  END; / 

A ROLLBACK statement will cause the audit trail records to be rolled back as well as the actual UPDATE/DELETE statement, so you won't end up with a lot of false audit records.

11.1.10 The Package and Procedure Creation Scripts

The procedures used in this application simply take input from the three triggers we've defined and actually create the audit records. Read the documentation included in the script for details.

 /* Definition of Parameters received from the calling trigger...   raid    = Row Audit ID.   tabname = Name of table involved in the transaction.   erid    = Existing RID (Record ID) of row being audited.     act     = Action that has occurred (Update or Delete).   colname = Name of the column involved in the update.   oldval  = Pre-Update (old) value of the column data.   newval  = Post-Update (new) value of the column data. */ CREATE OR REPLACE PACKAGE audit_trail IS   PROCEDURE record_transaction      (raid IN NUMBER,      tabname IN VARCHAR2,      erid IN NUMBER,      act IN VARCHAR2);   PROCEDURE column_update     (raid IN NUMBER,      colname IN VARCHAR2,      oldval IN VARCHAR2,      newval IN VARCHAR2); END audit_trail; / CREATE OR REPLACE PACKAGE BODY audit_trail IS   PROCEDURE record_transaction    (raid IN NUMBER,      tabname IN VARCHAR2,      erid IN NUMBER,      act IN VARCHAR2) IS   BEGIN      -- Write a record of the transaction to the master audit trail   -- table.  Timestamp and User_Name are not included here -    -- they are filled in by column defaults defined at the table   -- level (should be faster).      INSERT INTO audit_row       (raid, table_name, rid, action, timestamp, user_name)       VALUES         (raid, tabname, erid, act, sysdate, user);    -- Return a scary message if for some reason the statement failed.       IF sql%NOTFOUND THEN         RAISE_APPLICATION_ERROR         (-20000, 'Error creating Row Audit record. Contact DBA.');     END IF;      END;   PROCEDURE column_update     (raid IN NUMBER,       colname IN VARCHAR2,       oldval IN VARCHAR2,       newval IN VARCHAR2) IS    BEGIN   -- Record the column names and old/new data values of individual   -- columns altered in the transaction to the AUDIT_COLUMN table.     INSERT INTO audit_column        (raid, caid, column_name, old_value, new_value)        VALUES       (raid, seq_audit.nextval, colname, oldval, newval);   -- Return a scary message if for some reason the statement fails.     IF sql%NOTFOUND THEN         RAISE_APPLICATION_ERROR         (-20000, 'Error creating Column Audit record. Contact DBA.');     END IF;      END; END audit_trail; / 

Team-Fly    
Top


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