Row and Column Access Control (RCAC) in DB2 10.1 for LUW is a neat and simple means of shielding your data from users who don’t need to see it. It’s really two things melded together:
• Row permissions : which only return the rows of data that you are permitted to see
• Column masks : which over-write or obscure returned values with specified alternatives
Used together they can provide a useful security layer but there are a few usage considertaions to be aware of. I will illustrate this in a series of blogs.
To set the scene, I have a table called LOGBOOK which contains details of a private pilot’s flying log. A user query has been run to look at what was logged in the year 2011. The SQL for this query is shown below and this query will be used in all the RCAC blogs :
Select LOGBOOK_KEY,
Date(Brakes_Off) as FLIGHT_DATE,
REGISTRATION,
CAPACITY,
PILOT,
FROM_AIRFIELD,
CHECK_PILOT_ID
from LOGBOOK.logbook
where year(Brakes_Off) = ‘2011’
order by Brakes_Off
LOGBOOK_KEY FLIGHT_DATE REGISTRATION CAPACITY PILOT FROM_AIRFIELD CHECK_PILOT_ID
———– ———– ———— ——– ————————- ————- —————
232 24.01.2011 G-AYJR P1 Self EGBT –
233 17.04.2011 HB-CFN P1S Self LFSB –
234 30.04.2011 G-AYJR P1S Self EGBT UKFE236606A/A
235 21.06.2011 HB-CFN P1S Self LFSB –
236 21.06.2011 HB-CFN P1S Self LFGB –
237 23.07.2011 G-ETME P1S Self EGLM –
238 28.07.2011 HB-CFN P1S Self LFSB –
239 28.07.2011 HB-CFN P1S Self LSZQ –
240 11.10.2011 G-BORK P1 Self EGBT –
The LOGBOOK_KEY is a generated Primary Key, the airfields are shown using standard International Civil Aviation Organization (ICAO) codes and there is one row with a check pilot ID, indicating that the flight was a test of some sort.
Column Masks
The DBAs first task is to obfuscate the ICAO codes; specifically to change the value to UK where it’s an airfield in the UK (as indicated by a leading letter E) and XXXX for anywhere else. The column mask definition for this is:
CREATE or REPLACE MASK FOREIGN_AIRFIELD ON LogBook FOR
COLUMN from_airfield RETURN
CASE
WHEN SUBSTR(from_airfield,1,1) = ‘E’ THEN ‘UK’
ELSE ‘XXXX’
END
ENABLE;
And it is activated with this statement
ALTER TABLE LogBook ACTIVATE COLUMN ACCESS CONTROL;
Now the user running the same flight details query will see the following results
LOGBOOK_KEY FLIGHT_DATE REGISTRATION CAPACITY PILOT FROM_AIRFIELD CHECK_PILOT_ID
———– ———– ———— ——– ————————- ————- —————
232 24.01.2011 G-AYJR P1 Self UK –
233 17.04.2011 HB-CFN P1S Self XXXX –
234 30.04.2011 G-AYJR P1S Self UK UKFE236606A/A
235 21.06.2011 HB-CFN P1S Self XXXX –
236 21.06.2011 HB-CFN P1S Self XXXX –
237 23.07.2011 G-ETME P1S Self UK –
238 28.07.2011 HB-CFN P1S Self XXXX –
239 28.07.2011 HB-CFN P1S Self XXXX –
240 11.10.2011 G-BORK P1 Self UK –
There are two things to be aware of here from the user’s point of view:
1. The fact that you can’t see the underlying data does not prevent you actioning it
2. Your SQL will have the column mask inserted into it by the optimizer
To illustrate the first point, the user could run this INSERT statement
Insert into Logbook
(REGISTRATION,CAPACITY,PILOT,FROM_AIRFIELD,TO_AIRFIELD,BRAKES_OFF,BRAKES_ON,Landings,COMMENTS)
Values
(‘G-AVLT’,’P1S’,’Self’,’LSZQ’,’LSZQ’,’2011-07-30-14.35.00.000000′,’2011-07-30-15.35.00.000000′,6,’TEST ENTRY’)
which includes a non-UK ICAO code (LSZQ). That will work fine, but when the user re-runs the query, an extra row is visible but not the ICAO code that was just inserted (LOGBOOK_KEY 14).
LOGBOOK_KEY FLIGHT_DATE REGISTRATION CAPACITY PILOT FROM_AIRFIELD CHECK_PILOT_ID
———– ———– ———— ——– ————————- ————- —————
232 24.01.2011 G-AYJR P1 Self UK –
233 17.04.2011 HB-CFN P1S Self XXXX –
234 30.04.2011 G-AYJR P1S Self UK UKFE236606A/A
235 21.06.2011 HB-CFN P1S Self XXXX –
236 21.06.2011 HB-CFN P1S Self XXXX –
237 23.07.2011 G-ETME P1S Self UK –
238 28.07.2011 HB-CFN P1S Self XXXX –
239 28.07.2011 HB-CFN P1S Self XXXX –
14 30.07.2011 G-AVLT P1S Self XXXX –
240 11.10.2011 G-BORK P1 Self UK –
This is because the Column Mask was created without any context checking. Anyone accessing this data will now have the data obfuscated for them. In order to address this issue, the column mask definition needs to be changed to allow some users, maybe just the DBAs, to see the raw data. This can be accomplished by setting up a ROLE and assigning specific users to that ROLE, e.g.
CREATE ROLE DBA
GRANT ROLE DBA to USER userid
And including the following clause as the first WHEN clause in the Column Mask definition
WHEN VERIFY_ROLE_FOR_USER(SESSION_USER,’DBA’) = 1 THEN from_airfield
The contextual clauses in the Column Mask definition can be as complex or as simple as you like. For example, I can redefine the mask as:
CREATE or REPLACE MASK FOREIGN_AIRFIELD ON LogBook FOR
COLUMN from_airfield RETURN
CASE
WHEN SESSION_USER = ‘MGILLIS’ THEN from_airfield
WHEN SUBSTR(from_airfield,1,1) = ‘E’ THEN ‘UK’
ELSE ‘XXXX’
END
ENABLE;
This would now allow me, as the DBA, to see all the data that exists in the table in its raw form. However, the user who just inserted a row will still not see their own values.If the requirement is that the user always gets to see their own data regardless of the rest of the Column Mask definition, then the following may be more appropriate:
WHEN SESSION_USER = LAST_UPDATED_BY THEN from_airfield
To the second point, you might like to run an EXPLAIN on your query and then a db2exfmt to see what is going on under the covers. The output from db2exfmt will show the Original Statement and the Optimised Statement. What you will also get in between these two is a section headed Statement With FGAC Applied. The user’s original query will have changed as shown below:
Note that the column mask CASE statement is now part of your query and will also show up in the Optimized Statement. This shouldn’t have any significant effect at this stage (the Timeron value and the access path should be the same), but bear in mind what the optimizer is up to.
In the next posting, I will show the relevance of this when Row Permissions are being used.