ITPub博客

首页 > Linux操作系统 > Linux操作系统 > DB2_基于标签的访问控制LBAC

DB2_基于标签的访问控制LBAC

原创 Linux操作系统 作者:redhouser 时间:2011-07-14 17:19:09 0 删除 编辑

目的:
测试DB2基于标签的访问控制LBAC (Label Based Access Control),本脚本摘录自DB2安装目录admin_scripts/lbac.db2。
版本:Windows DB2 Express-C V9.7

操作系统用户:
secadm   with password "secadm1234"
joe      with password "joe1234"
bob      with password "bob1234"
pat      with password "pat1234"   


操作步骤:
使用"db2cmd db2 -t"进入交互模式,执行后续操作。

1,secadm用户准备
-- Grant SECADM authority to a user secadm
-- An user with SYSADM authority can grant SECADM authority to a user
CONNECT TO sample;
GRANT SECADM ON DATABASE TO USER secadm;

-- Connect as the user with SECADM authority
CONNECT TO sample USER secadm USING secadm1234;

2,创建标签
-- First, a user with SECADM authority creates the security label components
-- that will be part of the security policy. This sample uses three security
-- label components: level, departments, and groups.

--  The level component is of type ARRAY and has these elements:
-- 
--      TOP SECRET        (Highest)
--      SECRET
--      CONFIDENTIAL
--      UNCLASSIFIED      (Lowest)
--
--  This statement creates the level component:

CREATE SECURITY LABEL COMPONENT level ARRAY ['TOP SECRET',
                                             'SECRET',
                                             'CONFIDENTIAL',
                                             'UNCLASSIFIED'];

--  The departments component is of type SET and has these elements:
-- 
--      ALPHA, SIGMA, and DELTA
--
--  This statement creates the departments component:

CREATE SECURITY LABEL COMPONENT departments SET {'ALPHA', 'DELTA', 'SIGMA'};

--  The groups component is of type TREE and has these elements:
-- 
--          G1  (ROOT)
--       +--+--+
--       |     |
--      G2     G3
--          +--+--+
--          |     |
--         G4     G5
--
--  This statement creates the groups component:

CREATE SECURITY LABEL COMPONENT groups
  TREE ('G1' ROOT,
        'G2' UNDER 'G1',
        'G3' UNDER 'G1',
        'G4' UNDER 'G3',
        'G5' UNDER 'G3');


3,创建安全策略secpolicy
--  Next, a user with SECADM authority executes this statement to
--  create a security policy named secpolicy that has the three
--  components previously created and uses the DB2LBACRULES rule set.

CREATE SECURITY POLICY secpolicy
  COMPONENTS level, departments, groups
  WITH DB2LBACRULES;


4,创建安全标签
--  Now the user with SECADM authority can execute the following
--  statements to create some security labels that are part of the
--  security policy secpolicy.

--  For the purposes of this example the security label names end with
--  a number that indicates the relative "strength" of the label.
--  In other words seclabel1 is blocked by seclabel2 and seclabel2 is
--  blocked by seclabel3 but not by seclabel1, etc. This is only to make
--  the example easier to follow.

CREATE SECURITY LABEL secpolicy.seclabel1
  COMPONENT level 'UNCLASSIFIED',
  COMPONENT departments 'ALPHA', 'DELTA',
  COMPONENT groups 'G4';

CREATE SECURITY LABEL secpolicy.seclabel2
  COMPONENT level 'CONFIDENTIAL',
  COMPONENT departments 'ALPHA', 'DELTA',
  COMPONENT groups 'G4';

CREATE SECURITY LABEL secpolicy.seclabel3
  COMPONENT level 'SECRET',
  COMPONENT departments 'ALPHA', 'DELTA',
  COMPONENT groups 'G4';

CREATE SECURITY LABEL secpolicy.seclabel4
  COMPONENT level 'TOP SECRET',
  COMPONENT departments 'ALPHA', 'DELTA',
  COMPONENT groups 'G4';


5,用户joe使用标签创建表
-- Granting seclabel2 to user joe in order to create a column
-- secured with seclabel2
GRANT SECURITY LABEL secpolicy.seclabel2 TO USER joe;

CONNECT TO sample USER joe USING joe1234;


--  The user with SECADM authority now creates a protected table
--  and attaches the security policy to the table.
--  *** The table includes a column named rowseclabel that will hold the security labels
--  protecting the rows.
--  *** It also has a column named payrank that is protected by the security label seclabel2.
--  The payrank column has a default value of 0.

