Db2 (On Premise and Cloud)

RCAC in Federation Server 

Mon August 12, 2019 12:26 PM

ORIGINAL POST is available here
~~~~~~~~~~~~~~~~~~~~~~~~~

Education


Abstract

IBM Db2 LUW Federation Server can federate to various kinds of data source, such as relational databases, non-relational databases and flat file etc. For relational database, there are different levels of security control and one of them is RCAC (Row and Column Access Control). This article tries to explain what RCAC is and how to configure and enable RCAC with example. And finally this article will explain how RCAC works in federation server and how to federate to the data source with RCAC enabled.

Content

What is RCAC

IBM Db2 supports various kinds of security mechanism. In Db2 security model, there are different kinds of security control to protect data, for example Db2 supports database level, table level, role based, and group based access control. And all of these different levels of access control work together to protect the data. From Db2 v10.1, a new level of access control is introduced which is RCAC (Row and Column Access Control), RCAC sometimes is also referred to as Fine-Grained Access Control or FGAC. RCAC provides a more detailed access control over row and column and it can be used as the complement of table privilege.

RCAC can ensure that a user can only access the data which he/she has authority and necessarity for his/her work on the row or column level. For example given a RCAC enabled table, we can define that a user USERA can only access row 1, 3, 5 while user USERB can only access row 2, 4, 6; Similarly, the user USERA is able to get the actual value of column1, while user USERB can not get the actual value of this column, and instead user USERB can only get a pre-defined masked value of column1, which the actual value of this column is not exposed to USERB as defined by RCAC.

RCAC implements access control in following rules:
  • Row permission
    • A row permission is a database object that expresses a row access control rule for a specific table.
    • A row access control rule is an SQL search condition that describes what set of rows a user has access to.
  • Column mask
    • A column mask is a database object that expresses a column access control rule for a specific column in a table.
    • A column access control rule is an SQL CASE expression that describes what column values a user is permitted to see and under what conditions.
Compares to other security mechanisms, RCAC has several advantages over them:
  1.  RCAC provides data oriented protection, so no database user is inherently exempted from the row and column access control rules. This means no matter who needs to read data from the protected table the RCAC rules will be examined. Only users with security administrator (SECADM) authority can manage row and column access controls within a database.
  2. With the data oriented protection, the RCAC applies to all kind of applications, tools etc. which access the table data.
  3. No application changes are required to take advantage of this additional layer of data security.

For more details of the RCAC please refer to this https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.sec.doc/doc/c0057423.html.


How to setup and enable RCAC

Given the basic concept of RCAC, next I would like to explain how to configure and enable RCAC for a table and run some queries to verify the result.
I will start from the very beginning to show how to configure RCAC for a table.

  • Create a new database.
    db2 create database serialdb

  • Create table and roles.
   CONNECT TO SERIALDB USER DB2INST1 USING ******
   CREATE ROLE PCP
   GRANT ROLE PCP TO USER DASUSR1
   CREATE ROLE PATIENT
   GRANT ROLE PATIENT TO USER SESSIONUSER1
  
