LBAC – Label Based Access Control

To go with my recent article on RCAC/FGAC, I thought I would do some similar work using LBAC and see what I could learn about it and the differences between the two.

What is LBAC

Label Based Access Control essentially adds a column to a table that labels each row (think confidential, secret, top secret), and then grants uses of those labels to users to allow them to access the data. Complex hierarchical structures are possible, and some planning is needed to work things out. I’m focusing on LBAC at the row level in this post.

Environment

The environment I’m using for this article is again the SALES table in the sample database. I have two users with SECADM authority – db2inst1 and db2sec. Unless otherwise stated, all commands are done as db2inst1.

Creating Security Objects

There are a number of security objects that must be created in the right order for things to work. I’ll go through them in the order they should be created.

Security Label Components

This is the first object you must create. Basically, this defines the labels you will use and the organization of them. There are three types of security label components:

  • Set – a set is simply a list of labels, none of which encompass others. When checked, the label either matches exactly or it doesn’t.
  • Array – an array is a list of labels, each of which encompasses all the following ones. So a user with the first label in the array would have access to all the items the others have access to. A user with the second label in the array would have access to all the items except those labeled with the first label in the array.
  • Tree – a tree is something more like an organizational chart, where there can be any number of elements at each level, and you can define branches. The root label would have access to all the other labels.

For the purposes of this article, I’m creating a simple tree, with the “UNRESTRICTED” label as the root with three labels under it, with different permissions. The syntax I use to do this is:

$ db2 "create security label component reg_sec_comp tree ('UNRESTRICTED' ROOT, 
'MANITOBA' under 'UNRESTRICTED', 
'ONTARIO' under 'UNRESTRICTED', 
'Quebec' under 'UNRESTRICTED')"
DB20000I  The SQL command completed successfully.

I have intentionally used a different case for ‘Quebec’ to show where this shows up. I would recommend specifying EVERYTHING in upper case to avoid issues.

Security Policy

A security policy is a group of one or more security label components. Each table can have one and only one security policy. For this example, I created the security policy like this:

$ db2 "create security policy sec_policy components reg_sec_comp with db2lbacrules"
DB20000I  The SQL command completed successfully.

Security Labels

Finally, you have to create the actual security labels, mapping them to the items in the security label component. Here is the syntax I used to do that – note where the different capitalization of Quebec shows up here.

$ db2 "create security label sec_policy.manitoba component reg_sec_comp 'MANITOBA'"
DB20000I  The SQL command completed successfully.
$ db2 "create security label sec_policy.ontario component reg_sec_comp 'ONTARIO'"
DB20000I  The SQL command completed successfully.
$ db2 "create security label sec_policy.quebec component reg_sec_comp 'QUEBEC'"
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL20389N  The component element "QUEBEC" is not defined in the security label 
component "REG_SEC_COMP".  SQLSTATE=4274F
This is the first time the odd capitalization of quebec shows up.
$ db2 "create security label sec_policy.quebec component reg_sec_comp 'Quebec'"
DB20000I  The SQL command completed successfully.
$ db2 "create security label sec_policy.unrestricted component reg_sec_comp 'UNRESTRICTED'"
DB20000I  The SQL command completed successfully.

Additional Actions

Now before I can alter the table to enable the security policy and add the column for the security label, I have to use a different SECADM id to grant my id a label in the security policy – if I don’t, I will get SQL20402N.

This is done as db2sec, since no id can grant privileges to itself.

$ db2 "grant security label sec_policy.unrestricted to user db2inst1"
DB20000I  The SQL command completed successfully.

Now with that permission, I can switch back to db2inst1 and alter the table:

db2 "alter table db2inst1.sales add column sec_label DB2SECURITYLABEL add security policy sec_policy"
DB20000I  The SQL command completed successfully.

Now, let’s see what the table looks like with all this in place, as a user with that UNRESTRICTED label:

$ db2 "select SALES_DATE, SALES_PERSON, REGION, SALES, substr(SECLABEL_TO_CHAR('SEC_POLICY', SEC_LABLE),1,30) as sec_label from db2inst1.sales"