CREATE TABLE joe.employee_lbac (
  empno     int,
  lastname  char(10),
  deptno    int,
  payrank   int SECURED WITH seclabel2 DEFAULT 0,
  rowseclabel DB2SECURITYLABEL
  )
  SECURITY POLICY secpolicy;


---------------------------
connect to sample;
select * from joe.employee_lbac;
-->SQL20264N  对于表 "JOE.EMPLOYEE_LBAC" 而言,授权标识 "MH" 对列 "PAYRANK"
不具有 "READ" 访问权。  SQLSTATE=42512

connect to sample user secadm using secadm1234;
GRANT SECURITY LABEL secpolicy.seclabel4 TO USER mh;

select * from joe.employee_lbac;
---------------------------

6,插入数据
CONNECT TO sample USER secadm USING secadm1234;

REVOKE SECURITY LABEL secpolicy.seclabel2 FROM USER joe;
GRANT SECURITY LABEL secpolicy.seclabel1 TO USER joe;


6.1插入记录empno=1
CONNECT TO sample USER joe USING joe1234;

INSERT INTO joe.employee_lbac (empno, lastname, deptno, payrank)
  VALUES (1, 'Smith', 11, 3);
-->SQL20264N  对于表 "JOE.EMPLOYEE_LBAC" 而言,授权标识 "JOE" 对列 "PAYRANK"
不具有 "WRITE" 访问权。  SQLSTATE=42512


-- The insert fails because the column 'payrank' is protected by the
-- security label seclabel2 and joe holds security label seclabel1. The security
-- label seclabel1 cannot read from or write to security label seclabel2.

-- Joe removes the column payrank from the INSERT statement. This time the insert
-- is successful because the column payrank has a default value and therefore it
-- is not necessary that an explicit value be given for it.

INSERT INTO joe.employee_lbac (empno, lastname, deptno) VALUES (1, 'Smith', 11);

-- Because no value is given for the column rowseclabel, the security label that
-- the user holds for writing is inserted by default. In the case of user joe
-- that is seclabel1.

-- The rows in table employee_lbac now look like this:
--    EMPNO   LASTNAME   DEPTNO   PAYRANK  ROWSECLABEL
--     1       Smith      11       0        UNCLASSIFIED:(ALPHA,DELTA):G4


6.2插入记录empno=2
-- Now the user with SECADM authority revokes seclabel1 from joe and grants
-- him seclabel2 instead, by executing these statements:
CONNECT TO sample USER secadm USING secadm1234;

REVOKE SECURITY LABEL secpolicy.seclabel1 FROM USER joe;
GRANT SECURITY LABEL secpolicy.seclabel2 TO USER joe;

-- Joe now holds the security label seclabel2 for both read and write access.
-- He no longer holds the security label seclabel1.

-- Joe inserts another row as he did before:
CONNECT TO sample USER joe USING joe1234;
INSERT INTO joe.employee_lbac (empno, lastname, deptno) VALUES (2, 'Haas', 11);


6.3插入记录empno=3
--insert and delete,ok!
INSERT INTO joe.employee_lbac (empno, lastname, deptno, payrank)
  VALUES (3, 'Smith', 11, 3);
delete from joe.employee_lbac where empno=3;


-- Select the rows in the table employee_lbac
SELECT empno,
       lastname,
       deptno,
       payrank, CAST(rowseclabel AS VARCHAR(30))
  AS rowseclabel FROM joe.employee_lbac;

-- The values in the rowseclabel column are shown in a character representation
-- of the internal format. This is the default. Joe wants to read them in
-- security label string format so he executes the select again, this time using
-- the SECLABEL_TO_CHAR built-in function to convert the security labels as below:
-- CAST to VARCHAR(30) is done on rowseclabel column only to make the output fit
-- the screen and is not otherwise required.

SELECT empno,
       lastname,
       deptno,  
       payrank, CAST(SECLABEL_TO_CHAR('SECPOLICY', rowseclabel) AS VARCHAR(30))
  AS rowseclabel FROM joe.employee_lbac;
-->
--EMPNO       LASTNAME   DEPTNO      PAYRANK     ROWSECLABEL
------------- ---------- ----------- ----------- ------------------------------
--          1 Smith               11           0 UNCLASSIFIED:(ALPHA,DELTA):G4
--          2 Haas                11           0 CONFIDENTIAL:(ALPHA,DELTA):G4


