Skip to main content

Implementing Enhanced Table Auditing in the Oracle Dev Gym


I'd like to start off this post by thanking Connor McDonald, a member of my Oracle Developer Advocates team.

I'm a traditional sort of Oracle developer. I learned a long time ago to use row-level triggers to keep track of who created and changed a row and when. 

So, for example, for the DG_CLASSES table of the Oracle Dev Gym (which is used both for Dev Gym classes and AskTOM Office Hours), I have these two triggers:

TRIGGER dg_classes_bir
   BEFORE INSERT
   ON dg_classes
   FOR EACH ROW
DECLARE
BEGIN
   :new.created_on := SYSDATE;
   :new.created_by := qdb_config.apex_user;
   :new.changed_on := SYSDATE;
   :new.changed_by := qdb_config.apex_user;
END dg_classes_bir;
TRIGGER dg_classes_bur
   BEFORE UPDATE
   ON dg_classes
   FOR EACH ROW
DECLARE
BEGIN
   :new.changed_on := SYSDATE;
   :new.changed_by := qdb_config.apex_user;
END dg_classes_bur;

Clearly, this kind of auditing has some drawbacks, including:

  • I have no idea what was changed, just that something was changed.
  • Those triggers fire all the time, so if I wanted to, say, do a batch load of rows and preserve the existing created-changed column values, I have to disable the triggers, do the load, re-enable the triggers.
Connor recently published a blog post and utility for enhanced table auditing. I strongly suggest you read that before continuing. You will understand more thoroughly the drawbacks of my approach. And you will understand why I decided to use his utility in the Oracle Dev Gym. 

I'd like to share how I went about it, including some pages I added to the Dev Gym to give us easy access to the auditing data. Perhaps you'd want to do something similar.

The installation of Connor's code went smoothly (not that I expected anything differently). Then it was time to generate all the code needed for auditing on a select number of key tables. Again, Connor made it really easy by offering a single procedure, , that created all the code for a specific table. 

I built a layer of code over that to handle multiple tables and disable those pesky, out-of-date triggers:
DECLARE
   s          VARCHAR2 (100) := 'QDB_PROD';
   a          VARCHAR2 (100) := 'EXECUTE';

   TYPE tables_t IS TABLE OF VARCHAR2 (1000);
   
   l_tables tables_t
       := tables_t (
             'DG_CLASSES',   
             'QDB_COMPEV_ANSWERS',
             'QDB_COMP_EVENTS',
             'DG_STUDENTS',
             'QDB_USERS',
             'DG_WORKOUTS',
             'DG_USER_WORKOUTS');
BEGIN
   /* Enable auditing in the Dev Gym schema */
   INSERT INTO schema_list VALUES ('QDB_PROD');

   FOR indx IN 1 .. tables_in.COUNT
   LOOP
      /* Creates audit table, package, triggers */
      audit_util.generate_audit_support (
         p_owner        => schema_in,
         p_table_name   => tables_in (indx),
         p_action       => action_in);
                              
      /* Disable current BUI and BUR triggers. Can delete later. */
      EXECUTE IMMEDIATE 
         'ALTER TRIGGER ' || tables_in (indx) || '_BIR DISABLE';
      EXECUTE IMMEDIATE
         'ALTER TRIGGER ' || tables_in (indx) || '_BUR DISABLE'; 
   END LOOP;
END;
/
After I ran this block, I had 7 new tables, 7 new packages and 7 new triggers in my schema. I didn't write any of that code and will likely never look at it or change it. 

Now, whenever a row was inserted, changed or deleted in those tables, a row is inserted into the "generic" AUDIT_HEADER table and another row is inserted into the appropriate AUD$dg_table_name auditing table. And it works perfectly!

But the question then confronting me was: how do I view and work with this data? Clearly, the answer is to build some pages in the admin interface of the Dev Gym. 

The first and simplest step was to build an interactive report on the AUDIT_HEADER table. Here's my first pass, 100% default behavior:

Nice! But, wait....what does this tell me and what does it not tell me? I can now see when changes were made to the audited tables....but this "generic" table doesn't contain any of the really interesting data, such as which row in the audited table, and the values in that row before the change.

I'd really like to:
  • see the primary key value of the row that was affected. This will make it easier, for example, to filter the above report to show all changes to, say, user 4750.
  • view all the data for that row in the specific audit table
  • edit the affected row using an existing edit page in the Dev Gym
I'm going to show you (the interesting parts of) how I went about this, because it wasn't immediately obvious to me how to make it all work. 

There is a not-so-interesting part I will mention first: I had to create a display-only form for each of the AUD$ tables (such as AUD$DG_CLASSES). That's not very interesting because APEX makes it so, so VERY easy to do this: create a page of type form, based it on the table of interest, select the columns you want to see. After the page is created, change items to display only, remove ability to insert and change. It's tedious, but not very - compared to the effort you'd have to make if you building your UI in - gasp - JavaScript. So consider all that done. :-)

Now, here's the enhanced report:

The link on the aud$id column (primary key in AUDIT_HEADER table) takes me to a page for the audit table for the specific table name (e.g., if DG_CLASSES then go to the form for AUD$DG_CLASSES).

