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 developerWorks profile is displayed to the public, but you may edit the information at any time. Your first name, last name (unless you choose to hide them), and display name will accompany the content that you post.

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]

DB2 9 Fundamentals exam 730 prep, Part 2: Security

Graham G. Milne (gmilne@ca.ibm.com), I/T Specialist DB2 UDB, IBM Canada
Graham Milne, HBSc. - Computer Science, is a DB2 Certified Advance Technical Expert and has been working with DB2 since 1998. Currently Graham is a Premium Support Manager for DB2 supporting large premium customers. Previous to this, he was the senior advanced service consultant for DB2 support based out of the IBM Toronto Software Lab.

Summary:  This tutorial introduces the concepts of authentication, authorization, and privileges as they relate to DB2® 9. It is the second in a series of seven tutorials designed to help you prepare for the DB2 9 Fundamentals Certification Exam (730). You should have basic knowledge of database concepts and operating system security. This is the second in a series of seven tutorials to help you prepare for the DB2 9 for Linux®, UNIX®, and Windows® Fundamentals exam 730.

View more content in this series

Date:  20 Jul 2006
Level:  Intermediate PDF:  A4 and Letter (505 KB | 32 pages)Get Adobe® Reader®

Comments:  

DB2 privileges

Database and object privileges

In the preceding section, the concept of privileges was briefly touched. Privileges can generally be placed into two main categories: database-level privileges, which span all objects within the database, and object-level privileges, which are associated with a specific object.

The database-level privileges that a user might be given are:

  • CREATETAB: Users can create tables within the database.
  • BINDADD: Users can create packages in the database using the BIND command.
  • CONNECT: Users can connect to the database.
  • CREATE_NOT_FENCED: Users can create unfenced user-defined functions (UDFs).
  • IMPLICIT_SCHEMA: Users can implicitly create schemas within the database without using the CREATE SCHEMA command.
  • LOAD: Users can load data into a table
  • QUIESCE_CONNECT: Users can access a database while it is in a quiesced state.
  • CREATE_EXTERNAL_ROUTINE: Users can create a procedure for use by applications and other users of the database.

Database objects include tables, views, indexes, schemas, and packages. Fortunately, most of the object-level privileges are self explanatory. The following table summarizes these privileges.


Table 4. Summary of privileges
Privilege nameRelevant objectsDescription
CONTROL Table, View, Index, Package, Alias, Distinct Type, User Defined function, Sequence Provides full authority on the object. Users with this privilege can also grant or revoke privileges on the object to other users.
DELETE Table, View Allows users to delete records from the object.
INSERT Table, View Allows users to insert records into the object via the INSERT or the IMPORT commands.
SELECT Table, View Provides the ability to view the contents of the object using the select statement.
UPDATE Table, View Allows users to modify records within the object using the update statement.
ALTER Table Allows users to alter the object definition using the alter statement.
INDEX Table Allows users to create indexes on the object using the create index statement.
REFERENCES Table Provides the ability to create or drop foreign key constraints on the object.
BIND Package Allows users to rebind existing packages.
EXECUTE Package, Procedure, Function, Method Allows users to execute packages and routines.
ALTERIN Schema Allows users to modify definitions of objects within the schema.
CREATEIN Schema Allows users to create objects within the schema.
DROPIN Schema Allows users to drop objects within the schema.

Information on object-level privileges is stored in the system catalog views. The view names are syscat.tabauth, syscat.colauth, syscat.indexauth, syscat.schemaauth, syscat.routineauth, and syscat.packageauth.


Explicit privileges

Privileges can be explicitly granted and revoked to users or groups using the GRANT and REVOKE commands. Let's take a look at how you can use these commands on various objects.

While logged in as a user with Administrator authority on Windows, bring up two DB2 command windows. Make sure that the db2instance variable is set to DB2 in both windows!

From Window 1, issue the following command:

db2 connect to sample

Now, from Window 2, issue this command:

db2 connect to sample user test1 using password

Remember, the commands in Window 1 are being issued by a user with SYSADM authority. The commands in Window 2 are being issued by tst1, a user with no specific authority or privileges on the sample database. Note that the schema name associated with the tables in your sample database will be the name of the user that issued the db2sampl command. In these examples, that user is GMILNE.

