环境
apache-hive-2.3.3-bin
apache-sentry-2.1.0-bin 1 2 sentry是目前最新的版本，支持hive的最高版本为2.3.3，hive版本如果高于2.3.3，会出一些版本兼容问题[亲测]hive快速安装
wget http://mirrors.shu.edu.cn/apache/hive/hive-2.3.3/apache-hive-2.3.3-bin.tar.gz tar -zxvf apache-hive-2.3.3-bin.tar.gz 1 2 配置hive-site.xmlmv hive-default.xml.template hive-site.xml
mkdir -p /home/xiaobin/soft/apache-hive-2.3.3-bin/tmpdir vi hive-site.xml <property> <name>system:java.io.tmpdir</name> <value>/home/xiaobin/soft/apache-hive-2.3.3-bin/tmpdir</value> </property> <property> <name>system:user.name</name> <value>master</value> </property> <property> <name>javax.jdo.option.ConnectionURL</name> <value>jdbc:mysql://192.168.1.115/hive2?createDatabaseIfNotExist=true&useUnicode=true</value> </property> <property> <name>javax.jdo.option.ConnectionUserName</name> <value>root</value> </property> <property> <name>javax.jdo.option.ConnectionPassword</name> <value>123456</value> </property> <property> <name>javax.jdo.option.ConnectionDriverName</name> <value>com.mysql.jdbc.Driver</value> </property>1
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 下载驱动yum install mysql-connector-java -y
查看安装路径 [root@cluster-master conf]# rpm -qa|grep mysql-connector-java mysql-connector-java-5.1.25-3.el7.noarch [root@cluster-master conf]# rpm -ql mysql-connector-java-5.1.25-3.el7.noarch /usr/share/doc/mysql-connector-java-5.1.25 /usr/share/doc/mysql-connector-java-5.1.25/CHANGES /usr/share/doc/mysql-connector-java-5.1.25/COPYING /usr/share/doc/mysql-connector-java-5.1.25/docs /usr/share/doc/mysql-connector-java-5.1.25/docs/README.txt /usr/share/doc/mysql-connector-java-5.1.25/docs/connector-j.html /usr/share/doc/mysql-connector-java-5.1.25/docs/connector-j.pdf /usr/share/java/mysql-connector-java.jar /usr/share/maven-fragments/mysql-connector-java /usr/share/maven-poms/JPP-mysql-connector-java.pom 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 copy mysql-connector驱动cp /usr/share/java/mysql-connector-java.jar apache-hive-2.3.3-bin/lib/
1 创建元数据数据库mysql> create database hive2;
Query OK, 1 row affected (0.01 sec) 1 2 初始化元数据schematool -dbType mysql -initSchema
1 sentry安装 下载 http://sentry.apache.org/general/downloads.html wget http://apache.01link.hk/sentry/2.1.0/apache-sentry-2.1.0-bin.tar.gz tar -zxvf apache-sentry-2.1.0-bin.tar.gz 1 2 3 config cp sentry-site.xml.service.example sentry-site.xml vi sentry-site.xml <property> <name>sentry.hive.server</name> <value>server1</value> </property><property>
<name>sentry.verify.schema.version</name> <value>true</value> </property> <property> <name>sentry.service.allow.connect</name> <value>hive,impala,hue,hdfs</value> <description>comma separated list of users - List of users that are allowed to connect to the service (eg Hive, Impala) </description> </property><property>
<name>sentry.store.jdbc.url</name> <value>jdbc:mysql://localhost:3306/sentry</value> <description>JDBC connection URL for the backed DB</description> </property><property>
<name>sentry.store.jdbc.user</name> <value>sentry</value> <description>The username of the user that connects to the Sentry database</description> </property><property>
<name>sentry.store.jdbc.password</name> <value>sentry</value> <description>Sentry password for backend JDBC user </description> </property><property>
<name>sentry.service.server.keytab</name> <value></value> <description>Keytab for service principal</description> </property><property>
<name>sentry.service.server.rpcport</name> <value>8038</value> <description> TCP port number for service</description> </property><property>
<name>sentry.service.server.rpcaddress</name> <value>0.0.0.0</value> <description> TCP interface for service to bind to</description> </property><property>
<name>sentry.store.jdbc.driver</name> <value>com.mysql.jdbc.Driver</value> <description>Backend JDBC driver - org.apache.derby.jdbc.EmbeddedDriver (only when dbtype = derby) JDBC Driver class for the backed DB</description> </property> <property> <name>sentry.service.admin.group</name> <value>hive,impala,hue,hdfs</value> <description>Comma separates list of groups. List of groups allowed to make policy updates</description> </property><property>
<name>sentry.store.group.mapping</name> <value>org.apache.sentry.provider.common.HadoopGroupMappingService</value> <description> Group mapping class for Sentry service. org.apache.sentry.provider.file.LocalGroupMapping service can be used for local group mapping. </description> </property><property>
<name>sentry.store.group.mapping.resource</name> <value> </value> <description> Policy file for group mapping. Policy file path for local group mapping, when sentry.store.group.mapping is set to LocalGroupMapping Service class.</description> </property><property>
<name>sentry.service.security.mode</name> <value>none</value> <description>Options: kerberos, none. Authentication mode for Sentry service. Currently supports Kerberos and trusted mode </description> </property> <property> <name>sentry.service.server.principal</name> <value> </value> <description>Service Kerberos principal</description> </property><property>
<name>sentry.service.web.enable</name> <value>true</value> <description>Enable web service</description> </property><property>
<name>sentry.service.web.authentication.type</name> <value>NONE</value> <description>Options: kerberos, NONE. Authentication mode for Sentry web service.</description> </property><property>
<name>sentry.service.web.authentication.kerberos.keytab</name> <value></value> <description>Keytab for web service principal</description> </property><property>
<name>sentry.service.web.authentication.kerberos.principal</name> <value></value> <description>Web service Kerberos principal</description> </property><property>
<name>sentry.service.web.authentication.allow.connect.users</name> <value></value> <description>comma separated list of users - List of users that are allowed to connect to the web service (eg Hive, Impala) </description> </property>1
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 创建sentry元数据数据库Create Database sentry;
Create User sentry Identified By 'sentry'; Grant All On sentry.* To sentry@'localhost' Identified By 'sentry'; Grant All On sentry.* To sentry@'%' Identified By 'sentry'; flush privileges; 1 2 3 4 5 复制mysql-connector驱动cp mysql-connector-java.jar apache-sentry-2.1.0-bin/lib/
1 初始化元数据sentry --command schema-tool --conffile apache-sentry-2.1.0-bin/conf/sentry-site.xml --dbType mysql --initSchema
1
2 启动service./sentry --command service --conffile apache-sentry-2.1.0-bin/conf/sentry-site.xml
1 查看是否启动成功netstat -anpl|grep 8038
(Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.) tcp 0 0 0.0.0.0:8038 0.0.0.0:* LISTEN 11950/java 1 2 3 4 hive集成sentry copy sentry 客户端配置文件cp apache-sentry-2.1.0-bin/conf/sentry-site.xml.hive-client.example apache-hive-2.3.3-bin/conf/
cd apache-hive-2.3.3-bin/conf/ mv sentry-site.xml.hive-client.example sentry-site.xml 1 2 3 配置$HIVE_HOME/conf/sentry-site.xml<property>
<name>sentry.service.security.mode</name> <value>none</value> <description>Options: kerberos, none. Authentication mode for Sentry service. Currently supports Kerberos and trusted mode </description> </property><property>
<name>sentry.service.client.server.rpc-addresses</name> <value>localhost</value> <description> TCP address of the sentry store server</description> </property><property>
<name>sentry.service.client.server.rpc-port</name> <value>8038</value> <description>Port # of the sentry store server</description> </property><property>
<name>sentry.service.client.server.rpc-connection-timeout</name> <value>200000</value> <description>Client timeout default(200000) RPC connection timeout in milisecs</description> </property><property>
<name>sentry.metastore.service.users</name> <value>hive</value> <description> Comma separated list of users List of service users (eg hive, impala) to bypass the Sentry metastore authorization. These services handle the metadata authorization on their side. </description> </property><!--
Some common client properties same as file based provider --><property>
<name>sentry.hive.provider</name> <value>org.apache.sentry.provider.file.HadoopGroupResourceAuthorizationProvider</value> <description> Deprecated name: hive.sentry.provider. Group mapping which should be used at client side</description> </property><property>
<name>sentry.hive.server</name> <value>server1</value> <description> Deprecated name: hive.sentry.server. Defaut: HS2. Hive Server2 Server identifier like "server1"</description> </property><property>
<name>sentry.hive.failure.hooks</name> <value> </value> <description>Deprecated Name: hive.sentry.failure.hooks</description> </property> <property> <name>sentry.hive.testing.mode</name> <value>true</value> </property><property>
<name>sentry.hive.provider.backend</name> <value>org.apache.sentry.provider.db.SimpleDBProviderBackend</value> <description> Options: {org.apache.sentry.provider.db.SimpleDBProviderBackend, org.apache.sentry.provider.file.SimpleFileProviderBackend} Privilege provider to be used, we support file based or db based </description> </property> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 vi hive-site.xml<property>
<name>hive.metastore.pre.event.listeners</name> <value>org.apache.sentry.binding.metastore.MetastoreAuthzBinding</value> </property><property>
<name>hive.metastore.event.listeners</name> <value>org.apache.sentry.binding.metastore.SentrySyncHMSNotificationsPostEventListener</value> </property> <property> <name>hive.server2.enable.impersonation</name> <value>true</value> </property><property>
<name>hive.security.authorization.task.factory</name> <value>org.apache.sentry.binding.hive.SentryHiveAuthorizationTaskFactoryImpl</value> </property><property>
<name>hive.server2.session.hook</name> <value>org.apache.sentry.binding.hive.HiveAuthzBindingSessionHook</value> </property><property>
<name>hive.sentry.conf.url</name> <value>file:///home/xiaobin/soft/apache-hive-2.3.3-bin/conf/sentry-site.xml</value> </property> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 copy sentry jarscp apache-sentry-2.1.0-bin/lib/sentry-*.jar apache-hive-2.3.3-bin/lib/
cp apache-sentry-2.1.0-bin/lib/shiro-* apache-hive-2.3.3-bin/lib/1
2 3 启动hiveserver2hiveserver2 --hiveconf hive.root.logger=INFO,console
1 查看hiveserver2监听端口netstat -anpl|grep 10000
(Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.) tcp 0 0 0.0.0.0:10000 0.0.0.0:* LISTEN ---------------------使用hive用户登陆,在这个hive用户是在$HIVE_HOME/conf/sentry-site.xml中配置，
<property>
<name>sentry.metastore.service.users</name> <value>hive</value> </property> 1 2 3 4 ，hive是用于授权的账号，可以理解为超级用户beeline -u 'jdbc:hive2://localhost:10000' -n hive
1 查看所有roles，当前没有任何role0: jdbc:hive2://localhost:10000> show roles;
+-------+ | role | +-------+ +-------+ 1 2 3 4 5 创建admin role:admin_role,create role admin_role;
GRANT ALL ON SERVER server1 TO ROLE admin_role; 1 2 admin_role拥有server1上的所有权限，server1是再sentry-site.xml中配置<property>
<name>sentry.hive.server</name> <value>server1</value> </property> 1 2 3 4 简单理解拥有admin_role的用户组，拥有所有权限 将hive用户组设置为管理员用户,并使用hive用户创建数据库testGRANT ROLE admin_role TO GROUP hive;
create database test; 0: jdbc:hive2://localhost:10000> create database test; No rows affected (0.172 seconds) 0: jdbc:hive2://localhost:10000> show databases; +----------------+ | database_name | +----------------+ | default | | filtered | | sensitive | | test | | test1 | +----------------+ 5 rows selected (0.334 seconds) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 创建测试role,并将xn_role分配给xn用户组0: jdbc:hive2://localhost:10000> create role xn_role;
No rows affected (0.095 seconds) 0: jdbc:hive2://localhost:10000> GRANT ROLE xn_role TO GROUP xn; No rows affected (0.118 seconds) 1 2 3 4 xn这个拥有xn_role,但是xn_role没有任何权限使用xn用户登陆
beeline -u 'jdbc:hive2://localhost:10000' -n xnshow databases没有任何库列表输出
0: jdbc:hive2://localhost:10000> show databases; +----------------+ | database_name | +----------------+ | default | +----------------+ 1 row selected (0.71 seconds)并且也没有建库权限
0: jdbc:hive2://localhost:10000> create database xn;
Error: Error while compiling statement: FAILED: SemanticException No valid privileges User xn does not have privileges for CREATEDATABASE The required privileges: Server=server1->action=create->grantOption=false; (state=42000,code=40000) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 现在用hive用户账户创建数据库xn,并将xn_role的权限附给xn;0: jdbc:hive2://localhost:10000> create database xn
. . . . . . . . . . . . . . . .> ; No rows affected (0.196 seconds) 0: jdbc:hive2://localhost:10000> GRANT ALL ON DATABASE xn TO ROLE xn_role; No rows affected (0.1 seconds) 0: jdbc:hive2://localhost:10000> GRANT ROLE xn_role TO GROUP xn; No rows affected (0.135 seconds)1
2 3 4 5 6 7 8 使用xn用户登陆beeline -u 'jdbc:hive2://localhost:10000' -n xn
0: jdbc:hive2://localhost:10000> show databases; +----------------+ | database_name | +----------------+ | default | | xn | +----------------+ 2 rows selected (0.651 seconds) 0: jdbc:hive2://localhost:10000> 1 2 3 4 5 6 7 8 9 10 查看当前用户roles0: jdbc:hive2://localhost:10000> SHOW CURRENT ROLES;
+----------+ | role | +----------+ | xn_role | +----------+ 1 row selected (0.119 seconds) 1 2 3 4 5 6 7 查看xn_role拥有的权限0: jdbc:hive2://localhost:10000> SHOW GRANT ROLE xn_role;
+-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+ | database | table | partition | column | principal_name | principal_type | privilege | grant_option | grant_time | grantor | +-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+ | xn | | | | xn_role | ROLE | * | false | 1540965346000 | -- | +-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+ 1 row selected (0.112 seconds)1
2 3 4 5 6 7 8 给xn_role添加表sensitive.events查询权限GRANT SELECT ON table sensitive.events TO ROLE xn_role;
0: jdbc:hive2://localhost:10000> SHOW GRANT ROLE xn_role;
+------------+---------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+ | database | table | partition | column | principal_name | principal_type | privilege | grant_option | grant_time | grantor | +------------+---------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+ | xn | | | | xn_role | ROLE | * | false | 1540965346000 | -- | | sensitive | events | | | xn_role | ROLE | SELECT | false | 1540971733000 | -- | +------------+---------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+ 0: jdbc:hive2://localhost:10000> select * from sensitive.events; +---------------+-----------------+----------------+----------------+ | events.ip | events.country | events.client | events.action | +---------------+-----------------+----------------+----------------+ | 10.1.2.3 | US | android | createNote | | 10.200.88.99 | FR | windows | updateNote | | 10.1.2.3 | US | android | updateNote | | 10.200.88.77 | FR | ios | createNote | | 10.1.4.5 | US | windows | updateTag | +---------------+-----------------+----------------+----------------+ 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 可以看到xn这个用户已经可以查询表sensitive.events，之前授权的时候只给了select权限，现在来尝试插入一些数据，首先先创建一个表xn.events0: jdbc:hive2://localhost:10000> create table xn.events as select * from sensitive.events;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases. No rows affected (18.355 seconds) 0: jdbc:hive2://localhost:10000>1
2 3 4 5 尝试插入数据0: jdbc:hive2://localhost:10000> insert into sensitive.events select * from xn.events;
Error: Error while compiling statement: FAILED: SemanticException No valid privileges User xn does not have privileges for QUERY The required privileges: Server=server1->Db=sensitive->Table=events->action=insert->grantOption=false; (state=42000,code=40000) 1 2 3 4 现在用hive账号给xn_role添加对表sensitive.events的所有权限0: jdbc:hive2://localhost:10000> GRANT ALL ON table sensitive.events TO ROLE xn_role;
No rows affected (0.083 seconds) 1 2 查看xn用户权限0: jdbc:hive2://localhost:10000> SHOW GRANT ROLE xn_role;
+------------+---------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+ | database | table | partition | column | principal_name | principal_type | privilege | grant_option | grant_time | grantor | +------------+---------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+ | xn | | | | xn_role | ROLE | * | false | 1540965346000 | -- | | sensitive | events | | | xn_role | ROLE | * | false | 1540972283000 | -- | +------------+---------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+1
2 3 4 5 6 7 8 可以看到xn已经又有了对表sensitive.events的所有权限0: jdbc:hive2://localhost:10000> insert into sensitive.events select * from xn.events;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases. No rows affected (17.397 seconds) 0: jdbc:hive2://localhost:10000> select * from sensitive.events; +---------------+-----------------+----------------+----------------+ | events.ip | events.country | events.client | events.action | +---------------+-----------------+----------------+----------------+ | 10.1.2.3 | US | android | createNote | | 10.200.88.99 | FR | windows | updateNote | | 10.1.2.3 | US | android | updateNote | | 10.200.88.77 | FR | ios | createNote | | 10.1.4.5 | US | windows | updateTag | | 10.1.2.3 | US | android | createNote | | 10.200.88.99 | FR | windows | updateNote | | 10.1.2.3 | US | android | updateNote | | 10.200.88.77 | FR | ios | createNote | | 10.1.4.5 | US | windows | updateTag | | 10.1.2.3 | US | android | createNote | | 10.200.88.99 | FR | windows | updateNote | | 10.1.2.3 | US | android | updateNote | | 10.200.88.77 | FR | ios | createNote | | 10.1.4.5 | US | windows | updateTag | +---------------+-----------------+----------------+----------------+ 15 rows selected (0.412 seconds) 0: jdbc:hive2://localhost:10000> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 数据已经插入到表sensitive.events现在来看下怎样收回权限，首先来收回xn_role对表的所有权限
0: jdbc:hive2://localhost:10000> REVOKE ALL ON Table sensitive.events from role xn_role;
No rows affected (0.125 seconds) 1 2 查看xn_role的权限0: jdbc:hive2://localhost:10000> SHOW GRANT ROLE xn_role;
+-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+ | database | table | partition | column | principal_name | principal_type | privilege | grant_option | grant_time | grantor | +-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+ | xn | | | | xn_role | ROLE | * | false | 1540965346000 | -- | +-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+ 0: jdbc:hive2://localhost:10000> select * from sensitive.events; Error: Error while compiling statement: FAILED: SemanticException No valid privileges User xn does not have privileges for QUERY The required privileges: Server=server1->Db=sensitive->Table=events->action=select->grantOption=false; (state=42000,code=40000) 注意 这里账号xn，test，hive均为linux用户，在指定user使用beeline时，linux系统必须要有对应的用户组，否则会报group not exists的错误，或者授权已经成功，但是权限不生效