In the previous two blogs
(https://www.triton.co.uk/db2-10-1-rcac-hints-tips-part-1/ and
https://www.triton.co.uk/rcac-usage-considerations-part-2/ ) we were discussing the Column Mask and Row Permissions as two separate entities, with potentially different impacts to your database. I’m now going to try and demonstrate some of the issues that can occur when you have the two features enabled on the same table.
I’m going to use the same example as in the previous blog to illustrate these issues.: 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:
Select LOGBOOK_KEY, Date(Brakes_Off) as FLIGHT_DATE, REGISTRATION, CAPACITY, PILOT, FROM_AIRFIELD, CHECK_PILOT_IDfrom LOGBOOK.logbook where year(Brakes_Off) = ‘2011’ order by Brakes_Off
The output from this query would currently be as shown below. There are actually 10 rows of data that would qualify without RCAC being in place, but the Row Permissions have only allowed one row to be returned, because only one row has a CHECK_PILOT_ID value. The values in the column FROM_AIRFIELD are altered (the returned row should have the value EGBT) because the Column mask from the first blog is still in operation.
Masks and Permissions in conjunction
The other little gotcha to bear in mind is how the column mask and the row permissions actually operate. The column mask is being applied to data after it has been retrieved and is changing your view of what is there. That’s how we are able to insert data that we are not subsequently allowed to see. We could also UPDATE and DELETE data that is protected, or rather, obfuscated by the mask. Not so with the row permission.
Consider this statement
Insert into Logbook (REGISTRATION,CAPACITY,PILOT,FROM_AIRFIELD,TO_AIRFIELD,BRAKES_OFF,BRAKES_ON,Landings,COMMENTS)Values(‘G-AVLT’,’P1S’,’Self’,’EGBT’,’EGBT’,’2011-07-30-14.35.00.000000′,’2011-07-30-15.35.00.000000′,6,’TEST ENTRY’)
The problem with this is that the data doesn’t include a Check Pilot ID, so the result you get is:
SQL20471N The INSERT or UPDATE statement failed because a resulting row did not satisfy row permissions. SQLSTATE=22542
That’s pretty clear, but what if you try and update or delete a row that you’re not supposed to see? I happen to know that there’s a row in the table with a Primary Key of 232 (see below for the results of the query before RCAC was applied):
But if I try and delete it, whilst the RCAC Row Permissions are in place, the response is:
SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000
It is there but we’re not allowed to see it. The response is slightly misleading, if logically accurate.
Row permissions are applied before the query is executed and column masks after. Hence the concern about the access paths when row permissions are applied and hence the slightly misleading responses to UPDATE and DELETE statements.
Removing Masks and Permissions
And thereby hangs the last gotcha I wanted to mention. If the DBA now drops the column mask, the users query results set becomes:
Note that the Airfield ICAO code is once again visible, but that the result set only contains a single row because the row permission is still in place. So, now, the DBA drops the row permission and the users result set becomes:
This is because there is nothing now to refer to in terms of row permissions, so the default result set will be nothing. The row permissions will be examined to find out what the user is allowed to see; if there are no row permissions, they’re not allowed to see anything.
The DBA can remove the RCAC rows and permissions by running the two commands below and all the users data will be visible as it was in the first query we ran.
ALTER TABLE LogBook DEACTIVATE ROW ACCESS CONTROL ;ALTER TABLE LogBook DEACTIVATE COLUMN ACCESS CONTROL;
RCAC is a great feature and offers significant security options for the DBA, the business and the application but, like just about everything in the database, it’s got something that will come back and bite you if you’re not ready for it.
Download RCAC Hints and Tips.