數(shù)據(jù)倉庫權(quán)限管理 - Sentry

常用命令

1）創(chuàng)建Role

create role role_name;

2）為role賦予privilege

GRANT select ON DATABASE ods to role role_name;

GRANT all ON TABLE TEST to role role_name;

3）將role授予用戶組

GRANT ROLE role_name TO GROUP user_group_name;

4）查看權(quán)限授予情況

（1）查看所有role（管理員）

SHOW ROLES；

（2）查看指定用戶組的role（管理員）

SHOW ROLE GRANT GROUP user_group_name;

（3）查看指定ROLE的具體權(quán)限（管理員）

SHOW GRANT ROLE role_name;

詳細(xì)命令查看：https://docs.cloudera.com/documentation/enterprise/5-7-x/topics/sg_hive_sql.html

場景介紹

用戶fanyunli對Hive ODS數(shù)據(jù)庫有讀寫，修改表結(jié)構(gòu)等所有權(quán)限，用戶zhaominhui對Hive ODS數(shù)據(jù)庫僅開放讀數(shù)據(jù)權(quán)限。

Hive表讀寫權(quán)限使用案例

為admin角色賦予超級權(quán)限，并將admin角色授權(quán)hive用戶組

jdbc:hive2://hadoop1:10000> create role admin;
jdbc:hive2://hadoop1:10000> grant all on server server1 to role admin; 
jdbc:hive2://hadoop1:10000> grant role admin to group hive

注：這里必須授權(quán)hive用戶組為超級用戶權(quán)限

創(chuàng)建所有權(quán)限用戶組和讀權(quán)限用戶組

在所有Hive節(jié)點(diǎn)創(chuàng)建所有權(quán)限用戶組all_privilege和讀權(quán)限用戶組reader。

[root@hadoop2]# useradd all_privilege
[root@hadoop2]# passwd all_privilege
[root@hadoop2]# useradd reader
[root@hadoop2]# passwd reader</pre>

創(chuàng)建用戶fanyunli 和 zhaominhui，并歸入對應(yīng)用戶組

在所有Hive節(jié)點(diǎn)創(chuàng)建fanyunli和zhaominhui用戶，前者歸入all_privilege用戶組，后者歸入reader用戶組。

[root@hadoop2]# useradd fanyunli
[root@hadoop2]# passwd fanyunli
[root@hadoop2 ~]# usermod -a -G all_privilege fanyunli
[root@hadoop2]# useradd zhaominhui
[root@hadoop2]# passwd zhaominhui
[root@hadoop2 ~]# usermod -a -G reader zhaominhui
#查看結(jié)果，顯示fanyunli所在為用戶組fanyunli和all_pribilege，zhaominhui所在用戶組為zhaominhui和reader.
[root@hadoop1 ~]# id fanyunli
uid=1000(fanyunli) gid=1000(fanyunli) 組=1000(fanyunli),1006(all_privilege)
[root@hadoop1 ~]# id zhaominhui
uid=1007(zhaominhui) gid=1007(zhaominhui) 組=1007(zhaominhui),1001(reader)

創(chuàng)建讀權(quán)限角色reader和所有權(quán)限角色all_privilege，并分別賦予reader用戶組和all_privilege用戶組

Sentry的三種權(quán)限：

SELECT -> 文件的Read權(quán)限

INSERT -> 文件的Write權(quán)限

ALL -> 文件的Read和Write權(quán)限</pre>

創(chuàng)建角色及賦予用戶組過程：

[root@hadoop1 /opt/cloudera/parcels/CDH-5.7.4-1.cdh5.7.4.p0.2/lib/hive/bin]# ./beeline
beeline> !connect jdbc:hive2://hadoop1:10000
scan complete in 2ms
Connecting to jdbc:hive2://hadoop1:10000
Enter username for jdbc:hive2://hadoop1:10000: hive
Enter password for jdbc:hive2://hadoop1:10000: 
Connected to: Apache Hive (version 1.1.0-cdh5.7.4)
Driver: Hive JDBC (version 1.1.0-cdh5.7.4)
Transaction isolation: TRANSACTION_REPEATABLE_READ