-- Joe wants the next row he writes to be protected by seclabel1 rather than the
-- security label he currently holds. He uses the SECLABEL_BY_NAME built-in
-- function to provide seclabel1 for insert.
INSERT INTO joe.employee_lbac (empno, lastname, deptno, rowseclabel)
  VALUES (3, 'Miller', 11, SECLABEL_BY_NAME('SECPOLICY', 'SECLABEL1'));

-- The statement does not give joe the results he wants(HE GOT SECLABEL2). When you provide an
-- explicit security label to protect a row you must be able to write to data
-- protected by that security label, otherwise it cannot be used. You cannot
-- insert a row that you would be unable to write to.
-- The reason that seclabel2 cannot write to seclabel1 is the rule DB2LBACWRITEARRAY.
-- That rule prevents writing to any security label that has an element for an ARRAY
-- type component that is different than the element for the same component in your
-- security label. The security label seclabel1 has a value of 'UNCLASSIFIED' for
-- the level component while seclabel2 has a value of 'CONFIDENTIAL' for that component.

-- What happens when you try to insert a security label that you cannot write to
-- depends on whether or not the security policy was created with the
-- RESTRICT NOT AUTHORIZED WRITE SECURITY LABEL option. If the option was used
-- then the statement fails and an error is returned. If it was not used then no
-- error is given but the provided security label is ignored and the user's
-- security label for write access is used instead.
-- The security policy secpolicy was created without
-- the RESTRICT NOT AUTHORIZED WRITE SECURITY LABEL option, so no error is given
-- and joe's current security label is used instead.

SELECT empno,
       lastname,
       deptno,
       payrank, CAST(SECLABEL_TO_CHAR('SECPOLICY', rowseclabel) AS VARCHAR(30))
  AS rowseclabel FROM joe.employee_lbac;
-->
--EMPNO       LASTNAME   DEPTNO      PAYRANK     ROWSECLABEL
------------- ---------- ----------- ----------- ------------------------------
--          1 Smith               11           0 UNCLASSIFIED:(ALPHA,DELTA):G4
--          2 Haas                11           0 CONFIDENTIAL:(ALPHA,DELTA):G4
--          3 Miller              11           0 CONFIDENTIAL:(ALPHA,DELTA):G4


6.3插入记录empno=4
-- A user with SECADM authority now grants joe an exemption to the part of
-- the DB2LBACWRITEARRAY rule that prevents writing to elements that are lower
-- than yours (the write-down portion):

CONNECT TO sample USER secadm USING secadm1234;
GRANT EXEMPTION ON RULE DB2LBACWRITEARRAY WRITEDOWN
  FOR secpolicy
  TO USER joe;

-- Joe again tries to insert a row protected by seclabel1:
CONNECT TO sample USER joe USING joe1234;

INSERT INTO joe.employee_lbac (empno, lastname, deptno, rowseclabel)
   VALUES (4, 'Barberra', 11, SECLABEL_BY_NAME('SECPOLICY', 'SECLABEL1'));

-- Select the rows in the table employee_lbac
SELECT empno,
       lastname,
       deptno,
       payrank, CAST(SECLABEL_TO_CHAR('SECPOLICY', rowseclabel) AS VARCHAR(30))
  AS rowseclabel FROM joe.employee_lbac;
-->
--EMPNO       LASTNAME   DEPTNO      PAYRANK     ROWSECLABEL
------------- ---------- ----------- ----------- -----------------------------
--          1 Smith               11           0 UNCLASSIFIED:(ALPHA,DELTA):G4
--          2 Haas                11           0 CONFIDENTIAL:(ALPHA,DELTA):G4
--          3 Miller              11           0 CONFIDENTIAL:(ALPHA,DELTA):G4
--          4 Barberra            11           0 UNCLASSIFIED:(ALPHA,DELTA):G4


6.5插入记录empno=5
-- The next row that joe needs to insert must be protected by a security label in
-- which level = UNCLASSIFIED, departments = ALPHA, and groups = G4. There is no
-- named security label with those values so joe must use the SECLABEL built-in
-- function. The SECLABEL function creates a security label based on a list of
-- element values.

INSERT INTO joe.employee_lbac (empno, lastname, deptno, rowseclabel)
   VALUES (5, 'Kubrick', 11, SECLABEL('SECPOLICY', 'UNCLASSIFIED:ALPHA:G4'));

