Skip to main content

By clicking Submit, you agree to the developerWorks terms of use.

The first time you sign into developerWorks, a profile is created for you. Select information in your profile (name, country/region, and company) is displayed to the public and will accompany any content you post. You may update your IBM account at any time.

All information submitted is secure.

  • Close [x]

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerworks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

By clicking Submit, you agree to the developerWorks terms of use.

All information submitted is secure.

  • Close [x]

developerWorks Community:

  • Close [x]

DB2 Label-Based Access Control, a practical guide, Part 1: Understand the basics of LBAC in DB2

A step-by-step guide to protect sensitive data

Carmen K. Wong (ckmwong@ca.ibm.com), Software Developer, EMC
author photo
Carmen Wong has worked in the DB2 Administration Tools Team as a software developer for five years. Her experiences include designing and implementing Java GUI using Swing for DB2 Administration Tools, specializing in monitoring tools (Visual Explain and Event Monitor). She was also involved in the LBAC project in the DB2 Viper release. Carmen is the co-author of a DB2 security book, which will be released in fall 2006.
Stan Musker (smusker@ca.ibm.com), DB2 Information Developer, EMC
Stan Musker has worked as an information developer for 18 years, the last 8 years in Information Management. He currently leads the team that creates the documentation for the DB2 Administration Tools. In addition, he has helped develop product videos, tutorials, and eBooks.

Summary:  Label-Based Access Control (LBAC) is a security feature introduced in the DB2® Viper release. With LBAC, administrators can control read and write access of a user at the table column and row level. This tutorial includes use case scenarios that demostrate how users can apply LBAC to protect their data from illegal access, and yet have the flexibility of allowing users to access data restrictively. The tutorial provides a step-by-step guide to creating LBAC solutions based on use-case scenarios.

View more content in this series

Date:  04 May 2006
Level:  Intermediate PDF:  A4 and Letter (526 KB | 28 pages)Get Adobe® Reader®

Activity:  37315 views
Comments:  

Lesson 1: Protecting rows

In this lesson, you will learn how to use row level protection to control access to the rows in a table.

The sales division of Global Life Financial is hosting a competition for its regional sales managers. The winner will be the regional manager whose area exceeds its sales target by the widest margin. Company executives are administering the contest and so will have access to all the sales records for all the regions, but in order to keep the competition exciting, regional managers will only be able to look at their own sales figures.


Figure 1. Senior executives are allowed to see all the records, while regional managers are restricted to the rows for their region
fig 1

There are four sales regions participating in the competition. Two of the participating regions, Central-North and Central-South, are actually sub-regions of the Central region and take sales direction from its general manager. (Note: The Central region is not part of the competition, but its general manager is an interested observer.) The two senior executives who manage the Sales division are acting as the contest administrators. Those participating in the competition and their roles are summarized in the following table.


Table 1. Contest participants and administrators
NamePositionContest
PaulSenior Vice President (senior executive)Administrator
BobVice President (senior executive)Administrator
SamSales manager for the East-CoastParticipant
NickSales manager for the West-CoastParticipant
SeanGeneral manager for the Central RegionObserver
BeckySales manager for the Central-NorthParticipant
MarcSales manager for the Central-SouthParticipant

All data for the competition will be stored in the SALES table. This table must be created and will be similar to the existing PERFORMANCE table.


Table 2. Definition of the PERFORMANCE table
Column nameType schemaType nameLengthScaleNulls
SALES_DATESYSIBMDATE40Yes
SALES_PERSONSYSIBMVARCHAR150Yes
REGIONSYSIBMVARCHAR150Yes
SALESSYSIBMINTEGER40Yes
MARGINSYSIBMINTEGER40Yes

In the next section you will analyze your security requirements.


Analyzing data restrictions

In this exercise, you need to determine how you will manage access to the SALES table. You need to enforce the following restrictions:

  1. Regional sales managers are allowed READ/WRITE access only to the records for their region.
  2. The general manager for the Central region can read records for the Central-North and Central-South regions.
  3. Executives are allowed to read all records.

Based on this scenario, you summarize your security requirements as follows:


