postgresql 赋权语句 grant的正确使用说明_PostgreSQL

postgresql 赋权语句 grant的正确使用说明

2021-03-07 18:33weixin_42472771 PostgreSQL

这篇文章主要介绍了postgresql 赋权语句 grant的正确使用说明，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧

1	`grant` `select` `on` `all` `tables` `in` `schema` `public` `to` `username;`

此句是有效的复制语句

PG中有schema的概念，

以下的语句就是不行

postgresql 赋权语句 grant的正确使用说明

网上得来终觉浅，错误比较多，还是要自己试试才行。

补充：postgresql关于访问视图需要的权限

某个用户访问一个视图，这个用户需要具备这个视图的schema的usage和这个视图本身的select权限，如果视图的基表来自其他schema，可能还需要其他schema的usage权限(根据postgresql改造的redshift发现了此问题)，但是这个用户不需要视图对应基表的select权限

如果基表被修改了或重建了，需要把基表重新赋权给视图的owner，否则视图无法生效，但是不需要把基表授权给需要访问视图的用户，比如视图view1的owner是viewowner，一个用户user1有访问view1的权限，一旦view1的基本table1被重建了，需要把基表table1的权限重新赋给viewowner，但是不需要把table1的权限重新赋给uers1

案例1：

用户u2访问s2.view1,用户u2只需要s2的usage权限和s2.view1的select权限，而不需要s2.view1基表s1.table1的select权限和对应schema s1的usage权限

									t1=# \c

									You are now connected to database “t1” as user “postgres”

									t1=# \dns

									List of schemas

									Name | Owner

									--------±---------

									public | postgres

									s1 | postgres

									s2 | postgres

									(3 rows)

									t1=# create user u1 password ‘123456';

									CREATE ROLE

									t1=# create user u2 password ‘123456';

									CREATE ROLE

									t1=# grant all privileges on schema s1 to u1;

									GRANT

									t1=# grant all privileges on schema s2 to u1;

									GRANT

									t1=# \c - u1

									You are now connected to database “t1” as user “u1”.

									t1=> create table s1.table1(hid int);

									CREATE TABLE

									t1=> create view s2.view1 as select * from s1.table1;

									CREATE VIEW

									t1=> \c - u2

									You are now connected to database “t1” as user “u2”.

									t1=> select * from s2.view1;

									ERROR: permission denied for schema s2

									LINE 1: select * from s2.view1;

									t1=> \c - postgres

									You are now connected to database “t1” as user “postgres”.

									t1=# grant usage on schema s2 to u2;

									GRANT

									t1=# \c - u2

									You are now connected to database “t1” as user “u2”.

									t1=> select * from s2.view1;

									ERROR: permission denied for view view1

									t1=> \c - postgres

									You are now connected to database “t1” as user “postgres”.

									t1=# grant select on s2.view1 to u2;

									t1=# \c - u2

									You are now connected to database “t1” as user “u2”.

									t1=> select * from s2.view1;

									hid

									(0 rows)

案例2：

redshift数据库，用户lukes访问cl_crm.v_account,用户lukes不仅需要cl_crm的usage权限和cl_crm.v_account的select权限，还需要cl_crm.v_account基表对应schema的usage权限，但是不需要基表raw_wam.ibdwsurvey、raw_c3.ibdwsurveyinfo的select权限

创建用户lukes,并授权用户拥有视图cl_crm.v_account对应schema的usage权限

1 2	`create` `user` `lukes` `password` `‘X123_x123'` `grant` `usage` `on` `schema` `cl_crm` `to` `lukes`

lukes用户查询cl_crm.v_account视图报错

									select * from cl_crm.v_account limit 1

									Amazon Invalid operation: permission denied for schema raw_wam;

									1 statement failed.

对用户lukes授权usage在视图基表对应的schema上

1 2	`grant` `usage` `on` `schema` `raw_wam` `to` `lukes` `grant` `usage` `on` `schema` `raw_c3` `to` `lukes`

lukes用户查询cl_crm.v_account视图继续报错

1	`Amazon Invalid operation: permission denied` `for` `relation v_account;`

对用户lukes授权访问视图的select权限

1	`grant` `select` `on` `table` `cl_crm.v_account` `to` `lukes`

lukes用户查询cl_crm.v_account正常了

cl_crm.v_account视图语句是

									CREATE or replace view cl_crm.v_account as with userids as (

									select distinct su.ibsu_userid from raw_wam.ibdwsurvey su

									left join raw_c3.ibdwsurveyinfo si …)