redshift error when grant select on table: Operation not supported on external tables

Question

I have a redshift table that I would like to grant access to a user,
if I run:
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name to user_name;
it works,

however, if I run:
GRANT SELECT ON schema_name.table_name to user_name;
I got the following error:
ERROR: Operation not supported on external tables

Any ideas why? 
Thanks!

Accepted Answer

You are seeing this error since SELECT is not a supported privilege type for external schema. 
Supported privilege are CREATE, ALTER, DROP. Refer following doc for details. 
https://docs.aws.amazon.com/redshift/latest/dg/r_GRANT.html

```
GRANT { { CREATE | ALTER | DROP }  [, ...] | ALL [ PRIVILEGES ] }
    ON EXTERNAL SCHEMA schema_name [, ...]
    TO { IAM_ROLE iam_role } [, ...] [ WITH GRANT OPTION ]   
```

If you want to grant SELECT privilege for tables in an external schema, use the following syntax. 
```
GRANT { { SELECT | ALTER | DROP | DELETE | INSERT }  [, ...] | ALL [ PRIVILEGES ] }
    ON EXTERNAL TABLE schema_name.table_name [, ...]
    TO { { IAM_ROLE iam_role } [, ...] | PUBLIC } [ WITH GRANT OPTION ]
```

redshift error when grant select on table: Operation not supported on external tables

Contenuto pertinente