1 角色管理命令

1.1 創建角色

創建一個新角色，需要 admin 用戶執行

CREATE ROLE role_name;

1.2 洗掉角色

洗掉一個角色，需要 admin 用戶執行

DROP ROLE role_name;

1.3 顯示當前角色

顯示用戶當前角色串列

SHOW CURRENT ROLES;

1.4 設定角色

如果指定了role_name，則該角色將成為當前角色中的唯一角色
將Role_Name設定為All將重繪當前角色的串列(在新角色被授予用戶的情況下)，并將其設定為默認的角色串列，
將Role_Name設定為None將從當前用戶中洗掉所有當前角色，

SET ROLE (role_name|ALL|NONE);

1.5 顯示角色

列出所有當前存在的角色，
只有admin角色對此有特權，

SHOW ROLES;

1.6 賦權/撤銷語法

第一種
將一個或多個角色授予其他角色或用戶，
如果指定了“WITH ADMIN OPTION”，則用戶將獲得將該角色授予其他用戶/角色的權限，
如果授予陳述句最終在角色之間創建回圈關系，則該命令將失敗并出現錯誤，

  GRANT role_name [, role_name] ...
  TO principal_specification [, principal_specification] ...
  [ WITH ADMIN OPTION ];
  
  principal_specification
    : USER user
    | ROLE role

從FROM子句中的用戶/角色中撤消角色的成員權限，

 REVOKE [ADMIN OPTION FOR] role_name [, role_name] ...
  FROM principal_specification [, principal_specification] ... ;
  
  principal_specification
    : USER user
    | ROLE role

第二種

  GRANT ROLE role_name [, role_name] ...
  TO principal_specification [, principal_specification] ...
  [WITH ADMIN OPTION]
  
  REVOKE [ADMIN OPTION FOR] ROLE role_name [, role_name] ...
  FROM principal_specification [, principal_specification] ...
  
  principal_specification:
      USER user
    | GROUP group
    | ROLE role

1.7 顯示角色授予

principal_name是用戶或角色的名稱，
列出已授予給定用戶或角色的所有角色，

SHOW ROLE GRANT (USER|ROLE|GROUP ) principal_name;

示例：

0: jdbc:hive2://localhost:10000> SHOW ROLE GRANT USER user1;
+---------+---------------+----------------+----------+
|  role   | grant_option  |   grant_time   | grantor  |
+---------+---------------+----------------+----------+
| public  | false         | 0              |          |
| role1   | false         | 1398284083000  | uadmin   |
+---------+---------------+----------------+----------+

1.8 列出所有角色和屬于該角色的用戶

僅admin角色對此具有特權，

SHOW PRINCIPALS role_name;

示例：

0: jdbc:hive2://localhost:10000> SHOW PRINCIPALS role1;
+-----------------+-----------------+---------------+----------+---------------+----------------+
| principal_name  | principal_type  | grant_option  | grantor  | grantor_type  |   grant_time   |
+-----------------+-----------------+---------------+----------+---------------+----------------+
| role2           | ROLE            | false         | uadmin   | USER          | 1398285926000  |
| role3           | ROLE            | true          | uadmin   | USER          | 1398285946000  |
| user1           | USER            | false         | uadmin   | USER          | 1398285977000  |
+-----------------+-----------------+---------------+----------+---------------+----------------+

2 權限管理

2.1 賦權和移除權限

對表或視圖賦權/撤銷授權

GRANT
    priv_type [, priv_type ] ...
    ON table_or_view_name
    TO principal_specification [, principal_specification] ...
    [WITH GRANT OPTION];

REVOKE [GRANT OPTION FOR]
    priv_type [, priv_type ] ...
    ON table_or_view_name
    FROM principal_specification [, principal_specification] ... ;
 
principal_specification
  : USER user
  | ROLE role
  
priv_type
  : INSERT | SELECT | UPDATE | DELETE | ALL

對列賦權/撤銷授權

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    [ON object_specification]
    TO principal_specification [, principal_specification] ...
    [WITH GRANT OPTION]

REVOKE [GRANT OPTION FOR]
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    [ON object_specification]
    FROM principal_specification [, principal_specification] ...

REVOKE ALL PRIVILEGES, GRANT OPTION
    FROM user [, user] ...

priv_type:
    ALL | ALTER | UPDATE | CREATE | DROP
  | INDEX | LOCK | SELECT | SHOW_DATABASE 
 
object_specification:
    TABLE tbl_name
  | DATABASE db_name
 
principal_specification:
    USER user
  | GROUP group
  | ROLE role

如果授予用戶對表或視圖的WITH GRANT OPTION特權，則該用戶還可以賦權/撤消其他用戶的特權以及這些物件上的角色，
示例：

0: jdbc:hive2://localhost:10000/default> grant select on table secured_table to role my_role;
No rows affected (0.046 seconds)
 
0: jdbc:hive2://localhost:10000/default> revoke update, select on table secured_table from role my_role;
No rows affected (0.028 seconds)

2.2 顯示權限

SHOW GRANT [principal_specification] ON (ALL | [TABLE] table_or_view_name);
  