Table 3. Security requirements
PostitionREAD accessWRITE access
Regional sales managersOnly records for their own regionOnly records for their own region
General manager for the Central regionOnly records for the Central-North and Central-South regionsNo access
ExecutivesAll recordsNo access

From your analysis, you decide to protect the sales data at the row level. For row level protection on a table, LBAC allows you to tag each row with a security label. You can then grant users a security label that allows them to access the appropriate table rows. In this case, you will create the SALES table based on the existing PERFORMANCE table (Table 2), but with an additional column for the row security label.


Table 4. A row security label column is required to control access
SALES_DATESALES_PERSONREGIONSALESMARGINSecurity Label
12/31/2004LEEEast-Coast200050
12/31/2004GOUNOTWest-Coast100040
01/29/2005LUCCHESSICentral-South300030
01/29/2005LEECentral-North200045

Next you will design an LBAC security solution based on your analysis.


Designing the security solution

In this exercise, you will design the security labels that will control access to the data rows in the SALES table. In designing security labels, you need to consider the following:

  1. Row security labels that protect the rows.
  2. User security labels that grant users the appropriate access.
  3. Security label components that create the security labels.

Row security labels

From your analysis, you determine that each region requires a security label to control access to its data. So four security labels are needed, one for each sales region. The security label components for building these security labels can be constructed using the sales regions as the elements. Since all regions appear to be of equal importance, you might consider using a SET for this security label component.

User security labels for the regional managers

The appropriate security labels used to tag the rows will be granted to the regional managers so that they can access the data for their region. Their access is to this data is READ/WRITE.

User security label for general manager

The general manager of the Central region can read data for the Central-North and Central-South regions, so this security label should rank above the security labels used to protect the rows of those sub-regions. Because now there is a hierarchy, you might consider using an ARRAY or a TREE for another security label component.

Since the general manager is not allowed to write to the SALES table, some restriction should be imposed at the table level by revoking the INSERT, UPDATE, and DELETE privileges from this user. These types of restrictions will not be part of a security label component or security label, but will be imposed when you GRANT security labels to users.

User security label for executives

The executives can read all the sales data. One way to accomplish this is by granting all security labels to the executives, but this might not be the most efficient method. An alternative is to use a hierarchical structure, where the security label granted to the executives is higher than the security labels used to protect the rows. This hierarchy should match the organizational structure of the company and so is too complex for an ARRAY and you should consider using a TREE.

Like the general manager, the executives cannot write to the SALES table and so similar WRITE restrictions should be imposed.

Security label components

Since data access is based on Global Life Financial's geographic regions, the security label component can be constructed with a TREE structure with regions as the elements.


Figure 2. Tree type security label component
fig 2

Using the nodes in the tree to make up the security label component, you can create four row security labels: one for each region in the competition. for example, the security label for tagging a sales record from 'West-Coast' can be constructed using the 'WEST_COAST' element from the security label component.

Allowing the executives to access the whole SALES table requires that their security label have a higher level of authority than that for the sales managers. A security label created using the 'SALES_ORGANIZATION' element means that a user granted that security label can access all the table records tagged with security labels that have been created with elements below it in the tree.

The next section shows you how to implement the solution that you have designed using SQL commands.


Implementing the security solution

Steps overview:

  1. Defining the security policies and labels
    • Defining the security label component
    • Defining the security policy
    • Defining the security labels
  2. Creating the protected SALES table by including a column that holds the security label and attaching the security policy to the table.
  3. Granting the appropriate security labels to users.

Step 1: Defining the security policies and labels

Privilege and authority requirements

SECADM authority is required to execute commands for creating security policies and labels.

Step 1a: Defining the security label component

From your analysis, you have decided that a tree-type security label component can be used with the sales regions as elements. A security label component with a name SLC_REGION of tree type with the elements shown in Figure 2 can be created using the following command:

					
        CREATE SECURITY LABEL COMPONENT SLC_REGION
        TREE {'SALES_ORGANIZATION' ROOT,
              'CENTRAL' UNDER 'SALES_ORGANIZATION',
              'CENTRAL_NORTH' UNDER 'CENTRAL',
              'CENTRAL_SOUTH' UNDER 'CENTRAL',
              'WEST_COAST' UNDER 'SALES_ORGANIZATION',
              'EAST_COAST' UNDER 'SALES_ORGANIZATION'
        }
        

