cancel
Showing results for 
Search instead for 
Did you mean: 

How to prevent users from selecting from view if they don't have permissions on underlying tables?

Recently a customer asked this question. How can we prevent users from querying a view if the users don't have access to the base tables used in the view?

 

Here is the solution:

 

 

testdb=# select current_user;

current_user

--------------

enterprisedb

(1 row)

testdb=# create schema schema1;

CREATE SCHEMA

testdb=# create table schema1.table1 (col1 varchar(10));

CREATE TABLE

testdb=# insert into schema1.table1 values ('xyz'),('123');

INSERT 0 2

testdb=# create schema schema2;

CREATE SCHEMA

testdb=# create view schema2.myview as

testdb-# select * from schema1.table1 where has_table_privilege ('schema1.table1','select');

CREATE VIEW

testdb=# grant usage on schema schema2 to public;

GRANT

testdb=# grant select on schema2.myview to public;

GRANT

testdb=# grant usage on schema schema1 to public;

GRANT

 

testdb=# select * from schema2.myview;

col1

------

xyz

123

(2 rows)

 

testdb=# set role testuser;

SET

testdb=> select current_user;

current_user

--------------

testuser

(1 row)

testdb=> select * from schema2.myview;

col1

------

(0 rows)

testdb=> set role enterprisedb;

SET

testdb=# select * from schema2.myview;

col1

------

xyz

123

(2 rows)

 

 

Version history
Revision #:
1 of 1
Last update:
‎06-05-2019 09:21 AM
Updated by:
 
Labels (2)
Contributors