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 2: Protecting column

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

The Human Resource department of Global Life Financial would like to allow employees, managers, and HR staff to access data in the EMPLOYEE table. This table contains information with different levels of sensitivity and so some restrictions should be made with regards to access:

  • Name, gender, department, and phone number are considered to be unclassified information and can be available to all employees.
  • Employee number, hire date, job, and education level are confidential and should be restricted to managers and HR staff.
  • Birth date, salary, bonus, and commission are highly confidential information HR staff.

Figure 3. Levels of access for the EMPLOYEE table
fig 3

Some of the users who access the table are summarized in the following table.


Table 7. Staff who access the EMPLOYEE table.
NamePosition
JenHR staff
NoelManager
SunnyRegular employee

The existing EMPLOYEE table will be tagged with security labels that indicate a column's level of sensitivity.


Table 8. Classifying the columns of the existing EMPLOYEE table.
Column nameType schemaType nameLengthScaleNulls
(C) EMPNOSYSIBMCHARACTER60No
(U) FIRSTNMESYSIBMVARCHAR120No
(U) MIDINITSYSIBMCHARACTER10No
(U) LASTNAMESYSIBMVARCHAR150No
(U) WORKDEPTSYSIBMCHARACTER30Yes
(U) PHONENOSYSIBMCHARACTER40Yes
(U) GENDERSYSIBMCHARACTER10Yes
(U) GENDERSYSIBMCHARACTER10Yes
(C) HIREDATESYSIBMDATE40Yes
(C) JOBSYSIBMCHARACTER80Yes
(C) EDLEVELSYSIBMSMALLINT20No
(H) BIRTHDATESYSIBMDATE40Yes
(H) SALARYSYSIBMDECIMAL90Yes
(H) BONUSSYSIBMDECIMAL90Yes
(H) COMMSYSIBMDECIMAL90Yes
Column security class: (U) Unclassified, (C) Confidential, (H) Highly Confidential

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 columns of the EMPLOYEE table. You need to enforce the following restrictions:

  1. Anyone with access to the EMPLOYEE table can read unclassified columns.
  2. Managers can also read all confidential columns.
  3. HR staff have READ/WRITE access to all columns in the table.

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


Table 9. Security requirements
PostitionREAD accessWRITE access
HR staffAllAll
ManagersConfidential and Unclassified columnsNo access
Regular employeesUnclassified columnsNo access

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 columns in the EMPLOYEE table. In designing security labels, you need to consider the following:

  1. Column security labels that protect the different levels of sensitivity.
  2. User security labels that grant users the appropriate access.
  3. Security label components that create the security labels.

Column security labels

From your analysis, you determine that each column requires a security label based on its sensitivity. And so. three security labels are needed, one for each level of sensitivity: HIGHLY CONFIDENTIAL, CONFIDENTIAL, and UNCLASSIFIED. This seems to be a simple hierarchy and you consider using an ARRAY for the security label component.

User security labels for employees

Regular employees can only access unclassified information. If unclassified columns are protected with a security label of UNCLASSIFIED, then that label should be granted to regular employees.

Since regular employees are not allowed to write to the EMPLOYEE table, some restriction should be imposed at the table level by revoking the INSERT, UPDATE and DELETE privileges from these users when you GRANT the security label.

User security labels for managers

Mangers can access unclassified and confidential information. if confidential columns are protected with a security label of CONFIDENTIAL, then that label should be granted to managers. An ARRAY still seems to be appropriate for the security label. If the order of elements in the array is (CONFIDENTIAL, UNCLASSIFIED), then managers granted a CONFIDENTIAL security label would have access to all information at the CONFIDENTIAL level and any levels below (in this case UNCLASSIFIED).

Since managers are also not allowed to write to the EMPLOYEE table, some restriction should be imposed at the table level by revoking the INSERT, UPDATE and DELETE privileges from these users.

User security labels for human resources

HR has the highest level of access to the EMPLOYEE table and can access all information. If highly confidential columns are protected with a security label of HIGHLY CONFIDENTIAL, then that label should be granted to the HR staff. An ARRAY still seems to be appropriate for the security label. If the order of elements in the array is (HIGHLY CONFIDENTIAL, CONFIDENTIAL, UNCLASSIFIED), then HR members granted a HIGHLY CONFIDENTIAL security label would have access to all information at the HIGHLY CONFIDENTIAL level and below (in this case CONFIDENTIAL and UNCLASSIFIED).

Their access to data in the EMPLOYEE table should be READ/WRITE.

Security label components

Since data access is based on a linear hierarchy, the security label component can be constructed with an ARRAY ordered as HIGHLY CONFIDENTIAL, CONFIDENTIAL, UNCLASSIFIED.

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. Altering the EMPLOYEE table by protecting all columns with security labels 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

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

Step 1a: Defining the security label component

