Skip to content

Instantly share code, notes, and snippets.

@rtempleton

rtempleton/snowflake_database_ddl.py

Last active February 23, 2024 16:54
Show Gist options
  • Save rtempleton/8200729ba84afda63ee9bb106cdd3932 to your computer and use it in GitHub Desktop.
Save rtempleton/8200729ba84afda63ee9bb106cdd3932 to your computer and use it in GitHub Desktop.
Download ZIP
Sample Python script to loop through all the database objects and generate the DDL to replicate an environment prior to account replication being made available.
Raw
snowflake_database_ddl.py
#!/usr/bin/env python
# Get all the database objects and permissions.
# Can be used after running snowflake_roles.py to create the required roles
# 2018-10-23 jfrink added ddl for roles, modified connection parameters
# 2019-01-15 jfrink added Roles and permissions report by object.
# 2019-03-07 jfrink added extract script to create a dump of all the tables to a stage
# and also the corresponding script to load all the data.
# Converted show tables over to using information schema for cases greater then 10k rows.
# Converted show views over to using information schema for cases greater then 10k rows.
# Added Pipes to the output
# Added change to output if sqlfile is provided.
# 2019-03-11 jfrink Fixed grants of objects privs, and create schema having use schema before create.
# 2019-03-12 jfrink Added support for specifying the schema
# 2019-03-27 jfrink Added support for Procedures, this may change with snowflake information schema changes.
# 2019-04-10 jfrink Fixed sorting issue with 1-Database, 10-Pipes. used 01, 02, ..,10 to resolve.
# 2019-07-22 jfrink Added STREAMS, fixed PIPE to use get_ddl function now that it supports it.
# 2019-07-22 jfrink Added export Role Hierarchy to JSON.
# 2019-08-15 jfrink Fixed UDF's to leverage the new calling method.
# 2019-08-20 jfrink Added output files in CSV for roles
# 2019-08-21 jfrink Added support to set the role for executing the script, instead of the default role for the user.
# 2019-09-04 jfrink Removed the output of the extract and load ddl if sqlfile is not passed in as an argument.
# 2020-04-09 jfrink Adjusted the filename for export/load to address same table name in multiple schemas. --Added fully qualified DB.SCHEMA.objectname
# Changed the extract of DDL to have views at the end of the list to accomidate views that span databases.
# 2020-04-10 jfrink Added a parameter switch to ignore grants to shares for objects if this option is specified.
# 2020-06-04 jfrink Fixed bug on get_grants_to_role to include account level parameters and warehouse parameters.
# 2020-06-04 jfrink Added support to create role definitions if the roles only option is used with sqlfile parameter.
# 2020-07-09 rtempleton Added support for warehouse definitions
#
#
# Version 1.1.9
#
# -> ./snowflake_database_ddl_v3.py demoxx load_wh peter -db SALES -r -c -u -sqlfile sales_db.sql
#
# usage: ./snowflake_database_ddl_v3.py [-h] [-execrole ROLE] [-db DATABASE] [-schema SCHEMA] [-sqlfile SQLFILE]
# [-r] [-ro] [-u] [-ignoreshare] [-c] [-v] [-m MFA]
# Required parameters: account warehouse user
#
################################################################
# NOTE: This script is for educational purposes only.
# It is not meant to be run on production systems without
# modification and testing.
# Snowflake Inc. assumes no responsibility for use of
# this script.
################################################################
#import sys
#import os, glob, errno
import getpass
import argparse
import snowflake.connector
import re
#import json
# Verbose processing
def show_verbose(verbose, text_to_print):
if verbose:
print(text_to_print)
return
# run_sql to pass result set back
def run_sql(conn, sql, fetchall):
cur = conn.cursor()
try:
cur.execute(sql)
if fetchall:
res = cur.fetchall()
else:
res = cur.fetchone()
except snowflake.connector.errors.ProgrammingError as e:
print("Statement error: {0}".format(e.msg))
res = ('Statement error: ' + str(e.msg),)
except:
print("Unexpected error: {0}".format(e.msg))
finally:
cur.close()
return res
def get_grants_on_object(conn, object_type, object_name, verbose, objf, ignoreshare):
grant_list = []
if object_type == 'DATABASE':
sql = 'show grants on DATABASE ' + object_name
elif object_type == 'SCHEMA':
sql = 'show grants on SCHEMA ' + object_name
elif object_type == 'STAGE':
sql = 'show grants on STAGE ' + object_name
elif object_type == 'SEQUENCE':
sql = 'show grants on SEQUENCE ' + object_name
elif object_type == 'FILE_FORMAT':
sql = 'show grants on FILE FORMAT ' + object_name
elif object_type == 'TABLE':
sql = 'show grants on TABLE ' + object_name
elif object_type == 'VIEW':
sql = 'show grants on VIEW ' + object_name
elif object_type == 'FUNCTION':
sql = 'show grants on FUNCTION ' + object_name
elif object_type == 'PROCEDURE':
sql = 'show grants on PROCEDURE ' + object_name
elif object_type == 'PIPE':
sql = 'show grants on PIPE ' + object_name
elif object_type == 'STREAM':
sql = 'show grants on STREAM ' + object_name
elif object_type == 'WAREHOUSE':
sql = 'show grants on WAREHOUSE ' + object_name
else:
return grant_list # Not an option for getting GRANTS return back the empty list
show_verbose(verbose, sql)
grants = run_sql(conn, sql, True)
for grant in grants:
priv = grant[1]
granted_on = grant[2]
name = grant[3]
granted_to = grant[4]
grantee_name = grant[5]
if granted_to == 'SHARE' and ignoreshare:
continue
if priv == 'OWNERSHIP':
# Output to the grants on object file
printer(grantee_name + '|' + granted_to + '|' + priv + '|' + granted_on + '|' + name, objf)
continue
# grant_stmt = 'grant ' + priv + ' on ' + object_name + ' to ' + granted_to + ' ' + grantee_name +';'
grant_stmt = 'grant ' + priv + ' on ' + granted_on + ' ' + name + ' to ' + granted_to + ' ' + grantee_name + ';'
# Output to the grants on object file
printer(grantee_name + '|' + granted_to + '|' + priv + '|' + granted_on + '|' + name, objf)
if grant_stmt not in grant_list:
grant_list.append(grant_stmt)
return grant_list
def get_database_list(conn, dbname, verbose, objf, ignoreshare):
database_objects = {}
print('--Step 1. Getting Database List')
if dbname:
sql = "show databases like '{0}'".format(dbname)
else:
sql = 'show databases'
show_verbose(verbose, sql)
databases = run_sql(conn, sql, True)
for database in databases:
db_info = {}
role_obj = {}
name = database[1]
created_on = database[0]
origin = database[4]
role_owner = database[5]
comment = database[6]
retention = database[7]
# Resolve the issue with shares into the account
# If the data is shared, then the origin field is populated
if origin != '':
continue
grants_list = get_grants_on_object(conn, 'DATABASE', name, verbose, objf, ignoreshare)
ddl = 'CREATE DATABASE IF NOT EXISTS ' + name + " COMMENT = '" + comment + "';"
info = {'NAME': name, 'CREATED': created_on, 'ORIGIN': origin, 'OWNER': role_owner, 'COMMENT': comment,
'RETENTION': retention, 'DDL': ddl, 'SCHEMA': '', 'GRANTS': grants_list}
db_info['01-DATABASE'] = info
# list of hashes for DB info under role_obj
if role_owner in role_obj:
role_obj[role_owner].append(db_info)
else:
role_obj[role_owner] = []
role_obj[role_owner].append(db_info)
database_objects[name] = role_obj
return database_objects
def get_database_schemas(conn, database_objects, verbose, just_schema, objf, ignoreshare):
print('--Step 2. Getting Schema List')
# Loop through all the databases, or a single database and populate the schema.
for db in database_objects.keys():
if just_schema:
sql = "show schemas like '" + just_schema + "' in database " + db
else:
sql = 'show schemas in database ' + db
show_verbose(verbose, sql)
schemas = run_sql(conn, sql, True)
for schema in schemas:
db_info = {}
role_obj = {}
created_on = schema[0]
name = schema[1]
role_owner = schema[5]
comment = schema[6]
options = schema[7]
retention = schema[8]
# if name != 'INFORMATION_SCHEMA' and name != 'PUBLIC':
if name != 'INFORMATION_SCHEMA':
if options == 'TRANSIENT':
ddl = 'CREATE TRANSIENT schema IF NOT EXISTS ' + db + '.' + name + ';'
else:
ddl = 'CREATE SCHEMA IF NOT EXISTS ' + db + '.' + name + ';'
grants_list = get_grants_on_object(conn, 'SCHEMA', db + '.' + name, verbose, objf, ignoreshare)
info = {'NAME': name, 'SCHEMA': name, 'CREATED': created_on, 'OWNER': role_owner, 'COMMENT': comment,
'OPTIONS': options, 'RETENTION': retention, 'DDL': ddl, 'GRANTS': grants_list}
db_info['02-SCHEMAS'] = info
role_obj = database_objects[db]
if role_owner in role_obj:
role_obj[role_owner].append(db_info)
else:
role_obj[role_owner] = []
role_obj[role_owner].append(db_info)
database_objects[db] = role_obj
return database_objects
def get_database_stages(conn, database_objects, verbose, just_schema, objf, ignoreshare):
print('--Step 3. Getting Stages List')
# Loop through all the databases, or a single database and populate the stages.
for db in database_objects.keys():
if just_schema:
sql = 'use database ' + db
sqloutput = run_sql(conn, sql, False)
sql = 'show stages in schema ' + just_schema
else:
sql = 'show stages in database ' + db
show_verbose(verbose, sql)
stages = run_sql(conn, sql, True)
for stage in stages:
db_info = {}
role_obj = {}
created_on = stage[0]
name = stage[1]
schema = stage[3]
role_owner = stage[7]
comment = stage[8]
region = stage[9]
type = stage[10]
if type == 'EXTERNAL':
ddl = '--***** External Stage, must get permssions and manually create **** CREATE STAGE ' + db + '.' + schema + '.' + name + ';'
else:
ddl = 'CREATE STAGE ' + db + '.' + schema + '.' + name + ';'
grants_list = get_grants_on_object(conn, 'STAGE', db + '.' + schema + '.' + name, verbose, objf,
ignoreshare)
info = {'NAME': name, 'CREATED': created_on, 'OWNER': role_owner, 'COMMENT': comment, 'SCHEMA': schema,
'REGION': region, 'DDL': ddl, 'TYPE': type, 'GRANTS': grants_list}
db_info['03-STAGES'] = info
role_obj = database_objects[db]
if role_owner in role_obj:
role_obj[role_owner].append(db_info)
else:
role_obj[role_owner] = []
role_obj[role_owner].append(db_info)
database_objects[db] = role_obj
return database_objects
def get_database_sequences(conn, database_objects, verbose, just_schema, objf, ignoreshare):
print('--Step 4. Getting Sequences List')
# Loop through all the databases, or a single database and populate the stages.
for db in database_objects.keys():
if just_schema:
sql = 'use database ' + db
sqloutput = run_sql(conn, sql, False)
sql = 'show sequences in schema ' + just_schema
else:
sql = 'show sequences in database ' + db
show_verbose(verbose, sql)
sequences = run_sql(conn, sql, True)
for sequence in sequences:
db_info = {}
role_obj = {}
name = sequence[0]
schema = sequence[2]
created_on = sequence[5]
role_owner = sequence[6]
comment = sequence[7]
sql = "select get_ddl('SEQUENCE', '" + db + '.' + schema + '.' + name + "')"
show_verbose(verbose, sql)
ddl_tup = run_sql(conn, sql, False)
ddl = ddl_tup[0]
grants_list = get_grants_on_object(conn, 'SEQUENCE', db + '.' + schema + '.' + name, verbose, objf,
ignoreshare)
info = {'NAME': name, 'CREATED': created_on, 'OWNER': role_owner, 'COMMENT': comment, 'SCHEMA': schema,
'DDL': ddl, 'GRANTS': grants_list}
db_info['04-SEQUENCES'] = info
role_obj = database_objects[db]
if role_owner in role_obj:
role_obj[role_owner].append(db_info)
else:
role_obj[role_owner] = []
role_obj[role_owner].append(db_info)
database_objects[db] = role_obj
return database_objects
def get_database_fileformats(conn, database_objects, verbose, just_schema, objf, ignoreshare):
print('--Step 5. Getting FileFormats List')
# Loop through all the databases, or a single database and populate the stages.
for db in database_objects.keys():
if just_schema:
sql = 'use database ' + db
sqloutput = run_sql(conn, sql, False)
sql = 'show file formats in schema ' + just_schema
else:
sql = 'show file formats in database ' + db
show_verbose(verbose, sql)
fileformats = run_sql(conn, sql, True)
for fileformat in fileformats:
db_info = {}
role_obj = {}
name = fileformat[1]
schema = fileformat[3]
created_on = fileformat[0]
type = fileformat[4]
role_owner = fileformat[5]
comment = fileformat[6]
sql = "select get_ddl('FILE_FORMAT', '" + db + '.' + schema + '.' + name + "')"
show_verbose(verbose, sql)
ddl_tup = run_sql(conn, sql, False)
ddl = ddl_tup[0]
grants_list = get_grants_on_object(conn, 'FILE_FORMAT', db + '.' + schema + '.' + name, verbose, objf,
ignoreshare)
info = {'NAME': name, 'CREATED': created_on, 'OWNER': role_owner, 'COMMENT': comment, 'SCHEMA': schema,
'DDL': ddl, 'TYPE': type, 'GRANTS': grants_list}
db_info['05-FILE_FORMATS'] = info
role_obj = database_objects[db]
if role_owner in role_obj:
role_obj[role_owner].append(db_info)
else:
role_obj[role_owner] = []
role_obj[role_owner].append(db_info)
database_objects[db] = role_obj
return database_objects
def get_database_tables(conn, database_objects, verbose, just_schema, objf, ignoreshare):
print('--Step 6. Getting Table List')
# Loop through all the databases, or a single database and populate the stages.
for db in database_objects.keys():
# sql = 'show tables in database ' + db
if just_schema:
sql = 'select created, table_name, table_catalog, table_schema, table_type, comment, clustering_key, row_count, bytes, table_owner, retention_time, auto_clustering_on from ' + db + ".INFORMATION_SCHEMA.TABLES where table_schema = upper('" + just_schema + "') and TABLE_TYPE = 'BASE TABLE' "
else:
sql = 'select created, table_name, table_catalog, table_schema, table_type, comment, clustering_key, row_count, bytes, table_owner, retention_time, auto_clustering_on from ' + db + ".INFORMATION_SCHEMA.TABLES where table_schema != 'INFORMATION_SCHEMA' and TABLE_TYPE = 'BASE TABLE' "
show_verbose(verbose, sql)
tables = run_sql(conn, sql, True)
for table in tables:
db_info = {}
role_obj = {}
name = table[1]
schema = table[3]
kind = table[4]
comment = table[5]
created_on = table[0]
role_owner = table[9]
retention = table[10]
# Check if the table contains a variant column, if it does make sure its the only column in the table
sql = "select count(*) as total_cols, sum(case when data_type = 'VARIANT' then 1 else 0 end) as total_var_cols from " + db + ".INFORMATION_SCHEMA.COLUMNS where table_schema = '" + schema + "' and TABLE_NAME = '" + name + "' and table_catalog = '" + db + "'"
show_verbose(verbose, sql)
col_count_tup = run_sql(conn, sql, False)
col_count = col_count_tup[0]
variant_count = col_count_tup[1]
exportable = 'TRUE'
if variant_count > 0:
variant_flag = 'TRUE'
if variant_count > 1:
exportable = 'FALSE'
else:
variant_flag = 'FALSE'
# Now get the DDL for the table
sql = "select get_ddl('TABLE', '" + db + '.' + schema + '."' + name + '"' + "')"
show_verbose(verbose, sql)
ddl_tup = run_sql(conn, sql, False)
ddl = ddl_tup[0]
# ddl = 'USE SCHEMA ' + schema + ';\n '+ ddl
grants_list = get_grants_on_object(conn, 'TABLE', db + '.' + schema + '."' + name + '"', verbose, objf,
ignoreshare)
info = {'NAME': name, 'CREATED': created_on, 'OWNER': role_owner, 'COMMENT': comment, 'SCHEMA': schema,
'DDL': ddl, 'KIND': kind, 'RETENTION': retention, 'GRANTS': grants_list,
'VARIANT_FLAG': variant_flag, 'EXPORTABLE': exportable}
db_info['06-TABLES'] = info
role_obj = database_objects[db]
if role_owner in role_obj:
role_obj[role_owner].append(db_info)
else:
role_obj[role_owner] = []
role_obj[role_owner].append(db_info)
database_objects[db] = role_obj
return database_objects
def get_database_views(conn, database_objects, verbose, just_schema, objf, ignoreshare):
print('--Step 7. Getting View List')
# Loop through all the databases, or a single database and populate the stages.
for db in database_objects.keys():
# sql = 'show views in database ' + db
if just_schema:
sql = 'select created, table_name, is_updatable, table_catalog, table_schema, table_owner, comment, insertable_into, is_secure from ' + db + ".INFORMATION_SCHEMA.VIEWS where table_schema = upper('" + just_schema + "') and TABLE_OWNER is not NULL "
else:
sql = 'select created, table_name, is_updatable, table_catalog, table_schema, table_owner, comment, insertable_into, is_secure from ' + db + ".INFORMATION_SCHEMA.VIEWS where table_schema != 'INFORMATION_SCHEMA' and TABLE_OWNER is not NULL "
show_verbose(verbose, sql)
views = run_sql(conn, sql, True)
for view in views:
db_info = {}
role_obj = {}
name = view[1]
schema = view[4]
role_owner = view[5]
comment = view[6]
created_on = view[0]
secure = view[8]
if schema != 'INFORMATION_SCHEMA' and (secure == 'false' or secure == 'NO'):
sql = "select get_ddl('VIEW', '" + db + '.' + schema + '."' + name + '"' + "')"
show_verbose(verbose, sql)
ddl_tup = run_sql(conn, sql, False)
ddl = ddl_tup[0]
grants_list = get_grants_on_object(conn, 'VIEW', db + '.' + schema + '."' + name + '"', verbose, objf,
ignoreshare)
info = {'NAME': name, 'CREATED': created_on, 'OWNER': role_owner, 'COMMENT': comment, 'SCHEMA': schema,
'DDL': ddl, 'SECURE': secure, 'GRANTS': grants_list}
db_info['07-VIEWS'] = info
role_obj = database_objects[db]
if role_owner in role_obj:
role_obj[role_owner].append(db_info)
else:
role_obj[role_owner] = []
role_obj[role_owner].append(db_info)
database_objects[db] = role_obj
return database_objects
def get_database_udfs(conn, database_objects, verbose, just_schema, objf, ignoreshare):
print('--Step 8. Getting UDF List')
# Loop through all the databases, or a single database and populate the stages.
for db in database_objects.keys():
if just_schema:
sql = 'use database ' + db
sqloutput = run_sql(conn, sql, False)
sql = 'show USER functions in schema ' + just_schema
else:
sql = 'show USER functions in database ' + db
show_verbose(verbose, sql)
udfs = run_sql(conn, sql, True)
for udf in udfs:
db_info = {}
role_obj = {}
name = udf[1]
schema = udf[2]
comment = ''
created_on = udf[0]
secure = udf[13]
argument_signature = udf[8]
argument_signature = argument_signature[argument_signature.find('('):argument_signature.find(')') + 1]
sql = 'select function_owner , argument_signature from ' + db + ".INFORMATION_SCHEMA.FUNCTIONS where function_name = '" + name + "' and function_catalog = '" + db + "' and function_schema = '" + schema + "'"
show_verbose(verbose, sql)
owner_tup = run_sql(conn, sql, False)
role_owner = owner_tup[0]
# Old way of doing it, now changed in the metadata 2019-08-15 fix
# #argument_signature = owner_tup[1]
if schema != 'INFORMATION_SCHEMA':
sql = "select get_ddl('FUNCTION', '" + db + '.' + schema + '.' + name + argument_signature + "')"
show_verbose(verbose, sql)
ddl_tup = run_sql(conn, sql, False)
ddl = ddl_tup[0]
# ddl = 'USE SCHEMA ' + schema + ';\n '+ ddl
if ddl[0:9] != 'Statement':
grants_list = get_grants_on_object(conn, 'FUNCTION',
db + '.' + schema + '.' + name + argument_signature, verbose,
objf, ignoreshare)
else:
grants_list = [
'Error getting ' + "select get_ddl('FUNCTION', '" + db + '.' + schema + '.' + name + argument_signature + "')"]
info = {'NAME': name, 'CREATED': created_on, 'OWNER': role_owner, 'COMMENT': comment, 'SCHEMA': schema,
'DDL': ddl, 'SECURE': secure, 'GRANTS': grants_list}
db_info['08-UDFS'] = info
role_obj = database_objects[db]
if role_owner in role_obj:
role_obj[role_owner].append(db_info)
else:
role_obj[role_owner] = []
role_obj[role_owner].append(db_info)
database_objects[db] = role_obj
return database_objects
def get_database_procedures(conn, database_objects, verbose, just_schema, objf, ignoreshare):
print('--Step 9. Getting Procedure List')
# Loop through all the databases, or a single database and populate the stages.
for db in database_objects.keys():
if just_schema:
sql = 'use database ' + db
sqloutput = run_sql(conn, sql, False)
sql = 'show procedures in schema ' + just_schema
else:
sql = 'show procedures in database ' + db
show_verbose(verbose, sql)
procs = run_sql(conn, sql, True)
for proc in procs:
db_info = {}
role_obj = {}
name = proc[1]
schema = proc[2]
comment = ''
created_on = proc[0]
secure = proc[13]
proc_arguments = proc[8]
# Find the first ) for the closing paren for the Arguments list.
name_argument_signature = proc_arguments[0:proc_arguments.find(')') + 1]
# get the ownership role
sql = 'show grants on PROCEDURE ' + db + '.' + schema + '.' + name_argument_signature
show_verbose(verbose, sql)
results = run_sql(conn, sql, True)
# sql='select * from table(result_scan(last_query_id())) where "privilege"='+"'OWNERSHIP'"
# results = run_sql(conn, sql, False)
role_owner = 'PUBLIC --Please check this is correct'
for result in results:
if result[1] == 'OWNERSHIP':
role_owner = result[5]
sql = "select get_ddl('PROCEDURE', '" + db + '.' + schema + '.' + name_argument_signature + "')"
show_verbose(verbose, sql)
ddl_tup = run_sql(conn, sql, False)
ddl = ddl_tup[0]
grants_list = get_grants_on_object(conn, 'PROCEDURE', db + '.' + schema + '.' + name_argument_signature,
verbose, objf, ignoreshare)
info = {'NAME': name, 'CREATED': created_on, 'OWNER': role_owner, 'COMMENT': comment, 'SCHEMA': schema,
'DDL': ddl, 'SECURE': secure, 'GRANTS': grants_list}
db_info['09-PROCEDURES'] = info
role_obj = database_objects[db]
if role_owner in role_obj:
role_obj[role_owner].append(db_info)
else:
role_obj[role_owner] = []
role_obj[role_owner].append(db_info)
database_objects[db] = role_obj
return database_objects
def get_database_pipes(conn, database_objects, verbose, just_schema, objf, ignoreshare):
print('--Step 10. Getting Pipe List')
# Loop through all the databases, or a single database and populate the stages.
for db in database_objects.keys():
if just_schema:
sql = 'use database ' + db
sqloutput = run_sql(conn, sql, False)
sql = 'show pipes in schema ' + just_schema
else:
sql = 'show pipes in database ' + db
show_verbose(verbose, sql)
pipes = run_sql(conn, sql, True)
for pipe in pipes:
db_info = {}
role_obj = {}
name = pipe[1]
schema = pipe[3]
role_owner = pipe[5]
comment = pipe[7]
created_on = pipe[0]
# ddl = 'CREATE PIPE ' + db + '.' + schema + '.' + name + ' as ' + pipe[4] + ';'
sql = "select get_ddl('PIPE', '" + db + '.' + schema + '.' + name + "')"
show_verbose(verbose, sql)
ddl_tup = run_sql(conn, sql, False)
ddl = ddl_tup[0]
grants_list = get_grants_on_object(conn, 'PIPE', db + '.' + schema + '.' + name, verbose, objf, ignoreshare)
info = {'NAME': name, 'CREATED': created_on, 'OWNER': role_owner, 'COMMENT': comment, 'SCHEMA': schema,
'DDL': ddl, 'GRANTS': grants_list}
db_info['10-PIPES'] = info
role_obj = database_objects[db]
if role_owner in role_obj:
role_obj[role_owner].append(db_info)
else:
role_obj[role_owner] = []
role_obj[role_owner].append(db_info)
database_objects[db] = role_obj
return database_objects
def get_database_streams(conn, database_objects, verbose, just_schema, objf, ignoreshare):
print('--Step 11. Getting Streams List')
# Loop through all the databases, or a single database and populate the stages.
for db in database_objects.keys():
if just_schema:
sql = 'use database ' + db
sqloutput = run_sql(conn, sql, False)
sql = 'show streams in schema ' + just_schema
else:
sql = 'show streams in database ' + db
show_verbose(verbose, sql)
streams = run_sql(conn, sql, True)
for stream in streams:
db_info = {}
role_obj = {}
name = stream[1]
schema = stream[3]
role_owner = stream[4]
comment = stream[5]
created_on = stream[0]
sql = "select get_ddl('STREAM', '" + db + '.' + schema + '.' + name + "')"
show_verbose(verbose, sql)
ddl_tup = run_sql(conn, sql, False)
ddl = ddl_tup[0]
grants_list = get_grants_on_object(conn, 'STREAM', db + '.' + schema + '.' + name, verbose, objf,
ignoreshare)
info = {'NAME': name, 'CREATED': created_on, 'OWNER': role_owner, 'COMMENT': comment, 'SCHEMA': schema,
'DDL': ddl, 'GRANTS': grants_list}
db_info['11-STREAMS'] = info
role_obj = database_objects[db]
if role_owner in role_obj:
role_obj[role_owner].append(db_info)
else:
role_obj[role_owner] = []
role_obj[role_owner].append(db_info)
database_objects[db] = role_obj
return database_objects
def get_warehouse_list(conn, verbose, objf, ignoreshare):
wh_list = {}
print('--Step 12. Getting Warehouse List')
sql = 'show warehouses'
show_verbose(verbose, sql)
warehouses = run_sql(conn, sql, True)
for warehouse in warehouses:
name = warehouse[0]
wh_type = warehouse[2]
size = warehouse[3]
min_cluster_count = warehouse[4]
max_cluster_count = warehouse[5]
auto_suspend = warehouse[11]
auto_resume = warehouse[12]
owner = warehouse[20]
grants_list = get_grants_on_object(conn, 'WAREHOUSE', name, verbose, objf, ignoreshare)
ddl = 'CREATE WAREHOUSE IF NOT EXISTS ' + name + " WITH WAREHOUSE_SIZE = '" + str(size) + "' SCALING_POLICY = '" + wh_type + "' AUTO_SUSPEND = " + str(auto_suspend) + " AUTO_RESUME = " + str(auto_resume)
ddl = ddl + " MIN_CLUSTER_COUNT = " + str(min_cluster_count) + " MAX_CLUSTER_COUNT = " + str(max_cluster_count) + " INITIALLY_SUSPENDED = TRUE;"
#info = {'DDL': ddl, 'GRANTS': grants_list}
if owner not in wh_list:
wh_list[owner] = []
wh_list[owner].append(ddl)
for grants in grants_list:
wh_list[owner].append(grants)
return wh_list
def print_ddl(database_objects, items, verbose, outputfile, roles, ddl_roles, warehouses_ddl, filemode):
if outputfile:
try:
of = open(outputfile, filemode)
except IOError:
print("Could not open file! " + outputfile)
else:
of = ''
# Using the w for write vs a for append flag to determine if we put the role information in the file.
if roles and filemode == 'w':
for role in ddl_roles.keys():
printer('-------------------------------------', of)
printer('-- Generating: Roles', of)
printer('-------------------------------------', of)
printer(role, of)
for ddl in ddl_roles[role]['DDL']:
printer(ddl, of)
for grant in ddl_roles[role]['GRANTS']:
printer(grant, of)
for gu in ddl_roles[role]['GRANTS_USERS']:
printer(gu, of)
for priv in ddl_roles[role]['PRIVS']:
printer(priv, of)
printer('', of)
printer('-------------------------------------', of)
printer('-- Generating: Warehouses', of)
printer('-------------------------------------', of)
for role in warehouses_ddl.keys():
printer('USE ROLE ' + role + ';', of)
for ddl in warehouses_ddl[role]:
printer(ddl, of)
for db in sorted(database_objects.keys()): # Database Name
# Now loop through the new obj_hash to print out the ddl
for item in sorted(items):
printer('-------------------------------------', of)
printer('-- Generating: ' + str(item), of)
printer('-------------------------------------', of)
if item != '01-DATABASE':
printer('USE DATABASE ' + db + ';', of)
role_obj = database_objects[db]
for role in sorted(role_obj.keys()): # Role Name
first_time_flag = True
obj_list = role_obj[role] # Object list, 1-DATABASE, 2-SCHEMAS, etc.
for obj in obj_list:
for key in sorted(obj.keys(), reverse=True):
if key == item:
schema = obj[key]['SCHEMA']
list = obj[key]['DDL']
list_grants = obj[key]['GRANTS']
if first_time_flag:
printer('USE ROLE ' + role + ';', of)
first_time_flag = False
if schema != '' and str(item) != '02-SCHEMAS':
printer('USE SCHEMA ' + schema + ';', of)
printer(list, of)
printer('', of)
for list_grant in list_grants:
# printer('--GRANTS', of)
printer(list_grant, of)
printer('', of)
if of != '':
if not of.closed:
of.close()
return
def printer(text, fh):
if fh != '':
if not fh.closed:
fh.write(text + "\n")
else:
print(text)
return
def extract_ddl(database_objects, item, verbose, outputfile, roles, ddl_roles):
if outputfile:
match = re.search('([\w.-]+)\.([\w.-]+)', outputfile)
if match:
outputfile = match.group(1) + '_extract.sql'
outputfile2 = match.group(1) + '_load.sql'
else:
outputfile2 = outputfile + '_load.sql'
outputfile = outputfile + '_extract.sql'
try:
of = open(outputfile, 'w')
except IOError:
print("Could not open file! " + outputfile)
try:
of2 = open(outputfile2, 'w')
except IOError:
print("Could not open file! " + outputfile2)
print('')
print('--Extract DDL file name -> ' + outputfile)
print('--Load DDL file name -> ' + outputfile2)
else:
of = ''
of2 = ''
for db in sorted(database_objects.keys()): # Database Name
# Now loop through the new obj_hash to print out the ddl
printer('-------------------------------------', of)
printer('-- Generating Extract: ' + str(item), of)
printer('-------------------------------------', of)
printer('-------------------------------------', of2)
printer('-- Generating LOAD: ' + str(item), of2)
printer('-------------------------------------', of2)
if item != '01-DATABASE':
printer('USE DATABASE ' + db + ';', of)
printer('USE DATABASE ' + db + ';', of2)
role_obj = database_objects[db]
for role in sorted(role_obj.keys()): # Role Name
first_time_flag = True
obj_list = role_obj[role] # Object list, 1-DATABASE, 2-SCHEMAS, etc.
for obj in obj_list:
for key in sorted(obj.keys(), reverse=True):
if key == item:
schema = obj[key]['SCHEMA']
# Check to see if the table is exportable, and if it is, if it contains a variant column use the Paquet format.
if obj[key]['EXPORTABLE'] == 'TRUE':
if obj[key]['VARIANT_FLAG'] == 'TRUE':
list = "copy into @UTIL_DB_MIGRATION.PUBLIC.EXTRACT_STAGE/" + db + "/" + schema + "/" + \
obj[key]['NAME'] + '/' + obj[key]['NAME'] + ' from ' + db + "." + schema + "." + \
obj[key][
'NAME'] + " file_format=(format_name='UTIL_DB_MIGRATION.PUBLIC.PARQUET_FMT' compression='AUTO');"
list2 = "copy into " + db + "." + schema + "." + obj[key][
'NAME'] + " from @UTIL_DB_MIGRATION.PUBLIC.EXTRACT_STAGE/" + db + "/" + schema + "/" + \
obj[key]['NAME'] + '/' + obj[key][
'NAME'] + " file_format=(format_name='UTIL_DB_MIGRATION.PUBLIC.PARQUET_FMT');"
else:
list = "copy into @UTIL_DB_MIGRATION.PUBLIC.EXTRACT_STAGE/" + db + "/" + schema + "/" + \
obj[key]['NAME'] + '/' + obj[key]['NAME'] + ' from ' + db + "." + schema + "." + \
obj[key][
'NAME'] + " file_format=(format_name='UTIL_DB_MIGRATION.PUBLIC.CSV_FMT' compression='AUTO');"
list2 = "copy into " + db + "." + schema + "." + obj[key][
'NAME'] + " from @UTIL_DB_MIGRATION.PUBLIC.EXTRACT_STAGE/" + db + "/" + schema + "/" + \
obj[key]['NAME'] + '/' + obj[key][
'NAME'] + " file_format=(format_name='UTIL_DB_MIGRATION.PUBLIC.CSV_FMT');"
else:
list = "-------- Object will have to be manually exported because it contains mixed columns or multiple varaiant columns: " + \
obj[key]['NAME']
list2 = "-------- Object will have to be manually exported because it contains mixed columns or multiple varaiant columns: " + \
obj[key]['NAME']
if first_time_flag:
printer('USE ROLE ' + role + ';', of)
printer('USE ROLE ' + role + ';', of2)
first_time_flag = False
if schema != '':
printer('USE SCHEMA ' + schema + ';', of)
printer('USE SCHEMA ' + schema + ';', of2)
printer(list, of)
printer('', of)
printer(list2, of2)
printer('', of2)
if of2 != '':
if not of2.closed:
of2.close()
if of != '':
if not of.closed:
of.close()
return
def get_roles(conn, verbose):
roles_all = {}
ddl_roles = {}
print('--Step 12. Getting Roles List')
if verbose:
sql = "select current_version(), current_client(), current_account(), current_user(), current_role()"
res = run_sql(conn, sql, False)
print("Snowflake version: {0}, client: {1}, account: {2}, user: {3}, role: {4}".format(res[0], res[1], res[2],
res[3], res[4]))
# get all roles
sql = "show roles"
roles = run_sql(conn, sql, True)
print("-- Role count: " + str(len(roles)))
for row in roles:
if verbose:
print("created: {0}, name: {1}, comment: {2}".format(row[0], row[1], row[9]))
# remember each role and its comment
role = row[1]
owner = row[8]
comment = row[9]
# Add the use role to the DDL so the role is created with the same access.
if len(owner) != 0:
use_role = 'use role ' + owner + ';'
ddl = 'CREATE ROLE IF NOT EXISTS ' + role + ';'
else:
use_role = 'use role ACCOUNTADMIN;'
ddl = 'CREATE ROLE IF NOT EXISTS' + role + ';'
# Comment out the DDL statement for the built in roles
if role in ('ACCOUNTADMIN', 'SYSADMIN', 'SECURITYADMIN', 'PUBLIC', 'ORGADMIN', 'USERADMIN'):
ddl = '--' + ddl
# role_obj = {'name':role, 'comment':comment, 'OWNER':owner, 'USE_ROLE':use_role, 'DDL':ddl, 'GRANTS':[]}
role_obj = {'name': role, 'comment': comment, 'OWNER': owner}
roles_all[role] = role_obj
# Build the DDL_ROLES dict to handle the looping through the roles for permissions.
if use_role in ddl_roles:
ddl_roles[use_role]['DDL'].append(ddl)
else:
ddl_roles[use_role] = {'DDL': [], 'GRANTS': [], 'GRANTS_USERS': [], 'PRIVS': []}
ddl_roles[use_role]['DDL'].append(ddl)
return (roles_all, ddl_roles)
def get_grants_to_role(conn, verbose, roles_all, ddl_roles, objf):
print('--Step 13. Getting Grants on Role List')
for role in roles_all.keys():
sql = 'show grants to role "{0}"'.format(role)
# sql = 'show grants on role "{0}"'.format(role)
if verbose:
print(sql)
privs = run_sql(conn, sql, True)
for row in privs:
privilege = row[1]
granted_on = row[2]
object_name = row[3]
granted_to = row[4]
grantee = row[5]
grant_option = row[6]
granted_by = row[7]
if len(granted_by) == 0:
granted_by = 'ACCOUNTADMIN'
use_role = 'use role ' + granted_by + ';'
if granted_on == 'ACCOUNT':
object_name = ''
if grant_option == 'true':
grant = 'grant ' + privilege + ' on ' + granted_on + ' ' + object_name + ' to ' + granted_to + ' ' + grantee + ' with grant option;'
printer(grantee + '|' + granted_to + '|' + privilege + '|' + granted_on + '|' + object_name, objf)
else:
grant = grant = 'grant ' + privilege + ' on ' + granted_on + ' ' + object_name + ' to ' + granted_to + ' ' + grantee + ';'
printer(grantee + '|' + granted_to + '|' + privilege + '|' + granted_on + '|' + object_name, objf)
# Build the DDL_ROLES dict to handle the looping through the roles for permissions.
# if privilege not in ('USAGE', 'OWNERSHIP'):
# if privilege in ('WAREHOUSE', 'ACCOUNT'):
# 2020-06-04 fix applied.
if granted_on in ('WAREHOUSE', 'ACCOUNT'):
if use_role in ddl_roles:
ddl_roles[use_role]['PRIVS'].append(grant)
else:
ddl_roles[use_role] = {'DDL': [], 'GRANTS': [], 'GRANTS_USERS': [], 'PRIVS': []}
ddl_roles[use_role]['PRIVS'].append(grant)
return (roles_all, ddl_roles)
def get_grants_of_role(conn, verbose, roles_all, ddl_roles, off):
print('--Step 14. Getting Grants of Role List')
print('')
print('--Role Hierarchy')
print('-------------------------------------------------------')
roles_tree = {}
for role in roles_all.keys():
sql = 'show grants of role "{0}"'.format(role)
if verbose:
print(sql)
grants = run_sql(conn, sql, True)
parent_count = 0
for row in grants:
if verbose:
print("created: {0}, role: {1}, granted_to: {2}, grantee: {3}, granted by: {4}".format(row[0], row[1],
row[2], row[3],
row[4]))
# role = row[1] #already populated based on the role key.
granted_to = row[2]
grantee = row[3]
granted_by = row[4]
if granted_by == '':
granted_by = 'ACCOUNTADMIN'
if granted_to == 'ROLE':
parent_count += 1
if verbose:
print("\trole {0} parent: {1}".format(role, grantee))
parent = roles_all[grantee] # Ex. AccountAdmin parent
child = roles_all[role] # Ex. Sysadmin child
children = parent.get('children') # check to see if children is in the list.
if children != None:
children.append(child) # Ex. Adds new item to the list children.
else:
children = [child] # Ex. Defines a list of children and adds sysadmin
parent['children'] = children # Ex. dict parent['children']= list of children
# Build the DDL_ROLES dict to handle the looping through the roles for permissions.
use_role = 'use role ' + granted_by + ';'
if not (role in ('ACCOUNTADMIN', 'SYSADMIN', 'SECURITYADMIN', 'ORGADMIN', 'PUBLIC', 'USERADMIN')
and grantee in ('ACCOUNTADMIN', 'SYSADMIN', 'SECURITYADMIN', 'ORGADMIN', 'PUBLIC', 'USERADMIN')):
grant = 'grant role ' + role + ' to role ' + grantee + ';'
printer(role + '|' + grantee + '|' + 'ROLE', off)
if use_role in ddl_roles:
ddl_roles[use_role]['GRANTS'].append(grant)
else:
ddl_roles[use_role] = {'DDL': [], 'GRANTS': [], 'GRANTS_USERS': [], 'PRIVS': []}
ddl_roles[use_role]['GRANTS'].append(grant)
if verbose:
print("\t", role, children)
if granted_to == 'USER':
if verbose:
print("\trole {0} granted to: {1}".format(role, grantee))
parent = roles_all[role]
users = parent.get('users')
if users != None:
users.append(grantee)
else:
users = [grantee]
parent['users'] = users # Ex. dict parent['users']= list of users
# Build the DDL_ROLES dict to handle the looping through the roles for permissions.
use_role = 'use role ' + granted_by + ';'
grant = 'grant role ' + role + ' to user "' + grantee + '";'
printer(grantee + '|' + role + '|' + 'USER', off)
if use_role in ddl_roles:
ddl_roles[use_role]['GRANTS_USERS'].append(grant)
else:
ddl_roles[use_role] = {'DDL': [], 'GRANTS': [], 'GRANTS_USERS': [], 'PRIVS': []}
ddl_roles[use_role]['GRANTS_USERS'].append(grant)
if verbose:
print("\t", grantee, users)
if parent_count == 0:
# this role has no parents, add to root of tree
if verbose:
print("role '" + role + "' has no parents")
roles_tree[role] = roles_all[role]
# Add the public role to the roles_tree hierarchy in case it has no direct privs.
if 'PUBLIC' not in roles_tree.keys():
roles_tree['PUBLIC'] = roles_all['PUBLIC']
return (roles_all, roles_tree, ddl_roles)
def print_roles(all_roles, roles_tree, level, showusers, comments, db_obj, off):
for key in sorted(roles_tree.keys()):
role_obj = all_roles[key]
print_role(role_obj, level, showusers, comments, db_obj, off)
return
def print_role(role_obj, level, showusers, comments, db_obj, off):
indent = ' ' * level
name = role_obj['name']
role = role_obj['name']
# added below two lines to build out the grants_of_role.csv file.
if level == 0:
printer(role + '|' + '|' + 'ROLE', off)
# mark system roles with square brackets so they stand out
if name in ('ACCOUNTADMIN', 'SYSADMIN', 'SECURITYADMIN', 'PUBLIC'):
name = "[" + name + "]"
str = indent + name
comm = role_obj['comment']
if comments and len(comm) > 0:
str += "\t(" + comm + ")"
print(str)
# ---------------------------------------------------
# Print out the Database objects for this role
# ---------------------------------------------------
for db in db_obj.keys():
if role in db_obj[db]:
for itemtype in db_obj[db][role]: # 1-Database, 2-SCHEMAS, 3-TABLES
for k, v in itemtype.items():
if k in ('01-DATABASE', '02-SCHEMAS'):
print(indent + ' Granted Access to: ' + k + ' \t\tObject: ' + v['NAME'])
else:
print(indent + ' Granted Access to: ' + k + ' \t\tObject: ' + db + '.' + v['SCHEMA'] + '.' + v[
'NAME'])
# Now loop through any individual object grants
for grant in v['GRANTS']:
print(indent + ' Granted Individual Access to: ' + k + ' \t\tObject: ' + grant)
# print users that have been granted this role
if showusers:
str = indent + " " + "granted to user: "
users = role_obj.get('users')
if users != None:
for obj in users:
print(str + obj)
# recurse into our "children" (roles granted to the current "parent" role)
children = role_obj.get('children')
if children != None:
n = level + 1
for obj in children:
print_role(obj, n, showusers, comments, db_obj, off)
return
def main():
##### MAIN #####
parser = argparse.ArgumentParser(description='Show in a Snowflake account.',
epilog='Example: snowflake_database_ddl.py cust-account warehouse user')
parser.add_argument('account', action='store',
help='Snowflake account to explore')
parser.add_argument('warehouse', action='store',
help='Snowflake warehouse to use for queries')
parser.add_argument('user', action='store',
help="Snowflake user's login name")
parser.add_argument('-execrole', '--execrole', action='store',
help='If the program is to be run as a different role than the users default role.')
parser.add_argument('-db', '--database', action='store',
help='show ddl for a specific database')
parser.add_argument('-schema', '--schema', action='store',
help='show ddl for a specific schema in either a database if -db is used or all databases.')
parser.add_argument('-sqlfile', '--sqlfile', action='store',
help='Create an output file script containing the create role ddl and grants, to be used with -ddl switch')
parser.add_argument('-r', '--roles', action='store_true',
help='Print out the role hierarchy, and all objects and roles associated with that hierarchy')
parser.add_argument('-ro', '--rolesonly', action='store_true',
help='Print out just the role hierarchy, and all objects and roles associated with that hierarchy')
parser.add_argument('-u', '--users', action='store_true',
help='show user grants (if any) for each role')
parser.add_argument('-ignoreshare', '--ignoreshare', action='store_true',
help='Ignores grants to shares if this option is used.')
parser.add_argument('-c', '--comments', action='store_true',
help='show comments (if any) for each role')
parser.add_argument('-v', '--verbose', action='store_true', help='verbose')
parser.add_argument('-m', '--mfa', action='store',
help='optional MFA identifier, either Duo passcode or Yubikey <touch>')
args = parser.parse_args()
if args.verbose:
print("account=" + str(args.account)) # account to login to
print("warehouse=" + str(args.warehouse)) # warehouse to use for queries
print("user=" + str(args.user)) # user to login as
print("role=" + str(args.execrole)) # user to login as
print("database=" + str(args.database)) # show specific database
print("schema=" + str(args.schema)) # show specific schema
print("sqlfile=" + str(args.sqlfile)) # show sqlfile to output
print("roles=" + str(args.roles)) # show roles hierarchy and values
print("verbose=" + str(args.verbose)) # show debugging output
print("mfa=" + str(args.mfa)) # show mfa output
pwd = getpass.getpass(prompt="Password for '{0}': ".format(args.user))
# Dict of db_objects
# db_objects[dbname]={role_name, {object_type ie DB, table, view: ddl } }
#
# db_def
# db_def[role owner]= list of objects
# Define the connection to Snowflake
conn = snowflake.connector.connect(
user=args.user,
password=pwd,
passcode=args.mfa,
account=args.account,
warehouse=args.warehouse)
if args.verbose:
print("conn=" + str(conn))
print(' ')
items = ['01-DATABASE', '02-SCHEMAS', '03-STAGES', '04-SEQUENCES', '05-FILE_FORMATS', '06-TABLES', '07-VIEWS',
'08-UDFS', '09-PROCEDURES', '10-PIPES', '11-STREAMS']
# Define the output files for the roles, three files will be created.
if args.sqlfile:
match = re.search('([\w.-]+)\.([\w.-]+)', args.sqlfile)
if match:
objectfile = match.group(1) + '_grants_on_object.csv'
ofrolefile = match.group(1) + '_grants_of_role.csv'
else:
objectfile = args.sqlfile + '_grants_on_object.csv'
ofrolefile = args.sqlfile + '_grants_of_role.csv'
else:
objectfile = 'db_grants_on_object.csv'
ofrolefile = 'db_grants_of_role.csv'
try:
objf = open(objectfile, 'w')
except IOError:
print("Could not open file! " + objectfile)
try:
off = open(ofrolefile, 'w')
except IOError:
print("Could not open file! " + ofrolefile)
print('')
print('--Role Object file name -> ' + objectfile)
print('--Grants of Role file name -> ' + ofrolefile)
print('\n')
# Put the header in each file to make it easier to read
printer('ROLE_OR_SHARE|TYPE|PRIVILEGE|OBJECT_TYPE|OBJECT', objf)
printer('CHILD|PARENT|TYPE', off)
# If execrole supplied via command line, then switch to that role
if args.execrole:
sql = 'use role ' + args.execrole
sqloutput = run_sql(conn, sql, False)
# # Get Databases
db_objects = get_database_list(conn, args.database, args.verbose, objf, args.ignoreshare)
# Get the schemas for each database
#db_objects = get_database_schemas(conn, db_objects, args.verbose, args.schema, objf, args.ignoreshare)
# Get the Stages for each database
#db_objects = get_database_stages(conn, db_objects, args.verbose, args.schema, objf, args.ignoreshare)
# Get the sequences for each database
#db_objects = get_database_sequences(conn, db_objects, args.verbose, args.schema, objf, args.ignoreshare)
# Get the file formats for each database
#db_objects = get_database_fileformats(conn, db_objects, args.verbose, args.schema, objf, args.ignoreshare)
# Get the tables for each database
#db_objects = get_database_tables(conn, db_objects, args.verbose, args.schema, objf, args.ignoreshare)
# Get the views for each database
#db_objects = get_database_views(conn, db_objects, args.verbose, args.schema, objf, args.ignoreshare)
# Get the UDF for each database
#db_objects = get_database_udfs(conn, db_objects, args.verbose, args.schema, objf, args.ignoreshare)
# Get the Procedures for each database
#db_objects = get_database_procedures(conn, db_objects, args.verbose, args.schema, objf, args.ignoreshare)
# Get the Pipes for each database
#db_objects = get_database_pipes(conn, db_objects, args.verbose, args.schema, objf, args.ignoreshare)
# Get the Streams for each database
#db_objects = get_database_streams(conn, db_objects, args.verbose, args.schema, objf, args.ignoreshare)
# Get the Warehouses
warehouses_ddl = get_warehouse_list(conn, args.verbose, objf, args.ignoreshare)
# Get Roles
# Roles object contains all the roles, with nested children array and users.
# Role_tree object just countains the level 0 roles, or the highest level that have no parents.
if args.roles or args.rolesonly:
roles, roles_ddl = get_roles(conn, args.verbose)
roles, roles_ddl = get_grants_to_role(conn, args.verbose, roles, roles_ddl, objf)
roles, role_tree, roles_ddl = get_grants_of_role(conn, args.verbose, roles, roles_ddl, off)
print_roles(roles, role_tree, 0, args.users, args.comments, db_objects, off)
# Testing the Dict values for the roles
# with open('roles_example.json', 'w') as fp:
# json.dump(roles, fp)
# with open('roles_tree_example.json', 'w') as fp:
# json.dump(role_tree, fp)
# with open('roles_ddl_example.json', 'w') as fp:
# json.dump(roles_ddl, fp)
print('')
else:
roles_ddl = {}
# TESTING
# sys.exit(0)
if not args.rolesonly:
# Modified to print the DDL data with VIEWS, UDF, and PROCEDURES at the end of the file
# as some of these elements may span multiple databases.
items2 = ['07-VIEWS', '08-UDFS', '09-PROCEDURES']
items.remove('07-VIEWS')
items.remove('08-UDFS')
items.remove('09-PROCEDURES')
print_ddl(db_objects, items, args.verbose, args.sqlfile, args.roles, roles_ddl, warehouses_ddl, 'w')
print_ddl(db_objects, items2, args.verbose, args.sqlfile, args.roles, roles_ddl, warehouses_ddl, 'a')
if args.sqlfile:
extract_ddl(db_objects, items[5], args.verbose, args.sqlfile, args.roles, roles_ddl)
# 2020-06-04 Fix to pring out the roles informatin to sqlfile if -ro option
if args.rolesonly:
# If Roles Only option is used, do not output any of the ddl, other then the roles information.
items2 = []
print_ddl(db_objects, items2, args.verbose, args.sqlfile, args.rolesonly, roles_ddl, warehouses_ddl, 'w')
print('\nTo load the roles information into snowflake execute the following:')
print('snowsql -a ' + args.account + ' -u ' + args.user + ' -f snowflake_roles_db_setup.sql \n')
# Close the role files
if objf != '':
if not objf.closed:
objf.close()
if off != '':
if not off.closed:
off.close()
return
if __name__ == "__main__":
main()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment