The DATALAKE object requires two Authorization objects to be specified in the <auth-list> – one for the Catalog connection, and one for the Storage connection. The CREATE AUTHORIZATION DDL can be used to create the authorization objects. The AUTHORIZATION object holds the AWS credentials i.e. the access key id (user) and access secret key (password) of the service principal that’s trying to access services and resources in AWS. It is possible that the Storage & Catalog credentials are the same (For example: when IAM Roles are used for Amazon Glue/S3). In that case, the same Authorization object can be used for the Catalog and Storage Authorization definitions.
For more details on the CREATE AUTHORIZATION DDL, see CREATE AUTHORIZATION and REPLACE AUTHORIZATION Examples.
Syntax
CREATE AUTHORIZATION [ database_name. | user_name. ] authorization_name [ AS [ DEFINER | INVOKER ] TRUSTED ] USER ‘<username>’ PASSWORD ‘<password>’
Example: Creating a Simplified Authorization
Creating DATALAKE now supports simplified authorization which implies that no security constraints (INVOKER or DEFINER) need to be mentioned. This object is available for use for any user/database provided that are granted proper privileges. This is the recommended approach when creating Authorizations.
- GRANT and REVOKE statements will be used to support EXECUTE privilege on Authorization objects that were created without an external security type (INVOKER, DEFINER, TRUSTED).
- CREATE DATALAKE is enhanced to refer to an authorization object without a security type INVOKER TRUSTED, DEFINER TRUSTED or TRUSTED.
- If the data lake is using simplified Authorization objects, the user must have EXECUTE privileges on the data lake catalog and Storage Authorization objects.
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+- #CREATE A SIMPLIFIED AUTHORIZATION +---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+- CREATE AUTHORIZATION user1.simplfied_auth USER '<User Name>' PASSWORD '<Password>'; +---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+- #GRANT Execute privilege on Auth +---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+- grant execute on user1.simplfied_auth to user2 with grant option; +---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+- #Iceberg Example : CREATE DATALAKE with Simplified Authorization +---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+- CREATE DATALAKE catalog_glue EXTERNAL SECURITY CATALOG user1.simplfied_auth, EXTERNAL SECURITY STORAGE user1.simplfied_auth USING storage_location ('<Storage Location>') storage_region ('us-west-2') catalog_type ('glue') TABLE_FORMAT iceberg; +---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+- #Delta Lake Example : CREATE DATALAKE with Simplified Authorization +---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+- CREATE DATALAKE catalog_unity EXTERNAL SECURITY CATALOG user1.simplfied_auth, EXTERNAL SECURITY STORAGE user1.simplfied_auth USING catalog_location ('<Catalog Location>') storage_location ('<Storage Location>') storage_region ('us-west-2') unity_catalog_name_('unity_db') storage_account_name ('storageacct') tenant_id ('391c8c4c-6a3a-40fd-ab98-226b6baa5155') catalog_type ('unity') TABLE_FORMAT deltalake;
Example: Creating an AWS Assume Role Authorization
The AUTHORIZATION object holds the IAM role of the service principal that’s trying to access services and resources in AWS. This service principal must have STS: AssumeRole permission defined in the Trust Relationship of the IAM Role. As a best practice, it's recommended to use an Assume Role Policy with an external ID to enhance the security of the IAM role.
CREATE AUTHORIZATION assume_role_aws USING AUTHSERVICETYPE 'ASSUME_ROLE' ROLENAME 'IAM_role_literal' EXTERNAL_ID 'ARN_external_id_literal';
Example: Creating an Azure Active Directory Service Principal Authorization
The Authorization object for Azure contains the Azure Active Directory service principal client id (user) and the Azure Active Directory service principal client secret key(password). For Azure, managed Active Directory is recommended for Unity catalog and ADLS Gen2 storage access.
CREATE AUTHORIZATION iceberg_invoker_unity AS INVOKER TRUSTED -- Azure AD service principal client id USER '0123abc-678-1234-abcd-123456789' -- Azure AD service principal client secret key PASSWORD 'abcdefg!.hijklmn.opqsrtst';