Understanding Label-Based Access Control, Part 1

Identity theft is the fastest-growing crime in the United States. Thieves steal personal data (Social Security, bank account, and credit card numbers, for example) and use it to commit fraud or deception for economic gain. Criminals get this data from a variety of sources — faxes, computer networks, telephone and email scams, wallets, purses, mail, and trash, just to name a few. Many identity theft cases (up to 70 percent according to some estimates) are perpetrated by an employee of a business the victim patronizes. In these cases, all that’s needed is access to data found in a company database.

As you probably know, DB2 uses a combination of external security services and internal access control mechanisms to protect data against unauthorized access and modification. Authentication is performed at the operating system level to verify that users are who they say they are; authorities and privileges control access to a database and the objects and data that reside within it. Views, which allow different users to see different presentations of the same data, can be used in conjunction with privileges to limit access to specific columns.

But what if your security requirements dictate that you create and manage several hundred views? Or, more importantly, what if you want to restrict access to individual rows in a table? If you’re using DB2 9, the solution for these situations is label-based access control (LBAC).

What is LBAC?

LBAC is a new security feature that uses one or more security labels to control who has read access and who has write access to individual rows and columns in a table. Many governments use LBAC models in which hierarchical classification labels such as CONFIDENTIAL, SECRET, and TOP SECRET are assigned to data based on its sensitivity. Access to data labeled at a certain level (for example, SECRET) is restricted to users who have been granted that level of access or higher.

With LBAC, you can construct security labels to represent any criteria your company uses to determine who can read or modify particular data values. LBAC is flexible enough to handle the simplest to the most complex criteria.

Database-Level Authority

One problem with the traditional security methods DB2 uses is that security administrators and DBAs have access to sensitive data stored in the databases they oversee. To solve this problem, LBAC-security administration tasks are isolated from all other tasks. A new Security Administrator (SECADM) authority was introduced along with LBAC in DB2 9. SECADM authority allows designated users to configure LBAC elements that control access to tables containing restricted data that they most likely do not have access to themselves. Users who are granted this authority are only allowed to perform the following tasks:

• Create and drop security policies
• Create and drop security labels
• Grant and revoke security labels
• Grant and revoke LBAC rule exemptions
• Grant and revoke SETSESSIONUSER privileges
• Transfer ownership of any object not owned by the SECADM by executing the TRANSFER OWNERSHIP SQL statement.

SECADM authority can only be assigned to individual users; it can’t be assigned to groups. And only system administrators (SYSADMs) are allowed to assign SECADM authority.

Implementing Row-Level LBAC

Before you implement a row-level LBAC solution, make sure you understand the security requirements. Suppose you have a database that contains company sales data and you want to control how senior executives, regional managers, and sales representatives access data stored in that table. Security requirements might dictate that access to this data should comply with these rules:

• Senior executives are allowed to view, but not update, all records in the table.

• Regional managers are only allowed to view and update records that were entered by sales representatives who report to them.

• Sales representatives are only allowed to view and update records of the sales they made.

Once you determine the security requirements, you can define the appropriate security policies and labels, create an LBAC-protected table (or alter an existing table to add LBAC protection), and grant the proper security labels to the appropriate users.

Define a Security Label Component

Security label components represent criteria that may be used to decide whether a user should have access to specific data. Three types of security label components can exist:

• A set, a collection of elements (character string values) for which the order of elements is not important

• An array, an ordered set that can represent a simple hierarchy; the order of elements is important (the first element ranks higher than the second, and so on)

• A tree, a complex hierarchy that can have multiple nodes and branches.

To create security label components, you execute one of the following CREATE SECURITY LABEL COMPONENT SQL statements:

CREATE SECURITY LABEL COMPONENT [ComponentName]
\SET {StringConstant,...}

or

CREATE SECURITY LABEL COMPONENT [ComponentName]
ARRAY [StringConstant,...]

or

CREATE SECURITY LABEL COMPONENT [ComponentName]
TREE (StringConstant ROOT < StringConstant UNDER StringConstant >)]

where:

• ComponentName identifies the name to be assigned to the security label component you’re creating.

• StringConstant identifies one or more string constant values that make up the valid array, set, or tree of values to be used by the security label component you’re creating.