From your analysis, you have decided that an array type security label component can be used for this ordered set of elements: HIGHLY CONFIDENTIAL, CONFIDENTIAL, and UNCLASSIFIED. The security label component can be created using the following command:

					
        CREATE SECURITY LABEL COMPONENT SLC_LEVEL
        ARRAY ['HIGHLY CONFIDENTIAL', 'CONFIDENTIAL', 'UNCLASSIFIED']
        

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 ACCESS_EMPLOYEE_ POLICY that uses the SLC_LEVEL component can be created using the following command:

        CREATE SECURITY POLICY ACCESS_EMPLOYEE_POLICY
             COMPONENTS SLC_LEVEL
             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 classification type (three in total). Each security label is based on the ACCESS_EMPLOYEE_POLICY security policy that you created in Step 1b. The required security labels are created using the following commands:

        CREATE SECURITY LABEL ACCESS_EMPLOYEE_POLICY.HIGHCONFIDENTIAL
             COMPONENT SLC_LEVEL 'HIGHLY CONFIDENTIAL'
             
        CREATE SECURITY LABEL ACCESS_EMPLOYEE_POLICY.CONFIDENTIAL
             COMPONENT SLC_LEVEL 'CONFIDENTIAL'
             
        CREATE SECURITY LABEL ACCESS_EMPLOYEE_POLICY.UNCLASSIFIED
             COMPONENT SLC_LEVEL 'UNCLASSIFIED'
        

Step 2: Altering the EMPLOYEE table

Privilege and authority requirements

ALTER table privilege on the EMPLOYEE table:

To secure the EMPLOYEE table with column level protection, you will need to alter the columns by attaching the appropriate security labels and associate the table with the ACCESS_EMPLOYEE_POLICY security policy.

        ALTER TABLE EMPLOYEE
            ALTER EMPNO SECURED WITH CONFIDENTIAL
            ALTER FIRSTNME SECURED WITH UNCLASSIFIED
            ALTER MIDINIT SECURED WITH UNCLASSIFIED
            ALTER LASTNAME SECURED WITH UNCLASSIFIED
            ALTER WORKDEPT SECURED WITH UNCLASSIFIED
            ALTER PHONENO SECURED WITH UNCLASSIFIED
            ALTER GENDER  SECURED WITH UNCLASSIFIED
            ALTER HIREDATE SECURED WITH CONFIDENTIAL
            ALTER JOB SECURED WITH CONFIDENTIAL
            ALTER EDLEVEL SECURED WITH CONFIDENTIAL
            ALTER BIRTHDATE SECURED WITH HIGHCONFIDENTIAL
            ALTER SALARY SECURED WITH HIGHCONFIDENTIAL
            ALTER BONUS SECURED WITH HIGHCONFIDENTIAL
            ALTER COMM SECURED WITH HIGHCONFIDENTIAL
            ADD SECURITY POLICY ACCESS_EMPLOYEE_POLICY
        

Upon successful execution of the commands, the EMPLOYEE table is protected.

Step 3: Granting the security labels to users

Privilege and authority requirements

This requires SECADM authority to execute commands for granting labels to users.

After the EMPLOYEE table has been protected, no users can access the table until security labels are granted to them. To allow the staff access to the protected EMPLOYEE table, the following labels should be granted:

        GRANT SECURITY LABEL ACCESS_EMPLOYEE_POLICY.HIGHCONFIDENTIAL
            TO USER Jen
            FOR ALL ACCESS
        
        GRANT SECURITY LABEL ACCESS_EMPLOYEE_POLICY.CONFIDENTIAL
            TO USER Noel
            FOR READ ACCESS
        
        GRANT SECURITY LABEL ACCESS_EMPLOYEE_POLICY.UNCLASSIFIED
            TO USER Sunny
            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

Jen, from HR, tries to read the EMPLOYEE table by issuing:

        SELECT * from EMPLOYEE;
        

The command is successful.

Example 2

Noel, a manager, tries to read the EMPLOYEE table by issuing:

        SELECT * from HR.EMPLOYEE;
        

The command fails because it violates the read access restrictions for the highly confidential columns.

Noel then tries to read only the unclassified and confidential columns:

        SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, 
             PHONENO, HIREDATE, JOB, EDLEVEL 
             from HR.EMPLOYEE;
        

The command executes successfully.

Example 3

Sunny, a regular employee, tries to read the EMPLOYEE table by issuing:

           SELECT FIRSTNME, LASTNAME, PHONENO from HR.EMPLOYEE;
        

The command succeeds because Sunny holds the security label ACCESS_EMPLOYEE_POLICY.UNCLASSIFIED and is only trying to read unclassified information.

Sunny then tries to read the confidential columns:

        SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, 
             WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL
             from HR.EMPLOYEE;
        

The command fails because it violates the read access restrictions.

3 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=