Subscribe

Share

Database, SQL and PL/SQL

A Fresh Look at Auditing Row Changes

Triggers can provide auditing information, but there’s a future in flashback.

By Connor McDonald

March/April 2016

Barbara is looking forward to a long-overdue vacation. She’s been working extremely hard to get a suite of new features for the company’s human resources (HR) application through construction, testing, and finally deployment into the production environment. Satisfaction ratings from the HR department are high, and no serious bugs have been reported.

But Barbara’s vacation plans are about to be stymied. As often happens in IT projects, some requirements get pushed to the side during the construction phase, with the intent of tackling them later. Now, an email titled “Urgent: Need to capture all row changes for audit compliance” arrives in Barbara’s inbox. Audit functions, although important from a security and compliance perspective, are typically invisible to application stakeholders and are hardly the exciting part of any functionality showcase. So the auditing requirements for Barbara’s application enhancements were pushed aside early in the construction phase, only to resurface as an emergency postimplementation requirement.

Fortunately for Barbara, auditing row changes is a common need throughout the IT industry. A quick web search reveals the most prevalent solution: for each table in her application, there should be a partnering audit table to hold the history of row changes, plus some additional metadata.

For example, for the EMPLOYEES table in Barbara’s application:

SQL> desc EMPLOYEES
Name                          Null?    Type
————————————————————————————— ———————— ————————————
EMPLOYEE_ID                            NUMBER(6)
FIRST_NAME                             VARCHAR2(20)
LAST_NAME                     NOT NULL VARCHAR2(25)
EMAIL                         NOT NULL VARCHAR2(25)
PHONE_NUMBER                           VARCHAR2(20)
HIRE_DATE                     NOT NULL DATE
JOB_ID                        NOT NULL VARCHAR2(10)
SALARY                                 NUMBER(8,2)
MANAGER_ID                             NUMBER(6)
DEPARTMENT_ID                          NUMBER(4)
VACATION_BALANCE                       NUMBER(6,2)

there will be an appropriately named equivalent table for capturing any row changes:

SQL> desc AUDIT_EMPLOYEES
Name                          Null?    Type
————————————————————————————— ———————— ————————————
AUD_WHO                                VARCHAR2(20)
AUD_WHEN                               DATE
AUD_OPERATION                          VARCHAR2(1)
AUD_MODULE                             VARCHAR2(30)
EMPLOYEE_ID                            NUMBER(6)
FIRST_NAME                             VARCHAR2(20)
LAST_NAME                     NOT NULL VARCHAR2(25)
EMAIL                         NOT NULL VARCHAR2(25)
PHONE_NUMBER                           VARCHAR2(20)
HIRE_DATE                     NOT NULL DATE
JOB_ID                        NOT NULL VARCHAR2(10)
SALARY                                 NUMBER(8,2)
MANAGER_ID                             NUMBER(6)
DEPARTMENT_ID                          NUMBER(4)
VACATION_BALANCE                       NUMBER(6,2)

Some additional metadata columns are present in the AUDIT_EMPLOYEES table for capturing who performed the change, when the change occurred, what operation was performed (INSERT, UPDATE, or DELETE), and from which program or module the change originated. Barbara’s application is already live in the production environment, so the function for populating the AUDIT_EMPLOYEES table must be transparent to the existing code. Thus, database triggers are the natural choice.

Listing 1 shows the trigger Barbara has built to capture all changes to the EMPLOYEES table.

Code Listing 1: Audit data capture trigger for EMPLOYEES