SALES_DATE SALES_PERSON    REGION          SALES       SEC_LABEL                     
---------- --------------- --------------- ----------- ------------------------------
12/31/2005 LUCCHESSI       Ontario-South             1 UNRESTRICTED                  
12/31/2005 LEE             Ontario-South             3 UNRESTRICTED                  
12/31/2005 LEE             Quebec                    1 UNRESTRICTED                  
12/31/2005 LEE             Manitoba                  2 UNRESTRICTED                  
12/31/2005 GOUNOT          Quebec                    1 UNRESTRICTED                  
03/29/2006 LUCCHESSI       Ontario-South             3 UNRESTRICTED                  
03/29/2006 LUCCHESSI       Quebec                    1 UNRESTRICTED                  
03/29/2006 LEE             Ontario-South             2 UNRESTRICTED                  
03/29/1996 LEE             Ontario-North             2 UNRESTRICTED                  
03/29/2006 LEE             Quebec                    3 UNRESTRICTED                  
03/29/2006 LEE             Manitoba                  5 UNRESTRICTED                  
03/29/2006 GOUNOT          Ontario-South             3 UNRESTRICTED                  
03/29/2006 GOUNOT          Quebec                    1 UNRESTRICTED                  
03/29/2006 GOUNOT          Manitoba                  7 UNRESTRICTED                  
03/30/2006 LUCCHESSI       Ontario-South             1 UNRESTRICTED                  
03/30/2006 LUCCHESSI       Quebec                    2 UNRESTRICTED                  
03/30/2006 LEE             Ontario-South             7 UNRESTRICTED                  
03/30/2006 LEE             Ontario-North             3 UNRESTRICTED                  
03/30/2006 LEE             Quebec                    7 UNRESTRICTED                  
03/30/2006 LEE             Manitoba                  4 UNRESTRICTED                  
03/30/2006 GOUNOT          Ontario-South             2 UNRESTRICTED                  
03/30/2006 GOUNOT          Quebec                   18 UNRESTRICTED                  
03/31/2006 LEE             Ontario-South            14 UNRESTRICTED                  
03/31/2006 LEE             Ontario-North             3 UNRESTRICTED                  
03/31/2006 LEE             Quebec                    7 UNRESTRICTED                  
03/31/2006 LEE             Manitoba                  3 UNRESTRICTED                  
03/31/2006 GOUNOT          Ontario-South             2 UNRESTRICTED                  
03/31/2006 GOUNOT          Quebec                    1 UNRESTRICTED                  
04/01/2006 LUCCHESSI       Ontario-South             3 UNRESTRICTED                  
04/01/2006 LEE             Ontario-South             8 UNRESTRICTED                  
04/01/2006 LEE             Ontario-North             - UNRESTRICTED                  
04/01/2006 LEE             Quebec                    8 UNRESTRICTED                  
04/01/2006 LEE             Manitoba                  9 UNRESTRICTED                  
04/01/2006 GOUNOT          Ontario-South             3 UNRESTRICTED                  
04/01/2006 GOUNOT          Ontario-North             1 UNRESTRICTED                  
04/01/2006 GOUNOT          Quebec                    3 UNRESTRICTED                  
04/01/2006 GOUNOT          Manitoba                  7 UNRESTRICTED                  
11/13/2014 EMBER           Manitoba                  5 UNRESTRICTED                  

  38 record(s) selected.

Note that the root level label is associated with each row by default – meaning that only those users with that root label can see the data. The next thing I need to do is to associate the right labels with the right rows:

$ db2 "update db2inst1.sales set sec_lable = SECLABEL_BY_NAME('SEC_POLICY', 'MANITOBA') where region = 'Manitoba'"
DB20000I  The SQL command completed successfully.
$ db2 "update db2inst1.sales set sec_lable = SECLABEL_BY_NAME('SEC_POLICY', 'ONTARIO') where region like 'Ontario%'"
DB20000I  The SQL command completed successfully.
$ db2 "update db2inst1.sales set sec_lable = SECLABEL_BY_NAME('SEC_POLICY', 'Quebec') where region = 'Quebec'"
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL20396N  The security label named "Quebec" cannot be found for the security 
policy "SEC_POLICY".  SQLSTATE=4274I
Note here, that I need to use the upper case QUEBEC, because
 this is the security label, and not the element of the security policy component
$ db2 "update db2inst1.sales set sec_lable = SECLABEL_BY_NAME('SEC_POLICY', 'QUEBEC') where region = 'Quebec'"
DB20000I  The SQL command completed successfully.

Now, as a user with the UNRESTRICTED label, we see:

$ db2 "select SALES_DATE, SALES_PERSON, REGION, SALES, substr(SECLABEL_TO_CHAR('SEC_POLICY', SEC_LABLE),1,30) as sec_label from db2inst1.sales"

