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 3: Protecting rows and columns

In this lesson, you will learn how to use a combination of row level and column level protection to protect sensitive data in a table.

Global Life Financial requires all life insurance applicants to submit a Medical History report for analyzing the eligibility of their application. An applicant's medical history is highly confidential and should only be handled by the Medical Record Analysis department. Upon analyzing the report, that department comes up with a rating in form of a risk index. Department managers can then refer to the insurance risk index when they consider approving an insurance application. To maintain confidentiality, department managers cannot access the information related to the medical history of a client's record.

Medical histories and risk index are stored in the existing MEDICAL_RECORD table.


Figure 4. Levels of access for the MEDICAL_RERORT table.
fig 4

Those who access the table are summarized in the following table.


Table 10. Some of the staff who could access the MEDICAL_RECORD table.
NamePosition
PeterMedical record analyst
AndreaManager of department K01
SaraManager of department K02
KevinManager of department S01
JosephManager of department S02

In the MEDICAL_RERORT table, the confidential column can be accessed by medical record analysts only. Manager access is restricted to the client records for their department.


Table 11. Definition for the existing MEDICAL_RECORD table.
Column nameType schemaType nameLengthScaleNulls
RECORDIDSYSIBMCHARACTER60No
CLIENTNOSYSIBMCHARACTER60No
DEPTNOSYSIBMCHARACTER60No
APPLICATION_
DATE
SYSIBMDATE40Yes
LAST_
UPDATE
SYSIBMDATE40No
MEDICAL_
HISTORY
SYSIBMCLOB10485760No
RISK_
INDEX
SYSIBMSMALLINT10No
Note: The shaded column contains confidential information.

In the next section you will analyze your security requirements.


Analyzing data restrictions

In this exercise, you need to determine how to manage access to the confidential column and restrict record access by group. You need to enforce the following restrictions:

  • Columns
    • Medical record analysts have READ/WRITE access to confidential columns.
    • Managers have READ access to non-confidential columns.
  • Rows:
    • Medical record analysts can read and update all the records.
    • Managers can read but not update client records for their department.

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


Table 12. Security requirements
PostitionREAD accessWRITE access
Medical record analystAll recordsAll records
ManagersClient records for their department and only non-confidential columnsNo access

To restrict access to the column that is confidential, the column can be protected with a security label. To restrict managers' access to only the records for their department, each row can be tagged with a security label that indicates the department. The write restriction for managers can be implemented by revoking their write privileges to the table. In Table 13, below, see how a column security label is added to the MEDICAL_HISTORY column to control access based on job category.


Table 13. A row security label is added to control access by department.
RECORDIDCLIENTNODEPTNOALLOCATION_
DATE
LAST_
UPDATE
MEDICAL_
HISTORY
RISK_
FACTOR
Row Security Label
000010K108341K012005/01/052005/01/15...0
000020K181245K012005/02/092005/02/19...2
000030S245987S022005/02/112005/02/21...1
000050S231674S022005/03/232005/04/04...1

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 in the MEDICAL_RECORD table. In designing a security solution, you need to consider:

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

Column security labels

For column protection, a column security label is required for the confidential column. The security label component for building this security label could simply be an element called CONFIDENTIAL. Using a SET seems appropriate because there is only one element.

Row security labels

From your analysis, you determine that each department requires a security label to control access to its data. And so. four security labels are needed, one for each department. Since all departments appear to be of equal importance you might consider using a SET for this security label component.

User security label for the medical record analyst

The medical record analyst is allowed READ/WRITE access to the records for all departments, including the confidential column. So this security label should include the elements from both the column security label and the row security label. Because the medical record analyst can access all records for all departments, this indicates that there is a hierarchy and that a TREE might be a better alternative for the row security label.

User security label for the managers

The appropriate security labels used to tag the rows will be granted to the department managers so that they can access the records for their department. Their access is to this data is READ except for the confidential column.

Security label components

Two security label components are required.

A SET type security label component is required for the column security label, with one element 'CONFIDENTIAL'.

A TREE type security label component is required for the row security label, with 'LIFE_INS_DEPT' as the root, and the department names 'K01', 'K02', 'S01', 'S02' as the child elements.


Figure 5. Tree type security label component.
figure 5

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
  2. a. Defining the security label component
  3. b. Defining the security policy
  4. c. Defining the security labels
  5. Altering the MEDICAL_RECORD table by adding a security label column for row level protection, marking the confidential column as protected, and attaching the security policy to the table.
  6. Updating the MEDICAL_RECORD table security label column.
  7. 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 components