SQL> create or replace
  2  trigger TRG_AUDIT_EMPLOYEES
  3  after insert or update or delete on EMPLOYEES
  4  for each row
  5  declare
  6    l_operation varchar2(1) :=
  7        case when updating then 'U'
  8             when deleting then 'D'
  9             else 'I' end;
 10  begin
 11    if updating or inserting then
 12        insert into AUDIT_EMPLOYEES
 13          (aud_who
 14          ,aud_when
 15          ,aud_operation
 16          ,aud_module
 17          ,employee_id
 18          ,first_name
 19          ,last_name
 20          ,email
 21          ,phone_number
 22          ,hire_date
 23          ,job_id
 24          ,salary
 25          ,commission_pct
 26          ,manager_id
 27          ,department_id)
 28        values
 29          (user
 30          ,sysdate
 31          ,l_operation
 32          ,sys_context('USERENV','MODULE')
 33          ,:new.employee_id
 34          ,:new.first_name
 35          ,:new.last_name
 36          ,:new.email
 37          ,:new.phone_number
 38          ,:new.hire_date
 39          ,:new.job_id
 40          ,:new.salary
 41          ,:new.commission_pct
 42          ,:new.manager_id
 43          ,:new.department_id);
 44    else
 45        insert into AUDIT_EMPLOYEES
 46          (aud_who
 47          ,aud_when
 48          ,aud_operation
 49          ,aud_module
 50          ,employee_id
 51          ,first_name
 52          ,last_name
 53          ,email
 54          ,phone_number
 55          ,hire_date
 56          ,job_id
 57          ,salary
 58          ,commission_pct
 59          ,manager_id
 60          ,department_id)
 61        values
 62          (user
 63          ,sysdate
 64          ,l_operation
 65          ,sys_context('USERENV','MODULE')
 66          ,:old.employee_id
 67          ,:old.first_name
 68          ,:old.last_name
 69          ,:old.email
 70          ,:old.phone_number
 71          ,:old.hire_date
 72          ,:old.job_id
 73          ,:old.salary
 74          ,:old.commission_pct
 75          ,:old.manager_id
 76          ,:old.department_id);
 77    end if;
 78  end;
 79  /
Trigger created.

A quick series of tests confirms the correct operation of the trigger, so Barbara forges ahead and creates auditing tables and associated triggers for all the tables in the HR application.

Hidden Costs

Barbara’s vacation plans remind her that one of the tasks performed each night on the EMPLOYEES table is an update of the vacation balance for all employees in the organization. Before her auditing changes went live in the production environment, this task was efficient—it had to be, because it temporarily locks all rows in the EMPLOYEES table.

SQL> update EMPLOYEES
  2  set    VACATION_BALANCE = VACATION_BALANCE + 1;
219136 rows updated.
Elapsed: 00:00:03.01

A few seconds is an acceptable lock time. In the rare instance that anyone updates a single employee record outside of business hours, that person would experience only a slight pause—nothing that would be cause for alarm. But now that Barbara’s auditing changes have been implemented, a problem has surfaced. The vacation adjustment takes significantly longer:

SQL> update EMPLOYEES
  2  set    VACATION_BALANCE = VACATION_BALANCE + 1;
219136 rows updated.
Elapsed: 00:00:17.92

Barbara performs a trace on the vacation update to gauge the impact of auditing. Listing 2 shows the TKprof-formatted output of the trace file.

Code Listing 2: TKprof output from trace of vacation adjustment

update EMPLOYEES
set    VACATION_BALANCE = VACATION_BALANCE + 1
call     count       cpu   elapsed       disk      query    current       rows
——————— —————— ———————— —————————— —————————— —————————— —————————— ——————————
Parse        1     0.00       0.00          0          1          0          0
Execute      1    15.61      17.47          0       2380     223618     219136
Fetch        0     0.00       0.00          0          0          0          0
——————— —————— ———————— —————————— —————————— —————————— —————————— ——————————
total        2    15.61      17.48          0       2381     223618     219136
INSERT INTO AUDIT_EMPLOYEES (AUD_WHO ,AUD_WHEN ,AUD_OPERATION ,AUD_MODULE ,
  EMPLOYEE_ID ,FIRST_NAME ,LAST_NAME ,EMAIL ,PHONE_NUMBER ,HIRE_DATE ,JOB_ID ,
  SALARY ,MANAGER_ID ,DEPARTMENT_ID ,VACATION_BALANCE) 
VALUES
 (USER ,SYSDATE ,:B12 ,SYS_CONTEXT('USERENV','MODULE') ,:B1 ,:B2 ,:B3 ,:B4 ,
  :B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 )
