How-to Introduction

You have several catalogs in your environment and want to delete the catalogs you don't need. Currently to delete a catalog, you must grant ALL privileges to a user or group on specific catalogs one at a time in the UI. 

You want a way to bulk-assign permission to multiple catalogs at once.

Instructions

In Unity Catalog, it is not possible to directly grant or retrieve ALL privileges on all, or multiple, catalogs with a single query due to security and granularity considerations. 

Instead, you can use Python to get a catalog list and loop through each catalog explicitly to apply GRANT statements, and then programmatically delete catalogs one at a time. 

First, run the following code to gather and store your catalog names in a Python list.  

catalogs = spark.sql("show catalogs").collect()
display(catalogs)

# Convert the collected results to a Python list
catalog_list = [row['catalog'] for row in catalogs]

# Print the list
print(catalog_list)

Then use the following script to loop through the catalogs and give a user access as required to then delete. This code grants ALL PRIVILEGES access, but you can adjust to the level of access you choose. The code then drops the catalogs individually which you now have permission to drop.

catalog_list = ['<example-catalog1>', '<example-catalog2>']
 
for catalog_name in catalog_list:
   if catalog_name not in ["samples"]: # Exclude sample catalogs
    grant_query = f"GRANT ALL PRIVILEGES ON CATALOG {catalog_name} TO `<user-email>`"
    spark.sql(grant_query)
    print(f"Permissions granted to {catalog_name} successfully.")

drop_query = f"DROP CATALOG IF EXISTS {catalog_name} CASCADE"
    spark.sql(drop_query)
    print(f"Catalog {catalog_name} dropped successfully.")