Thus, to create a security label component named SEC_COMP that contains a set of values whose order is insignificant, you would execute a CREATE SECURITY LABEL COMPONENT statement like this:

CREATE SECURITY LABEL COMPONENT sec_comp
SET {'CONFIDENTIAL', 'SECRET', 'TOP_SECRET'}

To create a security label component that contains an array of values listed from highest to lowest order, you would execute a CREATE SECURITY LABEL COMPONENT statement like this:

CREATE SECURITY LABEL COMPONENT sec_comp
ARRAY ['MASTER_CRAFTSMAN', 'JOURNEYMAN', 'APPRENTICE']

And to create a security label component that contains a tree of values that describe a company’s organizational chart, you would execute a CREATE SECURITY LABEL COMPONENT statement similar to the one shown in Listing 1. I’ll use this security label component in the rest of the examples presented.

CREATE SECURITY LABEL COMPONENT sec_comp 
TREE ('EXEC_STAFF' ROOT,
     'N_MGR' UNDER 'EXEC_STAFF',
     'E_MGR' UNDER 'EXEC_STAFF',
     'S_MGR' UNDER 'EXEC_STAFF',
     'W_MGR' UNDER 'EXEC_STAFF',
     'C_MGR' UNDER 'EXEC_STAFF',
     'SALES_REP1' UNDER 'N_MGR',
     'SALES_REP2' UNDER 'W_MGR')

Defining a Security Policy

Security policies determine exactly how a table is to be protected by LBAC. Specifically, a security policy identifies:

• What security label components will be used in the security labels that will be part of the policy
• What rules will be used when security label components are compared (at this time, there’s only one set of rules supported: DB2LBACRULES)
• Which optional behaviors will be used when accessing data protected by the policy.

Every LBAC-protected table must have one (and only one) security policy associated with it. Rows and columns in that table can only be protected with security labels that are part of that security policy; all protected data access must adhere to the rules of that policy. You can have multiple security policies within a single database, but you can’t have more than one security policy protecting any given table.

To create a security policy, execute the CREATE SECURITY POLICY SQL statement as follows:

CREATE SECURITY POLICY [PolicyName]
COMPONENTS [ComponentName,...]
WITH DB2LBACRULES
< [OVERRIDE | RESTRICT] NOT AUTHORIZED WRITE SECURITY LABEL>

where:

• PolicyName identifies the name that is to be assigned to the security policy being created.

• ComponentName identifies one or more security label components that are to be part of security policy being created.

The [OVERRIDE | RESTRICT] NOT AUTHORIZED WRITE SECURITY LABEL option specifies the action to be taken when a user who is not authorized to write the security label explicitly specified with INSERT and UPDATE statements attempts to write data to the protected table. By default, the value of a user’s security label, rather than an specified security label, is used for write access during insert and update operations (OVERRIDE NOT AUTHORIZED WRITE SECURITY LABEL). If the RESTRICT NOT AUTHORIZED WRITE SECURITY LABEL option is used, insert and update operations will fail if the user isn’t authorized to write the explicitly specified security label to the protected table.

To create a security policy named SEC_POLICY that is based on the SEC_COMP security label component created earlier, we would execute a CREATE SECURITY POLICY statement that looks like this:

CREATE SECURITY POLICY sec_policy
COMPONENTS sec_comp
WITH DB2LBACRULES

Defining Security Labels

Security labels describe a set of security criteria and are used to protect data against unauthorized access or modification. Security labels are granted to users who are allowed to access or modify protected data; when users attempt to access or modify protected data, their security label is compared to the security label protecting the data to determine whether or not the access or modification is allowed. Every security label is part of exactly one security policy, and a security label must exist for each security label component found in the security policy.

Security labels are created by executing the CREATE SECURITY LABEL SQL statement. The syntax for this statement is:

CREATE SECURITY LABEL [LabelName]
[COMPONENT [ComponentName] [StringConstant] ,...]

where:

• LabelName identifies the name to be assigned to the security label being created. The name specified must be qualified with a security policy name, and must not match an existing security label for the security policy specified.
• ComponentName identifies a security label component that is part of the security policy specified (as the qualifier for the LabelName parameter).