call     count      cpu    elapsed       disk      query    current       rows
——————— —————— ———————— —————————— —————————— —————————— —————————— ——————————
Parse        1     0.00       0.00          0          0          0          0
Execute 219136     8.60       8.71          0       5865     245856     219136
Fetch        0     0.00       0.00          0          0          0          0
——————— —————— ———————— —————————— —————————— —————————— —————————— ——————————
total   219137     8.60       8.71          0       5865     245856     219136

From the trace file, Barbara observes that the INSERT statement for the audit table added 8.71 seconds of elapsed time to the process. It was called 219,136 times—once for every changed row, even though only a single UPDATE statement was issued. This also incurs the same number of context switches between the SQL and PL/SQL engines, further adding to the overall elapsed time. Barbara’s auditing changes are not as transparent to the application as they initially appeared to be. She needs a fix…fast.

To reduce the context switching, Barbara must refactor the audit trigger code to use bulk binding. This is not a trivial undertaking, because row changes need to be buffered as they occur and then applied in one pass after the issuing data manipulation language (DML) is complete. In earlier versions of Oracle Database, this would mandate a PACKAGE definition along with three separate triggers. But as of Oracle Database 11g, a single compound trigger can be used to provide the required functionality.

Listing 3 shows the compound trigger Barbara built to improve the efficiency of her audit capture function.

Code Listing 3: Compound trigger for audit capture

SQL> create or replace
  2  trigger TRG_AUDIT_EMPLOYEES
  3  for insert or update or delete
  4  on EMPLOYEES compound trigger
  5
  6  --
  7  -- an array structure to buffer all the row changes
  8  --
  9    type t_row_list is
 10      table of AUDIT_EMPLOYEES%rowtype index by pls_integer;
 11
 12    l_audit_rows      t_row_list;
 13
 14    l_operation varchar2(1) :=
 15        case when updating then 'U'
 16             when deleting then 'D'
 17             else 'I' end;
 18
 19  before statement is
 20  begin
 21    --
 22    -- initialize the array
 23    --
 24    l_audit_rows.delete;
 25  end before statement;
 26
 27  after each row is
 28  begin
 29    --
 30    -- at row level, capture all the changes into the array
 31    --
 32    l_audit_rows(l_audit_rows.count+1).aud_who     := sys_context('USERENV','SESSION_USER');
 33    l_audit_rows(l_audit_rows.count).aud_when      := sysdate;
 34    l_audit_rows(l_audit_rows.count).aud_operation := l_operation;
 35    l_audit_rows(l_audit_rows.count).aud_module    := sys_context('USERENV','MODULE');
 36
 37    if updating or inserting then
 38        l_audit_rows(l_audit_rows.count).employee_id      := :new.employee_id;
 39        l_audit_rows(l_audit_rows.count).first_name       := :new.first_name;
 40        l_audit_rows(l_audit_rows.count).last_name        := :new.last_name;
 41        l_audit_rows(l_audit_rows.count).email            := :new.email;
 42        l_audit_rows(l_audit_rows.count).phone_number     := :new.phone_number;
 43        l_audit_rows(l_audit_rows.count).hire_date        := :new.hire_date;
 44        l_audit_rows(l_audit_rows.count).job_id           := :new.job_id;
 45        l_audit_rows(l_audit_rows.count).salary           := :new.salary;
 46        l_audit_rows(l_audit_rows.count).manager_id       := :new.manager_id;
 47        l_audit_rows(l_audit_rows.count).department_id    := :new.department_id;
 48        l_audit_rows(l_audit_rows.count).vacation_balance := :new.vacation_balance;
 49    else
 50        l_audit_rows(l_audit_rows.count).employee_id      := :old.employee_id;
 51        l_audit_rows(l_audit_rows.count).first_name       := :old.first_name;
 52        l_audit_rows(l_audit_rows.count).last_name        := :old.last_name;
 53        l_audit_rows(l_audit_rows.count).email            := :old.email;
 54        l_audit_rows(l_audit_rows.count).phone_number     := :old.phone_number;
 55        l_audit_rows(l_audit_rows.count).hire_date        := :old.hire_date;
 56        l_audit_rows(l_audit_rows.count).job_id           := :old.job_id;
 57        l_audit_rows(l_audit_rows.count).salary           := :old.salary;
 58        l_audit_rows(l_audit_rows.count).manager_id       := :old.manager_id;
 59        l_audit_rows(l_audit_rows.count).department_id    := :old.department_id;
 60        l_audit_rows(l_audit_rows.count).vacation_balance := :old.vacation_balance;
 61    end if;
 62  end after each row;
 63
 64  after statement is
 65  begin
 66    --
 67    -- then at completion, do a single insert of all the rows into our audit table
 68    --
 69
 70    forall i in 1 .. l_audit_rows.count
 71       insert into AUDIT_EMPLOYEES
 72       values l_audit_rows(i);
 73    l_audit_rows.delete;
 74  end after statement;
 75
 76  end;
 77  /