-- Joe is able to write to data protected by a security label with the
-- values 'UNCLASSIFIED:ALPHA:G4' so the insert takes place and the
-- supplied security label is used.

SELECT empno,
       lastname,
       deptno,
       payrank, CAST(SECLABEL_TO_CHAR('SECPOLICY', rowseclabel) AS VARCHAR(30))
  AS rowseclabel FROM joe.employee_lbac;
--EMPNO       LASTNAME   DEPTNO      PAYRANK     ROWSECLABEL
------------- ---------- ----------- ----------- ------------------------------
--          1 Smith               11           0 UNCLASSIFIED:(ALPHA,DELTA):G4
--          2 Haas                11           0 CONFIDENTIAL:(ALPHA,DELTA):G4
--          3 Miller              11           0 CONFIDENTIAL:(ALPHA,DELTA):G4
--          4 Barberra            11           0 UNCLASSIFIED:(ALPHA,DELTA):G4
--          5 Kubrick             11           0 UNCLASSIFIED:ALPHA:G4


6.6插入记录empno=6
-- Joe must now insert a row that includes a payrank of 2 and is to be protected
-- by seclabel2. Joe currently holds security label seclabel2 for write access.
-- This means that he is able to write to the column payrank, which is protected
-- by seclabel2. It also means that when he does not explicitly provide a security
-- label for the column rowseclabel, the security label seclabel2 will be used.
-- Joe executes this insert statement, which executes without error:

INSERT INTO joe.employee_lbac (empno, lastname, deptno, payrank)
   VALUES (6, 'Little', 11, 2);


SELECT empno,
       lastname,
       deptno,
       payrank, CAST(SECLABEL_TO_CHAR('SECPOLICY', rowseclabel) AS VARCHAR(30))
  AS rowseclabel FROM joe.employee_lbac;
--EMPNO       LASTNAME   DEPTNO      PAYRANK     ROWSECLABEL
------------- ---------- ----------- ----------- ------------------------------
--          1 Smith               11           0 UNCLASSIFIED:(ALPHA,DELTA):G4
--          2 Haas                11           0 CONFIDENTIAL:(ALPHA,DELTA):G4
--          3 Miller              11           0 CONFIDENTIAL:(ALPHA,DELTA):G4
--          4 Barberra            11           0 UNCLASSIFIED:(ALPHA,DELTA):G4
--          5 Kubrick             11           0 UNCLASSIFIED:ALPHA:G4
--          6 Little              11           2 CONFIDENTIAL:(ALPHA,DELTA):G4


6.7插入记录empno=7
-- Joe has one last row to insert. This one must be protected by seclabel4 and
-- must have a payrank of 5. Security labels seclabel2 and seclabel4 both have
-- the same values for the departments and groups components. They are only
-- different in the level component. So, if Joe is granted an exemption to the
-- rule DB2WRITEARRAY for write-up he will be able to write to data protected
-- by seclabel4 and will therefore be able to insert seclabel4 into the column.

--可以插入,但ROWSECLABEL为SECLABEL2而不是SECLABEL4。
INSERT INTO joe.employee_lbac (empno, lastname, deptno, payrank, rowseclabel)
   VALUES (7, 'Addams', 22, 5, SECLABEL_BY_NAME('SECPOLICY', 'SECLABEL4'));
delete from joe.employee_lbac where empno=7;


-- Note: Granting an exemption is a somewhat drastic solution to this problem
-- and is only being done here for demonstration purposes.

-- A user with SECADM authority grants joe an exemption to the write-up portion
-- of the DB2WRITEARRAY rule by executing this statement:

CONNECT TO sample USER secadm USING secadm1234;
GRANT EXEMPTION ON RULE DB2LBACWRITEARRAY WRITEUP
  FOR secpolicy
  TO USER joe;

-- Joe now does an insert with security label seclabel4
CONNECT TO sample USER joe USING joe1234;
INSERT INTO joe.employee_lbac (empno, lastname, deptno, payrank, rowseclabel)
   VALUES (7, 'Addams', 22, 5, SECLABEL_BY_NAME('SECPOLICY', 'SECLABEL4'));


SELECT empno,
       lastname,
       deptno,
       payrank, CAST(SECLABEL_TO_CHAR('SECPOLICY', rowseclabel) AS VARCHAR(30))
  AS rowseclabel FROM joe.employee_lbac;