-- Create table:
   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))
   GRANT SELECT, UPDATE ON TABLE PATIENT TO ROLE PCP
   GRANT SELECT ON TABLE PATIENT TO ROLE PATIENT
   INSERT INTO PATIENT VALUES('123-55-1234', 'MAX', 'Max', 'First Strt', 'hypertension', 89.70,'LEE')
   INSERT INTO PATIENT VALUES('123-58-9812', 'MIKE', 'Mike', 'Long Strt', null, 8.30,'JAMES')
   INSERT INTO PATIENT VALUES('123-11-9856', 'SAM', 'Sam', 'Big Strt', null, 0.00,'LEE')
   INSERT INTO PATIENT VALUES('123-19-1454', 'DUG', 'Dug', 'Good Strt', null, 0.00,'JAMES')

  • Create row permission and column masks.  
   -- Create row permission:
   CREATE PERMISSION ROW_ACCESS1 ON PATIENT FOR ROWS WHERE (VERIFY_ROLE_FOR_USER(SESSION_USER,'PATIENT')  = 1 AND
   PATIENT.USERID = 'MIKE') ENFORCED FOR ALL ACCESS ENABLE
   -- Create another row permission, a table can be enabled multiple row permissions.
   CREATE PERMISSION ROW_ACCESS2 ON PATIENT FOR ROWS WHERE (VERIFY_ROLE_FOR_USER(SESSION_USER,'PCP')  = 1) ENFORCED FOR
   ALL ACCESS ENABLE
   ALTER TABLE PATIENT ACTIVATE ROW ACCESS CONTROL

   -- Create column mask:
   CREATE MASK ACCT_BALANCE_MASK ON PATIENT FOR
     COLUMN ACCT_BALANCE RETURN
       CASE WHEN VERIFY_ROLE_FOR_USER(SESSION_USER,'PATIENT') = 1
            THEN ACCT_BALANCE
         WHEN VERIFY_ROLE_FOR_USER(SESSION_USER, 'PCP') = 1
            THEN 1.00
        ELSE 0.00
     END
   ENABLE
   ALTER TABLE PATIENT ACTIVATE COLUMN ACCESS CONTROL

With the above example, we have successfully configured RCAC on table patient. Two row permissions has been created and one column mask has been created too.

-- Now we can do some basic tests against this table:
CONNECT TO SERIALDB USER DB2INST1 USING ******

-- We can see with secadm authority we can get all the data of the table patient:
SELECT * FROM PATIENT
SSN           USERID             NAME      ADDRESS     PHARMACY         ACCT_BALANCE   PCP_ID
-----------  ------------------ ----------  --------------- --------------------- ---------------------- ------------------
123-55-1234 MAX                Max         First Strt             hypertension        89.70                    LEE
123-58-9812 MIKE              Mike       Long Strt              -                              8.30                     JAMES
123-11-9856 SAM                 Sam        Big Strt                 -                              0.00                     LEE
123-19-1454 DUG                Dug         Good Strt             -                              0.00                     JAMES
  4 record(s) selected.

-- Now try to switch to another user sesseionuser1:
CONNECT TO SERIALDB USER SESSIONUSER1 USING ******
SELECT * FROM DB2INST1.PATIENT
SSN           USERID             NAME      ADDRESS     PHARMACY         ACCT_BALANCE   PCP_ID
-----------  ------------------ ----------  --------------- --------------------- ---------------------- ------------------
123-58-9812 MIKE              Mike       Long Strt              -                              8.30                     JAMES
1 record(s) selected.

--Now try to switch to another user dasusr1 to verify row permission and column mask:
CONNECT TO SERIALDB USER DASUSR1 USING ******
SELECT * FROM DB2INST1.PATIENT
SSN           USERID             NAME      ADDRESS     PHARMACY         ACCT_BALANCE   PCP_ID
-----------  ------------------ ----------  --------------- --------------------- ---------------------- ------------------
123-55-1234 MAX                Max         First Strt             hypertension        1.00                    LEE
123-58-9812 MIKE              Mike       Long Strt              -                              1.00                     JAMES
123-11-9856 SAM                 Sam        Big Strt                 -                              1.00                     LEE
123-19-1454 DUG                Dug         Good Strt             -                              1.00                     JAMES
  4 record(s) selected.

We can see user dasusr1 have all row access but it can not get the actual value of column 'ACCT_BALANCE'.


How RCAC impact federation nickname query

Federation server can federate to various kinds of remote data source (databases), and if the remote data source supports RCAC and also enables RCAC for some tables, what's the behavior of federation nicknames of those tables in such scenario? Let's explain this further.

Federation server is similar to a client application of database. It connects to database with specific authentication information. For example before doing a nickname query, typically user needs to first decide the host name, port, database name of remote data source. And besides that, user also needs to provide the authentication information of remote data source such as user name and password etc.

When the target of RCAC is the user who federation server uses to connect to remote data source, then the data returned from federation server will also be impacted (controlled).  For example when federation server uses user 'SESSIONUSER1' to connect to remote data source and run query against the table patient. The data will be protected and only one row will be returned.

