Privileges for SHOW GROUPS WITH USER '***@***'

[Cami]

Hello Briksters, 

i am looking for a giving a grand to developer being able execute with SHOW GROUPS WITH USER '***@***' without admin permission on UC.

Could you give any tips to do it? 

UC Admin is able to see result query, but developer is not. 

[Kaniz_Fatma]

Hi @Cami, To grant a developer the ability to execute the SHOW GROUPS WITH USER '***@***' query without requiring admin permissions on Azure Databricks, you can follow these steps:

1. Unity Catalog Privileges:

   • Unity Catalog is the metadata service in Azure Databricks. It allows you to manage access to data and other objects.
   • By default, users have no access to data in a metastore. You’ll need to grant specific privileges to allow the developer to execute the query.
   • Use the Unity Catalog privileges to control access to data and other objects within the metastore¹.

2. Metastore Admin Privileges:

   • The metastore admin is a highly privileged user or group in Unity Catalog.
   • Metastore admins have certain default privileges on the metastore, including the ability to create catalogs and connections to external databases.
   • However, you can customize these privileges to suit your requirements.
   • To grant the developer the necessary permissions, consider the following options:
     • CREATE CATALOG: Allows a user to create catalogs in the metastore.
     • CREATE CONNECTION: Allows a user to create a connection to an external database in a Lakehouse Feder...².

3. Specific Privilege for SHOW GROUPS WITH USER:

   • Unfortunately, there isn’t a direct privilege specifically for the SHOW GROUPS WITH USER command.
   • However, you can create a custom role or modify an existing one to grant the necessary permissions.
   • Here’s a high-level approach:
     • Create a custom role (e.g., “DeveloperRole”) with the required privileges.
     • Assign this role to the developer.
     • Ensure that the role includes the necessary permissions for querying group information.
     • Test the setup to ensure that the developer can execute the query without admin permissions.
     • Remember to restrict other unnecessary privileges to maintain security³.

4. Testing and Monitoring:

   • After granting the necessary privileges, test the setup thoroughly.
   • Verify that the developer can execute the SHOW GROUPS WITH USER '***@***' query successfully.
   • Monitor any additional queries executed by the developer to ensure compliance with security policies.

Remember to strike a balance between granting sufficient permissions for the developer’s tasks and maintaining security.

[Cami]

Thank you for your comprehensive answer.

I assume from what you have written that this cannot be done without admin permissions on the metastore.

So is there any other way to check who is in which group?