db2Dean  a.k.a. Dean Compher a.k.a ďThe Dean of DB2Ē is here to answer your DB2 Questions !

 

Utah Events

None currently Scheduled

 

Nevada Events

None currently Scheduled

 

Search db2Dean

 

 

Add your insights to the:

db2Dean and Friends

 

Get the Free Data Studio! 

Get DB2 Express-C Here!

A person wearing a red and black hair

Description automatically generated

dean@db2Dean.com

 

tp://twitter-badges.s3.amazonaws.com/twitter-a.png

 

tp://www.linkedin.com/img/webpromo/btn_profile_bluetxt_80x15.png

 

 

Online Events

Technical Conference Calls

 

Other Events

Think, San Francisco, Feb 12-15

   

 

Welcome to db2Deanís web site.  Iím Dean Compher an IBM IT Specialist who, along with my team, helps customers and prospective customers with DB2 on Linux, UNIX and Windows (LUW) technical questions and issues.  As this page makes painfully clear, I am a DBA and not a web designer, but I would be happy to get your DB2 questions answered or talk to you about the great features of DB2 or IBM Integration Products.  If you are looking at a new database solution or want to compare us to your existing database vendor, please do not hesitate to contact me about getting a presentation or just to ask questions.  My e-mail address is dean@db2Dean.com

 

I am located in Utah and primarily serve DB2/LUW and dashDB customers in the Western US, but I can forward requests to my peers in other technology and geographic areas as well.  My team also covers Big Data and Informix and products.  There are questions that I get on a regular basis, and I will write articles relating to them here.  I hope that you find them useful.  I also welcome suggestions for future content.  Click here for more information about me.

 

 

Favorite Links

 

Previous

Articles

 

Hot Links

 

Manuals

 

Info Center

 

Informix

 

DB2/zOS

 

DB2 i5/OS

 

DB2 Connect

 

Best Practices

 

 

 

 

 

 

 

 

 

 

 

Db2 Row and Column Access

Dean Compher

31 May 2019

 

 

Most organizations have some data that is sensitive enough that it shouldnít be seen by every user or every application that connects to the database.  Either certain users should only be able to see certain rows of a table or certain users should not see certain columns.  For example, if a hospital system has a patient table, you may want patients seeing only their own records and doctors only seeing their own patientsí records, but the not the patientsí social security numbers.   Db2 allows you to make these sorts of restrictions easily using the Row and Column Access Control (RCAC) feature.  Donít worry if database connections are made using a generic user id from the application server -- Iíll also discuss how you can get around that and allow different end users to see different data.

 

Some think that they need to design an elaborate security plan to get started with data base security, and having a good plan is never a bad idea.  However, if you have an existing database you can make improvements incrementally without a lot of effort.  You can probably identify a handful of the most sensitive columns in your database pretty quickly and then determine who actually needs access to them.  You can then create permissions giving access to the columns to those users or roles and mask the column for everyone else. 

 

To illustrate that Iíll us the example of a patient table in a hospital system.  My examples here are taken from a much more involved tutorial available on line that Iíll discuss later.  We recognize that this table has two really sensitive columns in it:  Social Security Number (SSN) and Account Balance (ACCT_BALANCE).  Here is the DDL for that table:

 

Example 1.  Table for the rest of the examples

 

CREATE TABLE PATIENT (

     SSN CHAR(11),

     USERID VARCHAR(18),

     NAME VARCHAR(128),

     ADDRESS VARCHAR(128),

     PHARMACY VARCHAR(250),

     ACCT_BALANCE DECIMAL(12,2) WITH DEFAULT,

     PCP_ID VARCHAR(18)

     );

 

Since our users are not already in grouped in LDAP or OS groups or into DB2 roles, lets create some roles to make this task easier.  Roles are just a convenient way to grant several authorities at one time and then grant or revoke those authorities easily.  In our case we would want PATIENT and ACCOUNTING roles because those are the only two types of users who should see the account balance and full social security number.  Letís also create a role for primary care physicians (PCP) who will not need access to either of these columns.  Here is how those roles could be created and have users assigned:

 

