DB2_基於標籤的訪問控制LBAC
目的：
測試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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- AWS身份和訪問管理模組新增標籤和基於屬性的訪問控制能力
- Flutter 基於ChoiceChip的標籤選擇控制元件Flutter控制元件
- 基於PLM訪問控制模型研究模型
- 資料安全合規需要從基於角色的訪問控制邁向基於屬性的訪問控制
- 基於角色管理的系統訪問控制
- 服務端指南 | 基於角色的訪問控制服務端
- 服務端指南 | 基於資料的訪問控制服務端
- 基於角色的訪問控制RBAC是什麼？ - TailscaleAI
- Java JDK11基於巢狀的訪問控制JavaJDK巢狀
- 基於linux下的selinux強制訪問控制Linux
- .Net Core實戰之基於角色的訪問控制的設計
- 基於Bootstrap的標籤頁元件bootstrap-tabboot元件
- 關於標籤庫的問題，請求援助
- 基礎標籤
- 【解構雲原生】K8s 的 RBAC - 基於角色的訪問控制K8S
- 基於主體和物件的訪問控制–附：電腦保安的層次物件
- 指標和標籤的基礎理解指標
- 用定製標籤庫和配置檔案實現對JSP頁面元素的訪問控制 (轉)JS
- Spring Security 實戰乾貨：基於配置的介面角色訪問控制Spring
- ASP.NET Core 基於宣告的訪問控制到底是什麼鬼？ASP.NET
- 檔案和目錄的訪問控制(2)新增訪問控制
- HTML基礎-標籤HTML
- Spring Security 實戰乾貨：基於註解的介面角色訪問控制Spring
- Swift 中的訪問控制Swift
- 關於html的a標籤的target="__blank "的安全漏洞問題HTML
- Mongodb訪問控制MongoDB
- Flask——訪問控制Flask
- RabbitMQ訪問控制MQ
- Nginx訪問控制Nginx
- Swift 訪問控制Swift
- 關於開發jsp標籤的環境配置問題JS
- 一個很奇怪的問題：關於struts自定義標籤
- Spring Security實現基於RBAC的許可權表示式動態訪問控制Spring
- javascript快速入門6--Script標籤與訪問HTML頁面JavaScriptHTML
- 解決img標籤與其它標籤間隙問題？
- 基於Docker的MongoDB實現授權訪問DockerMongoDB
- HTML標籤(基本標籤的使用)HTML
- java <%!%>標籤和<%%>標籤的使用Java