DB2 10.1 introduced a new feature commonly called RCAC (Row and Column Access Control) or FGAC(Fine-Grained Access Control). This is a bit less labor intensive to support than LBAC (Label Based Access Control), and solves some of the problems with LBAC. It allows a finer level of access control than the standard DB2 permissions scheme.

RCAC consists of two major components – Column masking and Row permissions.

Column masking allows a security administrator to limit or change what certain users, groups, or roles see when they look at data. You can prevent them from seeing the values in a column or replace the values in a column with other data.

Row permissions allow a security administrator to limit what rows a user, group. or role sees – drastically changing the output of queries.

This blog entry focuses on Row permissions

SECADM

A new database level permission called SECADM was introduced to separate the role of managing row permissions from database administration and other common database level roles. Once you grant the SECADM permission to another user, IDs with DBADM, including your instance owner will not be able to work with the security related operations. This ID can also manage auditing. See the IBM DB2 Knowledge Center for more details on SECADM: https://www-304.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.sec.doc/doc/c0021054.html?lang=en

To start working with RCAC, I created a user to have SECADM authority on the database, and also then granted SECADM back to my instance owner to make things easy, since this is only a test database.
As db2inst1:

$ db2 grant secadm on database to user db2sec
DB20000I  The SQL command completed successfully.

As db2sec:

$ db2 grant secadm on database to user db2inst1
DB20000I  The SQL command completed successfully.

Users

I then created two ids called client1 and client2 and set them up with the proper profile and such. For this exercise, I’ll be using the SALES table from the DB2 SAMPLE database.

The two ids for my experiments have to have the basic permissions:

$ db2 grant connect on database to user client1, user client2
DB20000I  The SQL command completed successfully.

I also created a DBA Role. The main reason I did this is because when you use RCAC and enable a table for it, it is applied for ALL users. This includes IDs with DATAACCESS or any other permissions. I did this as db2sec, because a user cannot grant a role to itself.

$ db2 create role DBA
DB20000I  The SQL command completed successfully.
$ db2 grant role DBA to user db2inst1
DB20000I  The SQL command completed successfully.

It is important to note that the db2sec id does not necessarily have access to the data. By default, this user cannot see the data:

$ db2 "select * from db2inst1.sales"
SQL0551N  The statement failed because the authorization ID does not have the 
required authorization or privilege to perform the operation.  Authorization 
ID: "DB2SEC".  Operation: "SELECT". Object: "DB2INST1.SALES".  SQLSTATE=42501

Next, I’ll create a row permission and activate row access control on the table. Note that you have to have permissions to alter the table to do this, so I did it as db2inst1. This permission allows client1 to only see rows where the REGION column starts with “Ontario”. It also allows client2 to only see rows where the REGION column is exactly ‘Quebec’. Finally, it lets anyone with the DBA role see any of the data in the table.

CREATE or REPLACE PERMISSION check_region on db2inst1.sales for rows 
WHERE VERIFY_ROLE_FOR_USER (USER, 'DBA') = 1 
OR USER = 'CLIENT1' and REGION like 'Ontario%' 
OR USER = 'CLIENT2' and REGION = 'Quebec' 
ENFORCED FOR ALL ACCESS 
ENABLE

ALTER TABLE db2inst1.sales activate row access control

Note that to activate or deactivate row access control on a table, the id being used must have SECADM and minimally ALTER authority on the table. Creating permissions is something that can be done only as an ID with SECADM.

Accessing Data

First, let’s see as DB2INST1 this whole table.

$ db2 "select * from db2inst1.sales"

