How to solve permission denied error when sharing late binding views in Amazon Redshift Datashare?

This post will provide solution to permission errors when sharing late binding views created on objects in another schema. This is very common use case for data sharing in Amazon Redshift. Usually the base objects will be in different schema and the views that are exposed to other clusters will be in different schema. You will get permission error if the view is a late binding view. Please go through below scenario to understand it better.

Problem:

Let us consider two schemas - schema1 and schema2 in producer cluster. schema1 contains the base objects and schema2 contains the views exposed to consumer cluster via datasharing. In below diagram, schema2.view1 is a late binding view created on schema1.table1.

On consumer cluster, a local database “db1” created on datashare. When a user tries to query db1.schema2.view1 (shared from producer), then user will get permission denied error. This case is applicable only for late binding views. If you query regular views, then you will not get this error. However some customers prefer late binding views to avoid schema dependencies.

Simulate the problem:

SQL script for Producer cluster:

create schema schema1; -- schema creation

create table schema1.table1(a int); -- base table creation

insert into schema1.table1 values (1); -- Inserting test record

create schema schema2; -- creating schema which hosts views

create view schema2.v_view1 as
select * from schema1.table1 WITH NO SCHEMA BINDING;

--Create data share and add objects

 create datashare test_ds;

 alter datashare test_ds add schema schema2;

 alter datashare test_ds add all tables in schema schema2;

 grant usage on datashare test_ds to namespace '<<consumer cluster namespace>>';

SQL script for Consumer cluster:

create database db1 from datashare test_ds of namespace '<<producer cluster namespace>>';
 
select * from db1.test_ds.v_view1; -- This will throw ERROR: Permission denied for schema schema1;

Solution:

The solution to solve this problem is to just add schema1 to the datashare. You DONT NEED to add table1 to the datashare or any other objects. You just add empty schema1 to the datashare. The will provide the necessary catalog access for consumer cluster users to query late binding views created on schema1.

-- Below is the SQL to add schema1. Again, we are not adding any objects under schema1. Just the empty schema.

alter datashare test_ds add schema schema1;

The solution will look like below visually.

Thanks for reading the post. Please let me know if you have any questions.