Greenplum权限管理

2023-03-03 00:00:00 创建用户权限撤销赋予

目录

1关于greenplum权限说明

1.1 概述

1.2 赋予权限的步骤总结

1.3 管理赋予在用户特殊属性上的权限

2 权限操作实例

2.1 在用户(USER)特殊属性上的权限

2.1.1 权限说明

2.1.2 权限创建实例

2.1.3 撤销用户的权限

2.2 在DATABASE(数据库)上的权限

2.2.1 权限说明

2.2.2 权限创建实例

2.2.3 权限撤销实例

2.2.4 总结数据库上权限

2.3 在SCHEMA(模式)上的权限

2.3.1 验证权限说明

2.3.2 创建USER2

2.3.3 把USAGE(使用权限)赋予用户

2.3.4 查看权限的效果

2.3.5 把CREATE(创建权限)赋予用户

2.3.6 在用户下查询数据

2.3.7 总结SCHEMA(模式)上的权限汇总

2.3.8 权限撤销实例

2.3.9 验证撤销的权限

2.4 在TABLE(表)上的权限

2.4.1 权限验证说明

2.4.2 把SELECT(查询)权限赋予用户

2.4.3 查看权限效果

2.4.4 赋予用户更多的权限

2.4.5 撤销用户的权限

2.4.6 查看撤销后的效果

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.**** -U gpmon -d ******

在管理员用户上创建以下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.***-U user1 -d ******

Password for user user1:

psql (8.3.23)

Type "help" for help.

******=>

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.*** -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.*** -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.*** -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下创建表。

数据库的CREATE权限，控制是否可以在库中创建schema,以及是否可以在schema下创建表与查询表中的数据。
通过身份验证的用户总有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.*** -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.*** -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.*** -U user1 -d auth_test

auth_test=> grant select on schema1.test1 to user2;

2.4.3 查看权限效果
登录到user2用户，查询user1的schema1下的表

$ psql -h 192.168.*** -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.*** -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.*** -U user2 -d auth_test

auth_test=> select * from schema1.test1;

ERROR: permission denied for relation test1

3 权限参数详解
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...]
| ALL SEQUENCES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
ON DOMAIN domain_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN DATA WRAPPER fdw_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN SERVER server_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...]
| ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECT loid [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
ON TYPE type_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

where role_specification can be:

[ GROUP ] role_name
| PUBLIC
| CURRENT_USER
| SESSION_USER

GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]

本文来源：https://blog.csdn.net/xfg0218/article/details/83010312

相关文章