SALES_DATE SALES_PERSON    REGION          SALES      
---------- --------------- --------------- -----------
12/31/2005 LUCCHESSI       Ontario-South             1
12/31/2005 LEE             Ontario-South             3
12/31/2005 LEE             Quebec                    1
12/31/2005 LEE             Manitoba                  2
12/31/2005 GOUNOT          Quebec                    1
03/29/2006 LUCCHESSI       Ontario-South             3
03/29/2006 LUCCHESSI       Quebec                    1
03/29/2006 LEE             Ontario-South             2
03/29/1996 LEE             Ontario-North             2
03/29/2006 LEE             Quebec                    3
03/29/2006 LEE             Manitoba                  5
03/29/2006 GOUNOT          Ontario-South             3
03/29/2006 GOUNOT          Quebec                    1
03/29/2006 GOUNOT          Manitoba                  7
03/30/2006 LUCCHESSI       Ontario-South             1
03/30/2006 LUCCHESSI       Quebec                    2
03/30/2006 LUCCHESSI       Manitoba                  1
03/30/2006 LEE             Ontario-South             7
03/30/2006 LEE             Ontario-North             3
03/30/2006 LEE             Quebec                    7
03/30/2006 LEE             Manitoba                  4
03/30/2006 GOUNOT          Ontario-South             2
03/30/2006 GOUNOT          Quebec                   18
03/31/2006 GOUNOT          Manitoba                  1
03/31/2006 LUCCHESSI       Manitoba                  1
03/31/2006 LEE             Ontario-South            14
03/31/2006 LEE             Ontario-North             3
03/31/2006 LEE             Quebec                    7
03/31/2006 LEE             Manitoba                  3
03/31/2006 GOUNOT          Ontario-South             2
03/31/2006 GOUNOT          Quebec                    1
04/01/2006 LUCCHESSI       Ontario-South             3
04/01/2006 LUCCHESSI       Manitoba                  1
04/01/2006 LEE             Ontario-South             8
04/01/2006 LEE             Ontario-North             -
04/01/2006 LEE             Quebec                    8
04/01/2006 LEE             Manitoba                  9
04/01/2006 GOUNOT          Ontario-South             3
04/01/2006 GOUNOT          Ontario-North             1
04/01/2006 GOUNOT          Quebec                    3
04/01/2006 GOUNOT          Manitoba                  7

  41 record(s) selected.

The users I want to access the table must also have permissions on the table at the table level, so I’ve granted them both select access:

$ db2 grant select on db2inst1.sales to user client1, user client2
DB20000I  The SQL command completed successfully.

Here is what client1 gets running the exact same query:

$ db2 "select * from db2inst1.sales"

SALES_DATE SALES_PERSON    REGION          SALES      
---------- --------------- --------------- -----------
12/31/2005 LUCCHESSI       Ontario-South             1
12/31/2005 LEE             Ontario-South             3
03/29/2006 LUCCHESSI       Ontario-South             3
03/29/2006 LEE             Ontario-South             2
03/29/1996 LEE             Ontario-North             2
03/29/2006 GOUNOT          Ontario-South             3
03/30/2006 LUCCHESSI       Ontario-South             1
03/30/2006 LEE             Ontario-South             7
03/30/2006 LEE             Ontario-North             3
03/30/2006 GOUNOT          Ontario-South             2
03/31/2006 LEE             Ontario-South            14
03/31/2006 LEE             Ontario-North             3
03/31/2006 GOUNOT          Ontario-South             2
04/01/2006 LUCCHESSI       Ontario-South             3
04/01/2006 LEE             Ontario-South             8
04/01/2006 LEE             Ontario-North             -
04/01/2006 GOUNOT          Ontario-South             3
04/01/2006 GOUNOT          Ontario-North             1

  18 record(s) selected.

Note how this user is not even aware that there are other rows in the table – they can only see the rows they have access to.

Now, the exact same query as client 2:

$ db2 "select * from db2inst1.sales"

SALES_DATE SALES_PERSON    REGION          SALES      
---------- --------------- --------------- -----------
12/31/2005 LEE             Quebec                    1
12/31/2005 GOUNOT          Quebec                    1
03/29/2006 LUCCHESSI       Quebec                    1
03/29/2006 LEE             Quebec                    3
03/29/2006 GOUNOT          Quebec                    1
03/30/2006 LUCCHESSI       Quebec                    2
03/30/2006 LEE             Quebec                    7
03/30/2006 GOUNOT          Quebec                   18
03/31/2006 LEE             Quebec                    7
03/31/2006 GOUNOT          Quebec                    1
04/01/2006 LEE             Quebec                    8
04/01/2006 GOUNOT          Quebec                    3

  12 record(s) selected.

One of the ways that users may be able to get around access controls is to run aggregate queries and use them to guess at other values. The row access controls are applied as a part of every query, so this is not a risk with RCAC. Here are some examples, as client2, that show client2 only accessing the data it is allowed to:

$ db2 "select sum(sales) from db2inst1.sales"