Here is the full example:
CREATE SERVER RCAC_SERVER1 TYPE DB2/UDB VERSION 11 WRAPPER "DRDAWRAPPER" AUTHORIZATION DB2INST1 PASSWORD "******" OPTIONS (HOST 'hostname', PORT '50000',DBNAME 'SERIALDB', PUSHDOWN 'Y', DB2_MAXIMAL_PUSHDOWN 'Y')
CREATE USER MAPPING FOR USER SERVER RCAC_SERVER1 OPTIONS (REMOTE_AUTHID 'SESSIONUSER1', REMOTE_PASSWORD '******')
CREATE NICKNAME NPATIENT FOR RCAC_SERVER1.DB2INST1.PATIENT
SELECT * FROM NPATIENT
SSN           USERID             NAME      ADDRESS     PHARMACY         ACCT_BALANCE   PCP_ID
-----------  ------------------ ----------  --------------- --------------------- ---------------------- ------------------
123-58-9812 MIKE              Mike       Long Strt              -                              8.30                     JAMES
1 record(s) selected.

We can see when we use 'SESSIONUSER1' to connect to remote data source, we only get one row and the column name equals to 'Mike' which matches the row permission condition.

DROP USER MAPPING FOR USER SERVER RCAC_SERVER1
CREATE USER MAPPING FOR USER SERVER RCAC_SERVER1 OPTIONS (REMOTE_AUTHID 'DASUSR1', REMOTE_PASSWORD 'LANLAN!123')
CREATE NICKNAME NPATIENT2 FOR RCAC_SERVER1.DB2INST1.PATIENT
SELECT * FROM NPATIENT2
SSN           USERID             NAME      ADDRESS     PHARMACY         ACCT_BALANCE   PCP_ID
-----------  ------------------ ----------  --------------- --------------------- ---------------------- ------------------
123-55-1234 MAX                Max         First Strt             hypertension        1.00                    LEE
123-58-9812 MIKE              Mike       Long Strt              -                              1.00                     JAMES
123-11-9856 SAM                 Sam        Big Strt                 -                              1.00                     LEE
123-19-1454 DUG                Dug         Good Strt             -                              1.00                     JAMES
  4 record(s) selected.

When we switch to user 'DASUSR1', we can see that we wil get all rows of table patient and this is correct according to row permission 'ROW_ACCESS2' defined in remote table. We will also get the column value of 'ACCT_BALANCE' also turns to 1.00 and this is correct according to column mask we defined in the remote table.

DESCRIBE TABLE NPATIENT2
                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
SSN                                        SYSIBM    CHARACTER           11     0     Yes
USERID                                SYSIBM    VARCHAR                18     0     Yes
NAME                                   SYSIBM    VARCHAR                128   0     Yes
ADDRESS                            SYSIBM    VARCHAR                128   0     Yes
PHARMACY                        SYSIBM    VARCHAR                250  0     Yes
ACCT_BALANCE               SYSIBM    DECIMAL                 12     2     Yes
PCP_ID                                SYSIBM    VARCHAR                 18    0     Yes
  7 record(s) selected.

I show the table schema here to demonstrate that federation server can get the full metadata of one table even with RCAC enabled. And RCAC doesn't impact the metadata, it only impacts the nickname column value from federation server point of view.


Summary

RCAC provides additional and more fine-grained access control to your data. It can be used as complement of table privilege. And it offers several advantages over other access control mechanism.

Federation server can federate to tables with RCAC. From the data protection point of view, federation server acts as an application and when it tries to read data from one table, the RCAC rules will be examined too. So the data returned from federation nickname query has been screened. And from the security point of view federation server can leverage remote data source's RCAC to achieve nickname row and column access control. But Federation nickname itself doesn't support RCAC in Db2 v11.5.

Original Publication Date

2019-08-09

Product Alias/Synonym

Db2, DB2 LUW, DB2 LUW BigSQL


#Db2LUW
#Database
#RCAC

Statistics
0 Favorited
2 Views
0 Files
0 Shares
0 Downloads

Tags and Keywords

Related Entries and Links

No Related Resource entered.