SALES_DATE SALES_PERSON    REGION          SALES       SEC_LABEL                     
---------- --------------- --------------- ----------- ------------------------------
12/31/2005 LUCCHESSI       Ontario-South             1 ONTARIO                       
12/31/2005 LEE             Ontario-South             3 ONTARIO                       
12/31/2005 LEE             Quebec                    1 Quebec                        
12/31/2005 LEE             Manitoba                  2 MANITOBA                      
12/31/2005 GOUNOT          Quebec                    1 Quebec                        
03/29/2006 LUCCHESSI       Ontario-South             3 ONTARIO                       
03/29/2006 LUCCHESSI       Quebec                    1 Quebec                        
03/29/2006 LEE             Ontario-South             2 ONTARIO                       
03/29/1996 LEE             Ontario-North             2 ONTARIO                       
03/29/2006 LEE             Quebec                    3 Quebec                        
03/29/2006 LEE             Manitoba                  5 MANITOBA                      
03/29/2006 GOUNOT          Ontario-South             3 ONTARIO                       
03/29/2006 GOUNOT          Quebec                    1 Quebec                        
03/29/2006 GOUNOT          Manitoba                  7 MANITOBA                      
03/30/2006 LUCCHESSI       Ontario-South             1 ONTARIO                       
03/30/2006 LUCCHESSI       Quebec                    2 Quebec                        
03/30/2006 LEE             Ontario-South             7 ONTARIO                       
03/30/2006 LEE             Ontario-North             3 ONTARIO                       
03/30/2006 LEE             Quebec                    7 Quebec                        
03/30/2006 LEE             Manitoba                  4 MANITOBA                      
03/30/2006 GOUNOT          Ontario-South             2 ONTARIO                       
03/30/2006 GOUNOT          Quebec                   18 Quebec                        
03/31/2006 LEE             Ontario-South            14 ONTARIO                       
03/31/2006 LEE             Ontario-North             3 ONTARIO                       
03/31/2006 LEE             Quebec                    7 Quebec                        
03/31/2006 LEE             Manitoba                  3 MANITOBA                      
03/31/2006 GOUNOT          Ontario-South             2 ONTARIO                       
03/31/2006 GOUNOT          Quebec                    1 Quebec                        
04/01/2006 LUCCHESSI       Ontario-South             3 ONTARIO                       
04/01/2006 LEE             Ontario-South             8 ONTARIO                       
04/01/2006 LEE             Ontario-North             - ONTARIO                       
04/01/2006 LEE             Quebec                    8 Quebec                        
04/01/2006 LEE             Manitoba                  9 MANITOBA                      
04/01/2006 GOUNOT          Ontario-South             3 ONTARIO                       
04/01/2006 GOUNOT          Ontario-North             1 ONTARIO                       
04/01/2006 GOUNOT          Quebec                    3 Quebec                        
04/01/2006 GOUNOT          Manitoba                  7 MANITOBA                      
11/13/2014 EMBER           Manitoba                  5 MANITOBA                      

  38 record(s) selected.

Finally, I also need to grant the security labels to the right users. Note I’m using the same users with access to the same data as I did in the RCAC article.

$ db2 "grant security label sec_policy.manitoba to user manitoba"
DB20000I  The SQL command completed successfully.
$ db2 "grant security label sec_policy.ontario to user client1"
DB20000I  The SQL command completed successfully.
$ db2 "grant security label sec_policy.quebec to user client2"
DB20000I  The SQL command completed successfully.

Selecting Data

Here is the data I can see as client1:

$ db2 "select * from db2inst1.sales"