Now, from Window 2, issue the following command:

db2 select * from gmilne.org

You should see this response:

SQL0551N  "TEST1" does not have the privilege to perform operation "SELECT" 
on object "GMILNE.ORG".

To correct the situation, issue the following command from Window 1:

db2 grant select on table gmilne.org to user test1

Now the earlier command will succeed! Next, let's issue a more ambitious command from Window 2:

db2 insert into gmilne.org values (100, 'Tutorial', 1, 'Eastern', 'Toronto')

Again, you'll see an error message:

SQL0551N  "TEST1" does not have the privilege to perform operation  "INSERT" 
on object "GMILNE.ORG"

So, enter the following command from Window 1:

db2 grant insert on table gmilne.org to group db2grp1

The earlier failed INSERT command should now complete successfully, because test1 is a member of group db2grp1.

Now, enter the following command in Window 2:

db2 drop table gmilne.emp_photo

Again, you'll see an error message:

SQL0551N  "TEST1" does not have the privilege to perform operation "DROP TABLE"
on object "GMILNE.EMP_PHOTO".

So, we'll have the grant that privilege. Enter the following from Window 1:

db2 grant dropin on schema gmilne to all

The DROP TABLE command should now complete successfully.

Now that we've finished our example, let's revoke all the privileges you just granted. Do so by issuing the following commands from Window 1:

db2 revoke select on table gmilne.org from user test1
db2 revoke insert on table gmilne.org from group db2grp1
db2 revoke dropin on schema gmilne from all

Note that revoking privileges from a group does not necessarily revoke it from all members of that group. For example, the following command could have been used to revoke all privileges (except CONTROL) from db2grp1 on the table gmilne.org:

db2 revoke all on table gmilne.org from group db2grp1

However, the user test1 (who is a member of db2grp1 ) would have kept the select privileges on that table, since he or she had been granted that privilege directly.


Implicit privileges

DB2 may grant privileges automatically when certain commands are issued, without the need for an explicit GRANT statement to be issued, as you saw previously. The table below summarizes some commands that result in privileges being implicitly granted by the database manager. Note that these privileges are implicitly revoked when the object created is dropped. They are not, however, revoked when higher-level privileges are explicitly revoked.


Table 5. Resulting commands from privileges being implicitly granted by the database manager
Command issuedPrivilege grantedTo whom privilege is granted
CREATE TABLE mytable CONTROL on mytable User issuing command
CREATE SCHEMA myschema CREATEIN, ALTERIN, DROPIN on myschema, plus the ability to grant these to others User issuing command
CREATE VIEW myview CONTROL on myview only if CONTROL is held on all tables and views referenced in the definition of myview User issuing command
CREATE DATABASE mydb SELECT on mydb 's system catalog tables, IMPLICIT_SCHEMA on mydb * PUBLIC**

*When a user creates a database, that user is implicitly granted DBADM authority on that database. With DBADM authority comes implicit CONNECT, CREATETAB, BINDADD, IMPLICIT_SCHEMA, and CREATE_NOT_FENCED privileges. These privileges will remain with the user even if the DBADM authority is revoked.

**PUBLIC is a special DB2 group that includes all users of a particular database. Unlike the other groups we've discussed thus far, PUBLIC does not have to be defined at the operating system level. There are some privileges granted to PUBLIC by default. For example, this group receives CONNECT privilege on the database and SELECT privilege on the catalog tables automatically. GRANT and REVOKE commands can be issued against the PUBLIC group, like so:

db2 grant select on table sysibm.systables to public
db2 revoke select on table sysibm.systables from public
			


Indirect privileges

Privileges can be obtained indirectly when packages are executed by the database manager. A package contains one or more SQL statements that have been converted into a format that DB2 uses internally to execute them. In other words, a package contains multiple SQL statements in an executable format. If all the statements in the package are static, a user would only require EXECUTE privilege on the package to successfully execute the statements in the package.

For example, assume db2package1 executes the following static SQL statements:

db2 select * from org
db2 insert into test values (1, 2, 3)
			

