Redshift Spectrum- How to grant user group permission to SELECT from a view created on external table, without granting access to the underlying external table

Question

Hi,
I want to restrict access to some fields of the external table to user group. To cater this requirement I am creating a view in internal schema on top of that external table to select only required field. I want to grant access to the view created. I did so by grant select on internal_schema.view_name to group g1;
But while querying the view it is returning error saying underlying external schema access is required. I don't want to grant Usage on the external schema as it will expose all the tables to the group again. Do we have a way to restrict this?

Answer

Hello,

Firstly, I would like to inform you that if you GRANT USAGE privilege to a user over an external schema, then the user would be able to query all the tables under that external schema. This is an expected behavior.

You can achieve your use case by using materialized views instead of simple views. A materialized view contains a precomputed result set, based on an SQL query over one or more base tables. You can issue SELECT statements to query a materialized view, in the same way that you can query other tables or views in the database. Amazon Redshift returns the precomputed results from the materialized view, without having to access the base tables at all [1].

Please kindly refer the sample steps below,

1. Create a materialized view [2].
2. Grant SELECT access to this view to the concerned user/group using the below command,

- grant select on local_schema.materialized_view_name to username;

3. Now if you query the view from the concerned user, you would be successfully able to get the expected results, without giving explicit access to the user on your external schema.

However, here please note that, you would have to update the data in the materialized view, by refreshing it. Amazon Redshift identifies changes that have taken place in the base table or tables, and then applies those changes to the materialized view. As a materialized view owner, make sure to refresh materialized views whenever a base table changes.

References -
[1] https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-overview.html
[2] https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-create-sql-command.html

Redshift Spectrum- How to grant user group permission to SELECT from a view created on external table, without granting access to the underlying external table

Relevant content