principal_specification
  : USER user
  | ROLE role

2.3 管理物件權限的示例

2.3.1 創建角色，并將指定資料庫所有/只讀權限賦給該角色

--創建名為bigdata_admin_role的角色
CREATE ROLE bigdata_admin_role;
--將資料庫bigdata_db的所有權限賦給bigdata_admin_role角色
GRANT ALL ON DATABASE bigdata_db TO ROLE bigdata_admin_role；
--將集群路徑所有權限賦權給bigdata_admin_role角色
GRANT ALL ON URI 'hdfs://nameservice/user/bigdata' TO bigdata_admin_role;
--將bigdata_admin_role角色的權限給到bigdata_g組
GRANT ROLE bigdata_admin_role TO GROUP bigdata_g;
--至此，bigdata_g 組下的所有用戶擁有了操作bigdata_db庫的所有權限

--創建名為bigdata_read_role的角色,該角色只有只讀權限
CREATE ROLE bigdata_read_role;
--將資料庫bigdata_db的select權限賦給bigdata_read_role角色
GRANT SELECT ON DATABASE bigdata_db TO ROLE bigdata_read_role；
--將集群路徑所有權限賦權給bigdata_admin_role角色
GRANT ALL ON URI 'hdfs://nameservice/user/bigdata' TO bigdata_read_role;
--將bigdata_admin_role角色的權限給到bigdata_g組
GRANT ROLE bigdata_read_role TO GROUP bigdata_g;
--至此，bigdata_g 組下的所有用戶擁有了操作bigdata_db庫的讀權限

--創建一個用戶組hive_g,并給該角色賦權查詢bigdata_db.test_tb的權限
CREATE ROLE hive_read_role;
GRANT ROLE hive_read_role TO GROUP hive_g;
GRANT SELECT ON TABLE bigdata_db.test_tb TO ROLE hive_read_role;

找出用戶ashutosh對表hivejiratable擁有的特權：

0: jdbc:hive2://localhost:10000> show grant user ashutosh on table hivejiratable;
+-----------+----------------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+
| database  |     table      | partition  | column  | principal_name  | principal_type  | privilege  | grant_option  |   grant_time   | grantor  |
+-----------+----------------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+
| default   | hivejiratable  |            |         | ashutosh        | USER            | DELETE     | false         | 1398303419000  | thejas   |
| default   | hivejiratable  |            |         | ashutosh        | USER            | SELECT     | false         | 1398303407000  | thejas   |
+-----------+----------------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+

找出用戶ashutosh對所有物件具有的特權：

0: jdbc:hive2://localhost:10000> show grant user ashutosh on all;                              
+-----------+-------------------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+
| database  |       table       | partition  | column  | principal_name  | principal_type  | privilege  | grant_option  |   grant_time   | grantor  |
+-----------+-------------------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+
| default   | hivecontributors  |            |         | ashutosh        | USER            | DELETE     | false         | 1398303576000  | thejas   |
| default   | hivecontributors  |            |         | ashutosh        | USER            | INSERT     | false         | 1398303576000  | thejas   |
| default   | hivecontributors  |            |         | ashutosh        | USER            | SELECT     | false         | 1398303576000  | thejas   |
| default   | hivejiratable     |            |         | ashutosh        | USER            | DELETE     | false         | 1398303419000  | thejas   |
| default   | hivejiratable     |            |         | ashutosh        | USER            | SELECT     | false         | 1398303407000  | thejas   |
+-----------+-------------------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+

找出所有用戶對表hivejiratable擁有的特權：

0: jdbc:hive2://localhost:10000> show grant on table hivejiratable;
+-----------+----------------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+
| database  |     table      | partition  | column  | principal_name  | principal_type  | privilege  | grant_option  |   grant_time   | grantor  |
+-----------+----------------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+
| default   | hivejiratable  |            |         | ashutosh        | USER            | DELETE     | false         | 1398303419000  | thejas   |
| default   | hivejiratable  |            |         | ashutosh        | USER            | SELECT     | false         | 1398303407000  | thejas   |
| default   | hivejiratable  |            |         | navis           | USER            | INSERT     | false         | 1398303650000  | thejas   |
| default   | hivejiratable  |            |         | navis           | USER            | SELECT     | false         | 1398303650000  | thejas   |
| default   | hivejiratable  |            |         | public          | ROLE            | SELECT     | false         | 1398303481000  | thejas   |
| default   | hivejiratable  |            |         | thejas          | USER            | DELETE     | true          | 1398303380000  | thejas   |
| default   | hivejiratable  |            |         | thejas          | USER            | INSERT     | true          | 1398303380000  | thejas   |
| default   | hivejiratable  |            |         | thejas          | USER            | SELECT     | true          | 1398303380000  | thejas   |
| default   | hivejiratable  |            |         | thejas          | USER            | UPDATE     | true          | 1398303380000  | thejas   |
+-----------+----------------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/1515.html

標籤：其他

上一篇：達夢資料庫備份還原-概述

下一篇：Oracle資料庫exp和imp方式匯出資料

hive創建角色并賦權