創(chuàng)建角色

0: jdbc:hive2://hadoop1:10000> create role reader;
0: jdbc:hive2://hadoop1:10000> create role all_privilege;

賦予角色權(quán)限

0: jdbc:hive2://hadoop1:10000> GRANT select ON DATABASE ods  TO ROLE reader;
0: jdbc:hive2://hadoop1:10000> GRANT all ON DATABASE ods TO ROLE all_privilege;

將角色授予用戶組

0: jdbc:hive2://hadoop1:10000> GRANT ROLE reader to group reader;
0: jdbc:hive2://hadoop1:10000> GRANT ROLE all_privilege TO GROUP all_privilege;

查看所有角色

0: jdbc:hive2://hadoop1:10000> show roles;
+----------------+--+
|      role      |
+----------------+--+
| reader         |
| all_privilege  |
| admin          |
+----------------+--+

查看指定角色role的具體權(quán)限,如下reader角色權(quán)限為select , all_privilege角色權(quán)限為*.


0: jdbc:hive2://hadoop1:10000> SHOW GRANT ROLE reader;
+-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+-------------------+----------+--+
| database  | table  | partition  | column  | principal_name  | principal_type  | privilege  | grant_option  |    grant_time     | grantor  |
+-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+-------------------+----------+--+
| ods       |        |            |         | reader          | ROLE            | select     | false         | 1597975087736000  | --       |
+-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+-------------------+----------+--+
0: jdbc:hive2://hadoop1:10000> SHOW GRANT ROLE all_privilege;
+-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+-------------------+----------+--+
| database  | table  | partition  | column  | principal_name  | principal_type  | privilege  | grant_option  |    grant_time     | grantor  |
+-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+-------------------+----------+--+
| ods       |        |            |         | all_privilege   | ROLE            | *          | false         | 1597975111268000  | --       |
+-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+-------------------+----------+--+

權(quán)限測試

使用reader用戶組的用戶zhaominhui登錄Hive客戶端，查詢ods庫表數(shù)據(jù)，并插入數(shù)據(jù)到ods庫表，查看權(quán)限設(shè)置是否生效。

#使用reader用戶身份登錄
[root@hadoop1 /opt/cloudera/parcels/CDH-5.7.4-1.cdh5.7.4.p0.2/lib/hive/bin]# ./beeline
beeline> !connect jdbc:hive2://hadoop1:10000
scan complete in 2ms
Connecting to jdbc:hive2://hadoop1:10000
Enter username for jdbc:hive2://hadoop1:10000: zhaominhui
Enter password for jdbc:hive2://hadoop1:10000: 
Connected to: Apache Hive (version 1.1.0-cdh5.7.4)
Driver: Hive JDBC (version 1.1.0-cdh5.7.4)
Transaction isolation: TRANSACTION_REPEATABLE_READ

查詢ods庫student表數(shù)據(jù)

0: jdbc:hive2://hadoop1:10000> select * from ods.student;
+-------------+---------------+------------------+--+
| student.id  | student.name  | student.teacher  |
+-------------+---------------+------------------+--+
| 1           | a             | NULL             |
| 2           | b             | NULL             |
| 1           | ddd           | ee               |
| 3           | ggg           | rr               |
| 4           | rrr           | rrr              |
| 5           | dd            | ter              |
+-------------+---------------+------------------+--+

插入數(shù)據(jù)到student表，顯示沒有查詢權(quán)限？？？？應(yīng)該顯示沒有insert權(quán)限。

0: jdbc:hive2://hadoop1:10000> insert into ods.student values(6,"ee","gg",4);
Error: Error while compiling statement: FAILED: SemanticException No valid privileges
 User zhaominhui does not have privileges for QUERY
 The required privileges: Server=server1->Db=default->Table=values__tmp__table__1->Column=tmp_values_col1->action=select; (state=42000,code=40000)

插入數(shù)據(jù)到ods庫的另外一張表user_model，顯示沒有查詢權(quán)限，但要求的權(quán)限提示正確，要求insert權(quán)限。

