大数据Hive系列之Hive用户权限管理

 开启权限

1、环境变量设置，编辑hive-env.sh 主要设置以下两个参数

# Set HADOOP_HOME to point to a specific hadoop install directory
#HADOOP_HOME=${bin}/../../hadoop
HADOOP_HOME=/usr/local/work/hadoop/hadoop-2.7.2

# Hive Configuration Directory can be controlled by:
export HIVE_CONF_DIR=/usr/local/work/hive/apache-hive-2.3.3/conf

2、、修改参数hive-default.xml

hive用户对底层文件的访问权限设置

<property>  
  <name>hive.files.umask.values</name>  
  <value>0022</value>  
  <description>当hive在hdfs上创建文件时，对应的默认掩码。此处的0022,第一个0表示八进制；剩下       的022用二进制表示即000010010，然后取反得111101101，即rwxr-xr-x，这样其他用户登录hive       或hdfs时候，就没权限删除该文件</description>  
</property>  
  
<property>  
  <name>hive.metastore.authorization.storage.checks</name>  
  <value>true</value>  
  <description>Should the metastore do authorization checks against the underlying storage for operations like drop-partition (disallow the drop-partition if the user in question doesn't have permissions to delete the corresponding directory on the storage)，就是配合hive.files.umask.values参数做权限控制.</description>  
</property>  
  
<property>  
  <name>hive.metastore.execute.setugi</name>  
  <value>true</value>  
  <description>In unsecure mode, setting this property to true will cause the metastore to execute DFS operations using the client's reported user and group permissions. Note that this property must be set on both the client and server sides. Further note that its best effort. If client sets its to true and server sets it to false, client setting will be ignored.简单说就是，hadoop在非安全模式（未使用kerborers认证）时，使用hive客户端进程对应的用户和组权限操作hdfs</description>  
</property>  

 

通过以上配置，进入hive的用户就不能随意对底层文件随意操作了，必须具有相应权限。接下来可以进行hive自身类似mysql一样的权限授权管理了。只不过这种权限不完善，只能防止误操作；要做好一点，可以用自定义超级管理员权限缓解这个问题。

首先，要开启权限功能开关，即两项配置：

<property>   
   <name>hive.security.authorization.enabled</name>   
   <value>true</value>   
   <description>enable or disable the hive client authorization.开启权限验证</description>   
</property>   
<property>   
   <name>hive.security.authorization.createtable.owner.grants</name>   
   <value>ALL</value>   
   <description>the privileges automatically granted to the owner whenever a table gets created. An example like "select,drop" will grant select and drop privilege to the owner of the table.表的创建者对表拥有所有权限.  
   </description>  
</property> 

 

3、编辑hive-site.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
    <property>
        <!-- 查询数据时 显示出列的名字 -->
        <name>hive.cli.print.header</name>
        <value>true</value>
    </property>
    <property>
        <!-- 在命令行中显示当前所使用的数据库 -->
        <name>hive.cli.print.current.db</name>
        <value>true</value>
    </property>
    <!-- mysql 连接用户名 -->
    <property>
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>root</value>
    </property>
    <!-- mysql 连接密码 -->
    <property>
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>@Xx123456</value>
    </property>
    <!-- mysql 连接URL 如果hive和mysql在同一服务器上，使用localhost -->
    <property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://127.0.01:3306/hive?createDatabaseIfNotExist=true&amp;useSSL=false&amp;serverTimezone=Asia/Shanghai</value>
    </property>
    <!-- mysql 连接驱动 -->
    <property>
        <name>javax.jdo.option.ConnectionDriverName</name>
        <value>com.mysql.cj.jdbc.Driver</value>
    </property>
    <!-- 管理员角色 -->
    <property>
        <name>hive.users.in.admin.role</name>
        <value>root</value>
　　</property>
</configuration>

 

权限设置

 1、角色

#创建角色
create role role_name;

#显示角色
show roles;

#删除角色
drop role role_name;

 

2、用户

#用户进入admin角色权限
set hive.users.in.admin.role;
set role admin;

#查看某用户的所有角色
show role grant user user_name

#给角色添加用户
grant role role_name to user user_name;

 

3、用户授权

#基于数据库
grant select on database default to useradmin;

#基于某张表
grant select on table ppdata to user admin;

 

4、组授权

#基于数据库
grant select on database default to  group  admin;

#基于某张表
grant select on table ppdata  to  group  admin;

 

5、角色授权

#基于数据库
grant select on database default to  role  admin;

#基于某张表
grant select on table ppdata to role  admin;

 

6、用户移出权限

#基于数据库
revoke select on database default from useruserB;

#基于某张表
revoke select on table ppdata from useruserB;

 

7、查看用户权限

#在某个数据库的权限
show grant role role_name on database database_name;

#在某表的权限
show grant role role_name on [table] table_name;

 

综合如下：

1、库级权限管理

#授予当前使用库的指定表的所有权限给某个用户
grant all on {tableName} to user {userName};
#授权admin privilege权限
grant ADMIN PRIVILEGE on database {dbName} to user {userName};

#授予某个库的权限给某个用户
grant select on database {dbName} to user {userName};
grant insert on database {dbName} to user {userName};
grant update on database {dbName} to user {userName};
grant delete on database {dbName} to user {userName};

#回收某个库的权限给某个用户
revoke select on database {dbName} from user {userName};
revoke insert on database {dbName} from user {userName};
revoke update on database {dbName} from user {userName};
revoke delete on database {dbName} from user {userName};

#查看指定用户在所有库下面的权限
show grant user {userName};
#查看指定用户在某个库的权限
show grant user {userName} on database {dbName};

 

2、表级权限

#授予表的权限给某个用户
grant create on database {dbName} to user {userName};
grant select on table {dbName}.tableName to user {userName};
grant insert on table {dbName}.tableName to user {userName};
grant update on table {dbName}.tableName to user {userName};
grant delete on table {dbName}.tableName to user {userName};


#回收某个用户的表的权限
revoke create on table {dbName}.tableName from user {userName}; 
revoke select on table {dbName}.tableName from user {userName};
revoke insert on table {dbName}.tableName from user {userName};
revoke update on table {dbName}.tableName from user {userName}; 
revoke delete on table {dbName}.tableName from user {userName};

#查看指定用户在指定表的权限
show grant user {userName} on table {dbName}.{tableName};

 

HIVE支持以下权限：

 

权限名称	含义
ALL	所有权限
ALTER	允许修改元数据（modify metadata data of object）---表信息数据
UPDATE	允许修改物理数据（modify physical data of object）---实际数据
CREATE	允许进行Create操作
DROP	允许进行DROP操作
INDEX	允许建索引（目前还没有实现）
LOCK	当出现并发的使用允许用户进行LOCK和UNLOCK操作
SELECT	允许用户进行SELECT操作
SHOW_DATABASE	允许用户查看可用的数据库

 

 

权限和hive常用操作的对应关系如下：

 

Operation	ALTER	UPDATE	CREATE	DROP	INDEX	LOCK	SELECT	SHOW_DATABASE
LOAD		√
EXPORT							√
IMPORT	√	√
CREATE TABLE			√
CREATE TABLE AS SELECT		√					√
DROP TABLE				√
SELECT							√
ALTER TABLE ADD COLUMN	√
ALTER TABLE REPLACE COLUMN	√
ALTER TABLE RENAME	√
ALTER TABLE ADD PARTITION			√
ALTER TABLE DROP PARTITION				√
ALTER TABLE ARCHIVE		√
ALTER TABLE UNARCHIVE		√
ALTER TABLE SET PROPERTIES	√
ALTER TABLE SET SERDE	√
ALTER TABLE SET SERDEPROPERTIES	√
ALTER TABLE CLUSTER BY	√
ALTER TABLE PROTECT MODE	√
ALTER PARTITION PROTECT MODE	√
ALTER TABLE SET FILEFORMAT	√
ALTER TABLE SET LOCATION		√
ALTER PARTITION SET LOCATION		√
ALTER TABLE CONCATENATE		√
ALTER PARTITION CONCATENATE		√
SHOW DATABASE								√
LOCK TABLE						√
UNLOCK TABLE						√