DB2 V10.1 introduced Row and Column Access Control (RCAC) as a new feature for securing data within a DB2 table. Here's a look at the performance of the permissions that control row access. (In addition, a good place to get started on understanding RCAC is to RTFM [read the fine manual], especially the two scenarios listed in the manual.)
Ways RCAC Permissions Can be Defined in DB2 V10.1
In Row and Column Access Control, permissions are defined on a table to restrict rows that are exposed. Rows being exposed means that they are available to be returned in the result set, or can be inserted, or can be updated or deleted, with the limitation of the authorities the user has on the table. There are no additional columns that are added to existing tables for RCAC. There are a number of ways that RCAC permissions can be defined.
The permission can check against the authid, or the groups connected to the authid (e.g., UNIX groups), or the DB2 Roles that are granted to the authid, or even an evaluation of information is another table. Whatever is being checked is compared against a value or range of values in the target table to evaluate what rows will be exposed. The permission can be altered, turned on, turned off. Whether groups or DB2 Roles or an additional table is used to help evaluate what an authid will be allowed to render as a result set, it all takes effect in real-time. Permissions can be absolute. When created, it does not matter who you are and what your level of DB2 authority is on the server. If you do not qualify to see certain rows through that permission, you will still not see those rows.
In addition to writing row permissions using the built-in functions to verify if the SESSION_USER is in a DB2 Role or a group (read those Scenarios in the manual), the row permission can be written to evaluate data in another table. Maybe your security situation is too complex to be managed by groups or roles. A permission can be written like the following example:
CREATE PERMISSION CUSTSAT.CSATMETRIC_RCAC1 ON CUSTSAT.CSATMETRIC
FOR ROWS
WHERE EXISTS
(select 1
from MSTR.COMPANY_MQT MQT
where MQT.COMPANYID = CUSTSAT.CSATMETRIC.COMPANYID
and MQT.DBUSER in (SESSION_USER, 'NOTRESTRICTED'))
ENFORCED FOR ALL ACCESS
ENABLE;
To carry out the permission, DB2 will modify every query that runs against a table with an active permission. This is where concerns for performance should grab everyone's attention. The DB2 Explain facility can be used to see how DB2 will modify the query and to see the access path. For example, with the permission created earlier in effect, run Explain on the following query:
SELECT * FROM CUSTSAT.CSATMETRIC
Running db2exfmt (DB2 Explain table format command) we can see the following areas in the generated report:
? The original SQL statement that we ran the Explain on:
Original Statement:
------------------
select
*
from
CUSTSAT.CSATMETRIC
? The SQL after DB2 modified it with the permission defined on the table (FGAC is the abbreviation for 'fine grained access controls'):
Statement With FGAC Applied:
---------------------------
..(I omitted the list of columns)
FROM
CUSTSAT.CSATMETRIC AS Q3
WHERE
EXISTS
(SELECT
1
FROM
MSTR.COMPANY_MQT AS Q1
WHERE
((Q1.COMPANYID = Q3.COMPANYID) AND
Q1.DBUSER IN (USER, 'NOTRESTRICTED'))
)
? The SQL that has been optimized by DB2:
Optimized Statement:
--------------------
..
FROM
MSTR.COMPANY_MQT AS Q4,
CUSTSAT.CSATMETRIC AS Q5
WHERE
(Q4.COMPANYID = Q5.COMPANYID) AND
Q4.DBUSER IN (USER, 'NOTRESTRICTED') AND
(Q4.COMPANYID = INTEGER(Q5.COMPANYID))
? The Access Plan, which is of primary importance to review.
Writing a permission with a number of OR predicates against a table, like the following, may cause considerable performance problems:
CREATE PERMISSION CUSTSAT.CSATMETRIC_RCAC1 ON CUSTSAT.CSATMETRIC
FOR ROWS
WHERE exists (select 1
from MSTR.COMPANY_SUBCOMPANY_MQT MQT
where MQT.COMPANYID = CUSTSAT.CSATMETRIC.COMPANYID
and MQT.COMPANYRESTRICTEDCD = 'N'
and MQT.SUBCOMPANYRESTRICTEDCD = 'N')
or exists (select 1
from MSTR.COMPANY_SUBCOMPANY_MQT MQT
where MQT.COMPANYID = CUSTSAT.CSATMETRIC.COMPANYID
and MQT.COMPANYRESTRICTEDCD = 'Y'
and MQT.SUBCOMPANYRESTRICTEDCD = 'N'
and MQT.DBUSER = SESSION_USER)
or exists (select 1
from MSTR.COMPANY_SUBCOMPANY_MQT MQT
where MQT.COMPANYID = CUSTSAT.CSATMETRIC.COMPANYID
and MQT.SUBCOMPANYID = CUSTSAT.CSATMETRIC.SUBCOMPANYID
and MQT.COMPANYRESTRICTEDCD = 'Y'
and MQT.SUBCOMPANYRESTRICTEDCD = 'Y'
and MQT.DBUSER = SESSION_USER)
ENFORCED FOR ALL ACCESS
ENABLE;
This is where the DB2 Explain facility comes to the rescue. In the rewritten query, easy to see in db2exfmt output, we can see how DB2 is integrating the rewritten row permission into the SQL statement that is to be run against the table. We can see any effect that it may be having on the Access Plan.
Rewritting a performance problem permission can change an Access Plan from this:
Total Cost: 17864.4
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
22877.5
FILTER
( 2)
17864.4
2355.89
|
22879
>^HSJOIN
( 3)
17853.1
2355.89
/------------+-------------\
22879 0.002944
>^HSJOIN IXSCAN
( 4) ( 10)
17849.6 0.0228633
2355.89 0
/-----------+------------\ |
22879 0.002944 46
>^HSJOIN IXSCAN INDEX: MSTR
( 5) ( 9) IX3_COMPANYSUB_MQT
17847.2 0.0228633 Q1
2355.89 0
/----------+----------\ |
22879 0.0736 46
FETCH IXSCAN INDEX: MSTR
( 6) ( 8) IX3_COMPANYSUB_MQT
17844.7 0.0665351 Q2
2355.89 0
/---+----\ |
22879 22879 46
IXSCAN TABLE: CUSTSAT INDEX: MSTR
( 7) CSATMETRIC IX2_COMPANYSUB_MQT
493.263 Q4 Q3
63
|
22879
INDEX: CUSTSAT
IX2_CSATMETRIC
Q4
To this:
Total Cost: 3640.55
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
2904.08
TBSCAN
( 2)
3640.55
478.729
|
2904.08
SORT
( 3)
3640.24
478.729
|
2904.08
NLJOIN
( 4)
3630.85
478.729
/------------+-------------\
1.63333 1778.01
FETCH FETCH
( 5) ( 7)
7.58887 3611.47
1 476.729
/------+------\ /---+----\
1.63333 49 2859.88 22879
IXSCAN TABLE: MSTR IXSCAN TABLE: CUSTSAT
( 6) COMPANY_SUBCOMPANY_MQT ( 8) CSATMETRIC
0.0223212 Q1 82.4809 Q2
0 10.625
| |
49 22879
INDEX: MSTR INDEX: CUSTSAT
IX4_COMPANYSUB_MQT IX1_CSATMETRIC
Q1 Q2
Here is a sample SQL statement to query on permissions that are defined:
select char(C.controlschema,8) as coschema, char(C.controlname,40) as controlname, char(C.tabschema,8) as tabschema, char(C.tabname,20) as tabname, C.controltype as type, C.enforced, C.enable, C.valid, C.ownertype as owner, T.control from syscat.controls C, syscat.tables T where T.tabschema = C.tabschema and T.tabname = C.tabname
Note: Alway check the permissions defined for a table. The permission on the table may have been dropped, but the table may still be Active for Row Access Control, having a default permission. By itself, the default permission will effectively add a 1 = 0 predicate to any query that runs against the table.
CSCHEMA CONTROLNAME TABSCHEMA TABNAME TYPE ENFORCED ENABLE VALID OWNER CONTROL
-------- ---------------------------------------- --------- ----------- ---- -------- ------ ----- ----- -------
CUSTSAT SYS_DEFAULT_ROW_PERMISSION__CSATMETRIC CUSTSAT CSATMETRIC R A Y Y S R
To see what it looks like from the Explain facility, we can create a default permission by issuing a command like:
ALTER TABLE CUSTSAT.CSATOTHERTAB ACTIVATE ROW ACCESS CONTROL
Using the Explain facility, we can see the effect of that default permission:
Original Statement:
------------------
SELECT
*
FROM
CUSTSAT.CSATOTHERTAB
Statement With FGAC Applied:
---------------------------
SELECT
...
FROM
CUSTSAT.CSATOTHERTAB AS Q1
WHERE
(1 = 0)
Optimized Statement:
-------------------
SELECT
NULL AS "COL1",
...
FROM
(VALUES
) AS Q1
WHERE
(1 = 0)
Reviewing the Explain results to see how DB2 is rewriting the query, with the requirements of the default permission, we can see why no data will be returned from queries against this table when there is only the default permission. The default permission is removed with a command like:
ALTER TABLE CUSTSAT.CSATOTHERTAB DEACTIVATE ROW ACCESS CONTROL
There are visual explain tools that have options to display the orignal SQL and the optimized SQL. Using Explain is important to understand what SQL is actually being executed when using RCAC permissions. It is important to review the effect that a permission will have on the Access Plan.
In addition, other query performance tools, like DB2 Design Advisor, may also provide valuable feedback for performance alternatives. Using these tools will help in developing a permission for Row and Column Access Control that will perform the security control required with the least overhead.