In this case, a user with EXECUTE privilege on db2package1 would indirectly be granted SELECT privilege on the table org and INSERT privilege on the table test.


Label-based access control

New to DB2 9 is the concept of label-based access control (LBAC). What LBAC provides the DBA is the ability to restrict read / write privileges on the row or column level of a table.

Previously, the only way to introduce these restrictions was to create a view, authorize the view's use by the user in question, and remove access to the base table.

This tutorial will only demonstrate one example of a LBAC security scenario. For a more detailed explanation of LBAC, refer to DB2 Label-Based Access Control, a practical guide, Part 1: Understand the basics of LBAC in DB2 on developerWorks.

LBAC is set up by the security administrator by creating Security Policies. Each table may only be subscribed to one security policy, but the system may have as many security policies as you'd like. There are several steps required to set up LBAC. The first thing you must do is determine the type of access control you require for your data.

Let's assume the following. In your organization there are three sets of people:


Table 6. Example organization
NameOrganizational role
JaneHuman Resources Executive
JoeManager of Department D11 and E21
FrankTeam Lead - Department A00

Now, in the organization's database there is a table that defines Employee information. This will be based off of the EMP table in the SAMPLE database. It contains data on employees and the departments they belong to. Its existing definition is as follows:

				db2 => describe select * from emp

SQLDA Information

 sqldaid : SQLDA     sqldabc: 896  sqln: 20  sqld: 14

 Column Information

 sqltype               sqllen  sqlname.data                    sqlname.length
 --------------------  ------  ------------------------------  --------------
 452   CHARACTER            6  EMPNO                                        5
 448   VARCHAR             12  FIRSTNME                                     8
 453   CHARACTER            1  MIDINIT                                      7
 448   VARCHAR             15  LASTNAME                                     8
 453   CHARACTER            3  WORKDEPT                                     8
 453   CHARACTER            4  PHONENO                                      7
 385   DATE                10  HIREDATE                                     8
 453   CHARACTER            8  JOB                                          3
 500   SMALLINT             2  EDLEVEL                                      7
 453   CHARACTER            1  SEX                                          3
 385   DATE                10  BIRTHDATE                                    9
 485   DECIMAL           9, 2  SALARY                                       6
 485   DECIMAL           9, 2  BONUS                                        5
 485   DECIMAL           9, 2  COMM                                         4
			

The organization has rules in place that are audited on a regular basis. Part of this audit indicates that the employees should not have access to data that is considered confidential. The rules stipulate that executives have full read / write access to all employee records, Managers have read / write access to anyone in their department, and team leads have read access to anyone in the department they lead.

To set up LBAC security to enable these rules:

  1. Define the security policies and labels, and grant the security labels to the users.
  2. Modify the EMP table, including the security label column, and attach the security policy to it.

Defining the security policies and labels

To define the security policies and labels, SECADM authority is required.

Step 1a. Create the security label component

The first thing you need to do is to determine the best type of security component to define for this policy. In this particular case, the best fit is a policy type of "TREE". A Tree policy means that you can define a set of labels such that the children have a subset of the rights that their parent does. In this example, create a security component named "J_DEPT".

			CREATE SECURITY LABEL COMPONENT J_DEPT
        TREE ('HR_EXECUTIVE' ROOT,
              'MAN_D11_E21' UNDER 'HR_EXECUTIVE'
              'A00' UNDER 'HR_EXECUTIVE',
              'B01' UNDER 'HR_EXECUTIVE',
              'C01' UNDER 'HR_EXECUTIVE',
              'D11' UNDER 'MAN_D11_E21',
              'D21' UNDER 'HR_EXECUTIVE',
              'E01' UNDER 'HR_EXECUTIVE',
              'E11' UNDER 'HR_EXECUTIVE',
              'E21' UNDER 'MAN_D11_E21'
        )
      

The above layout indicates that the root is HR_EXECUTIVE, and all the departments are children under that executive.

Step 1b. Define the security policy

The next step required to use LBAC security in the above example is to define the policy associated with the security label component above. A security policy can use more than one component.

      	CREATE SECURITY POLICY J_DEPT_POLICY
             COMPONENTS J_DEPT
             WITH DB2LBACRULES
             RESTRICT NOT AUTHORIZED WRITE SECURITY LABEL