1          
-----------
         53

  1 record(s) selected.

$ db2 "select avg(sales) from db2inst1.sales"

1          
-----------
          4

  1 record(s) selected.

$ db2 "select min(sales) from db2inst1.sales"

1          
-----------
          1

  1 record(s) selected.

$ db2 "select max(sales) from db2inst1.sales"

1          
-----------
         18

  1 record(s) selected.

After trying this experiment, I wanted to ensure I could control access to a row based on some column in that row. To test this, I created a new user called manitoba, and granted it basic permisions:

$ db2 grant select on db2inst1.sales to user manitoba
DB20000I  The SQL command completed successfully.

I then changed my row permission definition to also allow a user to access rows if the user name is the same as the value in the REGION column:

CREATE or REPLACE PERMISSION check_region on db2inst1.sales for rows
WHERE VERIFY_ROLE_FOR_USER (USER, 'DBA') = 1
    OR USER = 'CLIENT1' and REGION like 'Ontario%'
    OR USER = 'CLIENT2' and REGION = 'Quebec'
    OR USER = lower(REGION)
ENFORCED FOR ALL ACCESS
ENABLE;

Then, as manitoba, I queried the table:

$ db2 "select * from db2inst1.sales"

SALES_DATE SALES_PERSON    REGION          SALES      
---------- --------------- --------------- -----------

  0 record(s) selected.

This didn’t work, because I used lower(REGION) – internally db2 handles all users in upper case (unless you enclose them in quotes – much like table and other object names). So I have to use upper(REGION) instead:

CREATE or REPLACE PERMISSION check_region on db2inst1.sales for rows
WHERE VERIFY_ROLE_FOR_USER (USER, 'DBA') = 1
    OR USER = 'CLIENT1' and REGION like 'Ontario%'
    OR USER = 'CLIENT2' and REGION = 'Quebec'
    OR USER = upper(REGION)
ENFORCED FOR ALL ACCESS
ENABLE;

Then as manitoba, I ran the query again:

$ db2 "select * from db2inst1.sales"

SALES_DATE SALES_PERSON    REGION          SALES      
---------- --------------- --------------- -----------
12/31/2005 LEE             Manitoba                  2
03/29/2006 LEE             Manitoba                  5
03/29/2006 GOUNOT          Manitoba                  7
03/30/2006 LUCCHESSI       Manitoba                  1
03/30/2006 LEE             Manitoba                  4
03/31/2006 GOUNOT          Manitoba                  1
03/31/2006 LUCCHESSI       Manitoba                  1
03/31/2006 LEE             Manitoba                  3
04/01/2006 LUCCHESSI       Manitoba                  1
04/01/2006 LEE             Manitoba                  9
04/01/2006 GOUNOT          Manitoba                  7

  11 record(s) selected.

This time I got the expected results.

There are a couple of confining factors with this approach. First, we have to remember that DB2 on AIX/Linux only works with IDs that are 8 characters or less. Had I chosen a region with a longer name this would not work. Likely if I needed a more complex ID to REGION mapping, I could write a function to perform that.

While it’s not too constraining, this also means that you could not in a multi-tennant situation get more than about 2.8 trillion tenants in a single database. Not something I could imagine anyone wanting to do.

Performance

The next question that comes to mind is performance. DB2 applies the conditions from row permissions as a part of the query. That means that a row permission could impact performance. You can see this in an explain of the simple query with and without the row permissions in place.

First, with the final row permission above in place:

 
Original Statement:
------------------
select
  *
from
  db2inst1.sales


Statement With FGAC Applied:
---------------------------
SELECT
  Q1.SALES_DATE AS "SALES_DATE",
  Q1.SALES_PERSON AS "SALES_PERSON",
  Q1.REGION AS "REGION",
  Q1.SALES AS "SALES"
FROM
  DB2INST1.SALES AS Q1
WHERE
  ((((VERIFY_ROLE_FOR_USER(3, $INTERNAL_FUNC$(), USER, 'DBA') = 1 SELECTIVITY 1.000000) OR
     ((USER = 'CLIENT1') AND
      (Q1.REGION LIKE 'Ontario%'))) OR
    ((USER = 'CLIENT2') AND
     (Q1.REGION = 'Quebec'))) OR
   (USER = UPPER(Q1.REGION)))