--EMPNO       LASTNAME   DEPTNO      PAYRANK     ROWSECLABEL
------------- ---------- ----------- ----------- ------------------------------
--          1 Smith               11           0 UNCLASSIFIED:(ALPHA,DELTA):G4
--          2 Haas                11           0 CONFIDENTIAL:(ALPHA,DELTA):G4
--          3 Miller              11           0 CONFIDENTIAL:(ALPHA,DELTA):G4
--          4 Barberra            11           0 UNCLASSIFIED:(ALPHA,DELTA):G4
--          5 Kubrick             11           0 UNCLASSIFIED:ALPHA:G4
--          6 Little              11           2 CONFIDENTIAL:(ALPHA,DELTA):G4
--看不到empno=7的记录


-- The rows in table employee_lbac now look like this:
-- EMPNO       LASTNAME   DEPTNO      PAYRANK     ROWSECLABEL
-- ----------- ---------- ----------- ----------- ------------------------------
--           1 Smith               11           0 UNCLASSIFIED:(ALPHA,DELTA):G4
--           2 Haas                11           0 CONFIDENTIAL:(ALPHA,DELTA):G4
--           3 Miller              11           0 CONFIDENTIAL:(ALPHA,DELTA):G4
--           4 Barberra            11           0 UNCLASSIFIED:(ALPHA,DELTA):G4
--           5 Kubrick             11           0 UNCLASSIFIED:ALPHA:G4
--           6 Little              11           2 CONFIDENTIAL:(ALPHA,DELTA):G4
--           7 Addams              22           5 TOP SECRET:(ALPHA,DELTA):G4

7,读受保护的表
SELECT COUNT(*) AS count FROM joe.employee_lbac;
-->6 not 7

-- Select the rows in the table employee_lbac
SELECT empno,
       lastname,
       deptno,
       payrank, CAST(SECLABEL_TO_CHAR('SECPOLICY', rowseclabel) AS VARCHAR(30))
  AS rowseclabel FROM joe.employee_lbac;
-->6 rows selected

-- The reason is that joe holds only seclabel2 for read access and has been granted
-- no exemptions to the rules for read access. He cannot read the last row because
-- his LBAC credentials prevent it.

-- To allow joe to view the entire table, a user with SECADM authority revokes
-- seclabel2 from joe and grants seclabel4 to him:

CONNECT TO sample USER secadm USING secadm1234;
REVOKE SECURITY LABEL secpolicy.seclabel2 FROM USER joe;
GRANT SECURITY LABEL secpolicy.seclabel4 TO USER joe;

-- Select the rows in the table employee_lbac
CONNECT TO sample USER joe USING joe1234;
SELECT empno,
       lastname,
       deptno,
       payrank, CAST(SECLABEL_TO_CHAR('SECPOLICY', rowseclabel) AS VARCHAR(30))
  AS rowseclabel FROM joe.employee_lbac;
-->7 rows selected

-- Joe is now done with the inserts so the user with SECADM authority revokes
-- all of joe's exemptions and sets his security label back to seclabel1 with
-- these statements:
CONNECT TO sample USER secadm USING secadm1234;
REVOKE EXEMPTION ON RULE ALL FOR secpolicy FROM USER joe;
REVOKE SECURITY LABEL secpolicy.seclabel4 FROM USER joe;
GRANT SECURITY LABEL secpolicy.seclabel1 TO USER joe;

-- Joe now tries to count the rows again. This time the count is 3 because with
-- a security label of seclabel1 he is only able to read 3 of the rows.
CONNECT TO sample USER joe USING joe1234;
SELECT COUNT(*) AS count FROM joe.employee_lbac;
-->3

-- Joe tries to view the rows with this statement but because the
-- asterisk (*) includes the column payrank in the select the
-- statement fails. Joe no longer has read access to the column payrank.

SELECT * FROM joe.employee_lbac;
-->SQL20264N  对于表 "JOE.EMPLOYEE_LBAC" 而言,授权标识 "JOE" 对列 "PAYRANK"
--不具有 "READ" 访问权。  SQLSTATE=42512

-- Joe changes the statement to exclude the payrank column and also to convert
-- the security labels to a security label string format, then executes it:
SELECT empno,
       lastname,
       deptno,
       CAST(SECLABEL_TO_CHAR('SECPOLICY', rowseclabel) AS VARCHAR(30)) AS rowseclabel
  FROM joe.employee_lbac;