Step 1c. Create the security labels

The third step in setting up the security policy is to create the security labels. This is where you will specify the different roles that each user has. In this case, since the example is fairly simple, there will only be three labels, Executive, Manager and Team Lead.

      CREATE SECURITY LABEL J_DEPT_POLICY.EXECUTIVE
             COMPONENT J_DEPT 'HR_EXECUTIVE'
             
      CREATE SECURITY LABEL J_DEPT_POLICY.MANAGE_D11_E21
             COMPONENT J_DEPT 'MAN_D11_E21'
             
      CREATE SECURITY LABEL J_DEPT_POLICY.A00
             COMPONENT J_DEPT 'A00'
             
      CREATE SECURITY LABEL J_DEPT_POLICY.B01
             COMPONENT J_DEPT 'B01'
             
      CREATE SECURITY LABEL J_DEPT_POLICY.C01
             COMPONENT J_DEPT 'C01'

      CREATE SECURITY LABEL J_DEPT_POLICY.D11
             COMPONENT J_DEPT 'D11'

      CREATE SECURITY LABEL J_DEPT_POLICY.D21
             COMPONENT J_DEPT 'D21'

      CREATE SECURITY LABEL J_DEPT_POLICY.E01
             COMPONENT J_DEPT 'E01'

      CREATE SECURITY LABEL J_DEPT_POLICY.E11
             COMPONENT J_DEPT 'E11'

      CREATE SECURITY LABEL J_DEPT_POLICY.E21
             COMPONENT J_DEPT 'E21'             
      

In the next step you'll define the actual permissions associated with these labels.

Step 1d. Grant rights based on labels

The following steps outline the procedures for granting the rights to the table data. Rights are either ALL ACCESS, WRITE ACCESS, or READ ACCESS. If none of these rights are granted to a user, then that user doesn't have the capability to access any of the table data. Remember that executives have full access, managers have full access to their departments, and team leads have read access to members of the departments they lead.

db2 grant security label J_DEPT_POLICY.A00 to user Frank for read access
db2 grant security label J_DEPT_POLICY.MANAGE_D11_E21 to user Joe for all access
db2 grant security label J_DEPT_POLICY.EXECUTIVE to user Jane for all access
      

Setting the above labels on the users will cascade rights based on the tree definitions in step 1a. Because user Joe is labeled as MANAGE_D11_E21, and is given all rights, he will be able to read and write rows that have a security tag of J_DEPT_POLICY.D11 or J_DEPT_POLICY.E21 (since they are his children).

Step 2: Modify the EMP table

When modifying the EMP table, you must create an extra column to store the security label. This is of type "DB2SECURITYLABEL". You are going to modify the existing EMP table in the SAMPLE database. To do this, you must user a user that has been granted root level privilege in the policy, so in this case the user Jane. You must also first drop the MQT table ADEFUSR from the sample database.

CONNECT TO SAMPLE

   Database Connection Information

 Database server        = DB2/NT 9.1.0
 SQL authorization ID   = GMILNE
 Local database alias   = SAMPLE  

DROP TABLE ADEFUSR

CONNECT RESET

CONNECT TO SAMPLE USER Jane USING password

ALTER TABLE EMP
		ADD COLUMN DEPT_TAG DB2SECURITYLABEL
		ADD SECURITY POLICY J_DEPT_POLICY
 
			

If you select from the EMP table, you will see the additional column defined. Because you performed the changes with a user defined on the EXECUTIVE level, all the security tags will have been added as EXECUTIVE. To change this, you need to update the table.

db2 => select EMPNO, FIRSTNME, LASTNAME, WORKDEPT, SALARY, 
varchar(SECLABEL_TO_CHAR('J_DEPT_POLICY',DEPT_TAG),30) from gmilne.emp