CREATE SECURITY LABEL sec_policy.exec_staff 
COMPONENT sec_comp 'EXEC_STAFF'
CREATE SECURITY LABEL sec_policy.n_mgr 
COMPONENT sec_comp 'N_MGR'
CREATE SECURITY LABEL sec_policy.e_mgr 
COMPONENT sec_comp 'E_MGR'
CREATE SECURITY LABEL sec_policy.s_mgr 
COMPONENT sec_comp 'S_MGR'
CREATE SECURITY LABEL sec_policy.w_mgr 
COMPONENT sec_comp 'W_MGR'
CREATE SECURITY LABEL sec_policy.c_mgr 
COMPONENT sec_comp 'C_MGR'
CREATE SECURITY LABEL sec_policy.sales_rep1 
COMPONENT sec_comp 'SALES_REP1'
CREATE SECURITY LABEL sec_policy.sales_rep2 
COMPONENT sec_comp 'SALES_REP2'

• StringConstant identifies one or more valid string constant values that are valid elements of the security label component specified (in the ComponentName parameter).

To create a set of security labels for the security policy named SEC_POLICY that we created earlier, you would execute the set of CREATE SECURITY LABEL statements shown in Listing 2 (at right).

Creating an LBAC-Protected Table

Once you’ve defined the security policy and labels needed to enforce your security requirements, you’re ready to create a table and configure it for LBAC protection. To configure a new table for row-level LBAC protection, you include a column with the data type DB2SECURITYLABEL in the table’s definition and associate a security policy with the table using the SECURITY POLICY clause of the CREATE TABLE SQL statement.

CREATE TABLE corp.sales (
     sales_rec_id     INTEGER NOT NULL,
     sales_date     DATE WITH DEFAULT,
     sales_rep     INTEGER,
     region     VARCHAR(15),
     manager     INTEGER,
     sales_amt     DECIMAL(12,2),
     margin     DECIMAL(12,2),
     sec_label     DB2SECURITYLABEL)
     SECURITY POLICY sec_policy

To create a table named corp.sales and configure it for row-level LBAC protection using a security policy named SEC_POLICY, you would execute a CREATE TABLE statement similar to the one shown in Listing 3 (at right).

To configure an existing table named corp.sales for row-level LBAC protection using a security policy named SEC_POLICY, you would execute an ALTER TABLE statement that looks like this:

ALTER TABLE corp.sales
ADD COLUMN sec_label DB2SECURITYLABEL
ADD SECURITY POLICY sec_policy

However, before you can execute such an ALTER TABLE statement, you must be granted a security label for write access that is part of the security policy that will be used to protect the table (which, in this case is SEC_POLICY). Otherwise, you won’t be able to create the DB2SECURITYLABEL column.

Granting Security Labels To Users

Once the security policy and labels needed to enforce your security requirements have been defined and a table has been enabled for LBAC-protection, you must grant the proper security labels to the appropriate users and indicate whether they are to have read access, write access, or full access to data that is protected by that label. Security labels are granted to users by executing a special form of the GRANT SQL statement. The syntax for this statement is:

GRANT ALL PRIVILEGES ON TABLE corp.sales TO exec_staff;
GRANT ALL PRIVILEGES ON TABLE corp.sales TO n_manager;
GRANT ALL PRIVILEGES ON TABLE corp.sales TO e_manager;
GRANT ALL PRIVILEGES ON TABLE corp.sales TO s_manager;
GRANT ALL PRIVILEGES ON TABLE corp.sales TO w_manager;
GRANT ALL PRIVILEGES ON TABLE corp.sales TO c_manager;
GRANT ALL PRIVILEGES ON TABLE corp.sales TO sales_rep1;
GRANT ALL PRIVILEGES ON TABLE corp.sales TO sales_rep2;

GRANT SECURITY LABEL [LabelName]
TO USER [UserName]
[ FOR ALL ACCESS | FOR READ ACCESS | FOR WRITE ACCESS ]

where:

• LabelName identifies the name of an existing security label. The name specified must be qualified with the security policy name that was used when the security label was created.
• UserName identifies the name of the user to which the security label is to be granted.

To give USER1 the ability to read data protected by the security label SEC_POLICY.EXEC_STAFF, you would execute the following GRANT statement:

GRANT SECURITY LABEL sec_policy.exec_staff
TO USER user1 FOR READ ACCESS

