如何使用Sentry管理Hive外部表权限

温馨提示：要看高清无码套图，请使用手机打开并单击图片放大查看。

1.文档编写目的

本文档主要讲述如何使用Sentry对Hive外部表权限管理，并基于以下假设：

1.操作系统版本：RedHat6.5

2.CM版本：CM 5.11.1

3.集群已启用Kerberos和Sentry

4.采用具有sudo权限的ec2-user用户进行操作

2.前置准备

2.1创建外部表数据父目录

1.使用hive用户登录Kerberos

[root@ip-172-31-8-141 1874-hive-HIVESERVER2]# kinit -kt hive.keytab hive/[e-mail][root@ip-172-31-8-141 1874-hive-HIVESERVER2]# klistTicket cache: FILE:/tmp/krb5cc_0Default principal: hive/[e-mail] starting     Expires            Service principal09/01/17 11:10:54  09/02/17 11:10:54  krbtgt/[e-mail]
        renew until 09/06/17 11:10:54[root@ip-172-31-8-141 1874-hive-HIVESERVER2]#

2.创建HDFS目录

使用如下命令在HDFS的根目录下创建Hive外部表的数据目录/extwarehouse

[root@ip-172-31-8-141 ec2-user]# hadoop fs -mkdir /extwarehouse[root@ip-172-31-8-141 ec2-user]# hadoop fs -ls /drwxr-xr-x   - hive   supergroup          0 2017-09-01 11:27 /extwarehousedrwxrwxrwx   - user_r supergroup          0 2017-08-23 03:23 /faysondrwx------   - hbase  hbase               0 2017-09-01 02:59 /hbasedrwxrwxrwt   - hdfs   supergroup          0 2017-08-31 06:18 /tmpdrwxrwxrwx   - hdfs   supergroup          0 2017-08-30 03:48 /user[root@ip-172-31-8-141 ec2-user]# hadoop fs -chown hive:hive /extwarehouse[root@ip-172-31-8-141 ec2-user]# hadoop fs -chmod 771 /extwarehouse[root@ip-172-31-8-141 ec2-user]# hadoop fs -ls /drwxrwx--x   - hive   hive                0 2017-09-01 11:27 /extwarehousedrwxrwxrwx   - user_r supergroup          0 2017-08-23 03:23 /faysondrwx------   - hbase  hbase               0 2017-09-01 02:59 /hbasedrwxrwxrwt   - hdfs   supergroup          0 2017-08-31 06:18 /tmpdrwxrwxrwx   - hdfs   supergroup          0 2017-08-30 03:48 /user[root@ip-172-31-8-141 ec2-user]#

2.2配置外部表数据父目录的ACL同步

1.确保HDFS已开启sentry并启用ACL同步

2.配置sentry同步路径（2.1创建的Hive外部表数据目录）

3.配置完成，重启服务。

3.创建Hive外部表

1.使用beeline命令行连接hive，创建Hive外部表

建表语句：

create external table if not exists student(
        name string,
        age int,
        addr string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ','LOCATION '/extwarehouse/student';

终端操作：

[root@ip-172-31-8-141 1874-hive-HIVESERVER2]# beeline Beeline version 1.1.0-cdh5.11.1 by Apache Hivebeeline> !connect jdbc:hive2://localhost:10000/;principal=hive/[e-mail]...
0: jdbc:hive2://localhost:10000/> create external table if not exists student(
. . . . . . . . . . . . . . . . >         name string,. . . . . . . . . . . . . . . . >         age int,. . . . . . . . . . . . . . . . >         addr string
. . . . . . . . . . . . . . . . > ). . . . . . . . . . . . . . . . > ROW FORMAT DELIMITED FIELDS TERMINATED BY ','. . . . . . . . . . . . . . . . > LOCATION '/extwarehouse/student';...
INFO  : OKNo rows affected (0.236 seconds)
0: jdbc:hive2://localhost:10000/>

2.向student表中load数据

准备测试数据

[root@ip-172-31-8-141 student]# pwd/home/ec2-user/student
[root@ip-172-31-8-141 student]# lltotal 4-rw-r--r-- 1 root root 39 Sep  1 11:37 student.txt[root@ip-172-31-8-141 student]# cat student.txt zhangsan,18,guangzhou
lisi,20,shenzhen
[root@ip-172-31-8-141 student]#

将student.txt文件put到hdfs的/tmp/student目录

[root@ip-172-31-8-141 student]# hadoop fs -mkdir /tmp/student[root@ip-172-31-8-141 student]# lltotal 4-rw-r--r-- 1 hive hive 39 Sep  1 11:37 student.txt[root@ip-172-31-8-141 student]# hadoop fs -put student.txt /tmp/student[root@ip-172-31-8-141 student]# hadoop fs -ls /tmp/studentFound 1 items-rw-r--r--   3 hive supergroup         39 2017-09-01 11:57 /tmp/stu
dent/student.txt[root@ip-172-31-8-141 student]#

在beeline命令行下，将数据load到student表

0: jdbc:hive2://localhost:10000/> load data inpath '/tmp/student' into table student;...
INFO  : Table default.student stats: [numFiles=1, totalSize=39]INFO  : Completed executing command(queryId=hive_20170901115858_5a76aa76-1b24-40ce-8254-42991856c05b); Time taken: 0.263 secondsINFO  : OKNo rows affected (0.41 seconds)
0: jdbc:hive2://localhost:10000/>

执行完load命令后，查看表数据

0: jdbc:hive2://localhost:10000/> select * from student;...
INFO  : OK+---------------+--------------+---------------+--+| student.name  | student.age  | student.addr  |+---------------+--------------+---------------+--+| zhangsan      | 18           | guangzhou     |
| lisi          | 20           | shenzhen      |+---------------+--------------+---------------+--+
2 rows selected (0.288 seconds)
0: jdbc:hive2://localhost:10000/>

4.使用fayson用户在beeline和impala-shell查看

使用fayson用户的principal初始化Kerberors的票据

[ec2-user@ip-172-31-8-141 cdh-shell-master]$ kinit faysonPassword for [e-mail]: [ec2-user@ip-172-31-8-141 cdh-shell-master]$ klistTicket cache: FILE:/tmp/krb5cc_500Default principal: [e-mail] starting     Expires            Service principal09/01/17 12:27:39  09/02/17 12:27:39  krbtgt/[e-mail]
        renew until 09/08/17 12:27:39[ec2-user@ip-172-31-8-141 cdh-shell-master]$

4.1访问hdfs目录

[ec2-user@ip-172-31-8-141 ~]$ hadoop fs -ls /extwarehouse/studentls: Permission denied: user=fayson, access=READ_EXECUTE, inode="/extwarehouse/student":hive:hive:drwxrwx--x[ec2-user@ip-172-31-8-141 ~]$

4.2beeline命令行查看

[ec2-user@ip-172-31-8-141 ~]$ beeline Beeline version 1.1.0-cdh5.11.1 by Apache Hivebeeline> !connect jdbc:hive2://localhost:10000/;principal=hive/[e-mail]...
INFO  : OK+-----------+--+| tab_name  |+-----------+--+
+-----------+--+
No rows selected (0.295 seconds)
0: jdbc:hive2://localhost:10000/> select * from student;Error: Error while compiling statement: FAILED: SemanticException No valid privileges
 User fayson does not have privileges for QUERY
 The required privileges: Server=server1->Db=default->Table=student->Column=addr->action=select; (state=42000,code=40000)0: jdbc:hive2://localhost:10000/>

4.3impala-shell命令行查看

[ec2-user@ip-172-31-8-141 cdh-shell-master]$ impala-shell ...
[Not connected] > connect ip-172-31-10-156.ap-southeast-1.compute.internal:21000;Connected to ip-172-31-10-156.ap-southeast-1.compute.internal:21000Server version: impalad version 2.8.0-cdh5.11.1 RELEASE (build 3382c1c488dff12d5ca8d049d2b59babee605b4e)
[ip-172-31-10-156.ap-southeast-1.compute.internal:21000] > show tables;Query: show tablesERROR: AuthorizationException: User '[e-mail]' does not have privileges to access: default.*