Trigger created.

Barbara re-examines the performance of the vacation adjustment with her new trigger in place.

SQL> update EMPLOYEES
  2  set    VACATION_BALANCE = VACATION_BALANCE + 1;
219136 rows updated.
Elapsed: 00:00:04.01

Her audit capture now imposes dramatically reduced overhead. A trace of the process confirms the benefit of bulk binding in this instance. Listing 4 shows that the number of calls to INSERT into the AUDIT_EMPLOYEES table has shrunk from more than 200,000 down to the optimal value, 1.

Code Listing 4: TKprof output from trace of vacation adjustment, with new compound trigger

update EMPLOYEES
set    VACATION_BALANCE = VACATION_BALANCE + 1
call     count      cpu    elapsed       disk      query    current       rows
——————— —————— ———————— —————————— —————————— —————————— —————————— ——————————
Parse        1     0.01       0.00          0          1          0          0
Execute      1     3.41       3.47          0       2365     224004     219136
Fetch        0     0.00       0.00          0          0          0          0
——————— —————— ———————— —————————— —————————— —————————— —————————— ——————————
total        2     3.43       3.47          0       2366     224004     219136
INSERT INTO AUDIT_EMPLOYEES 
VALUES
 (:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 ,:B13 ,:B14 ,
  :B15 ) 
call     count      cpu    elapsed       disk      query    current       rows
——————— —————— ———————— —————————— —————————— —————————— —————————— ——————————
Parse        1     0.00       0.00          0          0          0          0
Execute      1     0.43       0.51          0       3306      21864     219136
Fetch        0     0.00       0.00          0          0          0          0
——————— —————— ———————— —————————— —————————— —————————— —————————— ——————————
total        2     0.43       0.51          0       3306      21864     219136

Barbara’s auditing enhancements to the HR application are back on track, as are her vacation plans. Still, Barbara has a nagging concern about the amount of potential code maintenance overhead she has introduced. In the future, every time a structural change is made to the HR application, the auditing triggers will need to be refactored to accommodate that change. And Barbara’s fix does not address another potential pitfall with the trigger-based solution: although bulk binding improves performance and resolves the context-switching overhead, it also introduces the risk of excessive memory consumption. Because all rows changed by the issuing DML are buffered before being processed at statement completion, a significant amount of session memory can be consumed. This could strain the database server—or, worse, it could crash the application session with “ORA-4030: Out of process memory” errors.

Listing 5 shows the memory consumption Barbara’s bulk binding incurs when the vacation adjustment runs. The session requires more than 160 MB of program global area (PGA) memory. This may be acceptable for a single session running a nightly process such as the vacation update, but it obviously would not scale to a scenario where hundreds or thousands of sessions have a similar requirement.

Code Listing 5: Session memory consumption for the vacation adjustment with bulk binding

--
-- First we check the current ceiling of PGA memory before 
-- the vacation adjustment
--
SQL> select st.sid, s.name, st.value
  2  from v$statname s, v$sesstat st
  3  where st.statistic# = s.statistic#
  4  and st.sid = sys_context('USERENV','SID')
  5  and s.name = 'session pga memory max';
       SID NAME                                                    VALUE
—————————— ————————————————————————————————————————————————————— ———————
         6 session pga memory max                                2508344
1 row selected.
SQL> update EMPLOYEES
  2  set    VACATION_BALANCE = VACATION_BALANCE + 1;
