DB2 for z/OS: Goodbye "Security" Views, Hello Row Permissions and Column Masks

It's an age-old requirement -- one that's been around as long as DB2 itself: ensure that a particular group of users can see only certain rows in a given table, and/or ensure that values in a certain column of the table are transformed before being returned to users in the group. For example, employees who work in a branch office of an organization might be permitted to see information in a CUSTOMER table for customers assigned to that branch, but prohibited from seeing information for customers assigned to other branches; furthermore, these employees are permitted to see an indication of the relative magnitude of the income of a customer assigned to their branch, but not actual income values.

For a long time, such needs were commonly addressed through the creation of so-called "security" views. The mechanism could be implemented in something like the following manner:

1. A view that filters out rows that do not have the identifier of an employee's assigned branch in the BRANCH column is defined on the CUSTOMER table. The view might be distinguished by a high-level qualifier (e.g., BRANCH.CUSTOMER, versus PROD.CUSTOMER for the underlying table), or it might have a different unqualified name (e.g., BRANCH_CUSTOMER). Such a view could also transform and abstract information in the table's INCOME column as required.
2. The SELECT privilege on the view is granted to an appropriate secondary authorization ID (or set of secondary IDs), and/or (in the static SQL case) the EXECUTE privilege on packages of programs that access the view is granted to that secondary authorization ID (or set of secondary IDs).

That seems simple enough, but as many DBAs and security administrators will attest, it's not an ideal solution. Downsides of the view approach to row- and column-level data security include:

• The name of view must be different from the name of the table on which it's defined, and different from any other existing view in the catalog of the DB2 system. This can complicate things from an application perspective. If the unqualified name of the view is what makes the name unique (e.g., a view BRANCH_CUSTOMER is defined on the table named CUSTOMER), SQL statements targeting the view will have to explicitly reference the unique unqualified view name, or they will have to reference an alias that has the same unqualified name as the underlying table but which resolves to the different unqualified name of the view (and that alias will of course have to have a high-level qualifier that's different from the underlying table's high-level qualifier, since the unqualified names of the alias and the table are identical). If the view's high-level qualifier is what makes its name unique (e.g., a view BRANCH.CUSTOMER is defined on the table PROD.CUSTOMER), packages of programs with static SQL statements referencing unqualified object names will have to be bound into different collections using different bind-time qualifiers, and the right collection will have to be selected (likely via SET CURRENT PACKAGESET) when users need access to the view versus access to the underlying table. Also for a view distinguished from an underlying table by way of a different high-level qualifier, users issuing dynamic SQL statements (perhaps generated by an application or a tool) will have to utilize the correct fully qualified object name to access the view versus the table, or a SET CURRENT SQLID = 'BRANCH' will have to be issued to ensure that the right high-level qualifier is added to unqualified object names (and that may or may not be feasible, depending on the application in question). Any way you slice it, the requirement for a unique name for the view creates a hassle factor when it comes to object access and management.
• As the number of different row and column restrictions increases, the number of security views can become rather large. In some cases, security views can proliferate like crazy. Not only can this be a database administration headache, it can also get in the way of making database design changes that could improve application performance or functionality. I recall a situation, a few years back, in which a potentially performance-enhancing database design change was vetoed at a certain DB2 for z/OS site because it would require making a change to a view. There were views defined on other views at this site (primarily security views), going up several layers, and the view that would be changed was at the ground level, so to speak. There was so much concern about the effect that a modification of this foundational security view might have on other views that we had to go with a less desirable database change that would leave the view definition intact.
• The "super user" loophole. Security views can do a pretty good job of restricting data access at the row and/or column level for most users, but what about "super users," such as those who have SYSADM authority on the DB2 subsystem? You can have all the security views you want, but the underlying table will continue to exist as before, and someone with SYSADM authority can view all the data in that table. Knowing who your SYSADMs are, you may be comfortable with that, but are your auditors?

Kind of reminds you of a lot of product-pitching TV commercials you've seen, right? You know, the ones in which a flustered individual exclaims...

"There must be a better way!"

Well, Mr. or Ms. DB2 person, there is a better way. It's called row permissions and column masks, and it was introduced with DB2 10 (new-function mode). It's beautiful in its simplicity. In the remainder of this blog entry I'll briefly describe the feature and how you can put it to work, and I'll provide some simple usage examples. You can read more about row permissions and column masks in the DB2 10 for z/OS Information Center on the Web.

A row permission is implemented by way of the SQL statement CREATE PERMISSION. Through this statement, you specify column-filtering predicates for a table and associate these with a primary or secondary authorization ID, or with a role (the association is accomplished via new scalar functions that include VERIFY_GROUP_FOR_USER and VERIFY_ROLE_FOR_USER). Similarly, you can use the statement CREATE MASK to specify column-value transformation actions in the form of CASE expressions, and associate these with authorization IDs or roles through the aforementioned VERIFY functions.

To show you how permissions and masks can be utilized to implement data security safeguards at the row and column level in a DB2 database, I'll return to the example CUSTOMER table that includes, among other things, a column containing the identity of a branch to which a customer is assigned, and a column containing that customer's income. This is a very small and very simple table, as indicated by the result set of a SELECT * statement targeting the table:

CUSTNO  CUSTNAME    BRANCH       INCOME
---------+---------+---------+---------
111222  JONES       A             45000
222333  SMITH       A             57000
333444  TERRY       B            110000
444555  WILSON      B            230000