[ip-172-31-10-156.ap-southeast-1.compute.internal:21000] > select * from student;Query: select * from studentQuery submitted at: 2017-09-01 12:33:06 (Coordinator: ​​http://ip-172-31-10-156.ap-southeast-1.compute.internal:25000​​)
ERROR: AuthorizationException: User '[e-mail]' does not have privileges to execute 'SELECT' on: default.student

[ip-172-31-10-156.ap-southeast-1.compute.internal:21000] >

4.4测试总结

通过hive用户创建的外部表，未给fayson用户赋予student表读权限情况下，无权限访问hdfs的(/extwarehouse/student)数据目录，在beeline和impala-shell命令行下，fayson用户均无权限查询student表数据。

5.为fayson用户赋予student表读权限

注：以下操作均在hive管理员用户下操作

1.创建student_read角色

0: jdbc:hive2://localhost:10000/> create role student_read;...
INFO  : Executing command(queryId=hive_20170901124848_927878ba-0217-4a32-a508-bf29fed67be8): create role student_read...
INFO  : OKNo rows affected (0.104 seconds)
0: jdbc:hive2://localhost:10000/>

2.将student表的查询权限授权给student_read角色

0: jdbc:hive2://localhost:10000/> grant select on table student to role student_read;...
INFO  : Executing command(queryId=hive_20170901125252_8702d99d-d8eb-424e-929d-5df352828e2c): grant select on table student to role student_read...
INFO  : OKNo rows affected (0.111 seconds)
0: jdbc:hive2://localhost:10000/>

3.将student_read角色授权给fayson用户组

0: jdbc:hive2://localhost:10000/> grant role student_read to group fayson;...
INFO  : Executing command(queryId=hive_20170901125454_5f27a87e-2f63-46d9-9cce-6f346a0c415c): grant role student_read to group fayson...
INFO  : OKNo rows affected (0.122 seconds)
0: jdbc:hive2://localhost:10000/>

6.再次测试

使用fayson用户登录Kerberos

6.1访问HDFS目录

访问student数据所在hdfs目录/extwarehouse/student

[ec2-user@ip-172-31-8-141 ~]$ hadoop fs -ls /extwarehouse/studentFound 1 items-rwxrwx--x+  3 hive hive         39 2017-09-01 14:42 /extwarehouse/student/student.txt[ec2-user@ip-172-31-8-141 ~]$

6.2beeline查询student表

[ec2-user@ip-172-31-8-141 ~]$ klistTicket cache: FILE:/tmp/krb5cc_500Default principal: [e-mail] starting     Expires            Service principal09/01/17 12:58:59  09/02/17 12:58:59  krbtgt/[e-mail]
        renew until 09/08/17 12:58:59[ec2-user@ip-172-31-8-141 ~]$ [ec2-user@ip-172-31-8-141 ~]$ beeline Beeline version 1.1.0-cdh5.11.1 by Apache Hivebeeline> !connect jdbc:hive2://localhost:10000/;principal=hive/[e-mail]...
INFO  : OK+-----------+--+| tab_name  |+-----------+--+| student   |+-----------+--+
1 row selected (0.294 seconds)
0: jdbc:hive2://localhost:10000/> select * from student;...
INFO  : OK+---------------+--------------+---------------+--+| student.name  | student.age  | student.addr  |+---------------+--------------+---------------+--+| zhangsan      | 18           | guangzhou     |
| lisi          | 20           | shenzhen      |+---------------+--------------+---------------+--+
2 rows selected (0.241 seconds)
0: jdbc:hive2://localhost:10000/>

6.3impala-shell查询student表

[ec2-user@ip-172-31-8-141 cdh-shell-master]$ klistTicket cache: FILE:/tmp/krb5cc_500Default principal: [e-mail] starting     Expires            Service principal09/01/17 12:58:59  09/02/17 12:58:59  krbtgt/[e-mail]
        renew until 09/08/17 12:58:59[ec2-user@ip-172-31-8-141 cdh-shell-master]$ impala-shell ...
[Not connected] > connect ip-172-31-10-156.ap-southeast-1.compute.internal:21000;Connected to ip-172-31-10-156.ap-southeast-1.compute.internal:21000Server version: impalad version 2.8.0-cdh5.11.1 RELEASE (build 3382c1c488dff12d5ca8d049d2b59babee605b4e)
[ip-172-31-10-156.ap-southeast-1.compute.internal:21000] > show tables;Query: show tables+---------+| name    |+---------+| student |+---------+
Fetched 1 row(s) in 0.02s[ip-172-31-10-156.ap-southeast-1.compute.internal:21000] > select * from student;...
+----------+-----+-----------+| name     | age | addr      |+----------+-----+-----------+| zhangsan | 18  | guangzhou |
| lisi     | 20  | shenzhen  |+----------+-----+-----------+
Fetched 2 row(s) in 0.13s[ip-172-31-10-156.ap-southeast-1.compute.internal:21000] >

6.4测试总结

通过hive用户创建的外部表，给fayson用户赋予student表读权限后，可正常访问hdfs的(/extwarehouse/student)数据目录，在beeline和impala-shell命令行下，fayson用户均可查询student表数据。

7.Sentry管理Hive外部表权限总结

开启外部表的数据父目录ACL同步后，不需要单独的维护外部表数据目录权限。

参考文档：

​​https://www.cloudera.com/documentation/enterprise/latest/topics/sg_hdfs_sentry_sync.html​​

醉酒鞭名马，少年多浮夸！ 岭南浣溪沙，呕吐酒肆下！挚友不肯放，数据玩的花！

温馨提示：要看高清无码套图，请使用手机打开并单击图片放大查看。