Grant only access to View in Redshift Spectrum

Question

I created a simple view over an external table on Redshift Spectrum:

CREATE VIEW test_view AS (
 SELECT * 
 FROM my_external_schema.my_table
 WHERE my_field='x'
) WITH NO SCHEMA BINDING;

Reading the documentation, I see that is not possible to give access to view unless I give access to the underlying schema and table. So if I do the following statement:

GRANT SELECT ON test_view to my_user;

and I try to see data in the view, as expected I got the error:

Permission Denied on schema my_external_schema

To resolve this I could do GRANT USAGE ON SCHEMA my_external_schema TO my_user but this will give SELECT on all tables inside that schema and is what that I'm trying to avoid.

Is there a workaround to avoid this?

With normal Redshift Tables, grant usage on the schema is good enough, because the permission doesn't grant select on the underlying tables, is there something similar with Spectrum?

I can not use Materialised View because the dataset is too large.

Answer 1

Spectrum (external tables) permission is a bit tricky.

You can only do Grant and Revoke usage on schema level for the external tables.

You can only GRANT or REVOKE the USAGE permission on an external schema to database users and user groups that use the ON SCHEMA syntax.

If you are going to create a view on top of the external table, then you need to grant the usage permission on the external schema. And no need to set the SELECT ON EXTERNAL TABLE also it is not possible.

ERROR:  Operation not supported on external tables

In your case, you just grant the usage permission on the external schema for that user.

GRANT usage on my_external_schema to my_user;

Then grant the permission for the view.

GRANT Select on test_view to my_user;