Row and Column Access Control (Column Masking)

This article is based off the “DB2 v10.1 – Row and Column Access Control (Column Masking)” presentation for the DB2 Night Show (Episode #128) – DB2’s Got Talent competition held on Friday, March 7th at 11am EST. Progression within the competition is based solely on audience voting. So if you found this useful, want to see more like this, and don’t want to see a grown man cry – please vote for Mike Krafick based on the article or on the DB2 Night Show replay. You will have one week from the original air date to vote.

Progression depends heavily on your network and influence in the community. To alleviate the “vote for my friend” mentality you are required to vote for two. So you can watch the replay to judge yourself, randomly pick a contestant, or toss a second vote to Rob, Ken, or Prasad whom I thought did very well.

To vote, scroll to the bottom of the 128th Episode replay page. My presentation starts at 38:11 – stick around to the end where the judges sing me happy birthday (don’t ask).

* * *

Why Mask?

Having worked in the financial and insurance industries, security is something I have always scrutinized. I was intrigued by a 2013 IDUG Tech Conference presentation on “Data Masking” by the DB2 Locksmith, Rebecca Bond. Essentially a data mask is nothing more than displaying a value based on a rule for a specific column. My involvement on the DB2 Night Show and “DB2’s Got Talent” finally gave me the excuse to do more research, install v10.1 FP3 on a test box, and see how involved row and column access control really is. Row access control is specifically geared for the government and a level of security needed for top secret projects. However, column access control was designed to protect sensitive information from table owners or even the database administrator. For my experiment, I narrowed my scope to column access control. I wanted to see how complex it was to set up and exactly what the masking looked like.  I wanted to see if I could hide specific data from an employee but show everything to someone like a manager. ultimately, it was easy to set up and done in only two phases. Before starting, I concocted a table that I wanted to protect. Let’s just say for this example, it is an offshore account for some shady people you may know in the DB2 world.

Phase I – Assess Security Policy

Assess your security policy, and grant only enough to allow the user to get work done. Then create a role around this security policy.

For example:

Phase II – Create Column Mask and Activate

Creating the actual mask is a fairly easy command; just have in mind what you want displayed for a masked value. Then turn on the mask. Commands:

CREATE (OR REPLACE) MASK (NAME) ON (TABLE) FOR COLUMN (NAME) 
RETURN (EXPRESSION) ENABLE (OR DISABLE)
ALTER TABLE (NAME) ACTIVATE COLUMN ACCESS CONTROL

Example: There are two take-aways from my example. First, notice the RETURN clause rolls into a CASE statement to help determine the value to return for a column. Second, the VERIFY_GROUP_FOR_USER is a new v10.1 function that comes in handy when masking. Check these new functions for masking out:

• VERIFY_ROLE_FOR_USER: Is the USER a member of a ROLE? (True 1 | False 0)
• VERIFY_GROUP_FOR_USER: Is the USER a member of a GROUP? (True 1 | False 0)
• VERIFY_TRUSTED_CONTEXT_ROLE_FOR_USER: Does the USER have a specific trusted context role? (True 1 | False 0)

Masked Data

In the end, I was able to easily display data on an as needed basis based on job role. Managers, for example, could see all sensitive data such as accounts and social security numbers while customer service representatives could not. Total time to setup was about 10 minutes. Employee view (masked data): Manager view (unmasked data):

Michael Krafick is an occasional contributor to db2commerce.com. He has been a production support DBA for over 12 years in data warehousing and highly transactional OLTP environments. He was acknowledged as a top ten session speaker for “10 Minute Triage” at the 2012 IDUG Technical Conference. Michael also has extensive experience in setting up monitoring configurations for DB2 Databases as well as preparing for high availability failover, backup, and recovery. He can be reached at “Michael.Krafick (at) icloud (dot) com”. Linked-in Profile: http://www.linkedin.com/in/michaelkrafick. Twitter: mkrafick Mike’s blog posts include:

• 10 Minute Triage: Assessing Problems Quickly (Part I)
• 10 Minute Triage: Assessing Problems Quickly (Part II)
• Now, now you two play nice … DB2 and HACMP failover
• Technical Conference – It’s a skill builder, not a trip to Vegas.
• Why won’t you just die?! (Cleaning DB2 Process in Memory)
• Attack of the Blob: Blobs in a Transaction Processing Environment
• Automatic Storage Tablespaces (AST): Compare and Contrast to DMS