EMPNO  FIRSTNME     LASTNAME        WORKDEPT SALARY      6
------ ------------ --------------- -------- ----------- ------------------------------
000010 CHRISTINE    HAAS            A00        152750.00 HR_EXECUTIVE
000020 MICHAEL      THOMPSON        B01         94250.00 HR_EXECUTIVE
000030 SALLY        KWAN            C01         98250.00 HR_EXECUTIVE
000050 JOHN         GEYER           E01         80175.00 HR_EXECUTIVE
000060 IRVING       STERN           D11         72250.00 HR_EXECUTIVE
000070 EVA          PULASKI         D21         96170.00 HR_EXECUTIVE
000090 EILEEN       HENDERSON       E11         89750.00 HR_EXECUTIVE
000100 THEODORE     SPENSER         E21         86150.00 HR_EXECUTIVE
000110 VINCENZO     LUCCHESSI       A00         66500.00 HR_EXECUTIVE
000120 SEAN         O'CONNELL       A00         49250.00 HR_EXECUTIVE
000130 DELORES      QUINTANA        C01         73800.00 HR_EXECUTIVE
000140 HEATHER      NICHOLLS        C01         68420.00 HR_EXECUTIVE
000150 BRUCE        ADAMSON         D11         55280.00 HR_EXECUTIVE
000160 ELIZABETH    PIANKA          D11         62250.00 HR_EXECUTIVE
000170 MASATOSHI    YOSHIMURA       D11         44680.00 HR_EXECUTIVE
000180 MARILYN      SCOUTTEN        D11         51340.00 HR_EXECUTIVE
000190 JAMES        WALKER          D11         50450.00 HR_EXECUTIVE
000200 DAVID        BROWN           D11         57740.00 HR_EXECUTIVE
000210 WILLIAM      JONES           D11         68270.00 HR_EXECUTIVE
000220 JENNIFER     LUTZ            D11         49840.00 HR_EXECUTIVE
000230 JAMES        JEFFERSON       D21         42180.00 HR_EXECUTIVE
000240 SALVATORE    MARINO          D21         48760.00 HR_EXECUTIVE
000250 DANIEL       SMITH           D21         49180.00 HR_EXECUTIVE
000260 SYBIL        JOHNSON         D21         47250.00 HR_EXECUTIVE
000270 MARIA        PEREZ           D21         37380.00 HR_EXECUTIVE
000280 ETHEL        SCHNEIDER       E11         36250.00 HR_EXECUTIVE
000290 JOHN         PARKER          E11         35340.00 HR_EXECUTIVE
000300 PHILIP       SMITH           E11         37750.00 HR_EXECUTIVE
000310 MAUDE        SETRIGHT        E11         35900.00 HR_EXECUTIVE
000320 RAMLAL       MEHTA           E21         39950.00 HR_EXECUTIVE
000330 WING         LEE             E21         45370.00 HR_EXECUTIVE
000340 JASON        GOUNOT          E21         43840.00 HR_EXECUTIVE
200010 DIAN         HEMMINGER       A00         46500.00 HR_EXECUTIVE
200120 GREG         ORLANDO         A00         39250.00 HR_EXECUTIVE
200140 KIM          NATZ            C01         68420.00 HR_EXECUTIVE
200170 KIYOSHI      YAMAMOTO        D11         64680.00 HR_EXECUTIVE
200220 REBA         JOHN            D11         69840.00 HR_EXECUTIVE
200240 ROBERT       MONTEVERDE      D21         37760.00 HR_EXECUTIVE
200280 EILEEN       SCHWARTZ        E11         46250.00 HR_EXECUTIVE
200310 MICHELLE     SPRINGER        E11         35900.00 HR_EXECUTIVE
200330 HELENA       WONG            E21         35370.00 HR_EXECUTIVE
200340 ROY          ALONZO          E21         31840.00 HR_EXECUTIVE

  42 record(s) selected.

This listing is followed by the following:

update emp set DEPT_TAG=(SECLABEL_BY_NAME('J_DEPT_POLICY','A00')) where WORKDEPT='A00'

update emp set DEPT_TAG=(SECLABEL_BY_NAME('J_DEPT_POLICY','B01')) where WORKDEPT='B01'

update emp set DEPT_TAG=(SECLABEL_BY_NAME('J_DEPT_POLICY','C01')) where WORKDEPT='C01'

update emp set DEPT_TAG=(SECLABEL_BY_NAME('J_DEPT_POLICY','D11')) where WORKDEPT='D11'