If we want to make sure that employees in the respective branch offices can only see data for customers assigned to their branch, we can create a permission like the one below. It's assumed here that employees' individual authorization IDs have been connected to the RACF (or equivalent) groups STAFF and MGR (the former for professional staff and the other for managers), and that columns EMP_BRANCH and EMP_AUTHID of table EMP_INFO contain employees' branch office assignments and system authorization IDs, respectively). 

CREATE PERMISSION BRANCH_EMP ON CUSTOMER              
 FOR ROWS WHERE                                       
  VERIFY_GROUP_FOR_USER(SESSION_USER,'STAFF','MGR') = 1
  AND                                                 
  BRANCH = (SELECT EMP_BRANCH FROM EMP_INFO           
  WHERE EMP_AUTHID = SESSION_USER)                    
 ENFORCED FOR ALL ACCESS                              
 ENABLE;

This row permission is activated through an ALTER TABLE statement, as follows:

ALTER TABLE CUSTOMER       
ACTIVATE ROW ACCESS CONTROL;

With the row permission defined and activated, a user who is assigned to branch A and who has 'STAFF' (or 'MGR') as a secondary authorization ID will get the following result set if the statement SELECT * FROM CUSTOMER is executed:

CUSTNO  CUSTNAME    BRANCH       INCOME
---------+---------+---------+---------
111222  JONES       A             45000
222333  SMITH       A             57000 

Now, suppose there is also a requirement that non-management employees in branches not see actual customer income figures. Suppose that instead they are to see a number 1, 2, 3, or 4 in the INCOME column of a SELECT FROM CUSTOMER result set, with the number indicating a relative income level for a customer. If branch managers are allowed to see actual customer income figures for customers assigned to their branch, the data transformation requirement could be addressed through a column mask defined as follows:

CREATE MASK INCOME_MASK ON CUSTOMER                     
 FOR COLUMN INCOME RETURN                               
  CASE                                                  
   WHEN (VERIFY_GROUP_FOR_USER(SESSION_USER,'MGR') = 1) 
    THEN INCOME                                         
   WHEN (VERIFY_GROUP_FOR_USER(SESSION_USER,'STAFF') = 1)
     THEN CASE                                          
      WHEN INCOME >= 200000 THEN 4                      
      WHEN INCOME BETWEEN 100000 AND 199999 THEN 3      
      WHEN INCOME BETWEEN 50000 AND 99999 THEN 2        
      WHEN INCOME < 50000 THEN 1                        
     END                                                
    ELSE NULL                                           
  END                                                   
 ENABLE;

An ALTER TABLE statement activates column masking for the table:

ALTER TABLE CUSTOMER     
ACTIVATE COLUMN ACCESS CONTROL;

And now, when a non-management employee assigned to branch B executes the statement SELECT * FROM CUSTOMER, the following result set will be returned:

CUSTNO  CUSTNAME    BRANCH       INCOME
---------+---------+---------+---------
333444  TERRY       B                 3
444555  WILSON      B                 4

But when a manager in branch B issues the same statement, the result set will look like this:

CUSTNO  CUSTNAME    BRANCH       INCOME
---------+---------+---------+---------
333444  TERRY       B            110000
444555  WILSON      B            230000

Here's why I so prefer permissions and masks over views when it comes to implementing row- and column-level data security in a DB2 for z/OS database:

• No more "name game" headaches. Creating and activating row permissions and column masks does NOT require users and programmers to reference anything other than the actual target table. Instead of some access being to a security view and other access being to the underlying table as necessitated by data protection requirements, access is always to the CUSTOMER table (in my example) -- the permissions and masks defined on the table take care of filtering rows and transforming data values as needed.
• Expect a less-tangled data security set-up. The flexibility and power of the CREATE PERMISSION and CREATE MASK statements should enable you to implement the row- and column-level data access controls that your organization needs without the proliferation of related database constructs that you might have on your hands were you to go the security view route. Your permission- and mask-controlled data protection scheme is likely to be simpler in construction, easier to understand, and easier to manage than one that relies on security views.
• Super users get no special treatment, if that's what you want. When you create and activate a row permission or a column mask for a DB2 table, it applies to ALL access to that table by ALL users. Again referring to the example I've used throughout this blog entry, with the permission I called BRANCH_EMP defined and activated for the CUSTOMER table, will the table's creator be able to retrieve rows from CUSTOMER? How about someone with SYSADM authority? No and no -- unless 'STAFF' or 'MGR' happens to be an authorization ID (either primary or secondary) of the table creator or the person with SYSADM authority. Ah, but can't a user with SYSADM authority gain access to CUSTOMER data simply by executing the statement SET CURRENT SQLID = 'MGR' (or 'STAFF')? Yes, but only if the ZPARM called SEPARATE_SECURITY (new with DB2 10) is set to NO (which happens to be the default). If the value of SEPARATE_SECURITY is YES, someone with SYSADM authority can only change his or her current SQL ID to an ID that is among his or her primary and secondary IDs. Another way to tighten things up is to associate row permissions and column masks with roles, as opposed to authorization IDs -- SET CURRENT SQLID cannot be used to change one's secondary authorization ID to the name of a role.

A lot of DB2 people are drawn to new DB2 features that enhance system performance. I get that. It's always fun to tune a DB2 system and applications. Data security may not be as exciting to you as performance tuning, but it's more important now to your organization than it's ever been before. I'm telling you, DB2 10 row permissions and column masks are a big deal -- a major advance in DB2's already-strong data protection capabilities. Get to know this technology, and leverage it at your site. Your auditors (and, maybe, even your CEO) will appreciate your efforts.