0: jdbc:hive2://hadoop1:10000> insert into user_model values("a");
Error: Error while compiling statement: FAILED: SemanticException No valid privileges
 User zhaominhui does not have privileges for QUERY
 The required privileges: Server=server1->Db=ods->Table=user_model->action=insert; (state=42000,code=40000)
 
#修改表結(jié)構(gòu)，提示沒有該權(quán)限。
 
0: jdbc:hive2://hadoop1:10000> alter table ods.student add columns(age int);
Error: Error while compiling statement: FAILED: SemanticException No valid privileges
 User zhaominhui does not have privileges for ALTERTABLE_ADDCOLS
 
#創(chuàng)建表，提示沒有創(chuàng)建表權(quán)限
 
0: jdbc:hive2://hadoop1:10000> create table user_model(a int,b int);
Error: Error while compiling statement: FAILED: SemanticException No valid privileges
 User zhaominhui does not have privileges for CREATETABLE
 The required privileges: Server=server1->Db=default->action=*; (state=42000,code=40000)

使用all_privilege用戶組的用戶fanyunli登錄hive的beeline客戶端，測試查詢，插入，修改表結(jié)構(gòu)等操作。

#使用reader用戶身份登錄

[root@hadoop1 /opt/cloudera/parcels/CDH-5.7.4-1.cdh5.7.4.p0.2/lib/hive/bin]# ./beeline
beeline> !connect jdbc:hive2://hadoop1:10000
scan complete in 2ms
Connecting to jdbc:hive2://hadoop1:10000
Enter username for jdbc:hive2://hadoop1:10000: fanyunli
Enter password for jdbc:hive2://hadoop1:10000: 
Connected to: Apache Hive (version 1.1.0-cdh5.7.4)
Driver: Hive JDBC (version 1.1.0-cdh5.7.4)
Transaction isolation: TRANSACTION_REPEATABLE_READ

查詢ods庫student表數(shù)據(jù)

0: jdbc:hive2://hadoop1:10000> select * from ods.student;
+-------------+---------------+------------------+--------------+--+
| student.id  | student.name  | student.teacher  | student.num  |
+-------------+---------------+------------------+--------------+--+
| 1           | a             | NULL             | NULL         |
| 2           | b             | NULL             | NULL         |
| 1           | ddd           | ee               | NULL         |
| 3           | ggg           | rr               | NULL         |
| 4           | rrr           | rrr              | NULL         |
| 5           | dd            | ter              | NULL         |
+-------------+---------------+------------------+--------------+--+

插入數(shù)據(jù)到ods.student表，顯示執(zhí)行mapreduce，插入成功。

0: jdbc:hive2://hadoop1:10000> insert into student values(6,"dd","dd","ddd");
INFO  : Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0

查看插入的數(shù)據(jù)，發(fā)現(xiàn)已經(jīng)插入進(jìn)去了。

0: jdbc:hive2://hadoop1:10000> select * from ods.student;
+-------------+---------------+------------------+--------------+--+
| student.id  | student.name  | student.teacher  | student.num  |
+-------------+---------------+------------------+--------------+--+
| 1           | a             | NULL             | NULL         |
| 2           | b             | NULL             | NULL         |
| 1           | ddd           | ee               | NULL         |
| 3           | ggg           | rr               | NULL         |
| 4           | rrr           | rrr              | NULL         |
| 5           | dd            | ter              | NULL         |
| 6           | dd            | dd               | NULL         |
+-------------+---------------+------------------+--------------+--+

修改表結(jié)構(gòu)，并查看，顯示修改表結(jié)構(gòu)成功

0: jdbc:hive2://hadoop1:10000> alter table ods.student add columns(parent string);
0: jdbc:hive2://hadoop1:10000> desc ods.student；
+-----------+------------+----------+--+
| col_name  | data_type  | comment  |
+-----------+------------+----------+--+
| id        | int        |          |
| name      | string     |          |
| teacher   | string     |          |
| num       | int        |          |
| parent    | string     |          |
+-----------+------------+----------+--+

創(chuàng)建ods庫新表，顯示成功，查看表結(jié)構(gòu)