The link on the key value takes me to an existing page that edits the entity, such as the Class Editor page.

So that's what I want....but for those of you familiar with defining links on report columns, you might have a sense of the challenge. I cannot hard-code the page number for either of those links. They depend on the table name in the header row, which means it will/might change for each row. So my link specifications needs to be well, rather dynamic. Here's one of them:
The "#" indicates that these are columns from the report.  Talk about dynamic!

And I'll need even more than that. To drill down to the edit page for an entity, I need to figure out (a) the name of the primary key column for a given table and the primary key value for a given audit header row. 

So let's take a look at the query underlying the report, and then the code I reference in that query.

SELECT aud$tstamp,
       aud$id,
       table_name,
       dg_audit_util.audit_key_from_header (aud$id) key_value,
       dml,
       descr,
       action,
       client_id,
       HOST,
       module,
       os_user,
       form_page pagenum,
       'P' || form_page || '_AUD_ID' pageitem,
       edit_page keypage,
       'P' || edit_page || '_' || audit_util.key_colname_from_header (aud$id)
          keypageitem
  FROM (SELECT a.*,
               dg_audit_util.form_page (table_name)
                  form_page,
               dg_audit_util.edit_page (table_name)
                  edit_page
          FROM audit_header a)

Here's the package specification:

PACKAGE dg_audit_util
IS
   FUNCTION form_page (table_name_in IN VARCHAR2)
      RETURN INTEGER;

   FUNCTION edit_page (table_name_in IN VARCHAR2)
      RETURN INTEGER;

   FUNCTION audit_key_from_header (aud$id_in IN NUMBER)
      RETURN INTEGER;

   FUNCTION key_colname_from_header (aud$id_in IN NUMBER)
      RETURN VARCHAR2;
END;

Here's the package body. Actually I don't think the details are all that interesting. In the first two functions I am simply encapsulating the lists of relevant page numbers. The key_colname_from_header "reverse engineers" my naming convention which is that (a) table names include an application prefix, are plural and in most cases that means simply added an "S" to the entity name, (b) primary key column name is table name singular form (entity name) with "_ID" added to it. 

So...an example:

Entity = User

Table name = QDB_USERS

Primary key = USER_ID

But then of course there are exceptions like "DG_CLASSES" and "QDB_QUIZZES," which must be handled by special case.

Entity = Class

Table name = DG_CLASSES

Primary key = CLASS_ID

The audit_key_from_header gets the primary key value from the row in the per-entity audit table (e.g., AUD$DG_CLASSES)

PACKAGE BODY dg_audit_util
IS
   FUNCTION form_page (table_name_in IN VARCHAR2)
      RETURN INTEGER
   IS
   BEGIN
      RETURN CASE table_name_in
                WHEN 'DG_CLASSES' THEN 75
                WHEN 'QDB_COMPEV_ANSWERS' THEN 84
                WHEN 'QDB_USERS' THEN 90
                WHEN 'DG_STUDENTS' THEN 92
                WHEN 'DG_USER_WORKOUTS' THEN 101
                WHEN 'QDB_COMP_EVENTS' THEN 93
                ELSE 106
             END;
   END;

   FUNCTION edit_page (table_name_in IN VARCHAR2)
      RETURN INTEGER
   IS
   BEGIN
      RETURN CASE table_name_in
                WHEN 'DG_CLASSES' THEN 35
                WHEN 'QDB_COMPEV_ANSWERS' THEN 3
                WHEN 'QDB_USERS' THEN 109
                WHEN 'DG_STUDENTS' THEN NULL
                WHEN 'DG_USER_WORKOUTS' THEN NULL
                WHEN 'QDB_COMP_EVENTS' THEN 54
                ELSE 106
             END;
   END;

   FUNCTION key_colname_from_header (aud$id_in IN NUMBER)
      RETURN VARCHAR2
   IS
      l_name       VARCHAR2 (1000);
      l_col_name   VARCHAR2 (1000);
   BEGIN
      SELECT table_name
        INTO l_name
        FROM audit_header
       WHERE aud$id = aud$id_in;

      l_col_name :=
         CASE l_name
            WHEN 'DG_CLASSES'
            THEN
               'CLASS_ID'
            WHEN 'QDB_RESULT_CHOICES'
            THEN
               'RESULT_ID'
            ELSE
               SUBSTR (l_name,
                       INSTR (l_name, '_') + 1,
                       LENGTH (l_name) - INSTR (l_name, '_') - 1)
               || '_ID'
         END;
      RETURN l_col_name;
   END;

   FUNCTION audit_key_from_header (aud$id_in IN NUMBER)
      RETURN INTEGER
   IS
      l_name   VARCHAR2 (1000);
      l_key    INTEGER;
   BEGIN
      SELECT table_name
        INTO l_name
        FROM audit_header
       WHERE aud$id = aud$id_in;

      EXECUTE IMMEDIATE
            'select '
         || key_colname_from_header (aud$id_in)
         || ' from aud$'
         || l_name
         || ' where aud$id = :id'
         INTO l_key
         USING aud$id_in;

      RETURN l_key;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         RETURN NULL;
   END;
END;

Comments

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel