Newsletters




DB2's New Row and Column Access Control in Practice


Bookmark and Share

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.


Sponsors