DB2 Version 10.1 for Linux, UNIX, and Windows

Scenario: ExampleBANK using row and column access control - Column masks

The ExampleBANK security administrator, further restricts data access by using column masks, a part of row and column access control. Column masks hide data returned to users or applications by column unless they are permitted to view the data.

Customer service representatives can see all clients in the ExampleBANK system, but, they are not permitted to view full account numbers unless they are using a specific application.

The security administrator implements the following column mask so that a customer service representative is restricted to view a result set that they are privileged to view:

CREATE MASK ACCOUNT_COL_MASK ON RCACTSPM.CUSTOMER FOR
------------------------------------------------------------
-- Account number information:
-- Role customer service representative (CSR) is allowed to 
-- access account number information only when they are using 
-- the account update application. This application is
-- identified through stored procedure ACCOUNTS.ACCTUPDATE. 
-- If a CSR queries this data outside of this application, the
-- account information is masked and the first 12 digits are
-- replaced with "x".
------------------------------------------------------------
COLUMN ACCOUNT RETURN
  CASE WHEN (VERIFY_ROLE_FOR_USER (USER, 'CSR') = 1 AND
             ROUTINE_SPECIFIC_NAME = 'ACCTUPDATE' AND
             ROUTINE_SCHEMA = 'ACCOUNTS' AND
             ROUTINE_TYPE = 'P')
       THEN ACCOUNT
       ELSE 'xxxx-xxxx-xxxx-' || SUBSTR(ACCOUNT,16,4)
  END
ENABLE;

The security administrator observes that even after creating a column mask, the data can still be viewed by all employees. A column mask is not applied until it is activated on the table for which it was defined. The security administrator must now activate the mask:

--Activate column access control to implement column masks

ALTER TABLE RCACTSPM.CUSTOMER ACTIVATE COLUMN ACCESS CONTROL;