CDH的core-sit开启: 第一个false表示用系统用户来和hive用户的权限绑定,但经测试并没有生效,所以可以改为true
第二项设置成ALL，表示创建者对其创建的表拥有所有的权限，这样也是
比较合理的。可以不用默认，自定义权限
<property>
<name>
hive.security.authorization.createtable.owner.grants
</name>
<value>select,drop</value>
</property>
beeline的授权命令:
语法: https://www.cloudera.com/documentation/enterprise/6/6.2/topics/sg_hive_sql.html
建立简单的测试例子
create database db_test1;
create table users(id int);
insert into table users values(2);
drop table users;
create external table employee (
name string,
city array<string>,
sex_age struct<sex:string,age:string>,
score map<string,int>
)
row format delimited
fields terminated by '|' #字段之间用|号隔开
collection items terminated by ',' #数组内部用逗号隔开
map keys terminated by ':'; #map的k-v用冒号隔开
授权命令:
登陆一台有hive server2的节点
beeline
!connect jdbc:hive2://localhost:10000
hive hive
dev1 dev1
dev2 dev2
beeline -u "jdbc:hive2://localhost:10000/default"
create role admin;
grant all on server server1 to role admin; #如果授权给其它角色,那么拥有此角色的用户将拥有所有库权限
grant role admin to group hive;
例子:
#建立一个表的select和insert的不同角色,并赋予给dev1,dev2
create role read;
grant select on table test to role read; grant select on table db_test1.users1 to role read;
create role write;
grant insert on table test to role write; grant insert on table db_test1.users1 to role write;
grant role read to group dev1;
grant role write to group dev2;
#建一个库的所有权限和只能查看权限,并赋予不同用户
create role db_test2_all;
grant all on database db_test2 to role db_test2_all;
grant role db_test2_all to group dev2;
create role db_test2_select;
grant select on database db_test2 to role db_test2_select;
grant role db_test2_select to group dev1;
#查看:
SHOW GRANT ROLE write;
SHOW ROLES;
SHOW CURRENT ROLES;
SHOW ROLE GRANT GROUP groupname; #查看用户拥有的权限,group名是和beeline用户名对上,beeline用户名和入口机的系统用户名对上.
SHOW GRANT USER <user name>; #查看用户可以授权的object
SHOW GRANT USER <user name>; #查看用户可以授权的object
show create table test_snappy_01; #查建表语句
desc test_snappy_01; #查表结构
desc formatted test_snappy_01; #查owner是谁
0: jdbc:hive2://localhost:10000> SHOW GRANT USER hive;
+-----------+---------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+
| database | table | partition | column | principal_name | principal_type | privilege | grant_option | grant_time | grantor |
+-----------+---------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+
| db_test5 | | | | hive | USER | OWNER | true | 1568026223000 | -- |
| db_test1 | users1 | | | hive | USER | OWNER | true | 1568085570000 | -- |
DROP ROLE <role name>;
REVOKE ROLE <role name> [, <role name>]
FROM GROUP <group name> [,GROUP <group name>]
GRANT
<privilege> [, <privilege> ]
ON <object type> <object name>
TO ROLE <role name> [,ROLE <role name>]
回收权限:
REVOKE ROLE <role name> [, <role name>]
FROM GROUP <group name> [,GROUP <group name>]
例子:
REVOKE ROLE write FROM group dev2;
回收role中的权限:
REVOKE SELECT ON DATABASE coffee_database FROM ROLE write;
例子:
1/ 当把所有权限赋予了一个角色,需要收回.
grant all on server server1 to role write;
drop role write;
然后重新再授权role.
2/ 改动权限:
dev1原来对db_test1的users1只读,改为可写可读
dev2原来对db_test1的users1可写,改为只读
create role read_write_1;
grant select,insert on table db_test1.users1 to role read_write_1;
REVOKE ROLE read FROM group dev1;
grant role read_write_1 to group dev1;
REVOKE ROLE write FROM group dev2;
grant role read to group dev2;
alter的授权:
alter权限不包括在all里,需单独授权
https://docs.cloudera.com/documentation/enterprise/6/6.2/topics/sentry_object_ownership.html
- 无 -默认。在Sentry中禁用对象所有权。新主人的特权不能分配和创建对象并没有得到所有者权限的用户。但是，选择此选项不会影响现有的OWNER特权。
- 具有GRANT的ALL特权 -对象所有者对对象拥有ALL特权，并且可以在对象上转移OWNER特权，还可以授予和撤消对对象的其他特权。OWNER特权被授予创建对象的用户或使用ALTER DATABASE SET OWNER或ALTER TABLE SET OWNER操作的用户。
- ALL特权 -对象所有者对对象拥有ALL特权，但不能将所有者特权转移给另一个用户或角色。OWNER特权被授予创建对象的用户或使用ALTER DATABASE SET OWNER或ALTER TABLE SET OWNER操作的用户。
测试:
CREATE DATABASE test1_db;
SHOW CREATE DATABASE test1_db;
#前提是要db的创建者是db的owner,只有owner能给角色和用户alter的权限
alter database test1_db set owner role test_alter; #把某个库的alter权限赋给某个角色
alter database test1_db set owner user `mingze.yang`; #把某个库的alter权限赋给某个用户
#建好的库,查看owner要在hive的元数据库中查:
#查库的owner,在hdfs上的位置
SELECT * FROM DBS where name='test1_db';
MariaDB [hive]> SELECT * FROM DBS where name='test1_db';
+-------+------+---------------------------------------------------+----------+------------+------------+-------------+
| DB_ID | DESC | DB_LOCATION_URI | NAME | OWNER_NAME | OWNER_TYPE | CREATE_TIME |
+-------+------+---------------------------------------------------+----------+------------+------------+-------------+
| 86339 | NULL | hdfs://warehourse/user/hive/warehouse/test1_db.db | test1_db | test_alter | ROLE | 1572579900 |
+-------+------+---------------------------------------------------+----------+------------+------------+-------------+
1 row in set (0.00 sec)
#查库的owner
SELECT name as db_name,
DB_ID, owner_name
FROM DBS where name='test1_db';
+----------+-------+------------+
| db_name | DB_ID | owner_name |
+----------+-------+------------+
| test1_db | 86339 | test_alter |
+----------+-------+------------+
1 row in set (0.00 sec)
SELECT
b1.db_name,
t1.TBL_NAME,
c1.COLUMN_NAME,
c1.TYPE_NAME,
c1.COMMENT,
s1.LOCATION,
from_unixtime(t1.CREATE_TIME,'%Y-%m-%d %H:%i:%S') AS CREATE_TIME
FROM
(SELECT TBL_ID,
CREATE_TIME,
OWNER,
TBL_NAME,
TBL_TYPE,
DB_ID,
SD_ID
FROM TBLS) t1
JOIN
(SELECT name as db_name,
DB_ID
FROM DBS) b1 ON t1.DB_ID=b1.DB_ID
JOIN
(SELECT CD_ID,
COMMENT,
COLUMN_NAME,
TYPE_NAME
FROM COLUMNS_V2) c1
JOIN
(SELECT CD_ID,
LOCATION,
SD_ID
FROM SDS) s1 ON c1.CD_ID=s1.CD_ID
AND t1.SD_ID=s1.SD_ID
where t1.TBL_NAME='dim_oride_city'
and b1.db_name in ('oride_dw')
同事问题解决的例子：
SHOW ROLE GRANT GROUP dev1;
REVOKE ROLE read_write_1 FROM group dev1;
SHOW ROLES;
drop role table_insert;
create role db_test1_insert_table_users_only;
grant insert on table db_test1.users to role db_test1_insert_table_users_only;
grant role db_test1_insert_table_users_only to group dev3;
1、没有drop 权限，但是可以执行drop 操作
0: jdbc:hive2://localhost:10000> drop database db_test1;
Error: Error while compiling statement: FAILED: SemanticException No valid privileges
User dev1 does not have privileges for DROPDATABASE
The required privileges: Server=server1->Db=db_test1->action=drop->grantOption=false; (state=42000,code=40000)
2、没有select 权限，但是可以查询
3、有 insert 但是无法插入
beeline:
1、dev3 没有访问db_test1库和表的权限，但是可以访问
show create table test_snappy_01; #查建表语句
desc test_snappy_01; #查表结构
desc formatted test_snappy_01; #查owner是谁
2、对指定表赋权后，库里的所有表全部出现
grant select on table db_test5.test_snappy_01 to role db_test5_select;
grant role db_test5_select to group dev2;