Problem

You are trying to understand the permissions of a user/group for all the resources on a workspace level. You can’t get them in one go and have to browse the permissions individually on resources.

Cause

When you want to remove a user or a group from a workspace, you want to make sure there are no significant resources that are associated with these users or groups before deleting them. This is because if a user or group owns a resource, deleting it without changing the ownership would impact the functionality.

Solution

You can fetch the permissions of the concerned user or group programmatically in one go by running an automation script in your workspace. 

This Python script uses the Databricks REST API to retrieve and display the members of a specified group and their access permissions to SQL warehouses. After authenticating with an API token, it fetches the group's name and members, then lists all SQL warehouses in the workspace. For each warehouse, it checks and prints the permissions granted to either a specified user or a group, helping admins review access rights across the environment. 

Example code (Python)

Replace the following before running this example code:

• <workpsace-url> (string) - This is your workspace URL. For example, myworkspace.cloud.databricks.com.
• <api-token> (string) - Enter the OAuth access token (AWS | Azure | GCP) you want to use to make the API call. 
• <group-id> (int) - The group ID of the group you want to fetch the permissions for.
• [Optional] <username> (string) - You can enter a username (example: user@domain.com) for a specific user if you want to fetch the permissions for that user.

%python

import requests
import json


instance = <workspace-url> # Replace with your workspace URL
api_token = <api-token>  # Replace with your API token
group_id = <group-id> #mention your group id
username = <username> #optionally enter a username if you want to fetch permissions for a user



# Set up request headers
headers = {
       "Authorization": f"Bearer {api_token}",
       "Content-Type": "application/json"
   }
  
# Fetch the list of users from the mentioned group
url_users_list = f"https://{instance}/api/2.0/preview/scim/v2/Groups/{group_id}"
response_users_list = requests.get(url_users_list, headers=headers)
users_list = response_users_list.json()['members']
group_name = response_users_list.json()['displayName']
print(f"Group Name: {group_name}")
  
# Print the users in this group
users = [member['display'] for member in users_list]
print("Members in this group:")
for user in users:
   print(f"Member (User/Group): {user}")


def list_warehouses_and_permissions(api_token, group_id, username=None):
   # Send the request to fetch the list of warehouses
   url_warehouses_list = f"https://{instance}/api/2.0/sql/warehouses"
   response_warehouses_list = requests.get(url_warehouses_list, headers=headers)
   warehouses_response = response_warehouses_list.json()
  
   # Check if warehouses are present in the response
   if "warehouses" not in warehouses_response:
       print("No warehouses found in the response.")
       return
  
   warehouses_list = warehouses_response["warehouses"]
  
   # Loop through the warehouse IDs and fetch the permissions for each one
   for warehouse in warehouses_list:
       warehouse_id = warehouse["id"]
       warehouse_name = warehouse["name"]
       #print(f"\nWarehouse ID: {warehouse_id}")
       #print(f"Warehouse Name: {warehouse_name}")
      
       # Send the request to fetch the permissions for the warehouse
       url_permissions = f"https://{instance}/api/2.0/permissions/warehouses/{warehouse_id}"
       response_permissions = requests.get(url_permissions, headers=headers)
      
       # Extract the JSON response
       response_json = response_permissions.json()
      
       # Loop through the access control list and find the matching user_name/group_name
       if 'access_control_list' in response_json:
           for acl in response_json['access_control_list']:
               if ('user_name' in acl and acl['user_name'] == username):
                   print(f"Access control list for user {username} on warehouse {warehouse_id}:")
                   print(json.dumps(acl['all_permissions'], indent=4))
               elif ('group_name' in acl and acl['group_name'] == group_name):
                   print(f"Access control list for group {group_name} on warehouse {warehouse_id}:")
                   print(json.dumps(acl['all_permissions'], indent=4))
       else:
           print("No access control list found in the response.")


# Call the function to list warehouses and fetch permissions for them
list_warehouses_and_permissions(api_token, group_id, username)

You can use this example code as a reference to create a similar script for all other workspace resources. For more information, review the Permissions API (AWS | Azure | GCP) documentation.