219136 rows updated.
--
-- Then we check the same statistics once the adjustment has completed
--
SQL> select st.sid, s.name, st.value
  2  from v$statname s, v$sesstat st
  3  where st.statistic# = s.statistic#
  4  and st.sid = sys_context('USERENV','SID')
  5  and s.name = 'session pga memory max';
       SID NAME                                                    VALUE
—————————— ————————————————————————————————————————————————————— ———————
         6 session pga memory max                              164430744

To obviate that risk, the trigger can be adjusted to perform bulk binding in batches throughout the DML processing, as shown in Listing 6. But with both code maintenance overhead and complexity increasing, Barbara is keen to find a simpler solution.

Code Listing 6: Trigger to keep PGA consumption in check, still using bulk binding

SQL> create or replace
  2  trigger TRG_AUDIT_EMPLOYEES
  3  for insert or update or delete
  4  on EMPLOYEES compound trigger
  5
  6  --
  7  -- an array structure to buffer all the row changes
  8  --
  9    type t_row_list is
 10      table of AUDIT_EMPLOYEES%rowtype index by pls_integer;
 11
 12    l_audit_rows      t_row_list;
 13
 14    l_operation varchar2(1) :=
 15        case when updating then 'U'
 16             when deleting then 'D'
 17             else 'I' end;
 18
 19    procedure insert_logged_so_far is
 20    begin
 21      forall i in 1 .. l_audit_rows.count
 22         insert into AUDIT_EMPLOYEES
 23         values l_audit_rows(i);
 24      l_audit_rows.delete;
 25    end;
 26
 27  before statement is
 28  begin
 29    --
 30    -- initialize the array
 31    --
 32    l_audit_rows.delete;
 33  end before statement;
 34
 35  after each row is
 36  begin
 37    --
 38    -- at row level, capture all the changes into the array
 39    --
 40    l_audit_rows(l_audit_rows.count+1).aud_who     := sys_context('USERENV','SESSION_USER');
 41    l_audit_rows(l_audit_rows.count).aud_when      := sysdate;
 42    l_audit_rows(l_audit_rows.count).aud_operation := l_operation;
 43    l_audit_rows(l_audit_rows.count).aud_module    := sys_context('USERENV','MODULE');
 44
 45    if updating or inserting then
 46        l_audit_rows(l_audit_rows.count).employee_id      := :new.employee_id;
 47        l_audit_rows(l_audit_rows.count).first_name       := :new.first_name;
 48        l_audit_rows(l_audit_rows.count).last_name        := :new.last_name;
 49        l_audit_rows(l_audit_rows.count).email            := :new.email;
 50        l_audit_rows(l_audit_rows.count).phone_number     := :new.phone_number;
 51        l_audit_rows(l_audit_rows.count).hire_date        := :new.hire_date;
 52        l_audit_rows(l_audit_rows.count).job_id           := :new.job_id;
 53        l_audit_rows(l_audit_rows.count).salary           := :new.salary;
 54        l_audit_rows(l_audit_rows.count).manager_id       := :new.manager_id;
 55        l_audit_rows(l_audit_rows.count).department_id    := :new.department_id;
 56        l_audit_rows(l_audit_rows.count).vacation_balance := :new.vacation_balance;
 57    else
 58        l_audit_rows(l_audit_rows.count).employee_id      := :old.employee_id;
 59        l_audit_rows(l_audit_rows.count).first_name       := :old.first_name;
 60        l_audit_rows(l_audit_rows.count).last_name        := :old.last_name;
 61        l_audit_rows(l_audit_rows.count).email            := :old.email;
 62        l_audit_rows(l_audit_rows.count).phone_number     := :old.phone_number;
 63        l_audit_rows(l_audit_rows.count).hire_date        := :old.hire_date;
 64        l_audit_rows(l_audit_rows.count).job_id           := :old.job_id;
 65        l_audit_rows(l_audit_rows.count).salary           := :old.salary;
 66        l_audit_rows(l_audit_rows.count).manager_id       := :old.manager_id;
 67        l_audit_rows(l_audit_rows.count).department_id    := :old.department_id;
 68        l_audit_rows(l_audit_rows.count).vacation_balance := :old.vacation_balance;
 69    end if;
 70
 71    --
 72    -- bulk bind every 1000 rows to keep the memory down
 73    --
 74    if l_audit_rows.count > 1000 then
 75      insert_logged_so_far;
 76    end if;
 77  end after each row;
 78
 79  after statement is
 80  begin
 81    --
 82    -- then at completion, pick up the remaining rows
 83    --
 84    if l_audit_rows.count > 0 then
 85      insert_logged_so_far;
 86    end if;
 87  end after statement;
 88
 89  end;
 90  /
