Creating triggers for tables with row and column access control

With the required authority and privileges, you can create triggers for tables that are activated for row and access control.

Before you begin

If the SEPARATE_SECURITY system parameter on panel DSNTIPP1 is set to YES during installation or migration, you must have the SECADM authority to create triggers for tables that are activated with row and column access control. If SEPARATE_SECURITY is set to NO, you must have the SECADM or SYSADM authority.

About this task

Suppose that table HOSPITAL.PATIENT is activated for row and column access control. The table contains columns to record a patient's social security number (SSN), account authorization ID (USERID), name (NAME), address (ADDRESS), pharmacy (PHARMCY), account balance (ACCT_BALANCE), and doctor (PCD_ID). Paul, a database developer, needs to create a new AFTER UPDATE trigger for HOSPITAL.PATIENT to monitor the history of the ACCT_BALANCE column.

Procedure

To create a trigger for a table that is enforced with row and access control:

Make sure that all operations on the transition variables and transition tables inside the new trigger body are secure.
Only secure triggers can be defined on tables that are already enforced with row and column access control. The SECURED attribute is required for a trigger when the associated table is row or column access control enforced or the associated view whose underlying table is enforced with row or column access control. If a trigger exists but is not secure, row or column access control cannot be activated for the associated table.

Make sure that all operations on the transition variables and transition tables inside the new trigger body are actually secure. Then, you can issue the following GRANT CREATE_SECURE_OBJECT statement to allow userid PAUL the privilege for creating secure triggers for table HOSPITAL.PATIENT:
```
GRANT CREATE_SECURE_OBJECT TO PAUL;

COMMIT;
```
Db2 records the grant in SYSUSERAUTH: GRANTOR = GRANTORID, GRANTEE = PAUL, AUTHHOWGOT = E, and CREATESECUREAUTH = Y. This means that authid GRANTORID has used the SECADM authority (AUTHHOWGOT = E) to grant userid PAUL the CREATE_SECURE_OBJECT privilege.

Create a new trigger for table HOSPITAL.PATIENT

With the CREATE_SECURE_OBJECT privilege, Paul can create a secure NETHMO_ACCT_BALANCE_TRIGGER by issuing the following CREATE TRIGGER statement:

Begin general-use programming interface information.

CREATE TRIGGER NETHMO_ACCT_BALANCE_TRIGGER NO CASCADE
    AFTER UPDATE OF ACCT_BALANCE ON HOSPITAL.PATIENT SECURED
    REFERENCING OLD AS O NEW AS N
    FOR EACH ROW MODE DB2SQL
    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!

COMMIT!

End general-use programming interface information.

Db2 inserts a new row into SYSIBM.SYSTRIGGERS with SYSTRIGGERS.SECURE = 'Y'. Db2 completes other catalog table updates for the trigger creation.

After trigger NETHMO_ACCT_BALANCE_TRIGGER is created, revoke the CREATE_SECURE_OBJECT privilege from userid PAUL by issuing the following REVOKE CREATE_SECURE_OBJECT statement:
```
REVOKE CREATE_SECURE_OBJECT FROM PAUL;

COMMIT;
```
Db2 completes the privilege removal by deleting the row with GRANTOR = GRANTORID, GRANTEE = PAUL, AUTHHOWGOT = E, and CREATESECUREAUTH = Y from SYSUSERAUTH. This means that authid GRANTORID has used the SECADM authority (AUTHHOWGOT = E) to revoke the CREATE_SECURE_OBJECT privilege from userid PAUL.