--EMPNO       LASTNAME   DEPTNO      ROWSECLABEL
------------- ---------- ----------- ------------------------------
--          1 Smith               11 UNCLASSIFIED:(ALPHA,DELTA):G4
--          4 Barberra            11 UNCLASSIFIED:(ALPHA,DELTA):G4
--          5 Kubrick             11 UNCLASSIFIED:ALPHA:G4


8,更新受保护的表
-- These rows are in table employee_lbac:
-- EMPNO       LASTNAME   DEPTNO      PAYRANK     ROWSECLABEL
-- ----------- ---------- ----------- ----------- ------------------------------
--           1 Smith               11           0 UNCLASSIFIED:(ALPHA,DELTA):G4
--           2 Haas                11           0 CONFIDENTIAL:(ALPHA,DELTA):G4
--           3 Miller              11           0 CONFIDENTIAL:(ALPHA,DELTA):G4
--           4 Barberra            11           0 UNCLASSIFIED:(ALPHA,DELTA):G4
--           5 Kubrick             11           0 UNCLASSIFIED:ALPHA:G4
--           6 Little              11           2 CONFIDENTIAL:(ALPHA,DELTA):G4
--           7 Addams              22           5 TOP SECRET:(ALPHA,DELTA):G4

-- User bob needs to make some updates to the employee_lbac table. A user
-- with SECADM authority grants him security label seclabel1 with this statement:
CONNECT TO sample USER secadm USING secadm1234;
GRANT SECURITY LABEL secpolicy.seclabel1 TO USER bob;

-- Grant select, insert, update, delete privileges on employee_lbac
-- table to bob, pat. This can be done by joe using the below statement
CONNECT TO sample user joe USING joe1234;
GRANT INSERT, SELECT, UPDATE, DELETE ON TABLE joe.employee_lbac TO USER bob, pat;

-- Bob issues the following update statement:
CONNECT TO sample USER bob USING bob1234;
UPDATE joe.employee_lbac SET deptno = 0;

-- The update executes without error but rows to which bob does not have
-- read access are not affected. Also, because the update does not explicitly
-- set the column rowseclabel it is automatically set to the security label
-- that bob holds for write access (seclabel1).

-- After the statement, the rows in the table look like this:
-- EMPNO       LASTNAME   DEPTNO      PAYRANK     ROWSECLABEL
-- ----------- ---------- ----------- ----------- ------------------------------
--           1 Smith                0           0 UNCLASSIFIED:(ALPHA,DELTA):G4 *updated
--           2 Haas                11           0 CONFIDENTIAL:(ALPHA,DELTA):G4
--           3 Miller              11           0 CONFIDENTIAL:(ALPHA,DELTA):G4
--           4 Barberra             0           0 UNCLASSIFIED:(ALPHA,DELTA):G4 *updated
--           5 Kubrick              0           0 UNCLASSIFIED:ALPHA:G4         *updated
--           6 Little              11           2 CONFIDENTIAL:(ALPHA,DELTA):G4
--           7 Addams              22           5 TOP SECRET:(ALPHA,DELTA):G4

-- Now bob tries to change all payranks greater than 0 to 1. He executes this
-- statement but the statement fails because he does not have write access or
-- read access to the column payrank:

UPDATE joe.employee_lbac SET payrank = 1 WHERE payrank > 0;
--SQL20264N  对于表 "JOE.EMPLOYEE_LBAC" 而言,授权标识 "BOB" 对列 "PAYRANK"
--不具有 "READ" 访问权。  SQLSTATE=42512

CONNECT TO sample USER secadm USING secadm1234;
REVOKE SECURITY LABEL secpolicy.seclabel1 FROM USER bob;
GRANT SECURITY LABEL secpolicy.seclabel3 TO USER bob;

CONNECT TO sample USER bob USING bob1234;
UPDATE joe.employee_lbac SET payrank = 1 WHERE payrank > 0;
--SQL20264N  对于表 "JOE.EMPLOYEE_LBAC" 而言,授权标识 "BOB" 对列 "PAYRANK"
--不具有 "WRITE" 访问权。  SQLSTATE=42512

