Triton Db2 Geek

Confessions of a DB2 geek

IBM Gold Consultant Program and IBM Premier business Partner

RCAC: Usage Considerations Part 2

April 3rd, 2013 - by

In the previous blog
(http://www.triton.co.uk/confessions-of-a-db2-geek/article/65/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.

 

RCAC2_pic3

 

Now take another look at what the optimizer has done under the covers

 

RCAC2_pic4

 

The WHERE clause has been modified and a different access path has been invoked.

 

RCAC2_pic6

 

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.

 

Comments

« »

Tag Archives