Permission denied error when creating external location

You must be a metastore admin or have the CREATE EXTERNAL LOCATION privilege.

Written by John.Lourdu

Last published at: January 11th, 2023

Problem

An external location is a storage location, such as an S3 bucket, on which external tables or managed tables can be created. A user or group with permission to use an external location can access any storage path within the external location without direct access to the storage credential. Review the Manage external locations and storage credentials documentation for more information.

You are trying to create an external location when it fails due to a permission denied error message.

PERMISSION_DENIED: User does not have CREATE EXTERNAL LOCATION on Metastore <metastore_name>

Cause

You do not have the necessary permissions to create an external location.

The user should be a metastore admin or he/she should have the CREATE EXTERNAL LOCATION privilege in order to create external locations. 

Solution

Ask a metastore admin to give you the CREATE EXTERNAL LOCATION privilege on the METASTORE.

  • Show the existing permissions for the specified user on the metastore.
%sql

SHOW GRANTS `<user-name>` on METASTORE;
  • Give the specified user CREATE EXTERNAL LOCATION permissions on the metastore.
%sql

GRANT CREATE EXTERNAL LOCATION ON METASTORE TO `<user-name>`;
  • Show all permissions for access to the metastore.
%sql 

SHOW GRANTS ON METASTORE;