In the previous blog
(https://www.triton.co.uk/db2-10-1-rcac-hints-tips-part-1/) we were discussing the Column Mask part of the RCAC feature. To recap, there are 2 parts to RCAC
• Column masks : which over-write or obscure returned values with specified alternatives
• Row permissions : which only return the rows of data that you are permitted to see
I’m going to try and illustrate some of the concerns that can accompany the use of this feature. I’m going to use the same example as in the previous blog: 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_ID
from LOGBOOK.logbook
where year(Brakes_Off) = ‘2011’
order by Brakes_Off
The output from this query would currently be as shown below (Note that the values in the column FROM_AIRFIELD are obfuscated because the Column mask from the previous blog is still in operation).
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 –
Row Permissions
The second task the DBA wants to accomplish is to implement a row permission that will only show the flights with a Check Pilot. This will be created and enabled as follows:
CREATE or REPLACE PERMISSION CHECK_RIDES ON LogBook FOR ROWS WHERE CHECK_PILOT_ID is not null ENFORCED FOR ALL ACCESS enable
ALTER TABLE LogBook ACTIVATE ROW ACCESS CONTROL
The result set that the user query retrieves will now just be a single row as shown below. Note that the column mask is now operating in conjunction with the row permission, as the Airfield ICAO code is still obfuscated.
Now take another look at what the optimizer has done under the covers
The WHERE clause has been modified and a different access path has been invoked.
The user query execution estimate using the row permission is slightly worse than the original. That may not always be the case but implementing a RCAC could have significant performance considerations and it is worth checking out what the optimizer does to the access plan before releasing it into production. From a user or application point of view, a query with an acceptable execution time may change to unacceptable without the application query having been altered.
In the next blog I’ll look at using Column Masks and Row Permissions in conjunction and some of the pitfalls associated with using, and removing them.