Trigger created.
SQL> select st.sid, s.name, st.value
  2  from v$statname s, v$sesstat st
  3  where st.statistic# = s.statistic#
  4  and st.sid = sys_context('USERENV','SID')
  5  and s.name = 'session pga memory max';
       SID NAME                                                    VALUE
—————————— ————————————————————————————————————————————————————— ———————
         6 session pga memory max                                2508344
1 row selected.
SQL> update EMPLOYEES
  2  set    VACATION_BALANCE = VACATION_BALANCE + 1;
219136 rows updated.
Elapsed: 00:00:05.31
SQL> select st.sid, s.name, st.value
  2  from v$statname s, v$sesstat st
  3  where st.statistic# = s.statistic#
  4  and st.sid = sys_context('USERENV','SID')
  5  and s.name = 'session pga memory max';
       SID NAME                                                    VALUE
—————————— ————————————————————————————————————————————————————— ———————
         6 session pga memory max                                2508344
1 row selected.
A Better Way

The use of triggers for capturing row changes is common throughout the Oracle application development community. But few developers display Barbara’s diligence in ensuring that bulk binding caters to multirow DML statements, and even fewer explore potential alternatives.

Barbara peruses the Oracle Database Advanced Application Developer’s Guide and comes across Oracle Flashback technology. The Flashback Query feature catches her eye: “Oracle Flashback Query retrieves data as it existed at some time in the past.” Since any time could be nominated, Barbara infers that it should be doable to see every possible state of a database table, which sounds very much like the auditing requirement she has just implemented. In the same section of the documentation, she also discovers Oracle Flashback’s Oracle Flashback Data Archive feature: “Flashback Data Archive provides the ability to track and store all transactional changes to a table over its lifetime. It is no longer necessary to build this intelligence into your application. A Flashback Data Archive is useful for compliance with record stage policies and audit reports.” This sounds like a perfect match for Barbara’s audit needs, so she builds a Flashback Data Archive test case to examine its features.

First she allocates some storage to hold her archive data:

SQL> create tablespace SPACE_FOR_ARCHIVE
  2  datafile 'C:\ORACLE\DB12\ARCH_SPACE.DBF' 
  3  size 50G;
Tablespace created.

Next, she creates a new structure, called a flashback archive, which defines the retention duration of the row change history for any table placed into it.

SQL> CREATE FLASHBACK ARCHIVE longterm
  2  TABLESPACE space_for_archive
  3  RETENTION 1 YEAR;
Flashback archive created

And then she simply associates her HR tables with the flashback archive.

SQL> ALTER TABLE EMPLOYEES FLASHBACK ARCHIVE LONGTERM;
Table altered.

“So far, so good,” thinks Barbara. However, it is not immediately apparent where the audited row changes are located or how she is meant to access them. Listing 7 shows that some new objects have been created within Barbara’s schema. Their structure suggests they are related to Flashback Data Archive in some way, but she finds it unlikely that she’s supposed to query those tables directly.

Code Listing 7: Tables created to support Flashback Data Archive

SQL> select * from tab;
TNAME                                    TABTYPE CLUSTERID
———————————————————————————————————————— ——————— —————————
EMPLOYEES                                TABLE
SYS_FBA_DDL_COLMAP_102596                TABLE
SYS_FBA_HIST_102596                      TABLE
SYS_FBA_TCRV_102596                      TABLE
SQL> desc SYS_FBA_DDL_COLMAP_102596
 Name                          Null?    Type
 ————————————————————————————— ———————— ——————————————————
 STARTSCN                               NUMBER
 ENDSCN                                 NUMBER
 XID                                    RAW(8)
 OPERATION                              VARCHAR2(1)
 COLUMN_NAME                            VARCHAR2(255)
 TYPE                                   VARCHAR2(255)
 HISTORICAL_COLUMN_NAME                 VARCHAR2(255)
