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許可權
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](500310) Invalid operation: permission denied for schema raw_wam;
1 statement failed.

對使用者lukes授權usage在檢視基表對應的schema上
grant usage on schema raw_wam to lukes
grant usage on schema raw_c3 to lukes

lukes使用者查詢cl_crm.v_account檢視繼續報錯
[Amazon](500310) Invalid operation: permission denied for relation v_account;

對使用者lukes授權訪問檢視的select許可權
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 ....)
                      

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30126024/viewspace-2682551/，如需轉載，請註明出處，否則將追究法律責任。