grant select on all tables in schema public to username;
此句是有效的復(fù)制語(yǔ)句
PG中有schema的概念，
以下的語(yǔ)句就是不行
網(wǎng)上得來(lái)終覺(jué)淺，錯(cuò)誤比較多，還是要自己試試才行。
補(bǔ)充：postgresql關(guān)于訪問(wèn)視圖需要的權(quán)限
某個(gè)用戶訪問(wèn)一個(gè)視圖，這個(gè)用戶需要具備這個(gè)視圖的schema的usage和這個(gè)視圖本身的select權(quán)限，如果視圖的基表來(lái)自其他schema，可能還需要其他schema的usage權(quán)限(根據(jù)postgresql改造的redshift發(fā)現(xiàn)了此問(wèn)題)，但是這個(gè)用戶不需要視圖對(duì)應(yīng)基表的select權(quán)限
如果基表被修改了或重建了，需要把基表重新賦權(quán)給視圖的owner，否則視圖無(wú)法生效，但是不需要把基表授權(quán)給需要訪問(wèn)視圖的用戶，比如視圖view1的owner是viewowner，一個(gè)用戶user1有訪問(wèn)view1的權(quán)限，一旦view1的基本table1被重建了，需要把基表table1的權(quán)限重新賦給viewowner，但是不需要把table1的權(quán)限重新賦給uers1
案例1：
用戶u2訪問(wèn)s2.view1,用戶u2只需要s2的usage權(quán)限和s2.view1的select權(quán)限，而不需要s2.view1基表s1.table1的select權(quán)限和對(duì)應(yīng)schema s1的usage權(quán)限
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數(shù)據(jù)庫(kù)，用戶lukes訪問(wèn)cl_crm.v_account,用戶lukes不僅需要cl_crm的usage權(quán)限和cl_crm.v_account的select權(quán)限，還需要cl_crm.v_account基表對(duì)應(yīng)schema的usage權(quán)限，但是不需要基表raw_wam.ibdwsurvey、raw_c3.ibdwsurveyinfo的select權(quán)限
創(chuàng)建用戶lukes,并授權(quán)用戶擁有視圖cl_crm.v_account對(duì)應(yīng)schema的usage權(quán)限
create user lukes password ‘X123_x123'
grant usage on schema cl_crm to lukes
lukes用戶查詢cl_crm.v_account視圖報(bào)錯(cuò)
select * from cl_crm.v_account limit 1
Amazon Invalid operation: permission denied for schema raw_wam;
1 statement failed.
對(duì)用戶lukes授權(quán)usage在視圖基表對(duì)應(yīng)的schema上
grant usage on schema raw_wam to lukes
grant usage on schema raw_c3 to lukes
lukes用戶查詢cl_crm.v_account視圖繼續(xù)報(bào)錯(cuò)
Amazon Invalid operation: permission denied for relation v_account;
對(duì)用戶lukes授權(quán)訪問(wèn)視圖的select權(quán)限
grant select on table cl_crm.v_account to lukes
lukes用戶查詢cl_crm.v_account正常了
cl_crm.v_account視圖語(yǔ)句是
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 …)
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。
您可能感興趣的文章:- PostgreSQL 字符串拆分與合并案例
- postgresql合并string_agg函數(shù)的實(shí)例
- postgreSQL的crud操作詳解
- PostgreSQL 序列增刪改案例
- postgresql重置序列起始值的操作
- PostgreSQL批量修改函數(shù)擁有者的操作
- PostgreSQL 默認(rèn)權(quán)限查看方式
- PostgreSQL的upsert實(shí)例操作(insert on conflict do)