-- Bob tries the update again. This time it fails because seclabel3, which bob
-- holds for write access, has a value of 'SECRET' for the component level
-- and the security label protecting the row has a value of 'CONFIDENTIAL' for
-- that component. Writing to the row would violate the write-down part of the
-- DB2LBACWRITEARRAY rule. To allow bob to make the update, a user with SECADM
-- could either grant the security label seclabel2 to him or grant him an exemption
-- on the write-down portion of the DB2LBACWRITEARRAY rule. Granting a new security
-- label is by far the safest way to grant access, but for demonstration purposes
-- assume the user with SECADM authority grants the exemption:
CONNECT TO sample USER secadm USING secadm1234;
GRANT EXEMPTION ON RULE DB2LBACWRITEARRAY WRITEDOWN
  FOR secpolicy
  TO USER bob;

-- Bob executes the update again. This time it executes with no error because bob has
-- both read and write access to the column payrank and also to the row. The update
-- does not affect the row where empno = 7, however because bob is not able to read that
-- row. Also, the security label protecting the updated row is changed to the security
-- label that bob holds for write access, namely seclabel3.

CONNECT TO sample USER bob USING bob1234;
UPDATE joe.employee_lbac SET payrank = 1 WHERE payrank > 0;

-- After the statement, the rows in the table look like this:
-- EMPNO       LASTNAME   DEPTNO      PAYRANK     ROWSECLABEL
-- ----------- ---------- ----------- ----------- ------------------------------
--          1 Smith                0           0 UNCLASSIFIED:(ALPHA,DELTA):G4
--          2 Haas                11           0 CONFIDENTIAL:(ALPHA,DELTA):G4
--          3 Miller              11           0 CONFIDENTIAL:(ALPHA,DELTA):G4
--          4 Barberra             0           0 UNCLASSIFIED:(ALPHA,DELTA):G4
--          5 Kubrick              0           0 UNCLASSIFIED:(ALPHA,DELTA):G4
--          6 Little              11           1 SECRET:(ALPHA,DELTA):G4
--          7 Addams              22           5 TOP SECRET:(ALPHA,DELTA):G4

SELECT empno,
       lastname,
       deptno,
       payrank, CAST(SECLABEL_TO_CHAR('SECPOLICY', rowseclabel) AS VARCHAR(30))
  AS rowseclabel FROM joe.employee_lbac;

-- Bob needs to change the row where empno = 6 so that it it protected by seclabel2
-- instead of seclabel3. Because he already holds an exemption to the write-down portion
-- of the DB2LBACWRITEARRAY rule he can write to seclabel2 and can therefore explicitly
-- use it in his update:

UPDATE joe.employee_lbac
  SET rowseclabel = SECLABEL_BY_NAME('SECPOLICY', 'SECLABEL2')
  WHERE empno = 6;

-- After the statement, the rows in the table look like this:
-- EMPNO       LASTNAME   DEPTNO      PAYRANK     ROWSECLABEL
-- ----------- ---------- ----------- ----------- ------------------------------
--          1 Smith                0           0 UNCLASSIFIED:(ALPHA,DELTA):G4
--          2 Haas                11           0 CONFIDENTIAL:(ALPHA,DELTA):G4
--          3 Miller              11           0 CONFIDENTIAL:(ALPHA,DELTA):G4
--          4 Barberra             0           0 UNCLASSIFIED:(ALPHA,DELTA):G4
--          5 Kubrick              0           0 UNCLASSIFIED:(ALPHA,DELTA):G4
--          6 Little              11           1 CONFIDENTIAL:(ALPHA,DELTA):G4 *updated
--          7 Addams              22           5 TOP SECRET:(ALPHA,DELTA):G4

-- Bob is finished with his updates. The user with SECADM authority revokes all
-- exemptions from him and also changes his security label back to seclabel1

CONNECT TO sample USER secadm USING secadm1234;
REVOKE EXEMPTION ON RULE ALL FOR secpolicy FROM USER bob;
REVOKE SECURITY LABEL secpolicy.seclabel3 FROM USER bob;
GRANT SECURITY LABEL secpolicy.seclabel1 TO USER bob;

9,删除受保护的表
-- User pat needs to delete some rows from the table employee_lbac. A user
-- with SECADM authority grants her security label seclabel1 with this statement:

GRANT SECURITY LABEL secpolicy.seclabel1 TO USER pat;

-- Pat issues the following SQL statement. It fails because she has neither
-- read access nor write access to the column payrank.

