grant select on all tables in schema public to username;
此句是有效的復(fù)制語句
PG中有schema的概念，
以下的語句就是不行
網(wǎng)上得來終覺淺，錯誤比較多，還是要自己試試才行。
補充：postgresql關(guān)于訪問視圖需要的權(quán)限
某個用戶訪問一個視圖，這個用戶需要具備這個視圖的schema的usage和這個視圖本身的select權(quán)限，如果視圖的基表來自其他schema，可能還需要其他schema的usage權(quán)限(根據(jù)postgresql改造的redshift發(fā)現(xiàn)了此問題)，但是這個用戶不需要視圖對應(yīng)基表的select權(quán)限
如果基表被修改了或重建了，需要把基表重新賦權(quán)給視圖的owner，否則視圖無法生效，但是不需要把基表授權(quán)給需要訪問視圖的用戶，比如視圖view1的owner是viewowner，一個用戶user1有訪問view1的權(quán)限，一旦view1的基本table1被重建了，需要把基表table1的權(quán)限重新賦給viewowner，但是不需要把table1的權(quán)限重新賦給uers1
案例1：
用戶u2訪問s2.view1,用戶u2只需要s2的usage權(quán)限和s2.view1的select權(quán)限，而不需要s2.view1基表s1.table1的select權(quán)限和對應(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ù)庫，用戶lukes訪問cl_crm.v_account,用戶lukes不僅需要cl_crm的usage權(quán)限和cl_crm.v_account的select權(quán)限，還需要cl_crm.v_account基表對應(yīng)schema的usage權(quán)限，但是不需要基表raw_wam.ibdwsurvey、raw_c3.ibdwsurveyinfo的select權(quán)限
創(chuàng)建用戶lukes,并授權(quán)用戶擁有視圖cl_crm.v_account對應(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視圖報錯
select * from cl_crm.v_account limit 1 Amazon Invalid operation: permission denied for schema raw_wam; 1 statement failed.
對用戶lukes授權(quán)usage在視圖基表對應(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ù)報錯
Amazon Invalid operation: permission denied for relation v_account;
對用戶lukes授權(quán)訪問視圖的select權(quán)限
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 …)
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
