Databricks Unity Catalog Tutorial: Preventing Accidental Cross-Environment Access via External Location Credentials

This tutorial demonstrates how to use Unity Catalog’s external location credential protection to prevent accidental cross-environment access to data. We’ll focus solely on securing access to data stored in external locations within a Databricks SQL environment, using SQL commands and Unity Catalog features.

Scenario: Securing Access to an External Data Lake

Let’s assume you have a data lake (e.g., AWS S3, Azure Blob Storage, Google Cloud Storage) containing sales data. You want to allow specific Databricks SQL warehouses to access this data, but you want to prevent any other warehouse from accessing it directly. This example showcases setting up an external location with credential protection, limiting access to a specific warehouse, and validating the security.

Script 1: Creating the External Location and Credential Protection

This script creates the external location and defines the credential protection policy. This ensures that access is governed by the specific warehouse.

CREATE EXTERNAL LOCATION sales_lake
  DUAL
  LOCATION 's3://my-sales-bucket/sales-data/'
  WITH CREDENTIAL my_s3_credential;
 
CREATE CREDENTIAL my_s3_credential
  TYPE AWS_ACCESS_KEY_ID
  PROPERTY access_key_id = 'YOUR_ACCESS_KEY_ID'
  PROPERTY secret_key = 'YOUR_SECRET_KEY';

Explanation:

• `CREATE EXTERNAL LOCATION sales_lake …`: Creates a new external location named `sales_lake`.
• `DUAL LOCATION ‘s3://my-sales-bucket/sales-data/’`: Specifies the URI of the external location.
• `WITH CREDENTIAL my_s3_credential`: Associates the external location with a credential.
• `CREATE CREDENTIAL my_s3_credential …`: Creates a new credential named `my_s3_credential` of type `AWS_ACCESS_KEY_ID`, providing access key ID and secret key details. Important: Replace `YOUR_ACCESS_KEY_ID` and `YOUR_SECRET_KEY` with actual credentials for demonstration purposes. Never commit actual credentials to your code repository.

Script 2: Granting Access to a Specific Warehouse

This script grants access to the external location `sales_lake` to a designated Databricks SQL warehouse named `reporting_warehouse`. This is the ONLY warehouse that should have permission.

GRANT USAGE ON LOCATION sales_lake TO WAREHOUSE reporting_warehouse;

Explanation:

• `GRANT USAGE ON LOCATION sales_lake TO WAREHOUSE reporting_warehouse`: This command grants the `reporting_warehouse` the permission to use the `sales_lake` external location. Without this, `reporting_warehouse` wouldn’t be able to access the data in the external location, even if the credential was correctly set up.

Script 3: Attempting Access from a Restricted Warehouse

This script simulates an attempt to access the external location from a warehouse that should not have access (e.g., `analytics_warehouse`). This will fail, demonstrating the credential protection.

SELECT 
FROM sales_lake;

Explanation:

• This query attempts to read data from the `sales_lake` external location. Because `analytics_warehouse` was not granted access, this query will fail with an access denied error.

Validation and Output

The successful execution of Script 1 and Script 2, followed by the failure of Script 3, demonstrates the effectiveness of Unity Catalog’s external location credential protection.

{ "result": "Access Denied" }

Output: Access Denied