Optimized Statement:
-------------------
SELECT
  Q1.SALES_DATE AS "SALES_DATE",
  Q1.SALES_PERSON AS "SALES_PERSON",
  Q1.REGION AS "REGION",
  Q1.SALES AS "SALES"
FROM
  DB2INST1.SALES AS Q1
WHERE
  ((((VERIFY_ROLE_FOR_USER(3, $INTERNAL_FUNC$(), USER, 'DBA') = 1 SELECTIVITY 1.000000) OR
     ((USER = 'CLIENT1') AND
      (Q1.REGION LIKE 'Ontario%'))) OR
    ((USER = 'CLIENT2') AND
     (Q1.REGION = 'Quebec'))) OR
   (USER = UPPER(Q1.REGION)))

Access Plan:
-----------
        Total Cost:             6.8311
        Query Degree:           1

      Rows
     RETURN
     (   1)
      Cost
       I/O
       |
       41
     TBSCAN
     (   2)
     6.8311
        1
       |
       41
 TABLE: DB2INST1
      SALES
       Q1

Explain without perms:
Original Statement:
------------------
select
  *
from
  db2inst1.sales

Note the section Statement With FGAC Applied: – it shows how RCAC/FGAC changes the query. In this case, the access plan is not changed, but it is worth noting how the access plan looks without the RCAC/FGAC:

Optimized Statement:
-------------------
SELECT
  Q1.SALES_DATE AS "SALES_DATE",
  Q1.SALES_PERSON AS "SALES_PERSON",
  Q1.REGION AS "REGION",
  Q1.SALES AS "SALES"
FROM
  DB2INST1.SALES AS Q1

Access Plan:
-----------
        Total Cost:             6.82242
        Query Degree:           1

      Rows
     RETURN
     (   1)
      Cost
       I/O
       |
       41
     TBSCAN
     (   2)
     6.82242
        1
       |
       41
 TABLE: DB2INST1
      SALES
       Q1

It would be critical to test the performance impact of RCAC/FGAC on your specific environment and be careful of performance considerations when coding your rules.

Insert, Update, and Delete

First, I need to grant more general permissions to my manitoba id:

$ db2 "grant update, insert, delete on db2inst1.sales to user manitoba"
DB20000I  The SQL command completed successfully.

Now let’s try some basic operations as manitoba:

$ db2 "insert into db2inst1.sales VALUES (date('11/13/2014'), 'EMBER', 'Manitoba', 5)"
DB20000I  The SQL command completed successfully.

As expected, that works. But what if we try to insert sales for a region the manitoba user doesn’t have access to?

$ db2 "insert into db2inst1.sales VALUES (date('11/13/2014'), 'EMBER', 'Quebec', 1)"
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL20471N  The INSERT or UPDATE statement failed because a resulting row did 
not satisfy row permissions.  SQLSTATE=22542

A nice straight-forward error message and we see that manitoba cannot insert the data. Now let’s see what happens with updates. First an update to a row we know manitoba has access to:

$ db2 "update db2inst1.sales set sales=5 where sales_person='EMBER'"
DB20000I  The SQL command completed successfully.

Now what about updating that row from being a row we have access to to one that manitoba doesn’t?

$ db2 "update db2inst1.sales set region='Quebec' where sales_person='EMBER'"
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL20471N  The INSERT or UPDATE statement failed because a resulting row did 
not satisfy row permissions.  SQLSTATE=22542

Same error as on insert. Now what if we try to delete somehting manitoba does not have access to?

$ db2 "delete from db2inst1.sales where region='Quebec'"
SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a 
query is an empty table.  SQLSTATE=02000

Not quite as obvious an error message, but understandable given how this works. What if we do a delete that spans both rows manitoba has access to and those it does not?

$ db2 -m "delete from db2inst1.sales where sales=1"
  Number of rows affected : 4
DB20000I  The SQL command completed successfully.

The delete runs successfully, but it only deletes the 4 rows it has access to, not the other rows in the table that would be deleted if the same statement were run by a user with access to all the rows in the table.

Default

Note that once row access control is enabled on a table, if there is no row permission created on the table, then no one will have access to the data. This is why we generally create the row permission first – because if we didn’t, then there would be a period when no one would be able to access the table.