From your analysis, two security label components are required. The security label components can be created using the following commands:

					
        CREATE SECURITY LABEL COMPONENT SLC_LEVEL
             SET {'CONFIDENTIAL'}

        CREATE SECURITY LABEL COMPONENT SLC_LIFEINS_ORG
             TREE {'LIFE_INS_DEPT' ROOT,
             'K01' UNDER 'LIFE_INS_DEPT',
             'K02' UNDER 'LIFE_INS_DEPT',
             'S01' UNDER 'LIFE_INS_DEPT',
             'S02' UNDER 'LIFE_INS_DEPT'
             }
        

Step 1b: Defining the security policy

After the security label components are created, the next step is to create the security policy. A security policy with a name MEDICAL_RECORD_ POLICY that uses the SLC_LEVEL and SLC_LIFEINS_ORG security label components can be created using the following command:

        CREATE SECURITY POLICY MEDICAL_RECORD_POLICY
             COMPONENTS SLC_LEVEL, SLC_LIFEINS_ORG
             WITH DB2LBACRULES
             RESTRICT NOT AUTHORIZED WRITE SECURITY LABEL
        

Step 1c: Defining the security labels

From your analysis, the following security labels are needed:

  1. A column security label.
  2. Four security labels for row protection.
  3. A user security label for medical record analysts.

The column security label is created with the following command:

        CREATE SECURITY LABEL MEDICAL_RECORD_POLICY.MED_RECORD
             COMPONENT SLC_LEVEL 'CONFIDENTIAL'
        

The row security labels are created with the following commands:

        CREATE SECURITY LABEL MEDICAL_RECORD_POLICY.DEPT_K01
             COMPONENT SLC_LIFEINS_ORG 'K01'
             
        CREATE SECURITY LABEL MEDICAL_RECORD_POLICY.DEPT_K02
             COMPONENT SLC_LIFEINS_ORG 'K02'
             
        CREATE SECURITY LABEL MEDICAL_RECORD_POLICY.DEPT_S01
             COMPONENT SLC_LIFEINS_ORG 'S01'
             
        CREATE SECURITY LABEL MEDICAL_RECORD_POLICY.DEPT_S02
             COMPONENT SLC_LIFEINS_ORG 'S02'
        

The security label for the medical analyst is created with the following command:

        CREATE SECURITY LABEL MEDICAL_RECORD_POLICY.MEDICAL_ANALYST
             COMPONENT SLC_LEVEL 'CONFIDENTIAL',
             COMPONENT SLC_LIFEINS_ORG 'K01', 'K02', 'S01', 'S02'
        

Step 2: Creating the protected SALES table

Privilege and authority requirements

ALTER table privilege on the MEDICAL_RECORD table.

A database administrator should be granted a security label that is associated with the MEDICAL_RECORD_POLICY security policy. Since administration activities may require working with most rows and columns, you might consider granting the highest security label: MEDICAL_ANALYST. Initially the administrator's security label will be used as the default value for the new DEPARTMENT_TAG column (see Step 3).

        GRANT SECURITY LABEL MEDICAL_RECORD_POLICY.MEDICAL_ANALYST
            TO USER <administrator_auth_id>
            FOR ALL ACCESS
        

To secure the MEDICAL_RECORD table, all confidential columns need to be secured with the MEDICAL_RECORD_POLICY.MED_RECORD security label. To secure the rows, a new column will be added to hold the row security label. The table can be protected by executing the following command:

        ALTER TABLE MEDICAL_RECORD
            ALTER COLUMN MEDICAL_HISTORY
                SECURED WITH MED_RECORD
            ADD COLUMN DEPARTMENT_TAG DB2SECURITYLABEL
            ADD SECURITY POLICY MEDICAL_RECORD_POLICY

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


Table 14. Definition for the protected MEDICAL_RECORD table
Column nameType schemaType nameLengthScaleNulls
RECORDIDSYSIBMCHARACTER60No
CLIENTNOSYSIBMCHARACTER60No
DEPTNOSYSIBMCHARACTER60No
APPLICATION_
DATE
SYSIBMDATE40Yes
LAST_
UPDATE
SYSIBMDATE40No
MEDICAL_
HISTORY
SYSIBMCLOB10485760No
RISK_
INDEX
SYSIBMSMALLINT10No
DEPARTMENT_
TAG
SYSIBMDB2SECURITY
LABEL
00No
The shading shows the protected MEDICAL_HISTORY column and the added DEPARTMENT_TAG column.

Step 3: Updating the security label column

Privilege and authority requirements

UPDATE privilege on the MEDICAL_RECORD table:

The database administrator can either be granted a security label that can be used for updates (you did this in Step 2), or you could grant an EXEMPTION for WRITE access.

        GRANT EXEMPTION ON RULE DB2LBACWRITETREE
             FOR MEDICAL_RECORD_POLICY
             TO USER <administrator_auth_id>
        

Initially the DEPARTMENT_TAG column will be populated with the administrator's security label (MEDICAL_RECORD_POLICY.MEDICAL_ANALYST), when the table is altered. Next that column needs to be updated with the appropriate security label for each record using the following commands:

UPDATE MEDICAL_RECORD
        set DEPARTMENT_TAG= SECLABEL_BY_NAME ('MEDICAL_RECORD_POLICY', 'DEPT_K01')
        where DEPTNO='K01'

UPDATE MEDICAL_RECORD
     set DEPARTMENT_TAG= SECLABEL_BY_NAME ('MEDICAL_RECORD_POLICY', 'DEPT_K02')
     where DEPTNO='K02'

UPDATE MEDICAL_RECORD
     set DEPARTMENT_TAG= SECLABEL_BY_NAME ('MEDICAL_RECORD_POLICY', 'DEPT_S01')
     where DEPTNO='S01'

UPDATE MEDICAL_RECORD
     set DEPARTMENT_TAG= SECLABEL_BY_NAME ('MEDICAL_RECORD_POLICY', 'DEPT_S02')
     where DEPTNO='S02'             
        

The updated MEDICAL_RECORD table would look like this.


Table 15. Updated MEDICAL_RECORD table
RECORDIDCLIENTNODEPTNOALLOCATION_
DATE
LAST_
UPDATE
MEDICAL_
HISTORY
RISK_
FACTOR
Row Security
Label
000010K108341K012005/01/052005/01/15...0MEDICAL_RECORD_
POLICY.DEPT_K01
000020K181245K012005/02/092005/02/19...2MEDICAL_RECORD_
POLICY.DEPT_K01
000030S245987S022005/02/112005/02/21...1MEDICAL_RECORD_
POLICY.DEPT_S02
000050S231674S022005/03/232005/04/04...1MEDICAL_RECORD_
POLICY.DEPT_S02

Step 4: Granting the security labels to users

Privilege and authority requirements

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

After the MEDICAL_RECORD table has been protected, no user can access the table until security labels are granted to them. To allow Peter, Andrea, and Joseph access to the table, grant them security labels with the following commands:

        GRANT SECURITY LABEL MEDICAL_RECORD_POLICY. MEDICAL_ANALYST
            TO USER PETER
            FOR ALL ACCESS

        GRANT SECURITY LABEL MEDICAL_RECORD_POLICY.DEPT_K01
            TO USER Andrea
            FOR ALL ACCESS
            
        GRANT SECURITY LABEL MEDICAL_RECORD_POLICY.DEPT_S02
            TO USER Joseph
            for ALL 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 MEDICAL_RECORD table has been protected.

Example 1

Peter, the medical records analyst, tries to read the MEDICAL_RECORD table by issuing:

        SELECT * from INSURANCE.MEDICAL_RECORD;
        

Command executes successfully. All records of the MEDICAL_RECORD are returned.

Example 2

Andrea, the manager of department K01, tries to read the MEDICAL_RECORD table by issuing:

SELECT RECORDID, CLIENTNO, DEPTNO, APPLICATION_DATE, 
LAST_UPDATE, MEDICAL_HISTORY, RISK_INDEX 
             from INSURANCE.MEDICAL_RECORD;
        

The command execution fails because it violates the READ access rule on the protected MEDICAL_HISTORY column.

Example 3

Joseph, the manager of department S02, tries to manipulate a record in the MEDICAL_RECORD table. First he queries for the record:

           SELECT RISK_INDEX from INSURANCE.MEDICAL_RECORD
           WHERE CLIENTNO='S231674';
        

Command executes successfully. The value of RISK_INDEX is returned.

Joseph then tries to update the value of the RISK_INDEX.

        UPDATE INSURANCE.MEDICAL_RECORD
             SET RISK_INDEX = 0
             WHERE CLIENTNO='S231674'
        

The command fails because it violates the write access rule.
The same command executed by Peter would be successful since Peter has write access to the records.

Example 4

Joseph tries to update one of the confidential columns in the MEDICAL_RECORD table.

        UPDATE INSURANCE.MEDICAL_RECORD
             SET MEDICAL_HISTORY =''
             WHERE CLIENTNO='S231674'
        

The command fails because it violates the access rule for the protected column.


Conclusion

This completes Part 1 of the LBAC tutorial. You should now be familiar with the basics of row and column protection and be able to design a LBAC solution to protected your data. In Part 2, you will work through more complex scenarios and learn how to apply exemptions to your security controls.

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