ITPub博客

首页 > 数据库 > PostgreSQL > postgresql关于访问视图需要的权限

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

原创 PostgreSQL 作者:lusklusklusk 时间:2020-03-25 18:01:27 0 删除 编辑

某个用户访问一个视图,这个用户需要具备这个视图的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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论
Welcome to Lukes DB HOME。 10G OCP、11G OCM, 8年以上DBA工作经验,博客仅记录自己的一个学习过程,不代表完全准确,如有需要,欢迎转载。

注册时间:2015-02-02

  • 博文量
    396
  • 访问量
    578635