Step 1b: Defining the security policy

After the security label component is created, you need to create the security policy. A security policy with a name SALES_REGION_POLICY that uses the SLC_REGION component can be created using the following command:

        CREATE SECURITY POLICY SALES_REGION_POLICY
             COMPONENTS SLC_REGION
             WITH DB2LBACRULES
             RESTRICT NOT AUTHORIZED WRITE SECURITY LABEL
        

Step 1c: Defining the security labels

From your analysis, you have decided that a security label is required for each sales region (four in total), a security label is required for the general manager, and a security label is required for the executives. Each security label is based on the SALES_REGION_POLICY security policy that you created in Step 1b. The required security labels are created using the following commands:

        CREATE SECURITY LABEL SALES_REGION_POLICY.CENTRAL_SOUTH
             COMPONENT SLC_REGION 'CENTRAL_SOUTH'
        
        CREATE SECURITY LABEL SALES_REGION_POLICY.CENTRAL_NORTH
             COMPONENT SLC_REGION 'CENTRAL_NORTH'
             
        CREATE SECURITY LABEL SALES_REGION_POLICY.EAST_COAST
             COMPONENT SLC_REGION 'EAST_COAST'

        CREATE SECURITY LABEL SALES_REGION_POLICY.WEST_COAST
             COMPONENT SLC_REGION 'WEST_COAST'

        CREATE SECURITY LABEL SALES_REGION_POLICY.CENTRAL
             COMPONENT SLC_REGION 'CENTRAL'

        CREATE SECURITY LABEL SALES_REGION_POLICY.SALES_ORG_READ
             COMPONENT SLC_REGION 'SALES_ORGANIZATION'
        

Step 2: Creating the protected SALES table

Privilege and authority requirements

Ability to create tables:

To secure the SALES table with row level protection, you will need a column of type DB2SECUIRTYLABEL to hold the security label and associate the table with the SALES_REGION_POLICY security policy.

        CREATE TABLE SALES (SALES_DATE DATE,
             SALES_PERSON VARCHAR (15),
             REGION VARCHAR (15),
             SALES INTEGER,
             MARGIN INTEGER,
             REGION_TAG DB2SECURITYLABEL)
             SECURITY POLICY SALES_REGION_POLICY
        

Upon successful execution of the command, the SALES table is protected.


Table 5. Definition of the protected SALES table
Column nameType schemaType nameLengthScaleNulls
SALES_DATESYSIBMDATE40Yes
SALES_PERSONSYSIBMVARCHAR150Yes
REGIONSYSIBMVARCHAR150Yes
SALESSYSIBMINTEGER40Yes
MARGINSYSIBMINTEGER40Yes
REGION_TAGSYSIBMDB2SECURITYLABEL00No

And when populated with the data from the PERFORMANCE table, the SALES table would look like this.


Table 6. Each row is tagged with a row security label that controls access based on the region
SALES_DATESALES_PERSONREGIONSALESMARGINSecurity Label
12/31/2004LEEEast-Coast200050SALES_REGION_POLICY.EAST_COAST
12/31/2004GOUNOTWest-Coast100040SALES_REGION_POLICY.WEST_COAST
01/29/2005LUCCHESSICentral-South300030SALES_REGION_POLICY.CENTRAL_SOUTH
01/29/2005LEECentral-North200045SALES_REGION_POLICY.CENTRAL_NORTH

Step 3: Granting the security labels to users

Privilege and authority requirements

SECADM authority is required to execute commands for granting labels to users.

