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.
