Row and Column Access Control in DB2 Version 10

Row and column access control enables you to manage access to a table at the level of a row, a column, or both. It enables you to build policies for the particulars of which data can be accessed by specific users, groups, or roles. Row access can be controlled using row permissions and column access control can be accomplished using column masks.

Row and column access control differs from multilevel security in that it is integrated into the database system. All applications and tools that use SQL to access the database are automatically subject to the same control. Sensitive data need not be filtered at the application level when row and column access control is in place.

Prior to row permissions and column masks, row and column level security was implemented in DB2 using views or stored procedures. Using views and stored procedures is a viable approach for simple requirements, but it breaks down as a solution for more complex requirements. When a large number of views are built to support your security needs, it can be difficult to administer as the views need to be updated and maintained.

Let’s see how row permissions and column masks can be used to improve upon row- and column-level security.

Row Permissions: Row Access Control

A row permission must be created and activated to be enforced. The structure of a permission will be familiar to anyone who is used to coding SQL statements. The CREATE PERMISSION statement is used to create a row permission.

Let’s consider an example using a banking system. Assume that bank tellers should only be able to access customers from their local branch. But customer service representatives (CSRs) should be allowed to access all customer data. Assume further, that secondary authids are setup such that tellers have a secondary authid of TELLER, and CSRs have a secondary authid of CSR. Given this scenario, the following row permissions can be created to institute these policies:

CREATE PERMISSION TELLER_ROW_ACCESS
ON     CUST
FOR ROWS WHERE VERIFY_GROUP_FOR_USER(SESSION_USER, ′TELLER′) = 1
AND
BRANCH = (SELECT HOME_BRANCH
          FROM   INTERNAL_INFO
          WHERE  EMP_ID = SESSION_USER)
ENFORCED FOR ALL ACCESS
ENABLE;

COMMIT;

CREATE PERMISSION CSR_ROW_ACCESS
ON     CUST
FOR ROWS WHERE VERIFY_GROUP_FOR_USER(SESSION_USER, ′CSR′) = 1
ENFORCED FOR ALL ACCESS
ENABLE;

COMMIT;

These row permissions will not be enforced, however, until they are activated by alteringthe table, for example: 

ALTER TABLE CUST
 ACTIVATE ROW ACCESS CONTROL;

COMMIT;

With the row permissions in force, when tellers SELECT from the CUST table they will only be able to “see” customer data for their branch, whereas customer service representatives can see all customer data.

These row permission definitions use the VERIFY_GROUP_FOR_USER built-in function. This function returns a value indicating whether the primary authid and the secondary authids that are associated with the first argument are in the authorization names specified in the list of the second argument.

Data Masking: Column Access Control

Column access control allows you to manage access to a table with filtering and data masking. As with a row permission, a column mask must be created and activated before it can be enforced. The column mask defines the rules to be used for masking values returned for a specified column.

You use the CREATE MASK statement to create a column mask. Multiple column masks can be created for a table, but each column can have only one mask. The table and column must exist before the mask can be created.

For example, you can create a mask for employee social security numbers (assuming the table name is EMP and the column name is SSN) as follows:

CREATE MASK SSNMASK
ON     EMP
FOR COLUMN SSN RETURN
  CASE
    WHEN (VERIFY_GROUP_FOR_USER(SESSION_USER, ′PAYROLL′) = 1)
    THEN SSN
    WHEN (VERIFY_GROUP_FOR_USER(SESSION_USER, ′HR′) = 1)
    THEN ′XXX-XX-′ || SUBSTR(SSN,8,4)
    ELSE NULL
  END
ENABLE;

COMMIT;

This mask will return the actual data when accessed by a user in accounting, a version with the first 5 digits masked when access by human resources, and null for anyone else. Of course, column access control must be activated for the table before any mask will be enforced:

ALTER TABLE EMP
 ACTIVATE COLUMN ACCESS CONTROL;
 
COMMIT;

Summary

Using row and column access control a security administrator can enforce detailed security policies for the databases under their control.