Tuesday, April 23, 2013

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.

16 comments:

  1. Thanks for the clear explanation Robert.

    ReplyDelete
    Replies
    1. You're welcome. I'm glad that the information was helpful.

      Robert

      Delete
  2. Thanks for the wonderful article. I am working on a project which involves securing SSN field in all tables in the db. I was proposing masking as a solution instead of encryption. So, i was researching the best way to implement it which requires very little or no changes to programs and db objects. Your article was very thorough and to the point.

    I have couple of questions though -
    1. Do you prefer masking over encryption as a security solution?
    2. Does masking override the need to encrypt sensitive data?
    3. I dont like encrypting data due to performance issues. What are your thoughts on this?

    ReplyDelete
    Replies
    1. 1) For me, it's not so much "preferring" one over the other. You have to address the requirement, whatever that is. Sometimes, there is a regulatory requirement that certain data be encrypted, and if that's what you need to do then that's what you need to do. In other cases, data masking can address a security requirement, and in such a situation the DB2 for z/OS column-masking functionality can be very helpful.

      2) Not necessarily. There could still be, for an example, a need to encrypt data as it is sent between a client application and a DB2 for z/OS system. DB2 supports "on the wire" encryption via support for AT/TLS encryption (Application-Transparent Transport-Layer Security - this is basically the successor to SSL encryption). There can also be a need to encrypt data "at rest," on disk, so that if anyone were to get his or her hands on a disk assembly itself the data would not be usable. IBM's offering in this space is called Guardium Encryption for DB2 and IMS databases. IBM and other storage systems vendors also provide data storage products that have encryption capabilities.

      3) That's a legitimate concern. The IBM z13 servers helped a lot in this area by providing encryption processing "on the chip" - in essence, encryption capability built into the processors themselves. For AT/TLS (or SSL) "on the wire" encryption, the z13 can deliver a major performance improvement over previous z Systems servers.

      Robert

      Delete
  3. Can we create mask with same mask column name on multiple tables?

    ReplyDelete
  4. I mean duplicate mask name allowed on different tables?

    ReplyDelete
  5. Example:
    CREATE MASK COLUMN_MASK ON EMP FOR COLUMN COLUMN
    CREATE MASK COLUMN_MASK ON DEPT FOR COLUMN COLUMN
    Mask name COLUMN_MASK is duplicate, giving error while second mask creating

    ReplyDelete
    Replies
    1. No. The fully-qualified name of a mask must be unique within a Db2 subsystem or data sharing group. Two masks could have the same unqualified name if they have different high-level qualifiers.

      Robert

      Delete
  6. This comment has been removed by the author.

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete
  8. This comment has been removed by the author.

    ReplyDelete
  9. This comment has been removed by the author.

    ReplyDelete
  10. Thanks Robert for the details. Need more clarification on the same. below is the create statements(MASK 1& MASK 2) using for mask creation, MASK 1 creating fine but when creating MASK2 with same mask name(COLUMN NAME_MASK) and different table(DBNAME.TABLENAME111) getting below error.

    MASK 1:

    CREATE MASK COLUMN NAME_MASK ON DBNAME.TABLENAME
    FOR COLUMN COLUMN NAME RETURN
    CASE WHEN (VERIFY_GROUP_FOR_USER(SESSION_USER, 'VIP_READ') =1)
    THEN COLUMN NAME
    ELSE NULL
    END
    ENABLE;

    MASK 2:

    CREATE MASK COLUMN NAME_MASK ON DBNAME.TABLENAME111
    FOR COLUMN COLUMN NAME RETURN
    CASE WHEN (VERIFY_GROUP_FOR_USER(SESSION_USER, 'VIP_READ') =1)
    THEN COLUMN NAME
    ELSE NULL
    END
    ENABLE;

    Error:

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0601N The name of the object to be created is identical to the existing
    name "DBNAME._MASK" of type "MASK". SQLSTATE=42710


    How can I create second MASK, Do I have to give different mask name?
    what is fully-qualified mask name?
    what is high level qualifiers?

    ReplyDelete
    Replies
    1. You create a second mask by giving it a name that is different from the first mask you created. A mask is a type of Db2 object, and within one Db2 for z/OS subsystem no two objects of the same type (e.g., no two column masks) can have the same name. All objects within a Db2 subsystem have a 2-part name: schema-name.object-name. You can read about this naming convention in the Db2 for z/OS Knowledge Center online: https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/intro/src/tpc/db2z_schemaqualifiers.html.

      Robert

      Delete
  11. I would like to confirm or validate how column mask will work in my Db2. The IBM documentation for Db2 V12 is clear that CREATE MASK requires SECADM (or SYSADM).

    https://www.ibm.com/docs/en/db2-for-zos/12?topic=masks-creating-column

    I am not a system dba so I won't get SYSADM. And they do not want to do do this type of work... so I want to create the mask and validate it works.

    But we don't use SECADM today.

    How does one get SECADM?
    (I don't see any GRANT .. SECADM in the documentation... )

    ReplyDelete
    Replies
    1. Hey, Brian.

      SECADM authority is not granted. It is conveyed by way of the ZPARM parameters SECADM1 and SECADM2 (and the related parameters SECADM1_TYPE and SECADM2_TYPE). One way to provide several people with SECADM authority is to set SECADM1 or SECADM2 to a RACF (or equivalent) group ID to which the IDs of several individuals are connected, as mentioned in the blog entry at https://robertsdb2blog.blogspot.com/2021/09/db2-for-zos-separatesecurity-and-secadm.html. That blog entry also gets into the SEPARATE_SECURITY parameter in ZPARM, but note that SEPARATE_SECURITY=YES is not a requirement for someone to exercise SECADM authority. SEPARATE_SCURITY=YES means that there are Db2 security-related actions that can ONLY be taken by someone with SECADM authority. When SEPARATE_SECURITY is set to NO, most anything that a SECADM can do can also be done by a SYSADM (the one exception of which I'm aware: authority to execute the SQL statement TRANSFER OWNERSHIP requires actual SECADM authority if you're not the owner of the object in question - SYSADM authority won't cut it, even when SEPARATE_SECURITY is set to NO).

      Robert

      Delete