How do I grant access permissions to external schemas in Amazon Redshift?

Short description

When you use external schemas in Amazon Redshift, you might receive the following error messages:

• "Permission denied"
• "Operation is not supported"
• "External tables are not supported in views"

Incorrect AWS Lake Formation permissions configurations can also cause errors with Amazon Redshift Spectrum. To resolve this issue, you must grant permissions on the external schemas, not at the table level.

Resolution

Grant permission to access objects in the external schema

When an Amazon Redshift user doesn't have permission to access objects in an external schema, they might receive the following error:

"ERROR: permission denied for schema external_schema_name"

To grant permissions to the user, complete the following steps:

1. Open the Amazon Redshift console.

2. Log in as a superuser.

3. Run the following command to verify user permissions for the external schema that they want to access:

   SELECT * 
   FROM SVV_SCHEMA_PRIVILEGES 
   WHERE identity_name = 'name_of_the_user/role/group' AND namespace_name = 'external_schema_name';

   Note: Replace name_of_the_user/role/group with the name, role, or group of the user that you want to grant access to. Replace external_schema_name with the name of the external schema.

   If the preceding command doesn't return the USAGE permission type, then the user, role, or group doesn't have permission to query the external schema.

4. Run one of the following GRANT commands to grant permission to use the external schema:

   GRANT USAGE ON SCHEMA external_schema_name TO user_name;

   GRANT USAGE ON SCHEMA external_schema_name TO role role_name;

   GRANT USAGE ON SCHEMA external_schema_name TO group group_name;

   Note: Replace external_schema_name with the name of the external schema. Replace user_name, role_name or group_name with the user, role, or group that you want to grant access to.

Resolve datashare permission issues

When you try to grant permission on objects in an external schema, you might receive the following error message:

"ERROR: Operation is not supported through datashares".

This error occurs in the following situations:

• The external schema is from a database that you created from a datashare.
• The original datashare didn't include the WITH PERMISSIONS clause.

To resolve this issue, recreate the database from the datashare with the WITH PERMISSION clause.

Complete the following steps:

1. Run the following command to drop the existing datashare database:

   DROP database database_created_from_datashare_name;

   Note: Replace database_created_from_datashare_name with the name of your database.

2. Run the following command to recreate the database:

   CREATE DATABASE database_created_from_datashare_name 
   WITH PERMISSIONS 
   FROM DATASHARE 'datashare_name'
   OF ACCOUNT 'account_number' 
   NAMESPACE 'namespace_uuid';

   Note: Replace database_created_from_datashare_name with the name of your database and datashare_name with the name of your datashare. Replace account number with your AWS account number and namespace_uuid with your namespace's Universally Unique Identifier (UUID).

3. Run the following command to create the external schema:

   CREATE EXTERNAL SCHEMA external_schema_name 
   FROM REDSHIFT DATABASE database_created_from_datashare_name 
   SCHEMA schema_in_database_created_from_datashare;

   Note: Replace external_schema_name with the name of the external schema and database_created_from_datashare_name with the name of your database. Replace schema_in_database_created_from_datashare with the name of your schema that you created from a datashare.

4. Run the following command to grant usage permissions:

   GRANT USAGE on SCHEMA external_schema_name to user_name;
   GRANT USAGE ON SCHEMA database_created_from_datashare_name.schema_in_datashare_name TO user_name;

   Note: Replace external_schema_name with the external schema name. Replace user_name with the name of the user that you want to grant usage to. Replace database_created_from_datashare_name with the name of your database that you created from a datashare, and schema_in_datashare_name with the name of your schema.

5. Run the following SELECT command to grant permission to access specific tables:

   GRANT SELECT ON 
   TABLE database_created_from_datashare_name.schema_in_datashare_name.table_name 
   TO user_name;

   Note: Replace database_created_from_datashare_name with the name of the database that you created from a datashare and schema_in_datashare_name with the name of your schema. Replace table_name with the name of your table and user_name with your username.

   To grant access to all tables in the schema, run the following command:

   GRANT SELECT ON 
   ALL TABLES IN SCHEMA database_created_from_datashare_name.schema_in_datashare_name 
   TO user_name;

   Note: Replace database_created_from_datashare_name with the name of the database that you created from a datashare and schema_in_datashare_name with the name of your schema. Replace user_name with your username.

Resolve external schemas with Lake Formation permissions issues

Verify that you turned on Lake Formation for your AWS Glue Data Catalog, and your Amazon Redshift cluster's AWS Identity and Access Management (IAM) role has the required AWS Glue Data Catalog permissions.

For more information, see Redshift Spectrum and AWS Lake Formation and Managing Lake Formation permissions.

Grant granular access control with external schemas

Use Lake Formation or role chaining to grant granular access control in Amazon Redshift Spectrum. You can grant permissions at the schema, table, or column level.

For more information, see Restrict Amazon Redshift Spectrum external table access to Amazon Redshift IAM users and groups using role chaining and Granting permissions on Data Catalog resources.

Use the WITH NO SCHEMA BINDING clause when you create views with external tables

When you create a view that references an external table, you might receive the following error message:

"ERROR: External tables are not supported in views".

To resolve this issue, run the following command to create the view with the WITH NO SCHEMA BINDING clause:

CREATE OR REPLACE VIEW view_name AS 
SELECT * FROM external_schema_name.external_table_name 
WITH NO SCHEMA BINDING ;

Note: Replace external_schema_name with the external schema name and external_table_name with the name of the external table.

Related information

SVV_EXTERNAL_SCHEMAS

Parameters for using CREATE DATABASE with a datashare

Parameters