After the SALES table has been protected, no users can access the table until security labels are granted to them. To allow the regional managers access to the regional data of the SALES table, grant each manager the security label that corresponds to their sales region with READ/WRITE authority. For example, Nick is the sales manager of West-Coast, and so SALES_REGION_POLICY.WEST_COAST will be granted to Nick. The general manager, Sean, will be granted the security label to SALES_REGION_POLICY.CENTRAL with read access. The executives, Paul and Bob, will be granted SALES_REGION_POLICY.SALES_ORG_READ, with read access to all the records in the table.

        GRANT SECURITY LABEL SALES_REGION_POLICY.EAST_COAST
            TO USER Sam FOR ALL ACCESS
            
        GRANT SECURITY LABEL SALES_REGION_POLICY.WEST_COAST
            TO USER Nick FOR ALL ACCESS
            
        GRANT SECURITY LABEL SALES_REGION_POLICY.CENTRAL_NORTH
            TO USER Becky FOR ALL ACCESS
            
        GRANT SECURITY LABEL SALES_REGION_POLICY.CENTRAL_SOUTH
            TO USER Marc FOR ALL ACCESS
            
        GRANT SECURITY LABEL SALES_REGION_POLICY.CENTRAL
            TO USER Sean FOR READ ACCESS
            
        GRANT SECURITY LABEL SALES_REGION_POLICY.SALES_ORG_READ
            TO USER Paul FOR READ ACCESS
            
        GRANT SECURITY LABEL SALES_REGION_POLICY.SALES_ORG_READ
            TO USER Bob FOR READ ACCESS
        

The next section shows how well your LBAC security solution works.


Watching the solution in action

This section explains some possible runtime scenarios after the SALES table has been protected.

Example 1

Sam, the East-Coast sales manager, tries to insert into the SALES table:

        INSERT into INSURANCE.SALES VALUES (
            '2005-02-28',
            'LUCCHESSI',
            'East-Coast',
            344,
            40,
            SECLABEL_BY_NAME('SALES_REGION_POLICY', 'SL_EAST_COAST'));
        

The command executes successfully. The resulting row contains:

('2005-02-28','LUCCHESSI','East-Coast',344,40,<SALES_REGION_POLICY.EAST_COAST>)

Example 2

Sam tries to insert sales data of another region:

        INSERT into INSURANCE.SALES VALUES (
             '2005-02-28',
             'Brian',
             'Central-South',
             344,
             28,
             SECLABEL_BY_NAME ('SALES_REGION_POLICY', 'SL_CENTRAL_SOUTH'))
        

The command fails, because the security label SALES_REGION_POLICY.CENTRAL_SOUTH is not granted to Sam.

Note: If the RESTRICT NOT AUTHORIZED WRITE SECURITY LABEL option is not specified in the CREATE SECURITY POLICY command, this command will execute successfull. However, Sam's security label for write access will be writen in the last column instead of SL_CENTRAL_SOUTH.

Example 3

Sam tries to insert sales data using the security label granted to him:

        INSERT into INSURANCE.SALES (
             SALES_DATE, SALES_PERSON, REGION, SALES, MARGIN)
             VALUES (
             '2005-02-12',
             'Peter',
             'East-Coast',
             450,
             40)
        

The command executes successfully. The two rows inserted in Example 1 and 2 are returned:

('2005-02-28','LUCCHESSI','East-Coast',344,40,<SALES_REGION_POLICY.EAST_COAST>)
('2005-02-12','Peter','East-Coast',450,40,<SALES_REGION_POLICY.SL_EAST_COAST>)

Example 4

Marc, the Central-South sales manager, tries to insert sales data into the table:

        INSERT into INSURANCE.SALES (
             SALES_DATE, SALES_PERSON, REGION, SALES, MARGIN)
             VALUES (
             '2005-02-25',
             'Brian',
             'Central-South',
             390,
             43,
             SECLABEL_BY_NAME('SALES_REGION_POLICY', 'SL_CENTRAL_SOUTH'));
        

The command executes successfully. The resulting row contains:

('2005-02-25','Brian','Central-South',390,43,<SALES_REGION_POLICY.SL_CENTRAL_SOUTH>)

