Set Owners(Python)

Import Notebook

This notebook allows you to quickly set the owner for objects that already exist in your metastore.

Attach to a cluster enabled with table access control.

There are 2 common patterns:

  • Use a single administrator or group of administrators. Configure new_owner Cmd 2 to that user or group.
  • Delegated security model, where each database has a different owner. In this case, run the notebook multiple times, configuring a different database/owner combination in Cmd 2 each time.
# User or group to change ownership to
new_owner = "<owner>"
 
# List of databases to set owner for
databases = [<database1>, <database2>]
#Get all objects from the metastore for databases
from pyspark.sql.functions import col,lit, concat
 
objects = spark.sql("SHOW TABLES FROM  %s" % databases[0])
for db in databases[1:]:
  tables = spark.sql("SHOW TABLES FROM  %s" % db)
  objects = objects.union(tables)
  
objects_w_grant = objects.withColumn("ownerGrant", concat(lit("ALTER TABLE "), col("database"), lit("."), col("tableName"), lit(" OWNER TO `"), lit(new_owner), lit("`;") ))
  
display(objects_w_grant.select("ownerGrant"))
#Copy the following `GRANT` statements and issue in a new Cmd prefixed by `%sql`
#Change database permissions
for db in databases:
  grant = "ALTER DATABASE %s OWNER TO `%s`;" % (db, new_owner)
  print(grant)
  
#Copy the following `GRANT` statements and issue in a new Cmd prefixed by `%sql`
%sql
-- Copy GRANT statements from output of Cmd 3 + 4 and run here
-- They should look something like:
-- ALTER TABLE <table> OWNER TO `<owner>`;
-- ALTER DATABASE <database> OWNER TO `<owner>`;