Inceptor行级、列级权限控制实践（老版本）

  使用配置
内容纲要

概要描述

– 在很多数据敏感行业，需要更细粒度的权限控制，比较普遍的需求是 行级权限控制 和 列级权限控制。老版本的TDH 中 guardian 还未实现界面化配置如此细粒度的权限管控，但是 Inceptor 已经具备了更细粒度的权限控制。本案例针对 Inceptor 表做行和列级别的权限管控。

  • 如银行数据仓库，操作人员在查询带有敏感信息的表时，需要对敏感字段进行脱敏显示。

  • 如银行数据仓库，操作人员可能属于不同的支行，他们只应该看到其所属支行的数据。比较传统的做法是，将一张大表按支行切分成多个小表或者创建多个View，但是这种做法在表或者View的管理上相当繁琐，并且无法对 update, delete 操作做行级权限控制。

详细说明

  • 环境准备：TDH 6.0.1
    注意：
  • 行级权限控制 TDH 4.3 版本开始支持，列级权限控制 TDH 4.6 版本开始支持
  • 列级权限控制不可以通过admin角色下的租户实现，因为目前admin角色和admin权限是绑定的，也就是说即使租户A归属于admin角色，has_role(‘admin’)判断还是false，必须添加租户A的admin权限has_role(‘admin’)才能是true。这种情况我们一般建议避开admin角色，通过其他的角色来实现，比如下面例子中的testrole角色

列级权限控制

1. 数据准备

DROP TABLE bank_account;
CREATE TABLE bank_account (user_name string ,id_number BIGINT, email_address string)
CLUSTERED BY (user_name) INTO 1 BUCKETS  STORED AS ORC_TRANSACTION;

INSERT INTO bank_account  SELECT 'JACK',511702198907108849,'jack@126.com' FROM system.dual;
INSERT INTO bank_account  SELECT 'TOM',320702199203128764,'tom@outlook.com' FROM system.dual;
INSERT INTO bank_account  SELECT 'ALICE',530724198511061294,'alice@gmail.com' FROM system.dual;
INSERT INTO bank_account  SELECT 'AMY',440183198811183889,'amy@qq.com' FROM system.dual;

SELECT * FROM bank_account;

file

2. 设定列级权限控制

下面通过has_role()函数，设置角色级别的列级权限控制。

您也可以通过current_role()函数设置用户级的列级权限控制，语句如下：
grant permission on bank_account for column user_name case WHEN current_user()=’hive’ then user_name else mask(user_name, 1) end;
— 上述语句，会在执行SELECT * FROM bank_account;时，后台parsing会转化成select case WHEN current_user()=’hive’ then user_name else mask(user_name, 1) end user_name from default.bank_account;

--脱敏规则01：非testrole角色下的用户对user_name列，只显示第一个字符，其他隐藏为星号
> grant permission on bank_account for column user_name case when has_role('testrole') then user_name else mask(user_name, 1) end;
--脱敏规则02：非testrole角色下的用户对id_number列，只显示前三位和后四位，其他隐藏
> grant permission on bank_account for column id_number case when has_role('testrole') then id_number else mask(id_number, 3, 4) end;
--脱敏规则03：非testrole角色下的用户对email_address列，邮箱前缀只显示第一个字母，其他隐藏用星号代替，@符后面的地址显示
> grant permission on bank_account for column email_address case when has_role('testrole') then email_address else mask_email(email_address) end;

file

mask 函数的具体使用方法，参见 Inceptor 用户使用手册

3. 用户权限测试（testrole角色用户+非testrole角色用户）

