Greenplum权限管理
原创
1关于greenplum权限说明
1.1 概述
用户与角色在整个数据库中都是全局性的。
在安装数据库时已指定超级管理员，系统管理员，例如超级管理员:gpadmin
每个数据库的逻辑结构对象都有一个所有者，所有者默认拥有所有的权限，不需要重新赋予。
删除和任意修改它的权利不能赋予别人，为所有者固有，不能被赋予或撤销。
可以把操作该对象的权限赋予别人。
授权和撤销授权 用命令GRANT REVOKE
1.2 赋予权限的步骤总结
权限按如下几个层次进行管理
1、首先管理赋予在用户特殊属性上的权限
2、在数据库上的权限
3、在数据库中创建模式的权限
4、在模式中创建数据库对象的权限，表，索引等
5、 表的增删改查的权限
6、操作表中某些字段的权限
1.3 管理赋予在用户特殊属性上的权限
1、user的 Superuser与createuser属性不能同时拥有。
2、有superuser属性的用户实际可以创建库和创建用户，且nocreateuser nocreatedb 对superuser属性没有约束。
3、create role创建用户，alter role修改用户属性。删除用户drop role，同理删除数据库是drop database;
4、拥有资源的用户不能被drop，提示错误。但是资源可以被superuser drop掉。
5、修改用户属性用alter role
2 权限操作实例
2.1 在用户(USER)特殊属性上的权限
2.1.1 权限说明
1、user的 Superuser与createuser属性不能同时拥有。
2、有superuser属性的用户实际可以创建库和创建用户，且nocreateuser nocreatedb 对superuser属性没有约束。
3、create role创建用户，alter role修改用户属性。删除用户drop role，同理删除数据库是drop database;
4、拥有资源的用户不能被drop，提示错误。但是资源可以被superuser drop掉。
5、修改用户属性用alter role
2.1.2 权限创建实例
2.1.2.1 创建role
$ psql -h 192.168.100.55 -U gpmon -d chinadaas
在管理员用户上创建以下role
# create role user1 with login password '123456';
NOTICE: resource queue required -- using default resource queue "pg_default"
CREATE ROLE
2.1.2.2检验创建的role
$ psql -h 192.168.100.55 -U user1 -d chinadaas
Password for user user1:
psql (8.3.23)
Type "help" for help.
chinadaas=>
2.1.3 撤销用户的权限
2.1.3.1 删除没有授权的账户
2.1.3.1.1 撤销权限
登录到管理员账户删除刚创建的user1
auth_test=# drop role if exists user1;
DROP ROLE
2.1.3.1.2 查看撤销效果
$ psql -h 192.168.100.55 -U user2 -d auth_test
Password for user user2:
psql: FATAL: password authentication failed for user "user2"
2.1.3.2 删除授数据库的用户
2.1.3.2.1 撤销权限
登录到管理员用户执行删除用户，需要把user1下的创建的相关信息全部删除掉才可删除给用户,或者使用cascade强制删除
auth_test=# revoke connect on database auth_test from user1;
REVOKE
auth_test=# drop user user1;
DROP ROLE
错误信息提示
auth_test=# drop user user1;
ERROR: role "user1" cannot be dropped because some objects depend on it
DETAIL: owner of append only file visibility map pg_aoseg.pg_aovisimap_2214714
owner of append only file segment listing pg_aoseg.pg_aoseg_2214714
owner of append only table schema1.test1
或者使用以下语句强制删除
auth_test= # DROP TABLE if exists user1 cascade;
2.1.3.2.2 查看效果
$ psql -h 192.168.100.55 -U user1 -d auth_test
Password for user user1:
psql: FATAL: password authentication failed for user "user1"
2.2 在DATABASE(数据库)上的权限
2.2.1 权限说明
gpadmin创建一个数据库，依次授予某用户 CREATE CONNECT TEMPORARY TEMP 的权限。验证各选项的作用。
创建用户user1 ,赋予对auth_test数据库CREATE权限，则可以在auth_test下创建schema;
2.2.2 权限创建实例
2.2.2.1 创建数据库
在管理员的用户下创建以下数据库
# CREATE DATABASE auth_test;
2.2.2.2 把数据库的CREARE权限赋予用户
登录到管理员账号把权限赋予user1
$ psql -h 192.168.100.55 -U gpmon -d auth_test
auth_test=# GRANT CREATE ON DATABASE auth_test TO user1;
GRANT
2.2.2.3 在用户下创建SCHEMA
auth_test=> create schema schema1;
CREATE SCHEMA
2.2.2.4 在SCHEMA下创建表
创建test1并插入数据
auth_test=> create table schema1.test1(id int,name varchar) WITH (appendonly=true, compresstype=zlib, compresslevel=5) DISTRIBUTED BY (id);
CREATE TABLE
插入数据并查看数据
auth_test=> insert into schema1.test1(id,name) values(1,'1');
INSERT 0 1
auth_test=> select * from schema1.test1;
id | name
----+------
1 | 1
(1 row)
2.2.3 权限撤销实例
权限包括CREATE,CONNECT,TEMP
2.2.3.1 撤销用户的connect权限
# revoke connect on database auth_test from user1;
REVOKE
2.2.3.2 撤销用户的create权限
# revoke create on database auth_test from user1;
REVOKE
2.2.3.3 查看权限撤销后的效果
auth_test=> create schema schema2;
ERROR: permission denied for database auth_test
在以上可以看出用户已失去创建schema的权限，但还有以下的select权限，同理也可以撤销其他的权限。
auth_test=> select * from schema1.test1;
id | name
----+------
1 | 1
(1 row)
2.2.4 总结数据库上权限
1、实际上在数据库方面控制的权限有CREATE,CONNECT,TEMP,即使把这些权限全部取消了，仍可以有CONNECT和创建临时表(TEMP)的权限。
2、属性（nosuperuser,nocreatedb,nocreaterole）的用户默认情况下可以connect数据库。
不可以创建schema。可以创建temporary table ，自动生成临时的schema，在会话结束后自动销毁。可以在public schema中创建表。不能在owner为其他用户的schema下创建表。
3、数据库的CREATE权限，控制是否可以在库中创建schema,以及是否可以在schema下创建表与查询表中的数据。
4、通过身份验证的用户总有CONNECT库的权限。即使是通过REVOKE撤销CONNECT，也能正常连接数据库。
5、用户总有创建TEMP表的权限。即使是通过REVOKE撤销TEMP，也能创建临时表。
2.3 在SCHEMA(模式)上的权限
2.3.1 验证权限说明
创建user2验证对user1的schema的操作，看是否有权限，实验证明user1的schema不可分享给user2,这也是GP怕多用户操作混乱
2.3.2 创建USER2
# create role user2 with login password '123456';
NOTICE: resource queue required -- using default resource queue "pg_default"
CREATE ROLE
2.3.3 把USAGE(使用权限)赋予用户
登录到user1账号下并把使用权限赋予user2
# grant usage on schema schema1 to user2;
2.3.4 查看权限的效果
登录到user2的用户下
$ psql -h 192.168.100.55 -U user2 -d auth_test
auth_test=> select * from schema1.test1;
ERROR: permission denied for relation test1
2.3.5 把CREATE(创建权限)赋予用户
登录到user1用户下执行以下命令
auth_test=> grant create on schema schema1 to user2;
GRANT
2.3.6 在用户下查询数据
在user2下查询user1的schema下的数据
auth_test=> select * from schema1.test1;
ERROR: permission denied for relation test1
2.3.7 总结SCHEMA(模式)上的权限汇总
1、如果要在别人的schema中创建自己的表，需要用户对该shema有CREATE，USAGE权限，才可以对表和数据有足够权限。
2、用户默认无法在owner为别个用户的schema中创建表。
3、用户默认无法看到owner为别个用户的schema中的表，注意设置search_path 。（\dt命令查看）。
4、赋予USAGE权限后可以看到owner为别个用户的schema中的表，但无法在里面创建表。
5、赋予CREATE权限后可以在别个用户的schema中创建表，但如果没有USAGE权限，仍无法看到表，无法查询表中的数据，也无法更改表，即使owner也是不行。再赋予USAGE后可以查询自己创建的表，可以更改自己创建的表，但无法查询别人的表。
6、用户user1的schema信息无法分配给user2用户。
2.3.8 权限撤销实例
登录到管理员用户执行撤销权限
$ psql -h 192.168.100.55 -U gpmon -d auth_test
auth_test=# revoke select,update,delete,insert on schema1.test1 from user1;
REVOKE
auth_test=# revoke usage on schema schema1 from user1;
REVOKE
auth_test=# revoke create on schema schema1 from user1;
REVOKE
撤销时需要先从table到schema
2.3.9 验证撤销的权限
在user1上验证权限问题
auth_test=> select * from schema1.test1;
ERROR: permission denied for schema schema1
LINE 1: select * from schema1.test1;
2.4 在TABLE(表)上的权限
2.4.1 权限验证说明
登录到user1执行授权给user2用户的schema1下的某张表的查询权限，查看user2时候有查询的权限
2.4.2 把SELECT(查询)权限赋予用户
登录到user1用户，把schema1下的test1查询权限赋予user1
$ psql -h 192.168.100.55 -U user1 -d auth_test
auth_test=> grant select on schema1.test1 to user2;
2.4.3 查看权限效果
登录到user2用户，查询user1的schema1下的表
$ psql -h 192.168.100.55 -U user2 -d auth_test
auth_test=> select * from schema1.test1;
id | name
----+------
1 | 1
2 | 2
2 | 2
(3 rows)
2.4.4 赋予用户更多的权限
在用户user1下操作一下命令
$ psql -h 192.168.100.55 -U user1 -d auth_test
auth_test=> grant select,update,delete,insert on schema1.test1 to user2;
GRANT
2.4.5 撤销用户的权限
一下的操作全部在user1下操作
2.4.5.1 撤销创建权限
auth_test=> revoke create on schema schema1 from user2;
REVOKE
2.4.5.2 撤销更多的权限
auth_test=> revoke select,update,delete,insert on schema1.test1 from user2;
REVOKE
2.4.6 查看撤销后的效果
登录到user2用户下查询user1下的数据
$ psql -h 192.168.100.55 -U user2 -d auth_test
auth_test=> select * from schema1.test1;
ERROR: permission denied for relation test1
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。