Example 2 - Create roles and grant them to users.   

 

-- Create the roles

CREATE ROLE ACCOUNTING;

CREATE ROLE PATIENT;

CREATE ROLE PCP;

 

-- Grant access to a table to the roles. 

GRANT SELECT, UPDATE ON TABLE PATIENT TO ROLE ACCOUNTING;

GRANT SELECT ON TABLE PATIENT TO ROLE PATIENT;

GRANT SELECT, UPDATE ON TABLE PATIENT TO ROLE PCP;

 

-- Grant the roles to our users John and Bob and Dr. Lee. 

GRANT ROLE ACCOUNTING TO USER JOHN;

GRANT ROLE PATIENT TO USER BOB;

GRANT ROLE PCP TO USER LEE;

 

 

At this point, users John, Bob and Lee continue to have any authorities they had already been granted so we probably didnít need to grant table access to the roles, but I want explicitly show role examples.  These users now belong to their respective roles.  Next, we want to allow the patient and accounting roles to see the balance, but mask out the balance by showing zero to everyone else.  This is done by creating a MASK object on the column as shown here:

 

CREATE OR REPLACE MASK ACCT_BALANCE_MASK ON PATIENT

   FOR COLUMN ACCT_BALANCE RETURN

     CASE WHEN VERIFY_ROLE_FOR_USER(SESSION_USER,'ACCOUNTING') = 1 OR

                VERIFY_ROLE_FOR_USER(SESSION_USER,'PATIENT') = 1

                 THEN ACCT_BALANCE

           ELSE 0.00

     END

ENABLE;

 

ALTER TABLE PATIENT ACTIVATE COLUMN ACCESS CONTROL;

 

You can read more about he create mask statement in the knowledge center, but I want to point out a few things here:

      I used the SESSION_USER special register.  This is how we determine the user who is accessing the table when the mask gets invoked.  This special register is not an RCAC feature and can be used in your own select statement or other expression. 

      If you wanted to give access to the column to specific users instead of roles, then you could have used syntax like ď WHEN SESSION_USER = ďBOBĒ

      I used the VERIFY_ROLE_FOR_USER function to verify whether the session_user is in the ACCOUNTING or PATIENT roles.  This function is open to all and can be called in any query.  

      If you are using operating system or LDAP groups instead of roles, then you would use the VERIFY_GROUP_FOR_USER function in place of the VERIFY_ROLE_FOR_USER finction. 

      Finally, to make the column map active, I use the ALTER TABLE statement you see here. 

      Since acct_balance is a decimal(12.2) column any masking we do must return that data type.  In this case we return 0.00 but any conforming numeric value would be fine. 

 

Now that the column mask has been created, Iíll connect using the instance owner and select the account balance.  Notice that since db2inst1 is not in a role that is allowed to see the account balance, that column is displayed as zero:

 

$ db2 connect to sample user db2inst1

$ db2 select userid, ssn, acct_balance, pcp_id from db2inst1.patient

 

USERID             SSN         ACCT_BALANCE   PCP_ID           

------------------ ----------- -------------- ------------------

MAX                123-55-1234           0.00 LEE              

MIKE               123-58-9812           0.00 JAMES            

SAM                123-11-9856           0.00 LEE              

DUG                123-19-1454           0.00 JAMES             

BOB                123-45-6789           0.00 LEE 

 

 

Now, patient bob connects and sees all account balances because of his role.  The fact that he can access other patientsí data will be addressed later. 

 

$ db2 connect to sample user bob    

$ db2 select userid, ssn, acct_balance, pcp_id from db2inst1.patient

 

USERID             SSN         ACCT_BALANCE   PCP_ID           

------------------ ----------- -------------- ------------------

MAX                123-55-1234          89.70 LEE              

MIKE               123-58-9812           8.30 JAMES            