testrole角色或者表的owner用户测试：
--kevin租户在guardian配置中归属于inceptor的testrole角色
root@kevin1 ~# beeline -u jdbc:hive2://172.22.23.1:10000/default --silent=true -n kevin -p 123456    
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/root/TDH-Client/hadoop/hadoop/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/root/TDH-Client/inceptor/lib/shiva-client-shade-1.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
2019-11-04 16:36:34,261 INFO jdbc.Utils: Supplied authorities: 172.22.23.1:10000
2019-11-04 16:36:34,262 INFO jdbc.Utils: Resolved authority: 172.22.23.1:10000
0: jdbc:hive2://172.22.23.1:10000/default> SELECT * FROM bank_account;
+------------+---------------------+------------------+
| user_name  |      id_number      |  email_address   |
+------------+---------------------+------------------+
| JACK       | 511702198907108849  | jack@126.com     |
| TOM        | 320702199203128764  | tom@outlook.com  |
| ALICE      | 530724198511061294  | alice@gmail.com  |
| AMY        | 440183198811183889  | amy@qq.com       |
+------------+---------------------+------------------+
非testrole角色或者表的owner用户测试：
--discover租户在guardian配置中不归属于inceptor的testrole角色
root@kevin1 ~# beeline -u jdbc:hive2://172.22.23.1:10000/default --silent=true -n discover -p 123456 
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/root/TDH-Client/hadoop/hadoop/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/root/TDH-Client/inceptor/lib/shiva-client-shade-1.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
2019-11-04 16:38:06,436 INFO jdbc.Utils: Supplied authorities: 172.22.23.1:10000
2019-11-04 16:38:06,436 INFO jdbc.Utils: Resolved authority: 172.22.23.1:10000
0: jdbc:hive2://172.22.23.1:10000/default>  SELECT * FROM bank_account;
+------------+---------------------+------------------+
| user_name  |      id_number      |  email_address   |
+------------+---------------------+------------------+
| J***       | 511***********8849  | j***@126.com     |
| T**        | 320***********8764  | t**@outlook.com  |
| A****      | 530***********1294  | a****@gmail.com  |
| A**        | 440***********3889  | a**@qq.com       |
+------------+---------------------+------------------+

行级权限控制

1. 数据准备

--交易表
DROP TABLE bank_bill;
CREATE TABLE bank_bill (id string ,fund int, sub_bank_id int)
CLUSTERED BY (id) INTO 1 BUCKETS  STORED AS ORC_TRANSACTION;

INSERT INTO bank_bill  SELECT '01',100,1 FROM system.dual;
INSERT INTO bank_bill  SELECT '02',200,2 FROM system.dual;
INSERT INTO bank_bill  SELECT '03',150,1 FROM system.dual;
INSERT INTO bank_bill  SELECT '04',200,2 FROM system.dual;

SELECT * FROM bank_bill;

--用户表
DROP TABLE bank_user;
CREATE TABLE bank_user (user_id string ,sub_bank_id int)
CLUSTERED BY (user_id) INTO 1 BUCKETS  STORED AS ORC_TRANSACTION;

INSERT INTO bank_user  SELECT 'jack',1 FROM system.dual;
INSERT INTO bank_user  SELECT 'tom',2 FROM system.dual;

SELECT * FROM bank_user;

2. 设定行级权限控制

--提交SQL的用户只能够看到其所在支行（sub_bank_id）的交易记录，另外，管理员也能够看到表的所有数据。
GRANT PERMISSION ON TABLE bank_bill for rows
      where sub_bank_id = (select sub_bank_id from bank_user where user_id = current_user()) or has_role('ADMIN');

3. 用户权限测试（jack+tom+admin角色用户）