SQL> desc SYS_FBA_HIST_102596
 Name                          Null?    Type
 ————————————————————————————— ———————— ——————————————————
 RID                                    VARCHAR2(4000)
 STARTSCN                               NUMBER
 ENDSCN                                 NUMBER
 XID                                    RAW(8)
 OPERATION                              VARCHAR2(1)
 EMPLOYEE_ID                            NUMBER(6)
 FIRST_NAME                             VARCHAR2(20)
 LAST_NAME                              VARCHAR2(25)
 EMAIL                                  VARCHAR2(25)
 PHONE_NUMBER                           VARCHAR2(20)
 HIRE_DATE                              DATE
 JOB_ID                                 VARCHAR2(10)
 SALARY                                 NUMBER(8,2)
 MANAGER_ID                             NUMBER(6)
 DEPARTMENT_ID                          NUMBER(4)
 VACATION_BALANCE                       NUMBER(6,2)
SQL> desc SYS_FBA_TCRV_102596
 Name                          Null?    Type
 ————————————————————————————— ———————— ——————————————————
 RID                                    VARCHAR2(4000)
 STARTSCN                               NUMBER
 ENDSCN                                 NUMBER
 XID                                    RAW(8)
 OP                                     VARCHAR2(1)

To determine whether the flashback archive is working as expected, Barbara tries a test query examining the EMPLOYEES table at a past point in time. The execution plan in Listing 8 confirms her hypothesis that the new tables that were created are related to Flashback Data Archive and are accessing historical data.

Code Listing 8: Execution plan for tables associated with Flashback Data Archive

SQL> set autotrace traceonly explain
SQL> select * from EMPLOYEES AS OF TIMESTAMP SYSDATE-3;
Execution Plan
—————————————————————————————————————————————————————————————————
| Id  | Operation                 | Name                | Rows  |
—————————————————————————————————————————————————————————————————
|   0 | SELECT STATEMENT          |                     |  9399 |
|   1 |  VIEW                     |                     |  9399 |
|   2 |   UNION-ALL               |                     |       |
|*  3 |    FILTER                 |                     |       |
|   4 |     PARTITION RANGE SINGLE|                     |  1054 |
|*  5 |      TABLE ACCESS FULL    | SYS_FBA_HIST_102596 |  1054 |
|*  6 |    FILTER                 |                     |       |
|   7 |     MERGE JOIN OUTER      |                     |  8345 |
|   8 |      SORT JOIN            |                     |   203 |
|*  9 |       TABLE ACCESS FULL   | EMPLOYEES           |   203 |
|* 10 |      SORT JOIN            |                     |   230K|
|* 11 |       TABLE ACCESS FULL   | SYS_FBA_TCRV_102596 |   230K|
—————————————————————————————————————————————————————————————————

Seeing a point-in-time state of the EMPLOYEES table is useful, but Barbara needs more than that. She needs to be able to see every point-in-time state of a row in the table to build up a history of changes and satisfy her audit requirements. With each update to the table, a new “version” of a row comes into existence, and Barbara needs to list every one of those versions. Having reviewed Oracle Flashback technology features in the documentation, Barbara knows that she can use the Flashback Version Query feature of Oracle Flashback to achieve this. Listing 9 shows how the VERSIONS BETWEEN syntax lists all the historical versions of a single employee record (EMPLOYEE_ID = 100) and how the VERSIONS_STARTTIME and VERSIONS_OPERATION pseudocolumns can be used to track the transaction time and operation performed on each row.

Code Listing 9: Flashback Version Query

SQL> select EMPLOYEE_ID, FIRST_NAME, JOB_ID, VACATION_BALANCE,
  2         VERSIONS_STARTTIME TS,
  3         nvl(VERSIONS_OPERATION,'I') OP
  4  from EMPLOYEES
  5  versions between timestamp  timestamp '2016-01-11 08:20:00' and systimestamp
  6  where EMPLOYEE_ID = 100
  7  order by EMPLOYEE_ID, ts;
