Databricks Unity Catalog Tutorial: Verify an External Location Before Granting a New Ingestion Team Access

This tutorial demonstrates how to leverage Unity Catalog’s access control features to verify an external location’s availability before granting access to a new ingestion team within a Databricks SQL environment.

The core concept is to define a location check that the ingestion team must satisfy before being granted permission to read data from the external location.

Script 1: Creating the External Location and Sample Data

This script sets up the external location and populates it with sample data. This provides a controlled environment for testing the access verification process.

CREATE EXTERNAL LOCATION external_location
  DUPLICATE TABLE external_location
    PATH '/FileStore/ingestion_data/'
    FILE FORMAT CSV
    HEADER TRUE
    FIELD QUOTE '"'
    ENCODING 'UTF8'
    LOCATION PROPERTY 'LocationType' = 'S3';
  
INSERT INTO TABLE sample_data
  SELECT 'item1', 10, 'value1'
  UNION ALL
  SELECT 'item2', 20, 'value2'
  UNION ALL
  SELECT 'item3', 30, 'value3';
  
SELECT  FROM sample_data;

Explanation:

• `CREATE EXTERNAL LOCATION external_location`: This statement defines an external location accessible through Databricks SQL. The `DUPLICATE TABLE` clause makes a copy of the data directly in `/FileStore/ingestion_data/`. The `LOCATION PROPERTY` specifies the location type as S3 to allow the ingestion team to connect.
• `INSERT INTO TABLE sample_data`: This statement populates a simple table called `sample_data` with sample data. This data will be used for the verification step.
• `SELECT FROM sample_data`: This statement displays the data in the `sample_data` table.

The script creates an external location and populates it with some data. This serves as the source that the ingestion team will eventually access.

SELECT FROM sample_data;

item1 10 value1
item2 20 value2
item3 30 value3

Script 2: Defining the Location Check (SQL View)

This script creates a SQL view that checks if the external location is accessible. This view acts as the gatekeeper for access, ensuring the location is reachable before granting permissions. It effectively implements the “Verify an External Location” requirement.

CREATE OR REPLACE VIEW location_check_view AS
SELECT
    
FROM
    EXTERNAL_LOCATION
WHERE
    LOCATION_PROPERTY('LocationType') = 'S3';
    
SELECT  FROM location_check_view;

Explanation:

• `CREATE OR REPLACE VIEW location_check_view`: This statement creates a view named `location_check_view`. The `OR REPLACE` clause ensures that the view is overwritten if it already exists, which is helpful for iterative development.
• `SELECT FROM EXTERNAL_LOCATION WHERE LOCATION_PROPERTY(‘LocationType’) = ‘S3’`: This query defines the core logic of the view. It filters the external location to only include locations where the `LocationType` property is equal to ‘S3’. This enforces the location verification requirement.
• `SELECT FROM location_check_view`: This query displays the contents of the `location_check_view`. The view will only contain rows representing external locations of type ‘S3’.

This view checks that the external location is of type ‘S3’, enforcing the access verification requirement. The result of this view will be used in subsequent steps.

SELECT FROM location_check_view;

location_property
——————–
LocationType = S3

Script 3: Granting Access and Validating the Location Check

This script grants access to the `ingestion_team` to the `sample_data` table, but only after the `location_check_view` confirms the location is accessible. This demonstrates the flow of access control governed by Unity Catalog.

GRANT SELECT ON TABLE sample_data TO ingestion_team;

SELECT  FROM location_check_view;

SELECT  FROM sample_data;

Explanation:

• `GRANT SELECT ON TABLE sample_data TO ingestion_team`: This statement grants the `ingestion_team` the `SELECT` privilege on the `sample_data` table. Because the location check in `location_check_view` is already satisfied, the access is granted.
• `SELECT FROM location_check_view`: This query displays the contents of the `location_check_view`. The view will still only contain rows representing external locations of type ‘S3’.
• `SELECT FROM sample_data`: This statement displays the data in the `sample_data` table, accessible now to the `ingestion_team`.

This script first grants access and then confirms that the location check continues to pass, demonstrating that access control is enforced at the location level.

SELECT FROM location_check_view;

location_property
——————–
LocationType = S3

SELECT FROM sample_data;

item1 10 value1
item2 20 value2
item3 30 value3