SAM                123-11-9856         100.34 LEE              

DUG                123-19-1454         185.34 JAMES            

BOB                123-45-6789           9.00 LEE

 

 

While a patient should probably be able to see his social security number, others should probably not be able to see that.  Since SSN is a character column, we have more flexibility about how to mask it.  In this example, a user could get three different values depending on their role.  Also notice that you can use the column in functions such as CHAR, concatenate (||) and SUBSTR as shown here.

 

CREATE MASK SSN_MASK ON PATIENT FOR

COLUMN SSN RETURN

     CASE WHEN

           VERIFY_ROLE_FOR_USER(SESSION_USER,'PATIENT') = 1 OR

           VERIFY_ROLE_FOR_USER(SESSION_USER,'ACCOUNTING') = 1

     THEN SSN

     WHEN

           VERIFY_ROLE_FOR_USER(SESSION_USER,'MEMBERSHIP') = 1

                 THEN CHAR('XXX-XX-' || SUBSTR(SSN,8,4))

     ELSE CHAR('PROHIBITED!' )     END

ENABLE;

 

ALTER TABLE PATIENT ACTIVATE COLUMN ACCESS CONTROL;

 

 

Another use for a column mask is to make it easy to always mask data being extracted to be put into a test database.  In this case you would want to always use a particular user id when connecting for that purpose or to add any users who do that to a particular role.  Further, if you have a more complex masking algorithm that is used in more than one mask, then you can write your own user defined function and use in the masks.  There are a few restrictions on this, so see the command description before doing this.  If you do this strictly for masking then you may want to create a mask that masks for particular users and shows the real column to everyone else.  This is the opposite of what was done above where we didnít mask for some specified roles and masked everyone elseís.  For example, you could do this:

 

CREATE MASK SSN_TEST_EXTRACT_MASK ON PATIENT FOR

COLUMN SSN RETURN

     CASE WHEN

           VERIFY_ROLE_FOR_USER(SESSION_USER,'TEST_EXTRACTOR') = 1

     THEN CHAR('123-45-6789')

    

     ELSE SSN    END

ENABLE;

 

 

The fact that our patient can see the rows for other patients is probably not a good thing.  To remedy this, Db2 provides row PERMISSION object that allows you to limit what rows a user, role, or group can see. 

 

CREATE OR REPLACE PERMISSION ROW_ACCESS ON PATIENT

FOR ROWS WHERE(VERIFY_ROLE_FOR_USER(SESSION_USER,'PATIENT') = 1

               AND PATIENT.USERID = SESSION_USER)

         OR

              (VERIFY_ROLE_FOR_USER(SESSION_USER,'PCP') = 1

               AND

                 PATIENT.PCP_ID = SESSION_USER)

         OR

                (VERIFY_ROLE_FOR_USER(SESSION_USER,'ACCOUNTING') = 1)

ENFORCED FOR ALL ACCESS

ENABLE;

 

ALTER TABLE PATIENT ACTIVATE ROW ACCESS CONTROL;

 

You can read more about he create permission statement in the knowledge center, but I want to point out a few things here:

      This example limits rows returned in three cases:

1.     If the user is in the role patient, then they can see the rows where their session_user is the same as the userid column in the table.  In this case a patient can see his own row.

2.     If the user is in the PCP (Primary Care Physician) role then all rows where the session_user equals the value in the pcp_id column are returned.  In this case a primary care physician can see all of their patientsí rows.

3.     Anyone who is in the role ACCOUNTING will has access to all rows in the table. 

4.     If the session_user is not in one of these three roles, then they can not select any rows in the patient table. 

      Unless you have a reason not to, I recommend using the ENFORCED FOR ALL ACCESS so that the rule is always applied regardless of how the table is being accessed. 

      Scroll up to Example 2 if you need a reminder of who is in which role. 

 

Here is what different users see.  As you may recall, Bob is a patient, John is in accounting and Lee is a primary care physician:

 

