permissions for views that access other schema's table does not work; only dbadmin owner works?
Why doesn't granting select&usage appropriately give the right permissions to access a view in another schema?
I'm finding that the only way to let user 'bob' use a view you create as 'alice' in schema1 that accesses a table in schema2 is to let dbadmin be the owner.
-- as dbadmin create table bi_stats.test_table (col1 int); -- bob can access this table fine select * from bi_stats.test_table; col1 ------ (0 rows) -- as alice create or replace view bizint.test_table_view as select * from bi_stats.test_table; -- as dbadmin, give bob access to these schemas and the view grant select on bi_stats.test_table to bob with grant option; grant usage on schema bizint to bob; grant usage on schema bi_stats to bob; grant select on bizint.test_table_view to bob; -- bob still cannot use this view select * from bizint.test_table_view; ERROR 4367: Permission denied for relation test_table_view -- what works: as alice alter view bizint.test_table_view owner to dbadmin; -- testing as bob, now it works?!!!!! select * from bizint.test_table_view; col1 ------ (0 rows)
What is going on? Why do I need to set the owner of the test_table_view to dbadmin?