Re: Audit Trail

>anne@grumpy.ksc.nasa.gov said: > I would like to get a report of the changes to the database > for the day. The audit trail puts out a data file. Is there > someway of reading this file. > > Perhaps you know of a better way of getting a report? I'm > listening. > This is an articule I wrote for our user group newsletter last year on this subject. I hope it helps. - Lester --------------------------------------------------------------------------- Reading Informix SE audit files by Lester Knutsen Twice in the last month I have been asked how to read Informix SE audit files. Informix SE has a feature (this is not available in Informix Online) to create an audit trail of all adds, deletes and updates to a table. This can be used to trace which user is changing critical data. The procedures to create an audit file are simple and well documented. However, it is not well documented on how to read or use the audit file. The SQL syntax to create an audit trail is 'create audit for table_name in "pathname'". The full pathname is required for this command. An Informix audit file is structured the same as the original data file (.dat file) with a header. One way to access an audit file is to convert it into an Informix database table. Then you can perform searches on the changes made to your data. The following steps will create an Informix database table out of an audit file. As an example, I will use the stores database and the orders table that come with Informix products. I recommend that you try this in a test environment first. To create an audit file on the orders table, type the following SQL command in dbaccess or isql. create audit for orders in "/usr/lester/demo/orders.audit" Every change to the orders table will be captured in this file. The next step is to create a way of loading this into a database and using the data. 1. First you need to create an SQL schema for the new audit table. The schema will be based on the table you are auditing with an additional five field header. You can use dbschema to do this by typing: dbschema -s stores -t orders a_orders.sql 2. Edit the a_orders.sql script and add the additional fields for the audit header. The audit file includes the following five header fields: a_type char(2) Type of record where aa = added, dd = deleted, rr = before update image, ww = after update image. a_time integer Integer internal time value. a_process_id smallint Process ID that changed the record. a_usr_id smallint User ID that changed the record. a_rowid integer Original rowid. You will also need to change the table name in the SQL script produced by dbschema to the name you want to call the audit table. I like to use the old table name with an "a_" prefix. The Index statements will also need to be changed. There must be one index on this table for the next step with bcheck to work. The old unique indexes should be removed because in the audit file the same record could appear for multiple changes. Change the index statements to use the new table you are creating. The following example is the script for the orders table: create table a_orders ( a_type char(2), a_time integer, a_process_id smallint, a_usr_id smallint, a_rowid integer, order_num serial not null, order_date date, customer_num integer, ship_instruct char(40), backlog char(1), po_num char(10), ship_date date, ship_weight decimal(8,2), ship_charge money(6,2), paid_date date ); create index a_order_num_idx on a_orders ( order_num ); 3. Create the table with the new name. This should produce an empty table ready to hold your audit file data. 4. Copy the audit file to replace the new table data file. This step will destroy any data in the a_orders.dat table so proceed with caution. Look up the pathname of the data file created for this table. One way is to perform the following select: select dirpath from systables where tabname = "a_orders" On my system dirpath was "a_order007". Change to the directory where the database files are located and copy the audit file to replace the a_order007.dat file. cd stores.dbs cp /usr/lester/demo/orders.audit a_order007.dat 5. After overwriting the ".dat" file the data and the index will be out of sync. Use bcheck, the Informix index repair tool to fix the index file. Type the following command. bcheck a_order007 You now have an Informix table of your audit records and you can run SQL or build perform screens to see changes made to your data. Repeat steps 4 and 5 every time you need to update the table. The following is an example SQL statement. The results show an add (aa), change ( rr and ww) and a delete (dd) on the orders table. select a_type, a_time, a_process_id, a_usr_id, a_rowid, order_num from a_orders a_type a_time a_process_id a_usr_id a_rowid order_num aa 759109477 823 200 16 1016 rr 759109502 823 200 15 1015 ww 759109502 823 200 15 1015 dd 759109516 823 200 16 1016 ############################################################################# # Lester Knutsen lester@access.digex.net # # Advanced DataTools Corporation Voice: 703-256-0267 # # Grant group privileges for Informix databases with DB Privileges # #############################################################################