-
Notifications
You must be signed in to change notification settings - Fork 3
BigSQL, RCAC
RCAC (row and column access control) is very nice DB2 feature inherited by BigSQL. In BigSQL, it can also be applied for Hadoop tables. It is explained in the following Knowledge Center articles. https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.admin.sec.doc/doc/c0057423.html
https://www.ibm.com/support/knowledgecenter/en/SSCRJT_6.0.0/com.ibm.swg.im.bigsql.doc/doc/admin_rcac_overview.html
Examples provided there are long and complicated, I will try to make it clear using simpler scenarios.
The RCAC complements general SQL privileges. SQL privileges are applied to the whole SQL object. For instance, the user can scan all table or is denied access at all. The RCAC is more granular, the user is allowed to scan only the subset of the table rows making the rest of the table invisible. The SQL privileges take precedence over RCAC, firstly the user should be granted access to the table and secondly the appropriate RCAC policy is resolved.
Let's create a simple DB2 table.
db2 "create schema testdb"
db2 "create hadoop table testdb.testra (x int, name varchar(100))"
db2 "insert into testdb.testra values(1,'name1'),(2,'name2'),(3,'name3')<br
Assume four DB2 users.
| User | Rows visible |
|---|---|
| user1 | Rows X=1 |
| user2 | Rows X=2 |
| user3 | All rows |
| user4 | No rows |
Create RCAC policy
db2 "CREATE OR REPLACE PERMISSION testdb_rows ON testdb.testra as T for rows where (TRIM(SESSION_USER) = 'USER1' AND T.X = 1) OR (TRIM(SESSION_USER) = 'USER2' AND T.X = 2) OR TRIM(SESSION_USER) = 'USER3' ENFORCED FOR ALL ACCESS enable"
Enable table for RCAC
db2 "ALTER TABLE testdb.testra ACTIVATE ROW ACCESS CONTROL"
That's all, now try to run SELECT command against the table after authenticating as appropriate user.
db2 "select * from testdb.testra"
As user1
X NAME
----------- ----------------------------------------------------------------------------------------------------
1 name1
1 record(s) selected.
As user2
X NAME
----------- ----------------------------------------------------------------------------------------------------
2 name2
1 record(s) selected.
As user3
X NAME
----------- ----------------------------------------------------------------------------------------------------
1 name1
2 name2
3 name3
3 record(s) selected.
As user4
X NAME
----------- ----------------------------------------------------------------------------------------------------
0 record(s) selected.
RCAC policy is also enforced for other DML commands, UPDATE, DELETE and INSERT. For instance, user1 is allowed only to touch or insert rows meeting the expression defined in CREATE PERMISSION command, here X=1, and rejected for other rows.
As user1
db2 "insert into testdb.testra values(1,'name11')"
DB20000I The SQL command completed successfully.
db2 "insert into testdb.testra values(2,'name22')"
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
The SQL query in the WHERE clause of the CREATE PERMISSION statement can be also more complicated and meet the whole variety of demands. There are a lot of restrictions on this query: https://www.ibm.com/support/knowledgecenter/en/SSCRJT_6.0.0/com.ibm.swg.im.bigsql.commsql.doc/doc/r0057429.html
Assume we have a list of managers and a list of employees supervised by them. The managers and employees are linked by a foreign key in employees table.
db2 "create hadoop table testdb.managers(ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(100))"
db2 "create hadoop table testdb.employees (ENAME VARCHAR(100), MANID INTEGER NOT NULL, FOREIGN KEY (MANID) REFERENCES testdb.managers(ID))"
Insert some data
db2 "insert into testdb.managers values(1,'user1'),(2,'user2')"
db2 "insert into testdb.employees values ('empl11',1),('empl12',1),('empl21',2),('empl22',2)"
We want to create RCAC policy that the manager has access only to subordinate employees.
db2 "CREATE OR REPLACE PERMISSION manager_rows ON testdb.employees as E for rows where E.MANID IN (SELECT M.ID FROM testdb.managers as M where UPPER(NAME)=TRIM(SESSION_USER)) ENFORCED FOR ALL ACCESS enable"
Enable RCAC for testdb.employees:
db2 "ALTER TABLE testdb.employees ACTIVATE ROW ACCESS CONTROL"
RCAC is activated only for testdb.empoyees table, there is no need to do the same for testdb.managers.
Now run:
db2 "select * from testdb.employees"
As user1:
ENAME MANID
---------------------------------------------------------------------------------------------------- -----------
empl11 1
empl12 1
2 record(s) selected.
As user2:
ENAME MANID
---------------------------------------------------------------------------------------------------- -----------
empl21 2
empl22 2
2 record(s) selected.
As user3:
ENAME MANID
---------------------------------------------------------------------------------------------------- -----------
0 record(s) selected.
The same applies to INSERT command. "User2" manager can only add employees supervises by him, not by other managers.
As user2:
db2 "insert into testdb.employees values ('empl32',2)"
DB20000I The SQL command completed successfully.
db2 "select * from testdb.employees"
ENAME MANID
---------------------------------------------------------------------------------------------------- -----------
empl32 2
empl21 2
empl22 2
3 record(s) selected.
db2 "insert into testdb.employees values ('empl31',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
- Every table expected to be under RCAC policy should be activated by ALTER TABLE <table> ACTIVATE ROW ACCESS CONTROL. Specifying the policy by using CREATE PERMISSION statement without activating an appropriate table does not cause any effect
- The policy can be lifted or reactivated by ALTER PERMISSION / ENABLE/DISABLE statement.
- It is a good practice to specify a policy for a role or group instead of a specific user. In the WHERE clause use VERIFY_ROLE_FOR_USER or VERIFY_GROUP_FOR_USER function.
- More than one policy can be created for a single table.
- For HADOOP tables, the policy is implemented purely in the BigSQL engine. No changes are applied to the raw HDFS data. Regardless of the BigSQL RCAC policy, the data is available from HDFS level or Hive SQL level.
RCAC can be used not only to cut tables horizontally but also vertically. Columns containing confidential or vulnerable data can be masked. https://www.ibm.com/support/knowledgecenter/en/SSCRJT_6.0.0/com.ibm.swg.im.bigsql.commsql.doc/doc/r0058564.html
A masking rule is applied for a single column in a single table. The expression to determine how a column is to be masked is standard SQL CASE clause.
Assume testdb.managers table as above and testdb.emplsalaries table containing also SALARY column.
db2 "create hadoop table testdb.emplsalaries (ENAME VARCHAR(100), MANID INTEGER NOT NULL, salary INTEGER, FOREIGN KEY (MANID) REFERENCES testdb.managers(ID)) "
Insert some data:
db2 "insert into testdb.emplsalaries values ('empl11',1,100),('empl12',1,200),('empl21',2,300),('empl22',2,400)"
We want to create a mask policy that SALARY column in testdb.emplsalaries is exposed only for the manager. Create RCAC policy:
db2 "CREATE OR REPLACE MASK empl_salaries ON testdb.emplsalaries as S FOR COLUMN SALARY RETURN CASE WHEN S.MANID IN (SELECT M.ID FROM testdb.managers AS M WHERE TRIM(SESSION_USER)=UPPER(M.NAME)) THEN SALARY ELSE -1 END ENABLE"
The CASE expression after RETURN keyword specifies the masking rule.
Enable RCAC for column masking on table testdb.emplsalaries:
> db2 "ALTER TABLE testdb.emplsalaries ACTIVATE COLUMN ACCESS CONTROL"
Now run the SELECT statement after authenticating as different users.
db2 "ALTER TABLE testdb.emplsalaries ACTIVATE COLUMN ACCESS CONTROL"
As user1
NAME MANID SALARY
--------------------------------------------------------------------------------------- ----------- -----------
empl11 1 100
empl12 1 200
empl21 2 300
empl22 2 400
As user2
ENAME MANID SALARY
---------------------------------------------------------------------------------------- ----------- -----------
empl11 1 -1
empl12 1 -1
empl21 2 300
empl22 2 400
As user3 (all column is masked, the user3 does not manage anybody).
ENAME MANID SALARY
-------------------------------------------------------------------------------------- ----------- -----------
empl11 1 -1
empl12 1 -1
empl21 2 -1
empl22 2 -1
- More than one MASK policy can be defined for a single table. But any column could bear at most one policy.
- A table expected to be restricted by MASK rule should be activated for masking by ALTER TABLE <table> ACTIVATE COLUMN ACCESS CONTROL"
- The MASK policy can be lifted or reactivated by ALTER MASK / ENABLE/DISABLE statement.