Databricks SQL: Grant Least-Privilege Catalog Access

This tutorial demonstrates how to grant least-privilege access to a Databricks SQL catalog using SQL commands. We’ll focus on a practical scenario and build up the permissions incrementally.

Script 1: Create a Sample Catalog and User

This script creates a simple catalog and a user that we’ll use for granting permissions. We’ll ensure that the user has the necessary privileges to explore the catalog.


CREATE CATALOG IF NOT EXISTS my_catalog
  WITH CODE_ENCRYPTION = OFF;

CREATE SCHEMA IF NOT EXISTS my_schema
  IN my_catalog;

CREATE USER IF NOT EXISTS my_user
  WITH PASSWORD = 'your_password';

GRANT USAGE ON CATALOG my_catalog TO USER my_user;
GRANT USAGE ON SCHEMA my_schema TO USER my_user;

SELECT 'Catalog and schema created, user granted initial usage.' AS message;

Explanation:

`CREATE CATALOG IF NOT EXISTS my_catalog WITH CODE_ENCRYPTION = OFF;`: Creates a catalog named `my_catalog` if it doesn’t exist. Disabling code encryption simplifies the example for demonstration purposes.
`CREATE SCHEMA IF NOT EXISTS my_schema IN my_catalog;`: Creates a schema named `my_schema` within the `my_catalog` catalog.
`CREATE USER IF NOT EXISTS my_user WITH PASSWORD = ‘your_password’;`: Creates a user named `my_user` with a password. Replace `’your_password’` with a strong password.
`GRANT USAGE ON CATALOG my_catalog TO USER my_user;`: Grants the `my_user` user the ability to access and use the `my_catalog` catalog.
`GRANT USAGE ON SCHEMA my_schema TO USER my_user;`: Grants the `my_user` user the ability to access and use the `my_schema` schema within the `my_catalog` catalog.
`SELECT ‘Catalog and schema created, user granted initial usage.’ AS message;`: A final check to confirm the script executed successfully.

The `GRANT USAGE` statements provide the user with fundamental access rights—the ability to read and execute SQL within the defined catalog and schema. This is the minimal level of access needed to start working with the data.

Script 2: Grant SELECT Privilege on a Table

This script demonstrates granting a more specific privilege – `SELECT` – to the user on a table within the schema.


CREATE TABLE IF NOT EXISTS my_table
  IN my_schema
  ROW FORMAT DELIMITED
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY 'n'
  STORED AS TEXTFILE
  AS
  SELECT 'value1', 'value2', 'value3' AS col1, 'value4' AS col2;

GRANT SELECT ON TABLE my_table TO USER my_user;

SELECT 'SELECT privilege granted on my_table.' AS message;

Explanation:

`CREATE TABLE IF NOT EXISTS my_table IN my_schema ROW FORMAT DELIMITED …`: Creates a simple table named `my_table` inside the `my_schema` schema. This table is created with a basic structure to allow the granting of `SELECT` privilege.
`GRANT SELECT ON TABLE my_table TO USER my_user;`: Grants the `my_user` user the `SELECT` privilege on the `my_table` table. This allows the user to read data from the table.
`SELECT ‘SELECT privilege granted on my_table.’ AS message;`: Confirms the privilege was granted.

This script extends the previous example by explicitly granting the `SELECT` privilege. This enables the user to retrieve data from the table.

Script 3: Grant ALL PRIVILEGES on Schema (Demonstrating Least Privilege)

This script demonstrates granting `ALL` privileges on a schema. This demonstrates the principle of least privilege – granting only what’s absolutely necessary. While it is often discouraged in production due to potential risks, it’s used here to show the level of permission granted. Consider carefully before implementing this in a real environment.


GRANT ALL ON SCHEMA my_schema TO USER my_user;

SELECT 'ALL privileges granted on my_schema.' AS message;

Explanation:

`GRANT ALL ON SCHEMA my_schema TO USER my_user;`: Grants the `my_user` user `ALL` privileges on the `my_schema` schema. This means the user can perform any operation within the schema, including creating tables, altering tables, dropping tables, reading data, etc.
`SELECT ‘ALL privileges granted on my_schema.’ AS message;`: Confirms the privilege was granted.

This script is the culmination of the tutorial. It illustrates the granting of the most comprehensive level of access, demonstrating the principle of least privilege. In a production environment, you should always strive to grant only the minimum privileges necessary for a user to perform their tasks. The `ALL` grant is included purely for demonstration of the capability and should not be used in production.

Output


'Catalog and schema created, user granted initial usage.'


'SELECT privilege granted on my_table.'


'ALL privileges granted on my_schema.'