Metadata Driven Unity Catalog Permissions

Metadata driven permission for UC Securables

In this article, I would like share my experience with unity Catalog permissions model, which I have implemented for a quick POC. This concept has allowed me to efficiently manage the unity Catalog permissions using metadata and custom python function. It includes granting or revoking permission to/from the UC securable using metadata (Json File). You can find more details on the Unity Catalo privileges here.

Unity Catalog privileges and securable objects - Azure Databricks

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

Pre-requisites

• Catalog
• All Privileges at Catalog level
• Users or Group

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

Let me walk you through the implementation. There are four steps, which are detailed below:

1. Gather Requirements.

Gaither requirement for your UC permissions. I have taken simple example here, It contains group name, schema and function for granting access. However the custom python function will work for all UC securables. Technically speaking Bronze, Silver and Golden are layers in my case. Decrypt is a function which allows to decrypt encrypted fields.

• GroupName : Group name for granting access.
• Schema : schema such as Bonze, Silver, Gold and GDPR
• Functions: Decrypt

|   GroupName    | Bronze | Silver | Gold | Gdpr | Decrypt |
|----------------|--------|--------|------|------|---------|
| UC-Admin-Te    | rw     | rw     | rw   | rw   | x       |
| UC-Dev-Te      | rw     | rw     | r    | w    |         |
| UC-Test-Te     | r      | r      | r    | r    | x       |
| UC-Customer-Te |        |        |      |      |         |

2.Prepare Metadata

• UnityCatalogSecurable : It is Parent level Securable
• Description : Provide description for a reference.
• UsersOrGroup : List user name or group to you would like to grant permissions.
• UnityCatalogObjects : It is a securable where you would like to grant permission to the user or groups. it includes Catalog, schema, tables, views, volume, External Locations, Storage Credentials, Connections, Functions, Registered Models and Shares.

Example of Metadata File.

permissions = [
  {
    "UnityCatalogSecurable": "Catalog",
    "Description": "Gives use catalog privileges",
    "UsersOrGroups": ["UC-Admin-Te"],
    "UnityCatalogObjects": [
      {
        "Catalog": [
          {
            "env_te": ["USE CATALOG","USE SCHEMA"]
          }
        ],
        "Schema": [
          { "silver": ["MODIFY"] },
          { "gdpr": ["MODIFY"] },
          { "gold": ["MODIFY"] }
        ],
        "Tables": [],
        "Views": [],
        "Volume": [],
        "External Location": [
          { "non-pii-te": ["READ FILES", "WRITE FILES"] },
          { "configs-te": ["READ FILES", "WRITE FILES"] }
        ],
        "Storage Credential": [],
        "Connection": [],
        "Function": [{ "decrypt": ["EXECUTE"] }],
        "Registered Model": [],
        "Share": []
      }
    ]
  },
  {
    "UnityCatalogSecurable": "Catalog",
    "Description": "gives use catalog privileges",
    "UsersOrGroups": ["UC-Dev-Te"],
    "UnityCatalogObjects": [
      {
        "Catalog": [
          {
            "env_te": ["USE CATALOG","USE SCHEMA"]
          }
        ],
        "Schema": [
          { "silver": ["MODIFY"] },
          { "gdpr": ["MODIFY"] },
          { "gold": ["MODIFY"] }
        ],
        "Tables": [],
        "Views": [],
        "Volume": [],
        "External Location": [
          { "non-pii-te": ["READ FILES", "WRITE FILES"] },
          { "configs-te": ["READ FILES", "WRITE FILES"] }
        ],
        "Storage Credential": [],
        "Connection": [],
        "Function": [],
        "Registered Model": [],
        "Share": []
      }
    ]
  },
  {
    "UnityCatalogSecurable": "Catalog",
    "Description": "gives use catalog privileges",
    "UsersOrGroups": ["UC-Test-Te"],
    "UnityCatalogObjects": [
      {
        "Catalog": [
          {
            "env_te": ["USE CATALOG","USE SCHEMA"]
          }
        ],
        "Schema": [
          { "silver": ["SELECT"] },
          { "metadata": ["SELECT"] },
          { "gdpr": ["SELECT"] },
          { "gold": ["SELECT"] }
        ],
        "Tables": [],
        "Views": [],
        "Volume": [],
        "External Location": [
          { "non-pii-te": ["READ FILES", "WRITE FILES"] },
          { "configs-te": ["READ FILES", "WRITE FILES"] }
        ],
        "Storage Credential": [],
        "Connection": [],
        "Function": [{ "decrypt": ["EXECUTE"] }],
        "Registered Model": [],
        "Share": []
      }
    ]
  },
  {
    "UnityCatalogSecurable": "Catalog",
    "Description": "gives use catalog privileges",
    "UsersOrGroups": ["UC-Edl-Te"],
    "UnityCatalogObjects": [
      {
        "Catalog": [
          {
            "env_te": ["USE CATALOG","USE SCHEMA"]
          }
        ],
        "Schema": [
          { "silver": ["MODIFY"] },
          { "metadata": ["MODIFY"] },
          { "gdpr": ["MODIFY"] },
          { "gold": ["MODIFY"] }
        ],
        "Tables": [],
        "Views": [],
        "Volume": [],
        "External Location": [],
        "Storage Credential": [],
        "Connection": [],
        "Function": [{ "encrypt": ["EXECUTE"] }],
        "Registered Model": [],
        "Share": []
      }
    ]
  },
  {
    "UnityCatalogSecurable": "Catalog",
    "Description": "gives use catalog privileges",
    "UsersOrGroups": ["UC-Customer-Te"],
    "UnityCatalogObjects": [
      {
        "Catalog": [
          {
            "env_te": ["USE CATALOG","USE SCHEMA"]
          }
        ],
        "Schema": [
          { "silver": ["SELECT"] },
          { "gold": ["SELECT"] }
        ],
        "Tables": [],
        "Views": [],
        "Volume": [],
        "External Location": [],
        "Storage Credential": [],
        "Connection": [],
        "Function": [{ "decrypt": ["EXECUTE"] }],
        "Registered Model": [],
        "Share": []
      }
    ]
  }
]