以jack用户登陆，查询交易表 bank_bill
--jack租户在guardian配置中不归属于inceptor的admin角色
root@kevin1 ~# beeline -u jdbc:hive2://172.22.23.1:10000/default --silent=true -n jack -p 123456    
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/root/TDH-Client/hadoop/hadoop/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/root/TDH-Client/inceptor/lib/shiva-client-shade-1.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
2019-11-04 16:51:54,179 INFO jdbc.Utils: Supplied authorities: 172.22.23.1:10000
2019-11-04 16:51:54,180 INFO jdbc.Utils: Resolved authority: 172.22.23.1:10000
0: jdbc:hive2://172.22.23.1:10000/default> select * from bank_bill;
+-----+-------+--------------+
| id  | fund  | sub_bank_id  |
+-----+-------+--------------+
| 01  | 100   | 1            |
| 03  | 150   | 1            |
+-----+-------+--------------+
以tom用户登陆，查询交易表 bank_bill
--tom租户在guardian配置中不归属于inceptor的admin角色
root@kevin1 ~# beeline -u jdbc:hive2://172.22.23.1:10000/default --silent=true -n tom -p 123456         
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/root/TDH-Client/hadoop/hadoop/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/root/TDH-Client/inceptor/lib/shiva-client-shade-1.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
2019-11-04 16:48:11,605 INFO jdbc.Utils: Supplied authorities: 172.22.23.1:10000
2019-11-04 16:48:11,606 INFO jdbc.Utils: Resolved authority: 172.22.23.1:10000
0: jdbc:hive2://172.22.23.1:10000/default>       select * from bank_bill;
+-----+-------+--------------+
| id  | fund  | sub_bank_id  |
+-----+-------+--------------+
| 02  | 200   | 2            |
| 04  | 200   | 2            |
+-----+-------+--------------+
以admin角色的租户登陆，查询交易表 bank_bill
--hive租户在guardian配置中归属于inceptor的admin角色
root@kevin1 ~# beeline -u jdbc:hive2://172.22.23.1:10000/default --silent=true -n hive -p 123456      
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/root/TDH-Client/hadoop/hadoop/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/root/TDH-Client/inceptor/lib/shiva-client-shade-1.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
2019-11-04 16:52:39,401 INFO jdbc.Utils: Supplied authorities: 172.22.23.1:10000
2019-11-04 16:52:39,402 INFO jdbc.Utils: Resolved authority: 172.22.23.1:10000
0: jdbc:hive2://172.22.23.1:10000/default> select * from bank_bill;
+-----+-------+--------------+
| id  | fund  | sub_bank_id  |
+-----+-------+--------------+
| 01  | 100   | 1            |
| 02  | 200   | 2            |
| 03  | 150   | 1            |
| 04  | 200   | 2            |
+-----+-------+--------------+

FAQ1:

1. 如何获取所有配置了列级权限的表
SELECT
    col.INTEGER_IDX AS column_id,
    col.COLUMN_NAME AS column_name,
    col.TYPE_NAME AS column_type,
    DBS.NAME AS database_name,
    TBLS.TBL_NAME AS table_name,
    col.COMMENT AS commentString,
    col.DEFAULT_VAL AS default_value,
    col.NOTNULL_CONSTRAINT AS nullable,
    col.UNIQUE_CONSTRAINT AS unique_constraint,
    col.PERMISSION
FROM COLUMNS_V2@system_dblink col
JOIN SDS@system_dblink ON col.CD_ID = SDS.CD_ID
JOIN TBLS@system_dblink ON SDS.SD_ID = TBLS.SD_ID
JOIN DBS@system_dblink ON TBLS.DB_ID = DBS.DB_ID
WHERE col.PERMISSION IS NOT NULL;

--或者可以通过下面的方式获取表信息
select * from TBLS@system_dblink WHERE cls_enabled=1;

file

2. 查看某张表设置的列级权限配置
show permission on table bank_account;

file

3. 如何获取所有配置了行级权限的表
select db_id,tbl_name,row_permission from TBLS@system_dblink WHERE row_permission is not null;

file

4. 查看某张表设置的行级权限配置
show permission on table bank_bill;

file

这篇文章对您有帮助吗？

平均评分 5 / 5. 次数: 2

尚无评价，您可以第一个评哦！

非常抱歉，这篇文章对您没有帮助.

烦请您告诉我们您的建议与意见，以便我们改进，谢谢您。