Modifying column masks to reference UDFs

With the SECADM authority, you can modify column masks on tables that are activated for column access control.

Before you begin

Begin general-use programming interface information.

If the SEPARATE_SECURITY system parameter on panel DSNTIPP1 is set to YES during installation or migration, you must have the SECADM authority to modify a column mask. If SEPARATE_SECURITY is set to NO, you must have the SECADM or SYSADM authority.

About this task

Suppose that table HOSPITAL.PATIENT 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). The table is activated for column access control.

Also, suppose that database developer Paul has created a new powerful accounting application NetHMLAccountingUDF (an external scalar user-defined function). You want to modify column mask ACCT_BALANCE_MASK for column HOSPITAL.PATIENT.ACCT_BALANCE to include NetHMLAccountingUDF.

Procedure

To modify column mask ACCT_BALANCE_MASK to include NetHMLAccountingUDF:

Make sure that all the operations in the new UDF are secure.
Only secure UDFs can be invoked in a column mask. The SECURED attribute is required if the user-defined function is referenced in the definition of a row permission or column mask. This is because the user-defined function may access sensitive data. The SECURED attribute is also required for a user-defined function that is invoked in an SQL statement when the function arguments reference columns that are activated with column access control.

Make sure that all the operations inside the new application NetHMLAccountingUDF are actually secure. Then, you can issue the following GRANT CREATE_SECURE_OBJECT statement to allow userid PAUL the privilege for creating a secure UDF:
```
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.

With the CREATE_SECURE_OBJECT privilege, Paul issues the following ALTER FUNCTION statement to secure NetHMLAccountingUDF:
```
ALTER FUNCTION NETHMOACCOUNTINGUDF(ACCT_BALANCE) SECURED;

COMMIT;
```
Db2 sets the new column in catalog SYSROUTINES.SECURE to Y and invalidates all packages and dynamic cached statements that reference NetHMOAccountingUDF(ACCT_BALANCE).
After the UDF has been altered to be secure, 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 from SYSUSERAUTH with GRANTOR = GRANTORID, GRANTEE = PAUL, AUTHHOWGOT = E, and CREATESECUREAUTH = Y.
Drop the existing column mask ACCT_BALANCE_MASK for column ACCT_BALANCE
You can issue the DROP MASK statement to remove the existing column mask, but do not follow it with the COMMIT statement. This will prevent any ongoing transactions from accessing table HOSPITAL.PATIENT before you can put a new column mask in place.
```
DROP MASK ACCT_BALANCE_MASK;
```
Db2 invalidates all packages and dynamic cached statements that reference table HOSPITAL.PATIENT. It also deletes the row for ACCT_BALANCE_MASK in the catalog table SYSIBM.SYSCONTROLS. Since there isn't a COMMIT statement immediately after the DROP MASK statement, Db2 keeps possessing the lock on HOSPITAL.PATIENT and doesn't commit the work it has done for the DROP MASK statement. Any transactions that try to access HOSPITAL.PATIENT may be timed out.
Create a new column mask ACCT_BALANCE_MASK for column ACCT_BALANCE
You can issue the CREATE MASK statement to create a new column mask and follow it immediately with the COMMIT statement. This will enable Db2 to commit all the work it has done so far for HOSPITAL.PATIENT and allow other transactions to access HOSPITAL.PATIENT.
```
CREATE MASK NETHMO.ACCT_BALANCE_MASK ON HOSPITAL.PATIENT FOR
    COLUMN ACCT_BALANCE RETURN
       CASE WHEN VERIFY_TRUSTED_CONTEXT_ROLE_FOR_USER
            (SESSION_USER,'ACCOUNTING') = 1

            THEN NETHMOACCOUNTINGUDF(ACCT_BALANCE)
            ELSE 0.00
       END
    ENABLE;

COMMIT;
```
Db2 invalidates all packages and dynamic cached statements that reference table HOSPITAL.PATIENT and inserts the new ACCT_BALANCE_MASK definition into the catalog table SYSIBM.SYSCONTROLS. It also records the dependency on NetHMOAccountingUDF in SYSIBM.SYSDEPENDENCIES for ACCT_BALANCE_MASK.

The COMMIT statement immediately after the CREATE MASK statement ensures that Db2 commits all the work it has done so far on HOSPITAL.PATIENT and releases the lock from the table. This allows other transactions to access the same table without being timed out.