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.