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:
- 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.
- With the data oriented protection, the RCAC applies to all kind of applications, tools etc. which access the table data.
- 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 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')
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
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
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
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
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
----------- ------------------ ---------- --------------- --------------------- ---------------------- ------------------
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
----------- ------------------ ---------- --------------- --------------------- ---------------------- ------------------
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
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
----------- ------------------ ---------- --------------- --------------------- ---------------------- ------------------
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
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
09 August 2019
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"Federation Server, Security, RCAC, LBAC","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF006","label":"CentOS"},{"code":"PF016","label":"Linux"},{"code":"PF043","label":"Red Hat"},{"code":"PF048","label":"SUSE"},{"code":"PF031","label":"Ubuntu"},{"code":"PF033","label":"Windows"}],"Version":"10.1, 10.5, 11.1,11.5","Edition":"AESE, Developer","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Product Synonym
Db2, DB2 LUW, DB2 LUW BigSQL
Was this topic helpful?
Document Information
Modified date:
09 August 2019
UID
ibm10967077