Grant access to a view

Question

I have 2 schemas here,schema1 and schema2. I have created a view in schema2 from table in schema1. Now i have granted access to the group for schema2 but they are not able to access the view.

here is the query that i used to grant access.

grant select on all tables in schema schema2 to group viewusers_ro;

view:

create view schema2.view as select col1, col2,col3 from schema1.table;

users try accessing

select * from schema2.view

error is: permission denied for schema schema1

Note: We are not allowed to grant users access to that table as we created view with only few columns from that table.

Please help me to get desired access

Answer 1

You also need to grant usage on schema2

grant usage on schema schema2 to group viewusers_ro;

Answer 2

You need to grand access on schema1. Even though the view is in schema2, because it references schema1 Redshift also wants usage on the schema that the underlying object is in. Unfortunately that makes things the case where there is one misplaced select grant away from opening schema1 up to your viewusers_ro group but that is how Redshift operates.