SALES_DATE SALES_PERSON    REGION          SALES       SEC_LABLE                                                                                                                                                                                                                                                          
---------- --------------- --------------- ----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
12/31/2005 LUCCHESSI       Ontario-South             1 x'0000000000000002'                                                                                                                                                                                                                                                
12/31/2005 LEE             Ontario-South             3 x'0000000000000002'                                                                                                                                                                                                                                                
03/29/2006 LUCCHESSI       Ontario-South             3 x'0000000000000002'                                                                                                                                                                                                                                                
03/29/2006 LEE             Ontario-South             2 x'0000000000000002'                                                                                                                                                                                                                                                
03/29/1996 LEE             Ontario-North             2 x'0000000000000002'                                                                                                                                                                                                                                                
03/29/2006 GOUNOT          Ontario-South             3 x'0000000000000002'                                                                                                                                                                                                                                                
03/30/2006 LUCCHESSI       Ontario-South             1 x'0000000000000002'                                                                                                                                                                                                                                                
03/30/2006 LEE             Ontario-South             7 x'0000000000000002'                                                                                                                                                                                                                                                
03/30/2006 LEE             Ontario-North             3 x'0000000000000002'                                                                                                                                                                                                                                                
03/30/2006 GOUNOT          Ontario-South             2 x'0000000000000002'                                                                                                                                                                                                                                                
03/31/2006 LEE             Ontario-South            14 x'0000000000000002'                                                                                                                                                                                                                                                
03/31/2006 LEE             Ontario-North             3 x'0000000000000002'                                                                                                                                                                                                                                                
03/31/2006 GOUNOT          Ontario-South             2 x'0000000000000002'                                                                                                                                                                                                                                                
04/01/2006 LUCCHESSI       Ontario-South             3 x'0000000000000002'                                                                                                                                                                                                                                                
04/01/2006 LEE             Ontario-South             8 x'0000000000000002'                                                                                                                                                                                                                                                
04/01/2006 LEE             Ontario-North             - x'0000000000000002'                                                                                                                                                                                                                                                
04/01/2006 GOUNOT          Ontario-South             3 x'0000000000000002'                                                                                                                                                                                                                                                
04/01/2006 GOUNOT          Ontario-North             1 x'0000000000000002'                                                                                                                                                                                                                                                

  18 record(s) selected.

Note that the security label column now comes out in the output from a simple select *. That could trip up a poorly written applicaiton.

Here’s what I see as client2:

$ db2 "select * from db2inst1.sales"

SALES_DATE SALES_PERSON    REGION          SALES       SEC_LABLE                                                                                                                                                                                                                                                          
---------- --------------- --------------- ----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
12/31/2005 LEE             Quebec                    1 x'0000000000000004'                                                                                                                                                                                                                                                
12/31/2005 GOUNOT          Quebec                    1 x'0000000000000004'                                                                                                                                                                                                                                                
03/29/2006 LUCCHESSI       Quebec                    1 x'0000000000000004'                                                                                                                                                                                                                                                
03/29/2006 LEE             Quebec                    3 x'0000000000000004'                                                                                                                                                                                                                                                
03/29/2006 GOUNOT          Quebec                    1 x'0000000000000004'                                                                                                                                                                                                                                                
03/30/2006 LUCCHESSI       Quebec                    2 x'0000000000000004'                                                                                                                                                                                                                                                
03/30/2006 LEE             Quebec                    7 x'0000000000000004'                                                                                                                                                                                                                                                
03/30/2006 GOUNOT          Quebec                   18 x'0000000000000004'                                                                                                                                                                                                                                                
03/31/2006 LEE             Quebec                    7 x'0000000000000004'                                                                                                                                                                                                                                                
03/31/2006 GOUNOT          Quebec                    1 x'0000000000000004'                                                                                                                                                                                                                                                
04/01/2006 LEE             Quebec                    8 x'0000000000000004'                                                                                                                                                                                                                                                
04/01/2006 GOUNOT          Quebec                    3 x'0000000000000004'                                                                                                                                                                                                                                                

  12 record(s) selected.

And also the same aggregate tests I did in the article on RCAC/FGAC, to show that we cannot infer anything about the data we are not seeing. Executed as client2:

$ db2 "select sum(sales) from db2inst1.sales"

1          
-----------
         53

  1 record(s) selected.

$ db2 "select avg(sales) from db2inst1.sales"

1          
-----------
          4

  1 record(s) selected.

$ db2 "select min(sales) from db2inst1.sales"

1          
-----------
          1

  1 record(s) selected.

$ db2 "select max(sales) from db2inst1.sales"

1          
-----------
         18

  1 record(s) selected.

$ db2 "select count(sales) from db2inst1.sales"

1          
-----------
         12

  1 record(s) selected.

One of the places it gets very interesting is when doing inserts:

$ db2 "insert into db2inst1.sales VALUES (date('11/15/2014'), 'EMBER', 'Manitoba', 1)"
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0117N  The number of values assigned is not the same as the number of 
specified or implied columns or variables.  SQLSTATE=42802

First, we now have to specify something for the security label column. A Null would work if you just want the label of the current user:

