Scenario: ExampleBANK using row and column access control - Row permissions

The security administrator at ExampleBANK, starts to restrict data access by using row permissions, a part of row and column access control. Row permissions filter the data returned to users by row.

Tellers are permitted to view client data only from their home branch. Telemarketers and CSRs are permitted to see all ExampleBANK clients in the system, but telemarketers cannot see the full account number.

Row permissions restrict or filter rows based on the user who has logged on to the database. At ExampleBANK, the row permissions create a horizontal data restriction on the CUSTOMER table.

The security administrator implements the following row permissions so that a user in each role is restricted to view a result set that they are privileged to view:

CREATE PERMISSION TELLER_ROW_ACCESS ON RCACTSPM.CUSTOMER
-------------------------------------------------------
-- Teller information:
-- ROLE TELLER is allowed to access client data only 
-- in their branch.
------------------------------------------------------------
FOR ROWS WHERE VERIFY_ROLE_FOR_USER(USER, 'TELLER') = 1
AND 
BRANCH = (SELECT HOME_BRANCH FROM RCACTSPM.INTERNAL_INFO WHERE EMP_ID = USER)
ENFORCED FOR ALL ACCESS
ENABLE;

CREATE PERMISSION CSR_ROW_ACCESS ON RCACTSPM.CUSTOMER
-------------------------------------------------------
-- CSR and telemarketer information:
-- ROLE TELEMARKETER and CSR are allowed to access all client
-- data rows in ExampleBANK.
------------------------------------------------------------
FOR ROWS WHERE VERIFY_ROLE_FOR_USER (USER, 'CSR') = 1 
OR
VERIFY_ROLE_FOR_USER (USER, 'TELEMARKETER') = 1
ENFORCED FOR ALL ACCESS
ENABLE;

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

--Activate row access control to implement row permissions

ALTER TABLE RCACTSPM.CUSTOMER ACTIVATE ROW ACCESS CONTROL;