0: jdbc:hive2://hadoop1:10000> create table user_model(a string);
0: jdbc:hive2://hadoop1:10000> show tables;
+-----------------------------------+--+
|             tab_name              |
+-----------------------------------+--+
| eqs_long_page                     |
| mall_attribute                    |
| mall_attribute_value_product_ref  |
| mall_category_attribute_ref       |
| mall_price                        |
| platform_scene_sharing            |
| student                           |
| test                              |
| user_model                        |
+-----------------------------------+--+

總結(jié)：上述探討了兩種在Hive中常用的用戶場景。在測試只讀權(quán)限用戶zhaominhui時(shí)，插入數(shù)據(jù)提示不太正常，通過另外一個(gè)用戶組，僅有insert權(quán)限的測試，在插入數(shù)據(jù)時(shí)也出現(xiàn)沒有查詢的權(quán)限，通過賦予查詢權(quán)限之后，便可以正常插入數(shù)據(jù)，推測insert權(quán)限必須和select權(quán)限同時(shí)賦予才能生效。全部權(quán)限用戶fanyunli，上述測試場景全部符合預(yù)期。

Hdfs數(shù)據(jù)讀寫權(quán)限使用案例（僅針對hive家目錄文件）

需要統(tǒng)一設(shè)定Hive家目錄文件夾和文件的權(quán)限和所屬用戶。

#設(shè)定 /user/hive/warehouse目錄及子目錄使用權(quán)限設(shè)置為771，表示除hive用戶之外的用戶僅具有讀權(quán)限。
[hdfs@hadoop1 /opt/cloudera/parcels/CDH-5.7.4-1.cdh5.7.4.p0.2/lib/hive/bin]$ hadoop fs -chmod -R 771 /user/hive/warehouse
#設(shè)定/user/hive/warehouse目錄及子目錄所屬用戶組及用戶為hive
[hdfs@hadoop1 /opt/cloudera/parcels/CDH-5.7.4-1.cdh5.7.4.p0.2/lib/hive/bin]$ hadoop fs -chown -R hive:hive /user/hive/warehouse

讀權(quán)限用戶zhaominhui對Hdfs文件操作

zhaominhui用戶所在用戶組僅被賦予了Hive ODS庫的讀權(quán)限。


#測試zhaominhui用戶對/user/hive/warehouse的讀取權(quán)限
[root@hadoop1 /opt/cloudera/parcels/CDH-5.7.4-1.cdh5.7.4.p0.2/lib/hive/bin]# su zhaominhui -
[zhaominhui@hadoop1 /opt/cloudera/parcels/CDH-5.7.4-1.cdh5.7.4.p0.2/lib/hive/bin]$ hadoop fs -ls /user/hive/warehouse
ls: Permission denied: user=zhaominhui, access=READ_EXECUTE, inode="/user/hive/warehouse":hive:hive:drwxrwx--t
#讀取/user/hive/warehouse/ods.db，可以讀取到數(shù)據(jù)
[zhaominhui@hadoop1 /opt/cloudera/parcels/CDH-5.7.4-1.cdh5.7.4.p0.2/lib/hive/bin]$  hadoop fs -ls /user/hive/warehouse/ods.db/
Found 9 items
drwxrwx--x+  - hive hive          0 2019-06-14 10:56 /user/hive/warehouse/ods.db/eqs_long_page
drwxrwx--x+  - hive hive          0 2020-08-17 14:36 /user/hive/warehouse/ods.db/mall_attribute
drwxrwx--x+  - hive hive          0 2020-08-17 14:35 /user/hive/warehouse/ods.db/mall_attribute_value_product_ref
drwxrwx--x+  - hive hive          0 2020-08-17 14:35 /user/hive/warehouse/ods.db/mall_category_attribute_ref
drwxrwx--x+  - hive hive          0 2019-07-24 16:46 /user/hive/warehouse/ods.db/mall_price
drwxrwx--x+  - hive hive          0 2019-04-23 17:58 /user/hive/warehouse/ods.db/platform_scene_sharing
drwxrwx--x+  - hive hive          0 2020-08-21 11:42 /user/hive/warehouse/ods.db/student
drwxrwx--x+  - hive hive          0 2020-08-21 11:55 /user/hive/warehouse/ods.db/test
drwxrwx--x+  - hive hive          0 2020-08-21 13:33 /user/hive/warehouse/ods.db/user_model