GRANT SECURITY LABEL sec_policy.exec_staff TO USER exec_staff 
FOR READ ACCESS;
GRANT SECURITY LABEL sec_policy.n_mgr TO USER n_manager 
FOR ALL ACCESS;
GRANT SECURITY LABEL sec_policy.e_mgr TO USER e_manager 
FOR ALL ACCESS;
GRANT SECURITY LABEL sec_policy.s_mgr TO USER s_manager 
FOR ALL ACCESS;
GRANT SECURITY LABEL sec_policy.w_mgr TO USER w_manager 
FOR ALL ACCESS;
GRANT SECURITY LABEL sec_policy.c_mgr TO USER c_manager 
FOR ALL ACCESS;
GRANT SECURITY LABEL sec_policy.sales_rep1 TO USER sales_rep1 
FOR ALL ACCESS;
GRANT SECURITY LABEL sec_policy.sales_rep2 TO USER sales_rep2 
FOR ALL ACCESS;

Putting LBAC into Action

To enforce the security requirements listed at the beginning of this column, we must first give users the ability to perform DML operations against the corp.sales table by executing the SQL statements shown in Listing 4 as a user with SYSADM or DBADM authority.

Next, we must grant the proper security labels to the appropriate users and indicate whether they’re to have read access, write access, or full access to data that is protected by that label. This is done by executing the SQL statements in Listing 5 (as a user with SECADM authority).

Now, suppose the user SALES_REP1 adds three rows to the corp.sales table by executing the following SQL statements.

INSERT INTO corp.sales VALUES (1, DEFAULT, 1, 'NORTH', 5,
1000.50, 500.00, SECLABEL_BY_NAME('SEC_POLICY', 'SALES_REP1'));
INSERT INTO corp.sales VALUES (2, DEFAULT, 1, 'NORTH', 5,
2000.00, 400.00, SECLABEL_BY_NAME('SEC_POLICY', 'SALES_REP1'));
INSERT INTO corp.sales VALUES (3, DEFAULT, 1, 'NORTH', 5,
4500.90, 850.00, SECLABEL_BY_NAME('SEC_POLICY', 'SALES_REP1'));

SALES_REP1 has been given read/write access to the table using the SEC_POLICY.SALES_REP1 security label, so the statements execute successfully. Next, the user SALES_REP2 adds two additional rows to the corp.sales table by executing the following SQL statements:

INSERT INTO corp.sales VALUES (4, DEFAULT, 1, 'WEST', 20,
1000.50, 500.00, SECLABEL_BY_NAME('SEC_POLICY', 'SALES_REP2'));
INSERT INTO corp.sales VALUES (5, DEFAULT, 1, 'WEST', 20,
3200.00, 600.00, SECLABEL_BY_NAME('SEC_POLICY', 'SALES_REP2'));

SALES_REP2 also has been given read/write access to the table using the SEC_POLICY.SALES_REP2 security label.

When user EXEC_STAFF queries the corp.sales table, all five records entered will appear (because the security label SEC_POLICY.EXEC_STAFF is the highest level in the security policy’s security label component tree). However, if an attempt to insert additional records or update these records is made, an error will be generated because user EXEC_STAFF is only allowed to read the data (only read access was granted).

When user N_MANAGER queries the table, only records entered by the user SALES_REP1 will be displayed; the user W_MANAGER will only see records entered by the user SALES_REP2; and the users E_MANAGER, S_MANAGER, and C_MANAGER won’t see any records at all. (SALES_REP1 reports to N_MANAGER, SALES_REP2 reports to W_MANAGER, and no other managers have sales representatives reporting to them).

When SALES_REP1 or SALES_REP2 queries the corp.sales table, they will see only the records they personally entered. Likewise, they can only update the records they entered.

Stay Tuned

As you can see, label-based access control (LBAC) provides a very powerful way to protect data from improper access or modification. In this column, I described a simple way to limit access to rows. In my next column, I’ll show you how to use LBAC to protect data stored in individual columns as well as a combination of rows and columns.

Roger E. Sanders president of Roger Sanders Enterprises Inc., is the author of 12 books on DB2 for Linux, Unix, and Windows and teaches classes at many DB2 conferences. His most recent book, DB2 9 Fundamentals Certification Study Guide (MC Press, 2007), will be available in May.