CONNECT TO sample USER pat USING pat1234;
DELETE FROM joe.employee_lbac WHERE EMPNO >= 1;
--SQL20264N  对于表 "JOE.EMPLOYEE_LBAC" 而言,授权标识 "PAT" 对列 "PAYRANK"
--不具有 "WRITE" 访问权。  SQLSTATE=42512

-- A user with SECADM authority grants security label seclabel2 to pat:
CONNECT TO sample USER secadm USING secadm1234;
REVOKE SECURITY LABEL secpolicy.seclabel1 FROM USER pat;
GRANT SECURITY LABEL secpolicy.seclabel2 TO USER pat;

-- Pat tries the delete again.
CONNECT TO sample USER pat USING pat1234;
DELETE FROM joe.employee_lbac WHERE EMPNO >= 1;
--SQL20402N  授权标识 "PAT" 没有 LBAC 凭证,无法对表 "JOE.EMPLOYEE_LBAC" 执行
--"DELETE" 操作。  SQLSTATE=42519

-- This time the delete gives an error because some of the rows selected for deletion
-- are protected by security labels that pat cannot write to. For example the row in
-- which empno = 1 is protected by the security label seclabel1. Pat is able to read
-- that row but is unable to write to it because that would violate the write-down
-- portion of the DB2LBACWRITEARRAY rule.
-- No rows are affected by the statement.

CONNECT TO sample USER secadm USING secadm1234;
GRANT EXEMPTION ON RULE DB2LBACWRITEARRAY WRITEUP
  FOR secpolicy
  TO USER pat;
GRANT EXEMPTION ON RULE DB2LBACWRITEARRAY WRITEDOWN
  FOR secpolicy
  TO USER pat;

-- Components of type ARRAY will have no effect when pat is writing.

-- Pat tries the delete again. This time it executes without error because pat is able
-- to write to all of the rows she is able to read. The rows that she is unable to
-- read, however, are unaffected by the delete:

CONNECT TO sample USER pat USING pat1234;
DELETE FROM joe.employee_lbac WHERE EMPNO >= 1;

-- After the statement, there is only one row in the table:
--    EMPNO   LASTNAME   DEPTNO   PAYRANK  ROWSECLABEL
--    7       Addams     22       5        TOP SECRET:(ALPHA,DELTA):G4

-- If pat executes a select on the table, however, she will see no rows because she
-- is unable to read the row that is there.
SELECT empno,
       lastname,
       deptno,
       payrank, CAST(SECLABEL_TO_CHAR('SECPOLICY', rowseclabel) AS VARCHAR(30))
  AS rowseclabel FROM joe.employee_lbac;
-- No rows are returned.

-- The user with SECADM authority revokes all exemptions from pat and
-- grants her security label seclabel1:
CONNECT TO sample USER secadm USING secadm1234;
REVOKE EXEMPTION ON RULE ALL FOR secpolicy FROM USER pat;
REVOKE SECURITY LABEL secpolicy.seclabel2 FROM USER pat;
GRANT SECURITY LABEL secpolicy.seclabel1 TO USER pat;


10,清理环境
-- Revoke the security labels from joe, bob and pat
REVOKE SECURITY LABEL secpolicy.seclabel1 FROM USER joe;
REVOKE SECURITY LABEL secpolicy.seclabel1 FROM USER bob;
REVOKE SECURITY LABEL secpolicy.seclabel1 FROM USER pat;

-- Drop the protected table 'employee_lbac'
CONNECT TO sample USER joe USING joe1234;
DROP TABLE joe.employee_lbac;

-- Drop the security labels created
CONNECT TO sample USER secadm USING secadm1234;
DROP SECURITY LABEL secpolicy.seclabel1;
DROP SECURITY LABEL secpolicy.seclabel2;
DROP SECURITY LABEL secpolicy.seclabel3;
DROP SECURITY LABEL secpolicy.seclabel4;

-- Drop the security policy 'secpolicy'
DROP SECURITY POLICY secpolicy;

-- Drop the security label components
DROP SECURITY LABEL COMPONENT level;
DROP SECURITY LABEL COMPONENT departments;
DROP SECURITY LABEL COMPONENT groups;

-- Disconnect from the database
CONNECT RESET;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18922393/viewspace-702216/,如需转载,请注明出处,否则将追究法律责任。

上一篇: DB2_使用大表空间
下一篇: DB2_状态监视
请登录后发表评论 登录
全部评论

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    810031