hashdata关于分区表权限管理&hashdata关于多权限（相对固定的权限）多人员的情况下的权限管理

1. hashdata关于分区表权限分配的问题？

日常工作中，针对一些大表，我们可能会进行分区处理，即创建分区表。分区表可能存在一个或几个分区，也可能会存在几十个或者上百个分区。当我们想要将这个分区表的select,insert,updata,delete等权限分配给用户时，分区比较少的情况可以直接将各个分区的对应权限分配给用户，可是分区比较多的时候，我们如果还采用将子分区的权限分配给用户的话，将会极大的增大运维人员的工作量。

仔细看一下分区表的概念和属性。不难发现，分区表的的select,insert,updata,delete等权限是可以继承子分区的相对应的权限的。因此是否可以通过分配分区表的权限，将该分区表的子分区的权限同时分配给用户呢？

GRANT <SELECT,ISNERT,UPDATA,DELETE> ON <PARTITION_TABLE_NAME> TO <USERNAME>;

A： 答案当然是肯定的。可以通过分配分区表的权限，将该分区表的子分区的权限同时分配给用户。通过以下过程验证一下我们的结论。

示例：创建一个分区表hashdata_test，然后通过将分区表的select权限分配给visitor用户，来验证一下visitor是否同时拥有了分区表hashdata_test所有分区的权限。

创建分区表hashdata_test：

gpadmin=> create table hashdata_test (id int,name text) partition by range(id) (start (1) end (6) every(1),default partition prtdefault); 
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
NOTICE:  CREATE TABLE will create partition "hashdata_test_1_prt_prtdefault" for table "hashdata_test"
NOTICE:  CREATE TABLE will create partition "hashdata_test_1_prt_2" for table "hashdata_test"
NOTICE:  CREATE TABLE will create partition "hashdata_test_1_prt_3" for table "hashdata_test"
NOTICE:  CREATE TABLE will create partition "hashdata_test_1_prt_4" for table "hashdata_test"
NOTICE:  CREATE TABLE will create partition "hashdata_test_1_prt_5" for table "hashdata_test"
NOTICE:  CREATE TABLE will create partition "hashdata_test_1_prt_6" for table "hashdata_test"
CREATE TABLE

插入一些测试数据：

gpadmin=> insert into hashdata_test values(1,'a');
INSERT 0 1
gpadmin=> insert into hashdata_test values(2,'b');
INSERT 0 1
gpadmin=> insert into hashdata_test values(3,'c');
INSERT 0 1
gpadmin=> insert into hashdata_test values(4,'d');
INSERT 0 1
gpadmin=> insert into hashdata_test values(5,'f');
INSERT 0 1
gpadmin=> insert into hashdata_test values(6,'e');
INSERT 0 1

创建用户visitor，并将分区表hashdata_test的select权限分配给用户：

gpadmin=#  create user visitor with password 'visitor' login;
NOTICE:  resource queue required -- using default resource queue "pg_default"
CREATE ROLE
gpadmin=# grant select on hashdata_test to visitor;
GRANT

切换到visitor用户，验证visitor拥有了分区表hashdata_test的所有子分区的select权限：

gpadmin=# set role visitor;
SET
gpadmin=> select * from hashdata_test;
 id | name 
----+------
  1 | a
  2 | b
  6 | e
  3 | c
  4 | d
  5 | f
(6 rows)

gpadmin=> select * from hashdata_test_1_prt_prtdefault;
 id | name 
----+------
  6 | e
(1 row)

gpadmin=> select * from hashdata_test_1_prt_2;
 id | name 
----+------
  1 | a
(1 row)

gpadmin=> select * from hashdata_test_1_prt_3;
 id | name 
----+------
  2 | b
(1 row)

gpadmin=> select * from hashdata_test_1_prt_4;
 id | name 
----+------
  3 | c
(1 row)

gpadmin=> select * from hashdata_test_1_prt_5;
 id | name 
----+------
  4 | d
(1 row)