EMPLOYEE_ID FIRST_NAME         JOB_ID     VACATION_BALANCE TS                                  O
——————————— —————————————————— —————————— ———————————————— ——————————————————————————————————— —
        100 Steven             AD_PRES               45.76 11-JAN-16 08.27.55.000000000 AM     U
        100 Steven             AD_PRES               46.76 12-JAN-16 08.28.07.000000000 AM     U
        100 Steven             AD_PRES               47.76 13-JAN-16 08.53.56.000000000 AM     U
        100 Steven             AD_PRES               48.76 14-JAN-16 08.53.56.000000000 AM     U
        100 Steven             AD_PRES               49.76 15-JAN-16 08.53.56.000000000 AM     U

Barbara is excited about her progress. She can now list a history of row changes for the EMPLOYEES table, and no triggers are required. The vacation update routine will run at the same swift speed as it did before her audit changes were implemented.

But there is still an outstanding issue. Barbara’s original trigger-based audit implementation picked up additional metadata—namely, who did the change and what program was being executed. That data is not present in the Flashback Data Archive tables. Barbara doesn’t want to add those metadata columns to each of the base tables in her application, because that would break a primary implementation requirement for auditing—that it should be transparent to the existing application.

Luckily for Barbara, her application is deployed on Oracle Database 12c. Flashback Data Archive has been extended to enable the capture of context-level information automatically and without changes to the base tables. Barbara contacts her administrator and requests context-level tracking to be enabled for Flashback Data Archive:

SQL> exec dbms_flashback_archive.set_context_level('ALL');
PL/SQL procedure successfully completed.

Now, when rows are inserted, updated, or deleted in the tables Barbara has associated with a flashback archive, any context information in the standard USERENV context (as well as any custom user-defined contexts) will be captured for each transaction. From her trigger-based audit implementation, Barbara knows that she needs the MODULE and SESSION_USER attributes from the USERENV context. She performs some sample DMLs against the EMPLOYEES table and then checks to ensure that the context-level metadata is being collected. Listing 10 shows that the capture of context information for each transaction is working and Barbara’s implementation of the audit requirement with Flashback Data Archive is complete.

Code Listing 10: Flashback Version Query with context information

SQL> select EMPLOYEE_ID, FIRST_NAME, VACATION_BALANCE,
  2         VERSIONS_STARTTIME TS,
  3         dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','SESSION_USER') AS who,
  4         dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','MODULE') AS program
  5  from EMPLOYEES
  6  versions between timestamp  timestamp '2016-01-11 08:20:00' and systimestamp
  7  where EMPLOYEE_ID = 100
  8  order by EMPLOYEE_ID, ts;
EMPLOYEE_ID FIRST_NAME   VACATION_BALANCE TS                     WHO          PROGRAM
——————————— ———————————— ———————————————— —————————————————————— ———————————— ————————
        100 Steven                  52.76 11-JAN-16 10.04.03     BARBARA      SQL*Plus
        100 Steven                  51.76 11-JAN-16 11.14.03     BARBARA      SQL*Plus
        100 Steven                  53.76 11-JAN-16 12.05.06     BARBARA      SQL*Plus
        100 Steven                  54.76 11-JAN-16 13.01.06     BARBARA      SQL*Plus
        100 Steven                  55.76 11-JAN-16 14.23.09     BARBARA      SQL*Plus

Although Barbara initially implemented the auditing requirements with a trigger-based approach, she does not need to abandon the audited row changes she has already collected. Using the DBMS_FLASHBACK_ARCHIVE package, she can import her captured audit changes into the new Flashback Data Archive tables and then drop her old auditing tables and triggers.

Conclusion

The use of triggers for auditing is common and is still often regarded as the only possible solution for capturing row changes on Oracle Database tables. However, in Oracle Database 11.2.0.4 and above, auditing requirements can now be met with Flashback Data Archive—a simple, more efficient, and more secure solution.

Next Steps

 DOWNLOAD Oracle Database 12c.

Photography by Scott Webb, Unsplash