Principals given access to and their owners

[noorbasha534]

Hi all

In a large global data platform built with Azure Databricks, I like to know the best practice of how we maintain the users to which Databricks objects (typically views) have been access to, for example - a view has been given access to a service principal and there are few stakeholders associated with it, say 3 or 4 in number, to whom I should communicate in case we are changing the view definitions and so on.

appreciate the needful.

[jameshughes]

@noorbasha534 - I created a helper function/script to do this in my environment that queries the Unity Catalog system tables to generate a unique list of impacted principals/users.  It takes in a list of fully qualified object names and will display a unique list of principals that have access to one or more of the specified objects.  It just looks at Tables and Views for now, but could be expanded.

def getImpactedPrincipals(objectList: str):

    # Split and clean the object names
    fullObjectNames = [obj.strip() for obj in objectList.split(",") if obj.strip()]
    
    if not fullObjectNames:
        raise ValueError("No valid object names provided.")

    allGrants = None

    for fullObjectName in fullObjectNames:
        try:
            catalog, schema, objectName = fullObjectName.split(".")
        except ValueError:
            raise ValueError(f"Invalid object name format: '{fullObjectName}'. Use 'catalog.schema.object_name'.")

        table_info_df = spark.sql(f"""SELECT table_type FROM system.information_schema.tables 
            WHERE table_catalog = '{catalog}'
            AND table_schema = '{schema}'
            AND table_name = '{objectName}'""")
        
        table_info = table_info_df.first()

        if not table_info:
            print(f"Warning: Object `{fullObjectName}` not found — skipping.")
            continue

        objectType = table_info['table_type'].upper()

        if objectType == 'MANAGED' or objectType == 'VIEW':

            grantObject = "TABLE" if objectType == "MANAGED" else objectType

            try:
                grants = spark.sql(f"""SHOW GRANTS ON {grantObject} `{catalog}`.`{schema}`.`{objectName}`""")
            except Exception as e:
                print(f"Error querying grants for {fullObjectName}: {e}")
                continue

            # Union results
            if allGrants is None:
                allGrants = grants
            else:
                allGrants = allGrants.unionByName(grants)

        if allGrants is None:
            raise Exception("No valid grants found for any objects.")
    return allGrants.select("principal").distinct()

objectList = "catalogname.schemaname.objectname1,catalogname.schemaname.objectname2"
principals = getImpactedPrincipals(objectList)

display(principals)

[noorbasha534]

@jameshughes Thanks much for the reply. Do you also maintain somehow the contact information of those who needs to be notified? Or, any ideas there?

[jameshughes]

@noorbasha534 - It can get a little complex to deconstruct the exact contact information, but I have some work in process that I will be publishing an article on next week.  At a high-level once you have the list of principals, you then have to use the Databricks Account API to interrogate further and potentially use the Microsoft Graph API to deconstruct group membership.  The article I will publish is more focused on Key Vault Backed Secret Store permissions management, but the same API calls to get information on principals is there.  I'll tag you when I publish it as you can swipe the code.