讀取某一張表的文件

[zhaominhui@hadoop1 /opt/cloudera/parcels/CDH-5.7.4-1.cdh5.7.4.p0.2/lib/hive/bin]$ hadoop fs -cat /user/hive/warehouse/ods.db/test/000000_0
a,b
1,2
 
#將一個(gè)文件寫入到/user/hive/warehouse/ods.db/test/目錄下，顯示無此權(quán)限
[zhaominhui@hadoop1 /data/work/test]$ hadoop fs -put query_oracle.py /user/hive/warehouse/ods.db/test/
put: Permission denied: user=zhaominhui, access=WRITE, inode="/user/hive/warehouse/ods.db/test":hive:hive:drwxrwx--x

所有權(quán)限用戶fanyunli對Hdfs文件操作

fanyunli用戶對/user/hive/warehouse/ods.db數(shù)據(jù)庫表具有所有權(quán)限，包括但不限于讀寫權(quán)限。

#讀取/user/hive/warehouse，顯示無該權(quán)限
[fanyunli@hadoop1 /opt/cloudera/parcels/CDH-5.7.4-1.cdh5.7.4.p0.2/lib/hive/bin]$ hadoop fs -ls /user/hive/warehouse/
ls: Permission denied: user=fanyunli, access=READ_EXECUTE, inode="/user/hive/warehouse":hive:hive:drwxrwx--x
#讀取/user/hive/warehouse/ods.db/，可以正常讀取。
[fanyunli@hadoop1 /opt/cloudera/parcels/CDH-5.7.4-1.cdh5.7.4.p0.2/lib/hive/bin]$ hadoop fs -ls /user/hive/warehouse/ods.db/
Found 9 items
drwxrwx--x+  - hive hive          0 2019-06-14 10:56 /user/hive/warehouse/ods.db/eqs_long_page
drwxrwx--x+  - hive hive          0 2020-08-17 14:36 /user/hive/warehouse/ods.db/mall_attribute
drwxrwx--x+  - hive hive          0 2020-08-17 14:35 /user/hive/warehouse/ods.db/mall_attribute_value_product_ref
drwxrwx--x+  - hive hive          0 2020-08-17 14:35 /user/hive/warehouse/ods.db/mall_category_attribute_ref
drwxrwx--x+  - hive hive          0 2019-07-24 16:46 /user/hive/warehouse/ods.db/mall_price
drwxrwx--x+  - hive hive          0 2019-04-23 17:58 /user/hive/warehouse/ods.db/platform_scene_sharing
drwxrwx--x+  - hive hive          0 2020-08-21 11:42 /user/hive/warehouse/ods.db/student
drwxrwx--x+  - hive hive          0 2020-08-21 11:55 /user/hive/warehouse/ods.db/test
drwxrwx--x+  - hive hive          0 2020-08-21 13:33 /user/hive/warehouse/ods.db/user_model
#上傳數(shù)據(jù)到/user/hive/warehouse/ods.db/test/目錄下，顯示正常上傳，未報(bào)異常。
[fanyunli@hadoop1 /data/work/test]$ hadoop fs -put query_oracle.py /user/hive/warehouse/ods.db/test/

注：hdfs文件權(quán)限管理的前提，Sentry的實(shí)例在每個(gè)hdfs的datanode節(jié)點(diǎn)都存在。即安裝的時(shí)候，gateway選擇實(shí)例，將每個(gè)hdfs實(shí)例所在的節(jié)點(diǎn)都選擇上。否則可能出現(xiàn)無法讀取hdfs文件的情況，即便賦予該用戶select權(quán)限。

總結(jié)

上述著重介紹了，Sentry對Hive和Hdfs表/數(shù)據(jù)的權(quán)限管理使用方法，并給出可能出現(xiàn)問題的解決方法。上述僅對數(shù)據(jù)庫級別進(jìn)行了測試說明，Sentry對表級別及列級別也都可以設(shè)置權(quán)限。詳情參考官方文檔。

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

友情鏈接更多精彩內(nèi)容