$ db2 "insert into db2inst1.sales VALUES (date('11/15/2014'), 'EMBER', 'Manitoba', 1, SECLABEL_BY_NAME('SEC_POLICY', 'MANITOBA'))"
DB20000I  The SQL command completed successfully.
$ db2 "insert into db2inst1.sales VALUES (date('11/16/2014'), 'EMBER', 'Quebec', 3, SECLABEL_BY_NAME('SEC_POLICY', 'MANITOBA'))"
DB20000I  The SQL command completed successfully.
$ db2 "insert into db2inst1.sales VALUES (date('11/17/2014'), 'EMBER', 'Quebec', 2, SECLABEL_BY_NAME('SEC_POLICY', 'QUEBEC'))"
DB20000I  The SQL command completed successfully.
$ whoami
manitoba
$ db2 "insert into db2inst1.sales VALUES (date('11/17/2014'), 'EMBER', 'Quebec', 2, SECLABEL_BY_NAME('SEC_POLICY', 'UNRESTRICTED'))"
DB20000I  The SQL command completed successfully.
$ db2 -vm "update db2inst1.sales set sales = 5 where sales_person='EMBER'"
update db2inst1.sales set sales = 5 where sales_person='EMBER'
  Number of rows affected : 5
DB20000I  The SQL command completed successfully.

Note that I even checked who I was at one point, because I expected it to give me an error when I specify a security label that the user I’m logged in as does not have access to. But look at the results:

$ db2 "select SALES_DATE, SALES_PERSON, REGION, SALES, substr(SECLABEL_TO_CHAR('SEC_POLICY', SEC_LABLE),1,30) as sec_label from db2inst1.sales where sales_person='EMBER'"

SALES_DATE SALES_PERSON    REGION          SALES       SEC_LABEL                     
---------- --------------- --------------- ----------- ------------------------------
11/15/2014 EMBER           Manitoba                  5 MANITOBA                      
11/16/2014 EMBER           Quebec                    5 MANITOBA                      
11/17/2014 EMBER           Quebec                    5 MANITOBA                      
11/17/2014 EMBER           Quebec                    5 MANITOBA                      
11/13/2014 EMBER           Manitoba                  5 MANITOBA                      

No matter what I did as manitoba, it inisisted on using my security label and didn’t even tell me. My security labels are now out of sync with my regions, and worse, the people with access to Quebec couldn’t even see these rows because they have the Manioba security label. I liked how RCAC/FGAC handled this scenario much better – there was no way I could circumvent the region matching up with the security label.

Performance

I’ve heard that LBAC in large settings may have some locking issues. The crazy thing is that when I explain it, both explain plans are EXACTLY the same – both expect the same number of rows returned, even though the first of these only returned 12 rows because it was run as the manitoba user. That mis-estimation could cause some issues in some scenarios.
Here is the explain plan run as the manitoba user with LBAC in force:

Original Statement:
------------------
select
  *
from
  db2inst1.sales


Optimized Statement:
-------------------
SELECT
  Q1.SALES_DATE AS "SALES_DATE",
  Q1.SALES_PERSON AS "SALES_PERSON",
  Q1.REGION AS "REGION",
  Q1.SALES AS "SALES"
FROM
  DB2INST1.SALES AS Q1

Access Plan:
-----------
        Total Cost:             6.82242
        Query Degree:           1

      Rows
     RETURN
     (   1)
      Cost
       I/O
       |
       41
     TBSCAN
     (   2)
     6.82242
        1
       |
       41
 TABLE: DB2INST1
      SALES
       Q1

And here is the explain plan for this query with LBAC disabled for the table:

Original Statement:
------------------
select
  *
from
  db2inst1.sales


Optimized Statement:
-------------------
SELECT
  Q1.SALES_DATE AS "SALES_DATE",
  Q1.SALES_PERSON AS "SALES_PERSON",
  Q1.REGION AS "REGION",
  Q1.SALES AS "SALES",
  Q1.SEC_LABLE AS "SEC_LABLE"
FROM
  DB2INST1.SALES AS Q1

Access Plan:
-----------
        Total Cost:             6.82242
        Query Degree:           1

      Rows
     RETURN
     (   1)
      Cost
       I/O
       |
       41
     TBSCAN
     (   2)
     6.82242
        1
       |
       41
 TABLE: DB2INST1

Summary

There may be some efficiencies of administration when you can base your rules off of an existing column in a table with RCAC. Significant planning for your security component structure may be required with LBAC, and modifying applications and assigning appropriate labels for ongoing inserts could be a battle.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *