Advantage Developer Zone

 
 
 

Create a Table Open Audit

Friday, December 09, 2011

Table auditing is sometimes a necessary part of your application. To ensure the most accurate auditing it must be done within the database so all access to the data is audited regardless of the program that is accessing the data. This is easily accomplished using triggers.

However, Advantage triggers can only be created for insert, update and delete actions. If you need to track when a table is opened or which records have been read you will need to use a different approach.

In this article we will demonstrate how to audit table opens as well as a basic solution for read auditing. This is accomplished with the use of User Defined Functions and Views.

Requirements

Because there are no canned methods to accomplish open or read auditing, there are a few requirements to get such auditing implemented

  • The data must be stored in a Data Dictionary
  • The Data Dictionary must only allow Remote Server connections to be made (Server-Side Aliases may help better accomplish this)
  • All tables will require a separate view to facilitate the auditing
  • The database must be running on Advantage 10 or higher (for reliable auditing, transaction free tables must be available)
Getting Started

After the above requirements are met, open auditing can be accomplished using views and user defined functions. Views must be used to include the user defined function in the result set presented to the user.

For this example, we’ll first create a new data dictionary and the objects necessary to implement read auditing.

  1. Create the data dictionary.
    1. Name the new dictionary ReadAuditing
    2. Enable logins to accurately track which users access the table(s)
    3. Click OK to create the new dictionary
  2. Create a table to be audited
    1. Name the new table MyTable_bt (bt = base table)
    2. Create the following fields

      Name Type Size
      pk AutoInc n/a
      Name Character 25
      FavoriteFood Character 50

    3. Set the pk field as the primary key for the table
    4. Click OK to create the new table

  3. Create a table to store the audit records
    1. Name the new table AuditTable
    2. Create the following fields

      Name Type Size
      UserName Character 50
      TableName Character 50
      InitialOpen TimeStamp n/a
      LastOpen Timestamp n/a

    3. On the Table Properties tab set transaction free to true
    4. Click OK to create the new table


Implementing Open Auditing

Now that the necessary tables are created, the functionality is implemented with a user defined function and a view.  Create this function using the following steps.

  1. Create a new function named OpenAudit
    1. Add the following parameters

      Name Type Data Type Size
      TblName input Character 50
      ReturnValue logical return n/a

    2. Add the following SQL Script
      MERGE AuditTable ON ( UserName = USER() AND TableName = TblName)
      WHEN MATCHED THEN
        UPDATE SET LastOpen = NOW()
      WHEN NOT MATCHED THEN
        INSERT VALUES ( USER(), TblName, NOW(), NOW());
      
      RETURN TRUE;
    3. Click OK to create the new Function
  2. Create a new view named MyTable
    1. Add the following SQL statement
      SELECT pk, Name, FavoriteFood FROM MyTable_bt
      WHERE OpenAudit('MyTable') = true
    2. Click OK to create the view

Notice in the view, we have appended the audit_mytable() UDF to the result set of the SELECT statement. This causes the audit_mytable() UDF to be called everytime a row is fetched from the result set and therefore audit the access of that table. However, our UDF will only insert or update a single record into our audit table.

Testing the Auditing

Before getting straight to the read auditing, we need to make sure something is there to audit. The following SQL script can be executed one or more times to add data to mytable.

INSERT INTO mytable_bt(Name,FavoriteFood) VALUES('Cat','Mouse');
INSERT INTO mytable_bt(Name,FavoriteFood) VALUES('Mouse','Cheese');
INSERT INTO mytable_bt(Name,FavoriteFood) VALUES('Spider','Fly');
INSERT INTO mytable_bt(Name,FavoriteFood) VALUES('Worm','Dirt');
INSERT INTO mytable_bt(Name,FavoriteFood) VALUES('Human','Pizza');
INSERT INTO mytable_bt(Name,FavoriteFood) VALUES('Alien','Human');
INSERT INTO mytable_bt(Name,FavoriteFood) VALUES('Dog','Bone');

In our case we will remove rights to MyTable_bt to ensure that users only view the data using the view we created. This ensures that an open audit will be written every time the table is accessed. You can verify this by opening the MyTable view in ARC.

After opening the view, we can open the audit table and see that we have some audits.

Success! We can now have read auditing for MyTable.

Auditing Reads of Individual Records

If you need an audit of when individual records are read the audit table and functions are slightly different.

  1. Create a table to store the audit records
    1. Name the new table MyTable_ra (ra = read audit)
    2. Create the following fields

      Name Type Size
      AuditNo AutoInc n/a
      AuditTime Timestamp n/a
      pk AutoInc n/a
      Name Timestamp 25
      FavoriteFood Character 50

    3. On the Table Properties tab set transaction free to true
    4. Click OK to create the new table

  2. Create a new function named audit_mytable. The user defined function must be written in a way that it correctly identifies the record being accessed as well as by whom.
    1. Add the following parameters

      Name Type Data Type Size
      in_pk Input Integer n/a
      ReturnValue logical return n/a

    2. Add the following SQL Script
      INSERT INTO MyTable_ra( AuditUser, pk, Name, FavoriteFood )
      VALUES ( USER(), in_pk, 
        (SELECT Name FROM MyTable_bt WHERE pk = in_pk),
        (SELECT FavoriteFood FROM MyTable_bt WHERE pk = in_pk) );
      
      RETURN TRUE;
    3. Click OK to create the new Function
  3. Create a new View named MyTable1
    1. Add the following SQL Script
      SELECT pk, Name, FavoriteFood FROM MyTable_bt 
      WHERE audit_mytable(pk) = true;
    2. Click OK to create the new View

Now when you open the MyTable1 view a record will be added to MyTable_ra for every record returned from the view. To ensure the most accurate auditing you can use a WHERE clause to limit the records returned by the view. This will also limit the number of records added to the audit table.

Other Notes

There are a few caveats that arise in using this method to implement read auditing.

  • Static cursors will cause every record to be audited in a query instead of just the records that are fetched
  • Every SELECT statement will cause the database to grow
  • Every fetch and every SQL query will inherit some performance overhead

Summary

By enabling logging within the database audit records will be created regardless of which application is connecting to the data. The table open audit demonstrated here will be updated every time a table is opened by any user. The same function can be used for every table in the database. You will have to create a view for each table to do the logging and restrict access to the base table(s).

Read auditing is a bit more complicated and will generate many more records in your audit table. The mechanism demonstrated here will write a record in the audit table for each user and each record in the table(s) they open. Because of this you may wish to use a WHERE clause to limit the number of records returned. Finally this only audits retrieval of the records and does not guarantee that the user viewed the record.