Scenario: ExampleHMO using row and column access control - Secure triggers

Triggers defined on a table with row or column access control activated must be secure. Alex, the security administrator, discusses how Paul, a database developer at ExampleHMO, can create a secure trigger for his new accounting application.

Alex speaks to the accounting department and learns that an AFTER UPDATE trigger is needed for the PATIENT table. This trigger monitors the history of the ACCT_BALANCE column.

Alex explains to Paul, who has the necessary privileges to create the trigger, that any trigger defined on a row and column access protected table must be marked secure. Paul and Alex review the action of the new trigger and deem it to be secure.

ExampleHMO_ACCT_BALANCE_TRIGGER monitors the ACCT_BALANCE column in the PATIENT table. Every time that column is updated, the trigger is fired, and inserts the current account balance details into the ACCT_HISTORY table.

Paul creates the trigger:

CREATE TRIGGER HOSPITAL.NETHMO_ACCT_BALANCE_TRIGGER 
  AFTER UPDATE OF ACCT_BALANCE ON PATIENT 
  REFERENCING OLD AS O NEW AS N 
  FOR EACH ROW MODE DB2SQL SECURED 
  BEGIN  ATOMIC 
  INSERT INTO ACCT_HISTORY
  (SSN, BEFORE_BALANCE, AFTER_BALANCE, WHEN, BY_WHO) 
  VALUES(O.SSN, O.ACCT_BALANCE, N.ACCT_BALANCE,
  CURRENT TIMESTAMP, SESSION_USER); 
END;

John, from the accounting department, must update the account balance for the patient Bob whose SSN is '123-45-6789'.

John looks at the data for Bob before running the update:

SELECT ACCT_BALANCE FROM PATIENT WHERE SSN = '123-45-6789'; 

ACCT_BALANCE  
--------------
9.00

  1 record(s) selected.

SELECT * FROM ACCT_HISTORY WHERE SSN = '123-45-6789'; 

SSN         BEFORE_BALANCE AFTER_BALANCE  WHEN       BY_WHO              
----------- -------------- -------------- ---------- --------------------

  0 record(s) selected.

John then runs the update:

UPDATE PATIENT SET ACCT_BALANCE = ACCT_BALANCE * 0.9 WHERE SSN = '123-45-6789';

Since there is a trigger defined on the PATIENT table, the update fires the trigger. Since the trigger is defined SECURED, the update completes successfully. John looks at the data for Bob after running the update:

SELECT ACCT_BALANCE FROM PATIENT WHERE SSN = '123-45-6789'; 

ACCT_BALANCE  
--------------
8.10

  1 record(s) selected.

SELECT * FROM ACCT_HISTORY WHERE SSN = '123-45-6789'; 

SSN         BEFORE_BALANCE AFTER_BALANCE  WHEN       BY_WHO              
----------- -------------- -------------- ---------- --------------------
123-45-6789 9.00           8.10           2010-10-10 JOHN
  1 record(s) selected.