$ db2 connect to sample user bob

$ db2 select userid, ssn, acct_balance, pcp_id from db2inst1.patient

 

USERID             SSN         ACCT_BALANCE   PCP_ID           

------------------ ----------- -------------- ------------------

BOB                123-45-6789           9.00 LEE              

 

$ db2 connect to sample user lee

$ db2 select userid, ssn, acct_balance, pcp_id from db2inst1.patient

 

USERID             SSN         ACCT_BALANCE   PCP_ID           

------------------ ----------- -------------- ------------------

MAX                PROHIBITED!           0.00 LEE              

SAM                PROHIBITED!           0.00 LEE               

BOB                PROHIBITED!           0.00 LEE    

 

$ db2 connect to sample user john

$ db2 select userid, ssn, acct_balance, pcp_id from db2inst1.patient

 

USERID             SSN         ACCT_BALANCE   PCP_ID           

------------------ ----------- -------------- ------------------

MAX                123-55-1234          89.70 LEE              

MIKE               123-58-9812           8.30 JAMES             

SAM                123-11-9856         100.34 LEE              

DUG                123-19-1454         185.34 JAMES            

BOB                123-45-6789           9.00 LEE   

 

It is worth noting here that each user sees the table has having the number of rows to which they have access.  In the example above if each of the users issued a ďselect count(*) from ďdb2inst1.patientĒ then they would each get a different number.  Bob would see 1, Lee would get 3, and John would get 5 as the count.  If db2inst1 issues a select on the table he would get the same response as if there really were no rows in the table.  db2inst1 or any other user not in one of the defined roles would not get a security error, the table just appears to have zero rows to them. 

 

One thing that may concern some of us is that you have to add a column to each table to allow the use of row permissions.  While that is a straight forward way of being able to determine who can see what rows are there, it is not always necessary.  If you can use SQL to select who can use which rows then you can use row permissions without row labels.  This could be done by adding a cross reference table when you donít want to add an extra column to a table used by your applications. 

 

Many organizations use a three-tier architecture where the application server creates a pool of connections all connecting to the database with the same generic user id that services queries for all users of the application.  This would seem to prevent the use of RCAC since Db2 canít distinguish one user from another.  However, using Trusted Context, you can have the application server tell Db2 who the ultimate user is with very little effort.  Please see my previous article on Trusted Context.  In my examples in that article I connect with the generic user app_id and then switch to user db2dean before selecting from the db2inst1.employee table.  Using that same example I could change the query to select from the db2inst1.patient table that we have been using in this article.  If I did nothing else, the query would fail, because db2dean does not have any authorities on the patient table.  However, by merely granting PATIENT role to db2dean, we can give db2dean access to rows there db2dean is the userid value. 

 

GRANT ROLE PATIENT TO USER DB2DEAN;

 

This is actually quite a secure way of giving that access.  Since db2dean doesnít exist in the database server or have connect privileges, db2dean canít connect directly to the database.  That user can only see the data in the table by using that trusted context and that requires it to first connect to the database from a defined set of servers using the app_id and then switching the user using the trusted context. 

 

Instead of granting the role as shown above, you could use the WITH USE FOR authorization-name syntax when creating the Trusted Context to assign the role to a user there.  If you do that, then you would use the VERIFY_TRUSTED_CONTEXT_ROLE_FOR_USER function instead of the VERIFY_ROLE_FOR_USER function when creating MASKs and PRIVILEGEs. 

 

The above Row and Column Access samples are just a subset of a robust set of examples on the IBM Knowledge center.  I highly recommend reviewing them to see all that can be done. 

 

 

***

 

If you think of other interesting ways to use RCAC,  please post them on my Facebook Page or my db2Dean and Friends Community along with any other comments you may have.  

 

 

Top | Search

 

 

Db2Deanís

Other  Interests

 

Real Estate Investing

 

Quail at my Bird Feeder

 

My Googlegšngers

 

Fortune Cookie