Group-user link via SQL

[rcmarcelo]

I've been trying to check, via databricks SQL, which groups a user belongs to.

When I run SHOW GROUPS or SHOW USERS, everything is returned correctly.

However, when a try to run:

SHOW GROUPS WITH USER `user@domain.com`

Nothing is returned, but the user belongs to one of the existing groups.

What is the right way to execute this query and get the expected results?

[Anonymous]

@Marcelo Castro​ :

To show the groups that a user belongs to in Databricks using SQL, you can use the following query:

SELECT groupname FROM databricks_groups WHERE userid IN (SELECT userid FROM databricks_users WHERE username = 'user@domain.com')

This query uses the databricks_users and databricks_groups tables to get the userid associated with the username and then returns the group names associated with that userid. You can replace 'user@domain.com' with the username of the user you want to check. This should return the group names that the user belongs to.

Note that you will need the appropriate privileges to query the databricks_users and databricks_groups

tables.

[Anonymous]

Hi @Marcelo Castro​ 

Hope all is well! Just wanted to check in if you were able to resolve your issue and would you be happy to share the solution or mark an answer as best? Else please let us know if you need more help. 

We'd love to hear from you.

Thanks!

[rcmarcelo · Accepted solution]

Hey! The actual solution was to run SHOW GROUPS WITH USER as an administrator. This wasn't expected, since SHOW USERS and SHOW GROUPS doesn't require administrator privileges and there's nothing mentioned in the docs. Thanks!