update emp set DEPT_TAG=(SECLABEL_BY_NAME('J_DEPT_POLICY','D21')) where WORKDEPT='D21'

update emp set DEPT_TAG=(SECLABEL_BY_NAME('J_DEPT_POLICY','E01')) where WORKDEPT='E01'

update emp set DEPT_TAG=(SECLABEL_BY_NAME('J_DEPT_POLICY','E11')) where WORKDEPT='E11'

update emp set DEPT_TAG=(SECLABEL_BY_NAME('J_DEPT_POLICY','E21')) where WORKDEPT='E21'

db2 => select EMPNO, FIRSTNME, LASTNAME, WORKDEPT, SALARY, 
varchar(SECLABEL_TO_CHAR('J_DEPT_POLICY',DEPT_TAG),30) from emp

EMPNO  FIRSTNME     LASTNAME        WORKDEPT SALARY      6
------ ------------ --------------- -------- ----------- ------------------------------
000010 CHRISTINE    HAAS            A00        152750.00 A00
000020 MICHAEL      THOMPSON        B01         94250.00 B01
000030 SALLY        KWAN            C01         98250.00 C01
000050 JOHN         GEYER           E01         80175.00 E01
000060 IRVING       STERN           D11         72250.00 D11
000070 EVA          PULASKI         D21         96170.00 D21
000090 EILEEN       HENDERSON       E11         89750.00 E11
000100 THEODORE     SPENSER         E21         86150.00 E21
000110 VINCENZO     LUCCHESSI       A00         66500.00 A00
000120 SEAN         O'CONNELL       A00         49250.00 A00
000130 DELORES      QUINTANA        C01         73800.00 C01
000140 HEATHER      NICHOLLS        C01         68420.00 C01
000150 BRUCE        ADAMSON         D11         55280.00 D11
000160 ELIZABETH    PIANKA          D11         62250.00 D11
000170 MASATOSHI    YOSHIMURA       D11         44680.00 D11
000180 MARILYN      SCOUTTEN        D11         51340.00 D11
000190 JAMES        WALKER          D11         50450.00 D11
000200 DAVID        BROWN           D11         57740.00 D11
000210 WILLIAM      JONES           D11         68270.00 D11
000220 JENNIFER     LUTZ            D11         49840.00 D11
000230 JAMES        JEFFERSON       D21         42180.00 D21
000240 SALVATORE    MARINO          D21         48760.00 D21
000250 DANIEL       SMITH           D21         49180.00 D21
000260 SYBIL        JOHNSON         D21         47250.00 D21
000270 MARIA        PEREZ           D21         37380.00 D21
000280 ETHEL        SCHNEIDER       E11         36250.00 E11
000290 JOHN         PARKER          E11         35340.00 E11
000300 PHILIP       SMITH           E11         37750.00 E11
000310 MAUDE        SETRIGHT        E11         35900.00 E11
000320 RAMLAL       MEHTA           E21         39950.00 E21
000330 WING         LEE             E21         45370.00 E21
000340 JASON        GOUNOT          E21         43840.00 E21
200010 DIAN         HEMMINGER       A00         46500.00 A00
200120 GREG         ORLANDO         A00         39250.00 A00
200140 KIM          NATZ            C01         68420.00 C01
200170 KIYOSHI      YAMAMOTO        D11         64680.00 D11
200220 REBA         JOHN            D11         69840.00 D11
200240 ROBERT       MONTEVERDE      D21         37760.00 D21
200280 EILEEN       SCHWARTZ        E11         46250.00 E11
200310 MICHELLE     SPRINGER        E11         35900.00 E11
200330 HELENA       WONG            E21         35370.00 E21
200340 ROY          ALONZO          E21         31840.00 E21

  42 record(s) selected.

      

After the update, let's see what the individual users can do. You'll connect to the database using the Executive user ID Jane. Start with the same select statement performed before:

 
db2 => select EMPNO, FIRSTNME, LASTNAME, WORKDEPT, SALARY, 
varchar(SECLABEL_TO_CHAR('J_DEPT_POLICY',DEPT_TAG),30) from gmilne.emp

