Redshift - How to grant user permission to SELECT from a view without granting access to the underlying external table

Question

Hi,

I have external tables in an external schema(datashare). I have created views off these tables in a separate schema.  
I'm looking to grant a user access to only the views, and not the underlying tables.

Is this at all possible?

For example, when the user tries to read from the view thats pointing to the external table, they get error `"ERROR: permission denied for schema external_schema"`.

However, running `GRANT USAGE ON SCHEMA external_schema TO user;`gives the user SELECT access to both the view and the underlying external table, which is what I want to avoid.

Thanks!

AWS-User-4315904 · Accepted Answer

Hello,

Thank you for reaching out. Your understanding is right that views created on external tables for users who do not have access to the underlying tables.

For a user to access the view, they needed to be granted USAGE permission on the external schema. This is currently a limitation and we have a feature request in place to address this concern. However, we do not have an ETA for the feature at this point of time.

I request you to follow below blogs for information on new features.

[1] What's new: https://aws.amazon.com/redshift/whats-new/

[2] Blog: https://aws.amazon.com/blogs/aws/category/database/amazon-redshift/

We apologize for the inconvenience.

Redshift - How to grant user permission to SELECT from a view without granting access to the underlying external table

Add your answer

Relevant content