Extract all users from Databricks Groups

[steveKris]

Hey everyone,

we are trying to get an overview of all users that we have in our databricks groups. We have tried to do so with the REST API as well as the SQL-queries (with normal developer accounts as well as workspace administrator accounts). The problem was, that we had some users that were visible in the Databricks UI but not when using the above methods. Has anyone any experience on this subject and could help us?

Thanks a lot and greetings from Bolzano

Moritz & Stefan

[szymon_dybczak · Accepted solution]

Hi @steveKris ,

Maybe some of your groups are in account level? So for example for groups defined at workspace level you need to use following rest api call (workspace level):

But you have also api endpoint that will give you details of a group at account level:

[Raman_Unifeye · Accepted solution]

UI shows all provisioned users, but REST/SQL only expose subsets depending on whether you query account vs workspace vs UC. To get a true overview, you need to combine account SCIM API + workspace SCIM API + UC system tables.

[Hubert-Dudek]

Sometimes I just loop the workspace folder to get all users:

students = [d for d in WORKSPACE_ROOT.iterdir()
            if d.is_dir()
            and "@" in d.name.lower()]
print(students)

[Hubert-Dudek]

All just get group from Azure API when I am using Azure groups which is now popular with just in time provisioning.

authority_url = f"https://login.microsoftonline.com/{TENANT_ID}"
app = msal.ConfidentialClientApplication(CLIENT_ID, authority=authority_url, client_credential=CLIENT_SECRET)
token_result = app.acquire_token_for_client(scopes=["https://graph.microsoft.com/.default"])
if not token_result.get("access_token"):
    raise Exception("Failed to obtain Graph API token")
access_token = token_result["access_token"]
headers = {"Authorization": f"Bearer {access_token}", "Content-Type": "application/json"}

group_url = f"https://graph.microsoft.com/v1.0/groups/{GROUP_ID}/members/$ref"
resp_add = requests.get(group_url, headers=headers)

[szymon_dybczak]

i

[Poorva21]

Use the Databricks SQL system users table

SELECT * FROM system.users

Only shows fully provisioned users

Users pending invitation may not appear.