基于标签的访问控制(LBAC - Label-based access control)是在数据的行和列级别上工作的。表由安全策略保护，单个的行和列则由安全标签保护。数据库安全管理员（DBSECADM）创建安全策略和标签，并将标签授予用户。当具有安全标签的用户向由等效安全策略保护的表写入数据行时，将在该表的每个行生成数据标签。我们可以应用LABC来控制谁对数据的各个行和列具有访问权限和修改权限。
- 标签被应用到数据列上
— 防止未经授权的用户看到这些数据列 - 标签被应用到数据行上
— 防止未经授权的用户看到这些敏感的数据行
2. LBAC对象
用于支持 LBAC 实施的 SQL 对象是安全标签组件、安全策略和安全标签。这些对象用于保护表中的各个行和列：
-
安全标签组件(Security Label Components)：安全标签组件是LBAC安全策略的构建块。您可以使用这些组件来形成安全策略，这些策略与安全标签相结合，表示不同的用户访问权限。您可以创建各种安全标签组件，以及灵活的构建安全策略和安全标签，使您能够灵活地设计组织的 LBAC 解决方案。
-
安全策略(Security Policy)：您将安全策略附加到要保护的表，以防止未经授权的访问。要创建安全策略，需要定义安全标签，以确定谁可以访问表的数据。根据组织的要求，您可以在系统上设置一个或多个安全策略。
-
安全标签(Security Label)：可以将安全标签与表中的一个或多个对象（数据标签）以及用户（用户标签）相关联。当用户尝试访问受 LBAC 保护的表对象时，系统将用户标签与数据标签进行比较，以确定用户是否可以访问。如果用户没有被授予任何标签，那么在大多数情况下访问都会被自动阻止。
仅具备 DBSECADM 权限的用户能够创建 LBAC SQL 对象并将它们应用到表或授予用户。DBSECADM 是SinoDB提供的一个功能强大的服务器级内置角色，并且由数据库系统管理员将该角色授予个体用户。DBSECADM数据库安全管理员职责如下：
- create、drop、alter、rename安全标签构件
- create、drop、alter、rename安全策略
- create、drop、alter、rename安全标签
- 添加安全策略到数据表，从数据表上去除安全策略
- 授予和收回安全标签
- 授予和收回豁免权
- 授予和收回setsessionauth权限
3. LBAC使用步骤
创建 LBAC 的主要步骤如下：
① 创建安全标签组件(Security Label Components)
② 创建安全策略(Security Policy)
③ 创建安全标签(Security Label)
④ 安全策略应用于数据表
⑤ 将安全标签授予用户
⑥ 插入数据
4. LBAC使用场景举例
4.1 测试准备
① 创建DBSECADM用户
$ dbaccess - -
> create user dbsecadm with password '123456' properties user daemon;
User created.
② 创建secdb数据库，授予dbsecadm用户 resource 与DBSECADM权限
$ dbaccess - -
> create database secdb in datadbs2 with log;
Database created.
> grant connect to dbsecadm;
Permission granted.
> grant resource to dbsecadm;
Permission granted.
> grant dbsecadm to dbsecadm;
DBSECADM granted.
③ 创建3个用户fjuser ，fzuser，xmuser，并授予secdb库connect权限
$ dbaccess secdb -
Database selected.
> create user fjuser with password '123456' properties user daemon;
User created.
> create user fzuser with password '123456' properties user daemon;
User created.
> create user xmuser with password '123456' properties user daemon;
User created.
> grant connect to fjuser,fzuser,xmuser;
Permission granted.
4.2 tree安全标签组件测试
场景一：
某公司下福建销售总部。福建总部进一步划分为由各个区域主管领导的区域，如福州、厦门等。这个层次结构进一步划分为更小的区域，最后一级是个体销售人员。
存储员工数据的首要要求是，员工能够看到属于自己或自己管辖的下属组织的数据，同时他们又不能看到属于其他同事的数据。例如，福建区总监能够看到属于所有区域的数据，而每个区域的主管仅能看到属于他们的区域的数据。在该层级结构的最末端，个体销售人员仅能看到自己的数据。
可以通过将安全标签授予组织中的用户，并且让位于层次结构的更高层的人员比位于更底层的人员占有更多标签。这样，授予福建区销售总监的标签多于授予任何区域主管的标签，授予区域主管的标签多于授予任何子区域经理的标签，而授予子区域经理的标签多于授予个体销售人员的标签。这种关系最好通过为销售部门定义一个 TREE 组件来实现。
① 创建 tree 模式安全标签组件 ，dbsecadm用户操作
$ dbaccess - -
> connect to 'secdb' user 'dbsecadm';
ENTER PASSWORD:
Disconnected.
Connected.
> create security label component accesstree1 tree('Fujian' ROOT, 'Fuzhou' under 'Fujian', 'Xiamen' under 'Fujian');
Security label component created.
注：fjuser可以看到全部数据，fzuser只能看到fuzhou区域的数据，xmuser 只能看到xiamen区域的数据
② 创建安全策略 ，dbsecadm用户操作
> create security policy access_emp components accesstree1;
Security policy created.
③ 创建安全标签，dbsecadm用户操作
> create security label access_emp.fujian component accesstree1 'Fujian';
Security label created.
> create security label access_emp.fuzhou component accesstree1 'Fuzhou';
Security label created.
> create security label access_emp.xiamen component accesstree1 'Xiamen';
Security label created.
④ 创建带有安全标签的数据表，dbsecadm用户操作
> connect to 'secdb' user 'dbsecadm';
ENTER PASSWORD:
Connected.
> create table fj_emp(SL IDSSECURITYLABEL, fempno INT NOT NULL, fname VARCHAR(30)) SECURITY POLICY access_emp;
Table created.
⑤ 将安全标签授予用户，dbsecadm用户操作
> connect to 'secdb' user 'dbsecadm';
ENTER PASSWORD:
Connected.
> grant security label access_emp.fujian to fjuser;
Security label granted.
> grant security label access_emp.fuzhou to fzuser;
Security label granted.
> grant security label access_emp.xiamen to xmuser;
Security label granted.
⑥ 插入数据，fjuser用户操作
> connect to 'secdb' user 'fjuser';
ENTER PASSWORD:
Disconnected.
Connected.
> insert into fj_emp values (seclabel_by_name('access_emp', 'fujian'),0,'董大');
1 row(s) inserted.
> insert into fj_emp values (seclabel_by_name('access_emp', 'fuzhou'), 1,'王明');
1 row(s) inserted.
> insert into fj_emp values (seclabel_by_name('access_emp', 'fuzhou'), 2,'陈天旺');
1 row(s) inserted.
> insert into fj_emp values (seclabel_by_name('access_emp', 'xiamen'), 3,'陈强');
1 row(s) inserted.
> insert into fj_emp values (seclabel_by_name('access_emp', 'xiamen'), 4,'刘喜');
1 row(s) inserted.
⑦ 测试拥有不同标签的用户的查询结果
- fjuser进行查询，结果如下：
> connect to 'secdb' user 'fjuser';
ENTER PASSWORD:
Disconnected.
Connected.
> select substr(seclabel_to_char('access_emp',sl),1,30)::char(10) as seclabel ,fname from fj_emp;
seclabel fname
Fuzhou 王明
Fuzhou 陈天旺
Fujian 董大
Xiamen 陈强
Xiamen 刘喜
5 row(s) retrieved.
- fzuser进行查询，结果如下：
> connect to 'secdb' user 'fzuser';
ENTER PASSWORD:
Disconnected.
Connected.
> select substr(seclabel_to_char('access_emp',sl),1,30)::char(10) as seclabel ,fname from fj_emp;
seclabel fname
Fuzhou 王明
Fuzhou 陈天旺
2 row(s) retrieved.
>
- xmuser进行查询，结果如下：
> connect to 'secdb' user 'xmuser';
ENTER PASSWORD:
Disconnected.
Connected.
> select substr(seclabel_to_char('access_emp',sl),1,30)::char(10) as seclabel ,fname from fj_emp;
seclabel fname
Xiamen 陈强
Xiamen 刘喜
2 row(s) retrieved.
>
- 使用xmuser插入数据，发现只有自己跟拥有更高标签权限的用户才能查到数据
> connect to 'secdb' user 'xmuser';
ENTER PASSWORD:
Disconnected.
Connected.
> insert into fj_emp(fempno, fname) values (7,'陆川');
1 row(s) inserted.
> select substr(seclabel_to_char('access_emp',sl),1,30)::char(10) as seclabel ,fname from fj_emp;
seclabel fname
Xiamen 陈强
Xiamen 刘喜
Xiamen 陆川
3 row(s) retrieved.
> connect to 'secdb' user 'fjuser';
ENTER PASSWORD:
Disconnected.
Connected.
> insert into fj_emp(fempno, fname) values (5,'陆川');
1 row(s) inserted.
> select substr(seclabel_to_char('access_emp',sl),1,30)::char(10) as seclabel ,fname from fj_emp;
seclabel fname
Fuzhou 王明
Fuzhou 陈天旺
Fujian 董大
Xiamen 陈强
Xiamen 刘喜
Xiamen 陆川
6 row(s) retrieved.
>
> connect to 'secdb' user 'fzuser';
ENTER PASSWORD:
Disconnected.
Connected.
> select substr(seclabel_to_char('access_emp',sl),1,30)::char(10) as seclabel ,fname from fj_emp;
-- fzuser查不到xmuser插入的数据
seclabel fname
Fuzhou 王明
Fuzhou 陈天旺
2 row(s) retrieved.
>
总结： 在创建tree结构的安全标签构件时定义了3种范围：1.fujian 2. fuzhou 3. xiamen
- tree结构的安全标签构件在定义需要指定root以及使用under表示从属关系，如上述例子中fujian为root节点，fuzhou和xiamen都为其子节点。root节点可以操作所有节点数据，子节点只能操作各自节点的数据。
4.3 array 安全标签组件测试
场景二：
根据文档的敏感级别将其分类，拥有某个敏感级别的用户能够读取敏感级别更低的文档。例如：销售文档可以根据敏感级别进行分类，比如 Secret、Confidential 和 public。福建销售总监被授予组件元素为 Secret 的标签。福州区域作为重点发展地区，被授予Confidential 的敏感级别，以让他们能够访问核心竞争信息。这种关系最好通过定义一个敏感级别 ARRAY 组件来实现。
① 使用dbsecadm用户，创建array安全标签组件，安全策略以及安全标签；将安全标签授予用户,并创建带有安全标签的数据表
> connect to 'secdb' user 'dbsecadm';
ENTER PASSWORD:
Connected.
> create security label component accesslevel1 array['secret','confidential','public'];
Security label component created.
> create security policy access_doc components accesslevel1;
Security policy created.
> create security label access_doc.secret component accesslevel1 'secret';
Security label created.
> create security label access_doc.confidential component accesslevel1 'confidential';
Security label created.
> create security label access_doc.public component accesslevel1 'public';
Security label created.
> grant security label access_doc.secret to fjuser;
Security label granted.z
> grant security label access_doc.confidential to fzuser;
Security label granted.
> grant security label access_doc.public to xmuser;
Security label granted.
> create table fj_doc(SL IDSSECURITYLABEL, fno INT NOT NULL, fdocname VARCHAR(30)) SECURITY POLICY access_doc;
Table created.
② 插入数据测试
-- fjuser插入数据
> connect to 'secdb' user 'fjuser';
ENTER PASSWORD:
Disconnected.
Connected.
> insert into fj_doc values (seclabel_by_name('access_doc', 'secret'), 1,'secretdoc1');
1 row(s) inserted.
> insert into fj_doc values (seclabel_by_name('access_doc', 'confidential'), 2,'confidentialdoc2');
8247: User does not have the LBAC credentials to perform INSERT on table (dbsecadm.fj_doc).
Error in line 1
Near character position 95
> insert into fj_doc values (seclabel_by_name('access_doc', 'public'), 3,'publicdoc3');
8247: User does not have the LBAC credentials to perform INSERT on table (dbsecadm.fj_doc).
Error in line 1
Near character position 83
-- fzuser插入数据
> connect to 'secdb' user 'fzuser';
ENTER PASSWORD:
Disconnected.
Connected.
> insert into fj_doc values (seclabel_by_name('access_doc', 'confidential'), 2,'confidentialdoc2');
1 row(s) inserted.
> insert into fj_doc values (seclabel_by_name('access_doc', 'public'), 3,'publicdoc3');
8247: User does not have the LBAC credentials to perform INSERT on table (dbsecadm.fj_doc).
Error in line 1
Near character position 83
>
-- xmuser插入数据
> connect to 'secdb' user 'xmuser';
ENTER PASSWORD:
Connected.
> insert into fj_doc values (seclabel_by_name('access_doc', 'public'), 3,'publicdoc3');
1 row(s) inserted.
> insert into fj_doc values (seclabel_by_name('access_doc', 'confidential'), 2,'confidentialdoc2');
8247: User does not have the LBAC credentials to perform INSERT on table (dbsecadm.fj_doc).
Error in line 1
Near character position 95
>
③ 查询数据测试
-- fjuser 查询数据
> connect to 'secdb' user 'fjuser';
ENTER PASSWORD:
Connected.
> select substr(seclabel_to_char('access_doc',sl),1,30)::char(10) as seclabel ,fdocname from fj_doc;
seclabel fdocname
secret secretdoc1
confidenti confidentialdoc2
public publicdoc3
3 row(s) retrieved.
-- fzuser 查询数据
> connect to 'secdb' user 'fzuser';
ENTER PASSWORD:
Connected.
> select substr(seclabel_to_char('access_doc',sl),1,30)::char(10) as seclabel ,fdocname from fj_doc;
seclabel fdocname
confidenti confidentialdoc2
public publicdoc3
2 row(s) retrieved.
-- xmuser 查询数据
> connect to 'secdb' user 'xmuser';
ENTER PASSWORD:
Connected.
> select substr(seclabel_to_char('access_doc',sl),1,30)::char(10) as seclabel ,fdocname from fj_doc;
seclabel fdocname
public publicdoc3
1 row(s) retrieved.
总结： 在创建 array 安全标签构件时，定义了三种范围，分别为：①public②confidential③secret
- 在insert时，三种范围是平行的，用户的安全标签与数据表的安全策略相同时才能插入成功。
- 在select时，③>②>①，即例子中xmuser用户安全标签为public，则xmuser用户只能查询出public的安全策略数据，而fjuser用户安全标签为secret,则fjuser用户可以查询出secret、confidential以及public的安全策略数据。
4.4 set 安全标签组件测试
场景三：
回顾场景一提到的销售部门架构，每个区域经理 能够看到自己区域的员工的记录，但不能看到其他区域的员工的记录。不过福建区域总监应该能够看到福建区域的所有员工的记录。因此，福建区域总监的标签包含所有区域。其他区域经理的标签仅将各自的区域作为 SET 组件的一部分。
① 使用dbsecadm用户，创建set安全标签组件，安全策略以及安全标签；将安全标签授予用户,并创建带有安全标签的数据表
> connect to 'secdb' user 'dbsecadm';
ENTER PASSWORD:
Connected.
> create security label component accessset1 set{'Fujian','Fuzhou', 'Xiamen'};
Security label component created.
> create security policy access_emp1 components accessset1;
Security policy created.
> create security label access_emp1.fujian component accessset1 'Fujian';
Security label created.
> create security label access_emp1.fuzhou component accessset1 'Fuzhou';
Security label created.
> create security label access_emp1.xiamen component accessset1 'Xiamen';
Security label created.
> grant security label access_emp1.fujian to fjuser;
Security label granted.
> grant security label access_emp1.fuzhou to fzuser;
Security label granted.
> grant security label access_emp1.xiamen to xmuser;
Security label granted.
> create table fj_emp1(SL IDSSECURITYLABEL, fempno INT NOT NULL, fname VARCHAR(30)) SECURITY POLICY access_emp1;
Table created.
② 插入数据测试
-- fjuser 插入数据
> connect to 'secdb' user 'fjuser';
ENTER PASSWORD:
Connected.
> insert into fj_emp1 values (seclabel_by_name('access_emp11', 'fujian'),0,'董大');
(U0001) - Policy not found
Error in line 1
Near character position 82
> insert into fj_emp1 values (seclabel_by_name('access_emp1', 'fujian'),0,'董大');
1 row(s) inserted.
> insert into fj_emp1 values (seclabel_by_name('access_emp1', 'fuzhou'), 1,'王明');
8247: User does not have the LBAC credentials to perform INSERT on table (dbsecadm.fj_emp1).
Error in line 1
Near character position 81
> insert into fj_emp1 values (seclabel_by_name('access_emp1', 'xiamen'), 3,'陈强');
8247: User does not have the LBAC credentials to perform INSERT on table (dbsecadm.fj_emp1).
Error in line 1
Near character position 81
>
-- fzuser 插入数据
> connect to 'secdb' user 'fzuser';
ENTER PASSWORD:
Connected.
> insert into fj_emp1 values (seclabel_by_name('access_emp1', 'fuzhou'), 1,'王明');
1 row(s) inserted.
> insert into fj_emp1 values (seclabel_by_name('access_emp1', 'fuzhou'), 2,'陈天旺');
1 row(s) inserted.
> insert into fj_emp1 values (seclabel_by_name('access_emp1', 'xiamen'), 3,'陈强');
8247: User does not have the LBAC credentials to perform INSERT on table (dbsecadm.fj_emp1).
Error in line 1
Near character position 81
> insert into fj_emp1 values (seclabel_by_name('access_emp1', 'fujian'),0,'董大');
8247: User does not have the LBAC credentials to perform INSERT on table (dbsecadm.fj_emp1).
Error in line 1
Near character position 81
>
-- xmuser插入数据
> connect to 'secdb' user 'xmuser';
ENTER PASSWORD:
Connected.
> insert into fj_emp1 values (seclabel_by_name('access_emp1', 'xiamen'), 3,'陈强');
1 row(s) inserted.
> insert into fj_emp1 values (seclabel_by_name('access_emp1', 'xiamen'), 4,'刘喜');
1 row(s) inserted.
> insert into fj_emp1 values (seclabel_by_name('access_emp1', 'fujian'),0,'董大');
8247: User does not have the LBAC credentials to perform INSERT on table (dbsecadm.fj_emp1).
Error in line 1
Near character position 80
> insert into fj_emp1 values (seclabel_by_name('access_emp1', 'fuzhou'), 1,'王明');
8247: User does not have the LBAC credentials to perform INSERT on table (dbsecadm.fj_emp1).
Error in line 1
Near character position 81
>
从insert语句执行结果发现，使用set标签构件， 用户的安全标签与数据表的安全策略相同 时才能插入成功。
③ 查询数据测试
-- fjuser 查询
> connect to 'secdb' user 'fjuser';
ENTER PASSWORD:
Connected.
> select substr(seclabel_to_char('access_emp1',sl),1,30)::char(10) as seclabel ,fname from fj_emp1;
seclabel fname
Fujian 董大
1 row(s) retrieved.
>
-- fzuser查询
> connect to 'secdb' user 'fzuser';
ENTER PASSWORD:
Connected.
> select substr(seclabel_to_char('access_emp1',sl),1,30)::char(10) as seclabel ,fname from fj_emp1;
seclabel fname
Fuzhou 王明
Fuzhou 陈天旺
2 row(s) retrieved.
>
-- xmuser查询
> connect to 'secdb' user 'xmuser';
ENTER PASSWORD:
Connected.
> select substr(seclabel_to_char('access_emp1',sl),1,30)::char(10) as seclabel ,fname from fj_emp1;
seclabel fname
Xiamen 陈强
Xiamen 刘喜
2 row(s) retrieved.
>
从查询结果发现，使用set标签构件，每个用户只能查询到各自安全标签的数据。
④ 调整安全策略设置，使得fjuser 可以看到所有数据，使用dbsecad用户操作
> connect to 'secdb' user 'dbsecadm';
ENTER PASSWORD:
Connected.
> revoke security label access_emp1.fujian from fjuser;
Security label revoked.
> create security label access_emp1.all component accessset1 'Fujian','Fuzhou','Xiamen';
Security label created.
> grant security label access_emp1.all to fjuser;
Security label granted.
>
此时进行查询，结果如下：
-- fjuser 查询
> connect to 'secdb' user 'fjuser';
ENTER PASSWORD:
Connected.
> select substr(seclabel_to_char('access_emp1',sl),1,30)::char(10) as seclabel ,fname from fj_emp1;
seclabel fname
Fujian 董大
Fuzhou 王明
Fuzhou 陈天旺
Xiamen 陈强
Xiamen 刘喜
5 row(s) retrieved.
-- fjuser 插入数据
> insert into fj_emp1 values (seclabel_by_name('access_emp1', 'fuzhou'), 5,'喵喵');
1 row(s) inserted.
> insert into fj_emp1 values (seclabel_by_name('access_emp1', 'xiamen'), 6,'旺旺');
1 row(s) inserted.
-- fjuser 查询
> select substr(seclabel_to_char('access_emp1',sl),1,30)::char(10) as seclabel ,fname from fj_emp1;
seclabel fname
Fujian 董大
Fuzhou 王明
Fuzhou 陈天旺
Xiamen 陈强
Xiamen 刘喜
Fuzhou 喵喵
Xiamen 旺旺
7 row(s) retrieved.
>
-- fzuser 查询
> connect to 'secdb' user 'fzuser';
ENTER PASSWORD:
Connected.
> select substr(seclabel_to_char('access_emp1',sl),1,30)::char(10) as seclabel ,fname from fj_emp1;
seclabel fname
Fuzhou 王明
Fuzhou 陈天旺
Fuzhou 喵喵
3 row(s) retrieved.
>
-- xmuser 查询
> connect to 'secdb' user 'xmuser';
ENTER PASSWORD:
Connected.
> select substr(seclabel_to_char('access_emp1',sl),1,30)::char(10) as seclabel ,fname from fj_emp1;
seclabel fname
Xiamen 陈强
Xiamen 刘喜
Xiamen 旺旺
3 row(s) retrieved.
>
总结: 在创建 set 安全标签构件时，定义了三种范围：在创建tree结构的安全标签构件时定义了3种范围：1.fujian 2. fuzhou 3. xiamen
- 用户只能查看和插入各自安全标签的数据，如上述示例中：fzuser只能查询和操作fuzhou的数据;fujian用户在调整安全标签前也只能查询fujian的数据。
- 如果想查看其他用户的数据，在安全标签中必须包含这些关系。如示例中：fujian用户如果想查看全部的数据，就必须调整安全标签，在安全标签中包含’Fujian’,‘Fuzhou’,‘Xiamen’。
4.5 列级安全标签测试
场景四：
有一张销量表fj_sales，存储每日福建区域以及下属福州、厦门区域的销量数据。要求福建销售总监能够查看全部数据，福州以及厦门区域经理只能看到各自区域的数据。这里仍然使用前面创建的tree安全标签进行测试。
① 创建包含列级安全策略的数据表，dbsecadm用户操作
> connect to 'secdb' user 'dbsecadm';
ENTER PASSWORD:
Connected.
> create table fj_sales(id int,fjsales money column secured with fujian,fzsales money column secured with fuzhou,xmsales money column secured with xiamen) security policy access_emp;
Table created.
>
② 插入数据，fjuser用户操作
> connect to 'secdb' user 'fjuser';
ENTER PASSWORD:
Connected.
> insert into fj_sales values(1,10,6,4);
1 row(s) inserted.
> insert into fj_sales values(2,20,13,7);
1 row(s) inserted.
> insert into fj_sales values(3,16,10,6);
1 row(s) inserted.
③ 查询测试
-- fjuser查询
> connect to 'secdb' user 'fjuser';
ENTER PASSWORD:
Connected.
> select * from fj_sales;
id fjsales fzsales xmsales
1 $10.00 $6.00 $4.00
2 $20.00 $13.00 $7.00
3 $16.00 $10.00 $6.00
3 row(s) retrieved.
-- fzuser查询
> connect to 'secdb' user 'fzuser';
ENTER PASSWORD:
Connected.
> select * from fj_sales;
8245: User cannot perform READ access to the protected column (fjsales).
Error in line 1
Near character position 21
> select id,fzsales from fj_sales;
id fzsales
1 $6.00
2 $13.00
3 $10.00
3 row(s) retrieved.
> select id,xmsales from fj_sales;
8245: User cannot perform READ access to the protected column (xmsales).
Error in line 1
Near character position 30
-- xmuser查询
> connect to 'secdb' user 'xmuser';
ENTER PASSWORD:
Connected.
> select * from fj_sales;
8245: User cannot perform READ access to the protected column (fjsales).
Error in line 1
Near character position 21
> select id,xmsales from fj_sales;
id xmsales
1 $4.00
2 $7.00
3 $6.00
3 row(s) retrieved.
总结：将安全标签应用于列时，用户只能查看具备安全标签权限的列的数据。此时如果使用select * 去查询数据就会报错：8245: User cannot perform READ access to the protected column (fjsales).