Marc then tries to read the table by issuing:

        SELECT sales_date, sales_person, region, sales, margin, 
             varchar(SECLABEL_TO_CHAR("SALES_REGION_POLICY',REGION_TAG), 30) 
             from INSURANCE.SALES
        

The rows tagged with the SALES_REGION_POLICY.CENTRAL_SOUTH security label are returned:

('2005-02-25','Brian','Central-South',390,43,<SALES_REGION_POLICY.CENTRAL_SOUTH>)

Example 5

Sam tries to read sales data of other region by issuing:

        SELECT sales_date, sales_person, region, sales, margin, 
             varchar(SECLABEL_TO_CHAR('SALES_REGION_POLICY',REGION_TAG), 30)
             from INSURANCE.SALES where REGION='Central-South'
        

No row is returned. The row with Central-South in the region column is protected by secuirty label SALES_REGION_POLICY.SL_CENTRAL_SOUTH. The read access security label that Sam holds is not authoized to read that row.

Example 6

Sam tries to read sales data from the INSURANCE.SALES table by issuing:

           SELECT sales_date, sales_person, region, sales, margin, 
                varchar(SECLABEL_TO_CHAR('SALES_REGION_POLICY',REGION_TAG), 30)
                from INSURANCE.SALES;
        

The row with SALES_REGION_POLICY.SL_EAST_COAST as the value in the REGION_TAG is returned:

('2005-02-28','LUCCHESSI','East-Coast',344,40,<SALES_REGION_POLICY.SL_EAST_COAST>)
('2005-02-12','Peter','East-Coast',450,40,<SALES_REGION_POLICY.SL_EAST_COAST>)

Example 7

Becky, the Central-North sales manager, tries to insert sales data for another region with the security label granted to her:

        INSERT into INSURANCE.SALES (
             SALES_DATE, SALES_PERSON, REGION, SALES, MARGIN)
             VALUES ('2005-01-28',
             'Owen',
             'Central-North',
             300,
             36,
             SECLABEL_BY_NAME('SALES_REGION_POLICY', 'SL_CENTRAL_NORTH'));
        

The command executes successfully. The resulting row contains:

('2005-01-28','Owen','Central-North',300,36,<SALES_REGION_POLICY.SL_CENTRAL_NORTH>)

Becky, then tries to read the table by issuing:

        SELECT sales_date, sales_person, region, sales, margin, 
             varchar(SECLABEL_TO_CHAR('SALES_REGION_POLICY',REGION_TAG), 30)
             from INSURANCE.SALES
        

The rows tagged with the SALES_REGION_POLICY.SL_CENTRAL_NORTH security label are returned:

('2005-01-28','Owen','Central-North',300,36,SL_CENTRAL_NORTH)

Example 8

Sean, the Central region general manager, tries to read the sales data for the regions:

        SELECT sales_date, sales_person, region, sales, margin, 
             varchar(SECLABEL_TO_CHAR('SALES_REGION_POLICY',REGION_TAG), 30)
             from INSURANCE.SALES
        

The command returns the rows tagged with the SALES_REGION_POLICY.SL_CENTRAL_SOUTH or SALES_REGION_POLICY.SL_CENTRAL_NORTH security labels:

('2005-02-25','Brian','Central-South',390,43,<SALES_REGION_POLICY.SL_CENTRAL_SOUTH>)
('2005-01-28','Owen','Central-North',300,36,<SALES_REGION_POLICY.SL_CENTRAL_NORTH>)

Example 9

Paul, a senior executive, tries to read the sales data by issuing:

        SELECT sales_date, sales_person, region, sales, margin, 
             varchar(SECLABEL_TO_CHAR('SALES_REGION_POLICY',REGION_TAG), 30) 
             from INSURANCE.SALES
        

The command executes successfully and returns all the rows in the INSURANCE.SALES table:

('2005-02-28','LUCCHESSI','East-Coast',344,40,<SALES_REGION_POLICY.EAST_COAST>)
('2005-02-12','Peter','East-Coast',450,40,<SALES_REGION_POLICY.SL_EAST_COAST>)
('2005-02-25','Brian','Central-South',390,43,<SALES_REGION_POLICY.SL_CENTRAL_SOUTH>)
('2005-01-28','Owen','Central-North',300,36,<SALES_REGION_POLICY.SL_CENTRAL_NORTH>)

2 of 6 | Previous | Next

Comments



static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=110545
TutorialTitle=DB2 Label-Based Access Control, a practical guide, Part 1: Understand the basics of LBAC in DB2
publish-date=05042006
author1-email=ckmwong@ca.ibm.com
author1-email-cc=
author2-email=smusker@ca.ibm.com
author2-email-cc=