服务器之家

服务器之家 > 正文

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

时间:2021-03-07 18:33     来源/作者:weixin_42472771
?
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权限

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
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视图报错

?
1
2
3
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视图语句是

?
1
2
3
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 …)

以上为个人经验,希望能给大家一个参考,也希望大家多多支持服务器之家。如有错误或未考虑完全的地方,望不吝赐教。

原文链接:https://blog.csdn.net/weixin_42472771/article/details/100697322

标签:

相关文章

热门资讯

2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全 2019-12-26
yue是什么意思 网络流行语yue了是什么梗
yue是什么意思 网络流行语yue了是什么梗 2020-10-11
Intellij idea2020永久破解,亲测可用!!!
Intellij idea2020永久破解,亲测可用!!! 2020-07-29
背刺什么意思 网络词语背刺是什么梗
背刺什么意思 网络词语背刺是什么梗 2020-05-22
苹果12mini价格表官网报价 iPhone12mini全版本价格汇总
苹果12mini价格表官网报价 iPhone12mini全版本价格汇总 2020-11-13
返回顶部