EMPNO  FIRSTNME     LASTNAME        WORKDEPT SALARY      6
------ ------------ --------------- -------- ----------- ------------------------------
000010 CHRISTINE    HAAS            A00        152750.00 A00
000020 MICHAEL      THOMPSON        B01         94250.00 B01
000030 SALLY        KWAN            C01         98250.00 C01
000050 JOHN         GEYER           E01         80175.00 E01
000060 IRVING       STERN           D11         72250.00 D11
000070 EVA          PULASKI         D21         96170.00 D21
000090 EILEEN       HENDERSON       E11         89750.00 E11
000100 THEODORE     SPENSER         E21         86150.00 E21
000110 VINCENZO     LUCCHESSI       A00         66500.00 A00
000120 SEAN         O'CONNELL       A00         49250.00 A00
000130 DELORES      QUINTANA        C01         73800.00 C01
000140 HEATHER      NICHOLLS        C01         68420.00 C01
000150 BRUCE        ADAMSON         D11         55280.00 D11
000160 ELIZABETH    PIANKA          D11         62250.00 D11
000170 MASATOSHI    YOSHIMURA       D11         44680.00 D11
000180 MARILYN      SCOUTTEN        D11         51340.00 D11
000190 JAMES        WALKER          D11         50450.00 D11
000200 DAVID        BROWN           D11         57740.00 D11
000210 WILLIAM      JONES           D11         68270.00 D11
000220 JENNIFER     LUTZ            D11         49840.00 D11
000230 JAMES        JEFFERSON       D21         42180.00 D21
000240 SALVATORE    MARINO          D21         48760.00 D21
000250 DANIEL       SMITH           D21         49180.00 D21
000260 SYBIL        JOHNSON         D21         47250.00 D21
000270 MARIA        PEREZ           D21         37380.00 D21
000280 ETHEL        SCHNEIDER       E11         36250.00 E11
000290 JOHN         PARKER          E11         35340.00 E11
000300 PHILIP       SMITH           E11         37750.00 E11
000310 MAUDE        SETRIGHT        E11         35900.00 E11
000320 RAMLAL       MEHTA           E21         39950.00 E21
000330 WING         LEE             E21         45370.00 E21
000340 JASON        GOUNOT          E21         43840.00 E21
200010 DIAN         HEMMINGER       A00         46500.00 A00
200120 GREG         ORLANDO         A00         39250.00 A00
200140 KIM          NATZ            C01         68420.00 C01
200170 KIYOSHI      YAMAMOTO        D11         64680.00 D11
200220 REBA         JOHN            D11         69840.00 D11
200240 ROBERT       MONTEVERDE      D21         37760.00 D21
200280 EILEEN       SCHWARTZ        E11         46250.00 E11
200310 MICHELLE     SPRINGER        E11         35900.00 E11
200330 HELENA       WONG            E21         35370.00 E21
200340 ROY          ALONZO          E21         31840.00 E21

  42 record(s) selected.
			

And the update command:

db2 => update gmilne.emp set DEPT_TAG=(SECLABEL_BY_NAME('J_DEPT_POLICY','E01')) 
where WORKDEPT='E01' DB20000I  The SQL command completed successfully.

As you can see, Jane has full access to all the data in the table. Now let's look at what Joe can see. First, look at the select command again.

db2 => select EMPNO, FIRSTNME, LASTNAME, WORKDEPT, SALARY, 
varchar(SECLABEL_TO_CHAR('J_DEPT_POLICY',DEPT_TAG),30) from gmilne.emp

