oracle grant 多表 to user_从Oracle到PG-PostgreSQL中的用户权限注意点

Oracle中的对象权限，通常指schema(或user)下面具体对象的权限，如schema(或user)下的表、索引等。

但在postgresql中，DB service内的tablespace、database、scheme、table等都统称对象。所以，pg权限包括user或role、tablespace、database、schema、表、索引等层级。

更多的权限对象，通过grant语句查看：

(postgres@[local]:5432)[postgres]#h grantCommand: GRANTDescription: define access privilegesSyntax: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 | PROCEDURE | ROUTINE } routine_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...] | ALL { FUNCTIONS | PROCEDURES | ROUTINES } 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_USERGRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ](postgres@[local]:5432)[postgres]#

日常运维中，我们可能重点关注的是database、role、schema、table、index的权限比较多。

一、pg的权限只影响当前逻辑结构层级

如用户aken在database=akendb01下拥有schema=schema01，然后在该schema01下有一张表table01。

当aken把schema01的owner改成aken02的时候，即使akendb01这个database的owner=aken01，aken01依旧无法访问akenschema01下的表table01。因为schema01的owner=aken02，用户aken01要访问schema01里面属于自己的表，除了通过database层，还必须经过schema01这一道门禁。

解决的办法是要给aken01赋予该schema的usage权限才可以访问到对应的表：

grant usage on schema01 to aken01;

二、pg不支持跨库访问

pg中不能使用如下语句进行跨库访问：

select * from database01.schema01.table01;

上面把表的绝对路径database01.schema01.table01写上也不行！

关于跨库访问的疑问：

如果database01的owner为user01，database02的owner为user02，当user02继承了user01的权限属性之后，user02可以跨库访问到database01的表吗？

答案是依旧不能，即使user02拥有user01的表访问权限，必须将会话切到database01才可以查看user01的表，不支持直接database01.schema01.table01这种前缀跨库。

pg只支持到schema级别的前缀写法，如：

select * from schema.table；--和mysql一致(mysql中的database实际上是schema)

三、删除用户注意点

1.当user01下有对象时，该user01不能使用drop user username删除；

2.当user02有对应的role权限或存在with grant option级联授权的其他底层用户时，无法使用drop user username将该user01不能删除。

3.使用drop owned by user01语句则会直接删除当前database下的属于该user的对象，但不包括该database，也不包括其他database下该user的schema，仅限于当前database。

建议先执行reassign进行资产转移后再删除该owner下的对象。

(postgres@[local]:5432)[postgres]#h reassign Command: REASSIGN OWNEDDescription: change the ownership of database objects owned by a database roleSyntax:REASSIGN OWNED BY { old_role | CURRENT_USER | SESSION_USER } [, ...] TO { new_role | CURRENT_USER | SESSION_USER }(postgres@[local]:5432)[postgres]#(postgres@[local]:5432)[postgres]#h drop owned byCommand: DROP OWNEDDescription: remove database objects owned by a database roleSyntax:DROP OWNED BY { name | CURRENT_USER | SESSION_USER } [, ...] [ CASCADE | RESTRICT ](postgres@[local]:5432)[postgres]#

四、用户的权限回收

会将级联的user权限一并回收。可以使用dp查看表的权限授予情况 。表的授权格式解读：

grantee/privs/grantor

权限的回收，必须使用grantor执行revoke，使用grantee对自己进行revoke虽然不报错，但实际上权限没有回收。

后续有时间会把以上各点的实验附上。

---本文完---

欢迎扫码关注Aken头条号查看更多文章：