gpadmin=> select * from hashdata_test_1_prt_6;
 id | name 
----+------
  5 | f
(1 row)

2. hashdata如何将一组固定的权限分配给一批用户？

在日常工作中，我们经常会遇到将某个部门的所有数据库操作权限,分配给该部门下的所有用户的情况。针对这种多权限多人员的情况，如果我们仍然采用grant select on Table1 to userN;这样的方式来分配权限的话，那么不仅仅是在分配权限的时候会产生极大的工作量，而且极其容易产生权限分配混乱的情况。不利于权限管理。

若有一种方式可以将该部门的所有权限打包为一个整体（ROLE），那么在添加（or删除）权限的时候只要向这个ROLE添加（or删除）权限就可以了。在给人员分配（or取消）该部门权限的时候，只要分配（or取消）对应人员的ROLE就可以了。

CREATE ROLE <ROLE_NAME>;
<GRANT|REVOKE> <SELECT|INSERT|UPDATE|DELETE> ON <OBJECT_NAME> <TO|FROM> <ROLE_NAME>;
<GRANT|REVOKE> <ROLE_NAME> <TO|FROM> <USERNAME>;

A： 可以通过将该部门下的所有权限分配给一个ROLE作为一个集合，然后通过对这个ROLE的管理来完成上述复杂业务场景的权限管理操作。

示例：

场景一 ，部门A拥有hashdata_test1表的查询权限，现在部门A新加入了两个人visitor3，visitor4要将部门A的权限分配给两个新入职的员工。

创建测试表hashdata_test1:

gpadmin=# create table hashdata_test1(id int,name text);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
gpadmin=# insert into hashdata_test1 values(1,'a');
INSERT 0 1

创建权限集合ROLE：

gpadmin=# create role visitor_role;
NOTICE:  resource queue required -- using default resource queue "pg_default"
CREATE ROLE
gpadmin=# grant select on hashdata_test1 to visitor_role;
GRANT

给新加入的员工，创建两个访问用户visitor3，visitor4：

gpadmin=# create user visitor4 with password 'visitor' login;
NOTICE:  resource queue required -- using default resource queue "pg_default"
CREATE ROLE
gpadmin=# create user visitor3 with password 'visitor' login;
NOTICE:  resource queue required -- using default resource queue "pg_default"
CREATE ROLE

将权限集合ROLE分配给访问用户visitor3，visitor4：

gpadmin=# grant visitor_role to visitor3;
GRANT ROLE
gpadmin=# grant visitor_role to visitor4;
GRANT ROLE

这样两个新入职的员工就都拥有了部门A的权限，查看hashdata_test1：

gpadmin=# set role visitor3;
SET
gpadmin=> select * from hashdata_test1;
 id | name 
----+------
  1 | a
(1 row)

gpadmin=> set role visitor4;
SET
gpadmin=> select * from hashdata_test1;
 id | name 
----+------
  1 | a
(1 row)

场景二 ，如果visitor3要离职，那么直接将权限集合的role从访问者用户中revoke掉就可以了，不必一个一个权限去解除：

gpadmin=> set role gpadmin;
SET
gpadmin=# revoke visitor_role from visitor3;
REVOKE ROLE
gpadmin=# set role visitor3;
SET
gpadmin=> select * from hashdata_test1;
ERROR:  permission denied for relation hashdata_test1

场景三 ，如果部门A的权限增加了，比如：增加了hashdata_test2的查询权限。此时只需要将该权限分配给visitor_role角色就可以让所有部门A的人拥有hashdata_test2的查询权限，而不必去一个一个用户的去分配。

gpadmin=> create table hashdata_test2(id int,name text);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
gpadmin=> insert into hashdata_test2 values(1,'b');
INSERT 0 1
gpadmin=> grant select on hashdata_test2 to visitor_role;
GRANT
gpadmin=> set role visitor4;
SET
gpadmin=> select * from hashdata_test2;
 id | name 
----+------
  1 | b
(1 row)