How to Retrieve Admin and Non-Admin Permissions at Workspace Level in Azure Databricks

Question

I am working on a project to document permissions for both admins and non-admin users across all relevant objects at the workspace level in Azure Databricks (e.g., tables, jobs, clusters, etc.).

I understand that admin-level permissions might be partially available through information schema, but detailed permissions for non-admin users likely require the Databricks REST API.

Could you guide me on:

1. The most efficient way to retrieve and document these permissions?
2. Whether there are built-in tools or queries for this purpose?
3. Any recommended practices for structuring this data in a report?

Thank you!

Answer 1

For both admins and non-admin users across all relevant objects at the workspace level in Azure Databricks, you can follow these steps:

you can use Databricks INFORMATION_SCHEMA queries to retrieve information about tables and database-level permissions. The INFORMATION_SCHEMA is a SQL standard-based schema provided in every catalog created on Unity Catalog.

Query to Retrieve Table Permissions:

SELECT 
    table_catalog,
    table_schema,
    table_name,
    grantee,
    privilege_type- [ ] 
FROM 
    information_schema.table_privileges
WHERE 
    table_schema = 'your_schema_name'

The above query will return the catalog, schema, table name, grantee (user or group), and the type of privilege granted for each table in the specified schema.

Query to Retrieve Database-Level Permissions:

SELECT 
    catalog_name,
    schema_name,
    grantee,
    privilege_type
FROM 
    information_schema.schema_privileges
WHERE 
    schema_name = 'your_schema_name'

Results:

**Table Privileges:**
+-------------+------------+------------+-----------------+--------------+
|table_catalog|table_schema|  table_name|          grantee|privilege_type|
+-------------+------------+------------+-----------------+--------------+
|     catalog1|     schema1| sales_table|user1@example.com|        SELECT|
|     catalog1|     schema1| sales_table|user2@example.com|        INSERT|
|     catalog1|     schema1|orders_table|       team_sales|        SELECT|
|     catalog1|     schema1|orders_table|user3@example.com|        DELETE|
+-------------+------------+------------+-----------------+--------------+


**Schema Privileges:**
+------------+-----------+-----------------+--------------+
|catalog_name|schema_name|          grantee|privilege_type|
+------------+-----------+-----------------+--------------+
|    catalog1|    schema1|admin@example.com|        CREATE|
|    catalog1|    schema1|user1@example.com|         USAGE|
+------------+-----------+-----------------+--------------+

Regarding the Clusters Use the Clusters API to list clusters and their permissions. Use the Jobs API to list jobs and their permissions.