IBM Support

[Db2] Simple test case shell script for using Label-based access control (LBAC)

Question & Answer


Question

We want to have two level users for a specific column in a Db2 table.
Here is the scenario:
   User A: can read all columns in a table.
   User B: can read most of all columns except a specific column.
Could you educate us how to do that?

Answer

Here is a basic simple test case for your reference.
1. Create a Db2 instance by root user and set password
   # useradd db2inst1
   # useradd db2fenc1
   # /opt/ibm/db2/V11560/instance/db2icrt -u db2fenc1 db2inst1
   Run passwd command to set password for the instance owner db2inst1 as "Db2support"
2. Create two users at OS level and set password for them by root user
   # useradd user1
   # useradd user2
   Run passwd command for user1 and usr2 and set password as "Db2support"
3. Run db2start by instance owner db2inst1, save script as 
   repro.sh, run chmod 777 repro.sh and then execute it.
 
#!/bin/sh

db2 -v "drop db db1"
db2 -v "create db db1"

#### by instance owner
db2 -v "connect to db1 user db2inst1 using Db2support"
db2 -v "create security label component level array ['CONFIDENTIAL']"
db2 -v "create security policy secpolicy components level with db2lbacrules"
db2 -v "create security label secpolicy.level1 component level 'CONFIDENTIAL'"
db2 -v "grant security label secpolicy.level1 to user user1"
db2 -v "terminate"

#### by user1
db2 -v "connect to db1 user user1 using Db2support"
db2 -v "create table s1.t1 (c1 int, c2 int, c3 int secured with level1 ) security policy secpolicy"
db2 -v "insert into s1.t1 values (1,2,3)"
db2 -v "insert into s1.t1 values (4,5,6)"
db2 -v "insert into s1.t1 values (7,8,9)"
db2 -v "select c1, c2, c3 from s1.t1"
db2 -v "grant select on table s1.t1 to user user2"
db2 -v "terminate"

#### by user2
db2 -v "connect to db1 user user2 using Db2support"
db2 -v "select c1, c2, c3 from s1.t1"
db2 -v "select c1, c2 from s1.t1"
db2 -v "terminate"
4.  In the script, runs three "select statement".  Here are results.

User1's "select c1, c2, c3 from s1.t1" returns: 
C1          C2          C3
----------- ----------- -----------
          1           2           3
          4           5           6
          7           8           9
   * user1 can read all columns in table s1.t1.

User2's "select c1, c2, c3 from s1.t1" returns an error: 
SQL20264N  For table "S1.T1", authorization ID "USER2" does not have "READ"
access to the column "C3".  SQLSTATE=42512
   * user2 cannot read column c3, which is restricted by "secured with level1"

User2's "select c1, c2 from s1.t1" returns: 
C1          C2
----------- -----------
          1           2
          4           5
          7           8
   * user2 can read c1 and c2 column only.
   

Note:
For more samples, see directory:
UNIX directory: sqllib/samples/clp. Windows directory: sqllib\samples\clp.
 
Or click the link lbac.db2 at:
Command line processor (CLP) samples
https://www.ibm.com/docs/en/db2/11.5?topic=api-command-line-processor-clp-samples
---
lbac.db2    How to take advantage of Db2 LBAC (Label-Based Access Control) feature.
---
 

[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[{"code":"a8m500000008PkuAAE","label":"Security and Plug-Ins"}],"ARM Case Number":"TS006253853","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]

Document Information

Modified date:
26 July 2021

UID

ibm16475233

Page Feedback