EMPNO  FIRSTNME     LASTNAME        WORKDEPT SALARY      6
------ ------------ --------------- -------- ----------- ------------------------------
000060 IRVING       STERN           D11         72250.00 D11
000100 THEODORE     SPENSER         E21         86150.00 E21
000150 BRUCE        ADAMSON         D11         55280.00 D11
000160 ELIZABETH    PIANKA          D11         62250.00 D11
000170 MASATOSHI    YOSHIMURA       D11         44680.00 D11
000180 MARILYN      SCOUTTEN        D11         51340.00 D11
000190 JAMES        WALKER          D11         50450.00 D11
000200 DAVID        BROWN           D11         57740.00 D11
000210 WILLIAM      JONES           D11         68270.00 D11
000220 JENNIFER     LUTZ            D11         49840.00 D11
000320 RAMLAL       MEHTA           E21         39950.00 E21
000330 WING         LEE             E21         45370.00 E21
000340 JASON        GOUNOT          E21         43840.00 E21
200170 KIYOSHI      YAMAMOTO        D11         64680.00 D11
200220 REBA         JOHN            D11         69840.00 D11
200330 HELENA       WONG            E21         35370.00 E21
200340 ROY          ALONZO          E21         31840.00 E21

  17 record(s) selected.
			

See how he can only see information from departments D11 and E21? Let's see what happens when he tries to select data that is in the table, but he is not allowed to see:

db2 => select EMPNO, FIRSTNME, LASTNAME, WORKDEPT, SALARY, 
varchar(SECLABEL_TO_CHAR('J_DEPT_POLICY',DEPT_TAG),30) 
from gmilne.emp where empno='000130'

EMPNO  FIRSTNME     LASTNAME        WORKDEPT SALARY      6
------ ------------ --------------- -------- ----------- ------------------------------

  0 record(s) selected.
  		

You know from the previous select with Jane that there is an employee in there with empno 000130, but Joe is not allowed to see it.

Now, one last test, with Frank.

First, the same select the other two users have run:

db2 => select EMPNO, FIRSTNME, LASTNAME, WORKDEPT, SALARY, 
varchar(SECLABEL_TO_CHAR('J_DEPT_POLICY',DEPT_TAG),30) from gmilne.emp

EMPNO  FIRSTNME     LASTNAME        WORKDEPT SALARY      6
------ ------------ --------------- -------- ----------- ------------------------------
000010 CHRISTINE    HAAS            A00        152750.00 A00
000110 VINCENZO     LUCCHESSI       A00         66500.00 A00
000120 SEAN         O'CONNELL       A00         49250.00 A00
200010 DIAN         HEMMINGER       A00         46500.00 A00
200120 GREG         ORLANDO         A00         39250.00 A00

  5 record(s) selected.
			

In this case you can see that Frank can only see information about users from the department he leads. Let's see what happens when he tries to update:

db2 => update gmilne.emp set DEPT_TAG=(SECLABEL_BY_NAME('J_DEPT_POLICY','A00')) 
where WORKDEPT='A00'DB21034E  The command was processed as an SQL statement 
because it was not a valid Command Line Processor command.  During SQL processing it 
returned:
SQL20402N Authorization ID "FRANK" does not have the LBAC credentials to
perform the "UPDATE" operation on table "EMPLOYEE".  SQLSTATE=42519
			

Even though he is trying to update a record that is in his own department, you created his access security to only allow read access to the table. Our business requirements have been satisfied.

5 of 8 | Previous | Next

Comments



Help: Update or add to My dW interests

What's this?

This little timesaver lets you update your My developerWorks profile with just one click! The general subject of this content (AIX and UNIX, Information Management, Lotus, Rational, Tivoli, WebSphere, Java, Linux, Open source, SOA and Web services, Web development, or XML) will be added to the interests section of your profile, if it's not there already. You only need to be logged in to My developerWorks.

And what's the point of adding your interests to your profile? That's how you find other users with the same interests as yours, and see what they're reading and contributing to the community. Your interests also help us recommend relevant developerWorks content to you.

View your My developerWorks profile

Return from help

Help: Remove from My dW interests

What's this?

Removing this interest does not alter your profile, but rather removes this piece of content from a list of all content for which you've indicated interest. In a future enhancement to My developerWorks, you'll be able to see a record of that content.

View your My developerWorks profile

Return from help

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=147859
TutorialTitle=DB2 9 Fundamentals exam 730 prep, Part 2: Security
publish-date=07202006
author1-email=gmilne@ca.ibm.com
author1-email-cc=

Tags

Help
Use the search field to find all types of content in My developerWorks with that tag.

Use the slider bar to see more or fewer tags.

Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere).

My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).

Use the search field to find all types of content in My developerWorks with that tag. Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere). My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).