3. Apply Permissions (Grant or Revoked) using Python Function.

1. This function is used to grant or revoke permission to users for a list of Unity Catalog securable
2. The function takes two inputs, the first being a list of dictionaries containing information about the permissions
3. The second input is a Boolean value, grant_or_revoked, which determines whether the function should grant or revoke access. True will grant permissions and False will revoke permissions.
4. The function loops through the dictionaries and for each dictionary, loops through the Unity Catalog Objects
5. The function then iterates over the items in the Unity Catalog Objects and constructs a GRANT or REVOKE statement depending on the value of the ‘grant_or_revoked’ parameter
6. The statement constructed is then executed using spark.sql and the result is displayed
7. If the ‘grant_or_revoked’ is neither True nor False, nothing happens and None is returned
8. Grant or remove permission to users to a given list of Unity Catalog securables.

Args:
permissions (list): List of dictionary objects containing the permission details.
grant_or_revoked (bool): True if access should be granted, False if revoked.

Returns:
None

def grant_or_revoke_permission_uc_securables(permissions,grant_or_revoked):
    """
    1. This function is used to grant or revoke permission to users for a list of Unity Catalog securables
    2. The funcion takes two inputs, the first being a list of dictionaries containing information about the permissions
    3. The second input is a boolean value, grant_or_revoked, which detemines whether the function should grant or revoke access
    4. The function loops through the dictionaries and for each dictionary, loops through the Unity Catalog Objects
    5. The function then iterates over the items in the Unity Catalog Objects and constructs a GRANT or REVOKE statement depending on the value of 
       the  'grant_or_revoked'     parameter
    6. The statement constructed is then executed using spark.sql and the result is displayed
    7. If the 'grant_or_revoked' is neither True nor False, nothing happens and None is returned
    8. Grant or remove permission to users to a given list of Unity Catalog securables.

    Args:
        permissions (list): List of dictionary objects containing the permission details.
        grant_or_revoked (bool): True if access should be granted, False if revoked.
        
    Returns:
        None      
    """
    # iterates through each permission
    for p in permissions:
        # iterates through each object in the "UnityCatalogObjects" key of the permission dictionary
        for o in p['UnityCatalogObjects']:
            # iterates through each object in the nested object key
            for key, value in o.items():
                # set the SQL object type
                sql_object_type = key.upper()
                print(f"{key.upper()} : {value}\n")
                # iterate through each table and its permission
                for table_obj in value:
                    table_key = list(table_obj.keys())[0]
                    table_val = table_obj[table_key]
                    # iterate through each user and its privilege
                    for user in p['UsersOrGroups']:
                        for privilege in table_val:
                            if grant_or_revoked == True:
                               query = f"GRANT {privilege} ON {sql_object_type} `{table_key}` TO `{user}`"
                               print(query)
                               spark.sql(query).show()
                            elif grant_or_revoked == False:
                                query = f"REVOKE {privilege} ON {sql_object_type} `{table_key}` FROM `{user}`"
                                print(query)
                                spark.sql(query).show()

##########################################################################

grant_or_revoke_permission_uc_securables(permissions,True)

4. Validate Permissions. (Databricks Python Notebook Command)

The below code is written in Python and uses PySpark to get granted permission from each schema.

The code first imports the required PySpark libraries and defines a schema for a Spark DataFrame. The schema has four fields: Principal, Action Type, Object Type, and Object Key with their respective data types.

Next, the code creates an empty DataFrame with this

StructType([StructField('Principal', StringType(), False), StructField('ActionType', StringType(), False), StructField('ObjectType', StringType(), False), StructField('ObjectKey', StringType(), False)])

schema. Then, it iterates over a list of schemas and gets the database name from each schema. Using the database name, it queries the database permissions from a Catalog named env_te.

Finally, the code unionizes the queried permissions data with the empty DataFrame and filters any rows where the Principal column starts with “UC”. The resulting DataFrame is then displayed using the display() function.

from pyspark.sql.types import StructType, StructField, StringType

spark.sql("use catalog uc_te")
schemas = spark.sql("show schemas").collect()

schema       = StructType([StructField('Principal', StringType(), False), StructField('ActionType', StringType(), False), StructField('ObjectType', StringType(), False), StructField('ObjectKey', StringType(), False)])
catalog_name = 'env_te'
df           = spark.createDataFrame([], schema)

for sch in schemas:
    databaseName = sch["databaseName"]
    print(databaseName)
    permissions = spark.sql(f"show grants on schema {catalog_name}.{databaseName}")
    df = df.union(permissions)

display(df.filter("Principal like('UC%')"))

In four steps, I quickly manged to apply UC Permissions to user or group to the unity Catalog securables. I hope this will help you to manage UC Permissions using metadata.

Thanks