背景： 1、Apache Sentry是Cloudera公司发布的一个Hadoop开源组件，它提供了细粒度级、基于角色的授权以及多租户的管理模式， 2、Sentry当前可以和Hive/Hcatalog、Apache Solr 和Cloudera Impala集成， 为这些组件提供权限管理服务。 3、基于角色的管理（role-based acess control）通过创建角色，将每个组件的权限授予给此角色，然后在用户(组)中添加此角色，用户便具备此角色访问组件的权限， 4、使用sentry对hive进行权限管理时，这里的组件可以是整个server，也可以是单个db,或者单张table.
测试如下： 1.1 查看全部数据库 beeline -u "jdbc:hive2://uhadoop-4wvgxxla-master2:10000" -n hive -e "show databases;"
我尝试的先创建一个库 报错如下： [hadoop@uhadoop-4wvgxxla-master2 ~]$ beeline -u "jdbc:hive2://uhadoop-4wvgxxla-master2:10000" -n hive -e "create database test;" Transaction isolation: TRANSACTION_REPEATABLE_READ Error: Error while compiling statement: FAILED: SemanticException No valid privileges User hive does not have privileges for CREATEDATABASE 用户配置单元没有CREATEDATABASE的特权 The required privileges: Server=uhadoop-4wvgxxla-master1->action=create->grantOption=false; (state=42000,code=40000) Closing: 0: jdbc:hive2://uhadoop-4wvgxxla-master2:10000
1.2 查看全部角色 beeline -u "jdbc:hive2://uhadoop-4wvgxxla-master2:10000" -n hive -e "show roles;" Transaction isolation: TRANSACTION_REPEATABLE_READ +-------+ | role | +-------+ +-------+ No rows selected (1.151 seconds) Beeline version 2.3.3 by Apache Hive Closing: 0: jdbc:hive2://uhadoop-4wvgxxla-master2:10000 // 用户角色为空
1.3 查看当前角色 beeline -u "jdbc:hive2://uhadoop-4wvgxxla-master2:10000" -n hive -e "show current roles;" Driver: Hive JDBC (version 2.3.3) Transaction isolation: TRANSACTION_REPEATABLE_READ +-------+ | role | +-------+ +-------+ No rows selected (0.446 seconds) Beeline version 2.3.3 by Apache Hive Closing: 0: jdbc:hive2://uhadoop-4wvgxxla-master2:10000 // 显示当前没有任何的角色
1.4 查看当前用户 beeline -u "jdbc:hive2://uhadoop-4wvgxxla-master2:10000" -n hive -e "select current_user();" Driver: Hive JDBC (version 2.3.3) Transaction isolation: TRANSACTION_REPEATABLE_READ +-------+ | _c0 | +-------+ | hive | +-------+ 1 row selected (1.124 seconds) Beeline version 2.3.3 by Apache Hive Closing: 0: jdbc:hive2://uhadoop-4wvgxxla-master2:10000
// 当前操作hiveserver2的用户是hive
- hive用户授予管理员权限 2.1 创建管理员角色admin beeline -u "jdbc:hive2://uhadoop-4wvgxxla-master2:10000" -n hive -e "CREATE ROLE admin;"
2.2 为admin角色授予全部server权限
beeline -u "jdbc:hive2://uhadoop-4wvgxxla-master2:10000" -n hive // 进入到hiveserver2的内部之后执行如下：
0: jdbc:hive2://uhadoop-4wvgxxla-master2:1000> grant all on server uhadoop-4wvgxxla-master1 to role admin;
No rows affected (0.491 seconds)
2.3 为hive用户赋予admin角色 //经过这一步，hive用户已经可以作为管理员用户执行全部数据和权限操作。 beeline -u "jdbc:hive2://uhadoop-4wvgxxla-master2:10000" -n hive -e "GRANT ROLE admin TO GROUP hive;"
create table student( Sno char(9) COMMENT '用户ID', Sname char(20) , Ssex char(2), Sage int, Sdept char(20) );
insert into student values(200215121,'李勇','男',20,'CS');
没有出现中文乱码的问题，请测 验证方式需要： show create table xxx; desc xxx; desc formatted xxx; 查看3种方式是不是都没有中文乱码的问题
- 创建测试数据库（使用hive用户创建） 3.1 创建测试db1,db2 //使用管理员用户登陆，创建db1,db2两个数据库。 beeline -u "jdbc:hive2://uhadoop-4wvgxxla-master2:10000" -n hive -e "create database db1;create database db2;"
// 创建测试表，并插入数据
beeline -u "jdbc:hive2://uhadoop-4wvgxxla-master2:10000" -n hive -e "create table db1.t1(id string);"
beeline -u "jdbc:hive2://uhadoop-4wvgxxla-master2:10000" -n hive -e "insert into db1.t1 values ('t1_001'),('t1_002');"
beeline -u "jdbc:hive2://uhadoop-4wvgxxla-master2:10000" -n hive -e "create table db2.t2(id string);"
beeline -u "jdbc:hive2://uhadoop-4wvgxxla-master2:10000" -n hive -e "insert into db2.t2 values ('t2_001'),('t2_002');"
0: jdbc:hive2://uhadoop-4wvgxxla-master2:1000> use db1; No rows affected (0.173 seconds) 0: jdbc:hive2://uhadoop-4wvgxxla-master2:1000> show tables; +-----------+ | tab_name | +-----------+ | t1 | +-----------+ 1 row selected (0.208 seconds) 0: jdbc:hive2://uhadoop-4wvgxxla-master2:1000> select * from t1; +---------+ | t1.id | +---------+ | t1_001 | | t1_002 | +---------+ 2 rows selected (0.294 seconds)
0: jdbc:hive2://uhadoop-4wvgxxla-master2:1000> select * from db2.t2; +---------+ | t2.id | +---------+ | t2_001 | | t2_002 | +---------+ 2 rows selected (0.304 seconds)
3.2 master1,master2节点上创建linux测试用户user1, user2 useradd -M -s /sbin/nologin user1
useradd -M -s /sbin/nologin user2
cat /etc/passwd user1:x:1004:1005::/home/user1:/sbin/nologin user2:x:1005:1006::/home/user2:/sbin/nologin
3.3 hive中创建两个角色，分别授予不同的角色权限 //创建角色role1, 授予其对db1的管理权限 beeline -u "jdbc:hive2://uhadoop-4wvgxxla-master2:10000" -n hive -e "CREATE ROLE role1;" beeline -u "jdbc:hive2://uhadoop-4wvgxxla-master2:10000" -n hive -e "grant all on database db1 to role role1 with grant option;"
//创建角色role2, 授予其对db2的管理权限 beeline -u "jdbc:hive2://uhadoop-4wvgxxla-master2:10000" -n hive -e "CREATE ROLE role2;" beeline -u "jdbc:hive2://uhadoop-4wvgxxla-master2:10000" -n hive -e "grant all on database db2 to role role2 with grant option;"
// show grant role role1; (查看role1角色的权限列表) // show grant role role2; (查看role2角色的权限列表)
0: jdbc:hive2://uhadoop-4wvgxxla-master2:1000> show grant role role1; +-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+ | database | table | partition | column | principal_name | principal_type | privilege | grant_option | grant_time | grantor | +-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+ | db1 | | | | role1 | ROLE | * | true | 1583739035000 | -- | +-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+ 1 row selected (0.215 seconds) 0: jdbc:hive2://uhadoop-4wvgxxla-master2:1000> show grant role role2; +-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+ | database | table | partition | column | principal_name | principal_type | privilege | grant_option | grant_time | grantor | +-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+ | db2 | | | | role2 | ROLE | * | true | 1583739057000 | -- | +-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+ 1 row selected (0.119 seconds) 0: jdbc:hive2://uhadoop-4wvgxxla-master2:1000> show grant role admin; +-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+ | database | table | partition | column | principal_name | principal_type | privilege | grant_option | grant_time | grantor | +-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+ | * | | | | admin | ROLE | * | false | 1583737318000 | -- | +-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+ 1 row selected (0.131 seconds)
3.4 管理员用户登陆hive,为两个用户赋予不同的角色 beeline -u "jdbc:hive2://uhadoop-4wvgxxla-master2:10000" -n hive -e "GRANT ROLE role1 TO GROUP user1;"
beeline -u "jdbc:hive2://uhadoop-4wvgxxla-master2:10000" -n hive -e "GRANT ROLE role2 TO GROUP user2;"
// show role grant group user1 (查看user1的角色列表) // show role grant group user2（查看user2的角色列表） 0: jdbc:hive2://uhadoop-4wvgxxla-master2:1000> show role grant group user1; +--------+---------------+-------------+----------+ | role | grant_option | grant_time | grantor | +--------+---------------+-------------+----------+ | role1 | false | 0 | -- | +--------+---------------+-------------+----------+ 1 row selected (0.144 seconds) 0: jdbc:hive2://uhadoop-4wvgxxla-master2:1000> show role grant group user2; +--------+---------------+-------------+----------+ | role | grant_option | grant_time | grantor | +--------+---------------+-------------+----------+ | role2 | false | 0 | -- | +--------+---------------+-------------+----------+ 1 row selected (0.125 seconds)
4 使用user1, user2用户登陆，验证权限隔离 //user1登陆，只能看到db1数据库 beeline -u "jdbc:hive2://uhadoop-4wvgxxla-master2:10000" -n user1 -e "show databases;"
// user2用户登陆，只能看到db2数据库 beeline -u "jdbc:hive2://uhadoop-4wvgxxla-master2:10000" -n user2 -e "show databases;"
- 其他使用测试 5.1 将role从角色中剔除 REVOKE ROLE role1 FROM GROUP user1; 删除role //先查看角色列表 show roles
// 删除角色 drop role role2;
角色权限撤销 // 先查看角色当前授权信息 show grant role role1;
// 将db1的操作权限从role1撤销 revoke all on database db1 from role role1;
授权语句说明： 角色授权和撤销 GRANT ROLE role_name [, role_name] TO GROUP <groupName> [,GROUP <groupName>] REVOKE ROLE role_name [, role_name] FROM GROUP <groupName> [,GROUP <groupName>]
权限的授予和撤销 GRANT <PRIVILEGE> [, <PRIVILEGE> ] ON <OBJECT> <object_name> TO ROLE <roleName> [,ROLE <roleName>] REVOKE <PRIVILEGE> [, <PRIVILEGE> ] ON <OBJECT> <object_name> FROM ROLE <roleName> [,ROLE <roleName>]
查看角色/组权限 SHOW ROLES; SHOW CURRENT ROLES; SHOW ROLE GRANT GROUP <groupName>; SHOW GRANT ROLE <roleName>; SHOW GRANT ROLE <roleName> on OBJECT <objectName>;
查看所有的角色 0: jdbc:hive2://uhadoop-4wvgxxla-master2:1000> show roles; +--------+ | role | +--------+ | admin | | role1 | | role2 | +--------+ 3 rows selected (0.12 seconds)
#将某个数据库读权限授予给某个role GRANT SELECT ON DATABASE db_name TO ROLE role_name;
#将test 表的 S1 列的读权限授权给role_name （TABLE也可以不写） GRANT SELECT(s1) ON TABLE test TO ROLE role_name;
#test表的select 权限给 role_name 角色 GRANT SELECT ON TABLE test TO ROLE role_name;
例子： 目前有2个用户 user1 // 有db1下t1 表的所有权限 user2 // 有db2下t2 表的所有权限
目前有角色 +--------+ | role | +--------+ | admin | //所有库的最高权限 all | role1 | // 只有db1库的所有权限 | role2 | // 只有db2库下的所有权限 +--------+
0: jdbc:hive2://uhadoop-4wvgxxla-master2:1000> show databases;
+------------------------+
| database_name |
+------------------------+
| db1 |
| db2 |
| default |
| temp |
| test_hive_ucloud10086 |
+------------------------+
[hadoop@uhadoop-4wvgxxla-master1 ~]$ beeline -u "jdbc:hive2://uhadoop-4wvgxxla-master2:10000" -n user2 -e "select * from db2.t2;" Transaction isolation: TRANSACTION_REPEATABLE_READ +---------+ | t2.id | +---------+ | t2_001 | | t2_002 | +---------+ 2 rows selected (0.631 seconds) Beeline version 2.3.3 by Apache Hive [hadoop@uhadoop-4wvgxxla-master1 ~]$ beeline -u "jdbc:hive2://uhadoop-4wvgxxla-master2:10000" -n user2 -e "insert into db2.t2 values ('t2_003'),('t2_004');" Connected to: Apache Hive (version 2.3.3) Driver: Hive JDBC (version 2.3.3) Transaction isolation: TRANSACTION_REPEATABLE_READ No rows affected (25.708 seconds)
再查询一次，ok 插入成功 Transaction isolation: TRANSACTION_REPEATABLE_READ +---------+ | t2.id | +---------+ | t2_001 | | t2_002 | | t2_003 | | t2_004 | +---------+ 4 rows selected (0.605 seconds) Beeline version 2.3.3 by Apache Hive Closing: 0: jdbc:hive2://uhadoop-4wvgxxla-master2:10000
接下来，我有个需求，我想把temp下的student表给user2开放 首先user2对于的role2角色要有temp库的select权限 然后再把temp库下的student表的select权限给到role2 这个角色 那么user2属于role2 角色下 自然就有了temp下的student表的select权限
GRANT SELECT ON TABLE temp.student TO ROLE role2;
0: jdbc:hive2://uhadoop-4wvgxxla-master2:1000> GRANT SELECT ON TABLE temp.student TO ROLE role2; No rows affected (0.145 seconds) 0: jdbc:hive2://uhadoop-4wvgxxla-master2:1000> show role grant group user2; +--------+---------------+-------------+----------+ | role | grant_option | grant_time | grantor | +--------+---------------+-------------+----------+ | role2 | false | 0 | -- | +--------+---------------+-------------+----------+ 1 row selected (0.129 seconds) 0: jdbc:hive2://uhadoop-4wvgxxla-master2:1000> show grant role role2; +-----------+----------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+ | database | table | partition | column | principal_name | principal_type | privilege | grant_option | grant_time | grantor | +-----------+----------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+ | db2 | | | | role2 | ROLE | * | true | 1583739057000 | -- | | temp | student | | | role2 | ROLE | SELECT | false | 1583740481000 | -- | +-----------+----------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+ 2 rows selected (0.125 seconds)
验证如下： [hadoop@uhadoop-4wvgxxla-master1 ~]$ beeline -u "jdbc:hive2://uhadoop-4wvgxxla-master2:10000" -n user2 -e "show databases;" Transaction isolation: TRANSACTION_REPEATABLE_READ +----------------+ | database_name | +----------------+ | db2 | | default | | temp | +----------------+ 3 rows selected (0.614 seconds)
[hadoop@uhadoop-4wvgxxla-master1 ~]$ beeline -u "jdbc:hive2://uhadoop-4wvgxxla-master2:10000" -n user2 -e "use temp;show tables;" No rows affected (0.476 seconds) +-----------+ | tab_name | +-----------+ | student | +-----------+ 1 row selected (0.282 seconds) Beeline version 2.3.3 by Apache Hive [hadoop@uhadoop-4wvgxxla-master1 ~]$ beeline -u "jdbc:hive2://uhadoop-4wvgxxla-master2:10000" -n user2 -e "select * from temp.student;" Transaction isolation: TRANSACTION_REPEATABLE_READ +--------------+-----------------------+---------------+---------------+-----------------------+ | student.sno | student.sname | student.ssex | student.sage | student.sdept | +--------------+-----------------------+---------------+---------------+-----------------------+ | 200215121 | 李勇 | 男 | 20 | CS | +--------------+-----------------------+---------------+---------------+-----------------------+ 1 row selected (0.667 seconds) Beeline version 2.3.3 by Apache Hive
[hadoop@uhadoop-4wvgxxla-master1 ~]$ beeline -u "jdbc:hive2://uhadoop-4wvgxxla-master2:10000" -n user2 -e "insert into temp.student values(100215122,'刘晨','女',19,'CS');" Transaction isolation: TRANSACTION_REPEATABLE_READ Error: Error while compiling statement: FAILED: SemanticException No valid privileges User user2 does not have privileges for QUERY // 用户user2没有QUERY的特权 The required privileges: Server=uhadoop-4wvgxxla-master1->Db=temp->Table=student->action=insert->grantOption=false; (state=42000,code=40000) Closing: 0: jdbc:hive2://uhadoop-4wvgxxla-master2:10000
参考文档 基于 Sentry Hive 权限控制命令详解 https://blog.csdn.net/zhangshenghang/article/details/99212770 https://docs.cloudera.com/documentation/enterprise/5-5-x/topics/sg_hive_sql.html Sentry使用指南 https://doc.ucloud.cn/analysis/uhadoop/developer/sentrydev
