def grantAllRoles(ofile, cursor):
query = ( "select role_name, granted_to, grantee_name, granted_by from " +
'"' + crossrep.tb_pcrl + '"' + " where role_name != 'PUBLIC' and "
" (role_name not in ('ACCOUNTADMIN', 'SECURITYADMIN','SYSADMIN') OR grantee_name not in ('ACCOUNTADMIN', 'SECURITYADMIN','SYSADMIN', 'PUBLIC') ) " +
" order by role_name ,granted_to, grantee_name ")
'''
# SNOW-84205 do not use assigned_to_users data
query = ( "select role_name, granted_to, grantee_name, granted_by from " +
tbgrants + " where role_name in " +
"(select role_name from " + tbroles + " where granted_roles != 0 or assigned_to_users != 0 ) " +
" and role_name != 'PUBLIC' and "
" (role_name not in ('ACCOUNTADMIN', 'SECURITYADMIN','SYSADMIN') OR grantee_name not in ('ACCOUNTADMIN', 'SECURITYADMIN','SYSADMIN', 'PUBLIC') ) " +
" order by role_name ,granted_to, grantee_name ")
'''
if crossrep.verbose == True:
print(query)
cursor.execute(query)
rec = cursor.fetchall()
for r in rec:
role_name = r[0]
granted_to = r[1]
grantee_name = r[2]
granted_by = r[3]
if role_name.isdigit() == True or grantee_name.isdigit() == True:
continue
if crossrep.hasSpecial(role_name):
role_name = '"'+role_name+'"'
if crossrep.hasSpecial(grantee_name):
role_name = '"'+grantee_name+'"'
grantSQL = 'GRANT ROLE ' + role_name + ' TO '+ granted_to + ' ' + grantee_name
ofile.write(grantSQL+';\n')
if crossrep.verbose == True:
print(grantSQL)
def quoteID(id):
if re.search(r'[a-z]', id) or ' ' in id or crossrep.isKeywords(id)==True or crossrep.hasSpecial(id)==True:
if re.match(r'^[^"]',id):
return '"%s"' % id
else:
return id
else :
return id
def grantTargetRole(ofile, cursor):
trole = crossrep.getEnv('TARGET_ROLE')
query = ("select distinct role from "+crossrep.tb_priv+" where priv = 'OWNERSHIP' and role != 'PUBLIC' order by role")
#print(query)
cursor.execute(query)
rec = cursor.fetchall()
for r in rec:
rname = r[0]
if crossrep.hasSpecial(rname):
rname = '"' + rname + '"'
ofile.write('GRANT ROLE '+ rname + ' TO ROLE ' + trole + ';\n')
def genRoleDDL( ofile, cursor):
role_name = None
comment = None
ofile.write("use role securityadmin;\n")
query = "select role_name, comments from "+ crossrep.tb_role + " where role_name not in ('ACCOUNTADMIN','SECURITYADMIN','SYSADMIN','PUBLIC') order by role_name "
cursor.execute(query)
rec = cursor.fetchall()
for r in rec:
role_name = r[0]
if crossrep.hasSpecial(role_name) == True:
role_name = "\"" + role_name + "\""
if crossrep.verbose==True:
print(' role name: ' + role_name)
comment = r[1]
if role_name.isdigit()==True:
continue
croleSQL = "CREATE ROLE IF NOT EXISTS " + role_name
if not crossrep.isBlank (comment ):
croleSQL = croleSQL + ' comment = "' + comment + '"'
ofile.write(croleSQL+';\n')
def genUserDDL(options, ofile, cursor):
user_name = None
login_name = None
display_name = None
first_name = None
last_name = None
email = None
mins_to_unlock = None
days_to_expiry = None
comment = None
query = ( "select user_name,login_name,display_name,first_name,last_name,email,mins_to_unlock,days_to_expiry,comment, "+
" must_change_password, snowflake_lock, default_warehouse, default_namespace, default_role from " + crossrep.tb_user + " order by user_name ")
#" from " + tbusers+ " where disabled = false order by owner, user_name "
ofile.write("use role securityadmin;\n")
cursor.execute(query)
rec = cursor.fetchall()
for r in rec:
#user_name = quoteID(r[0])
user_name = r[0]
login_name = r[1]
display_name = r[2]
first_name = r[3]
last_name = r[4]
email = r[5]
mins_to_unlock = r[6]
days_to_expiry = r[7]
comment = r[8]
must_change_password = r[9]
default_warehouse = r[11]
default_namespace = r[12]
default_role = r[13]
if user_name.isdigit() == True:
continue
if crossrep.hasSpecial(user_name) == True:
user_name = "\"" + user_name + "\""
if crossrep.verbose==True:
print(' user name: ' + user_name)
cuserSQL = "CREATE USER IF NOT EXISTS " + user_name
if not crossrep.isBlank (login_name) :
cuserSQL = cuserSQL + " login_name='" + login_name + "'"
if not crossrep.isBlank (display_name ):
cuserSQL = cuserSQL + " display_name='" + display_name + "'"
if not crossrep.isBlank (first_name ):
cuserSQL = cuserSQL + " first_name='" + first_name + "'"
if not crossrep.isBlank (last_name ):
cuserSQL = cuserSQL + " last_name='" + last_name + "'"
if not crossrep.isBlank (email ):
cuserSQL = cuserSQL + " email='" + email + "'"
if not crossrep.isBlank (mins_to_unlock ):
cuserSQL = cuserSQL + " mins_to_unlock=" + mins_to_unlock
if not crossrep.isBlank (days_to_expiry ):
days_to_expiry = str(int(float(days_to_expiry)))
cuserSQL = cuserSQL + " days_to_expiry=" + days_to_expiry
if not crossrep.isBlank (comment ):
cuserSQL = cuserSQL + ' comment=\'' + comment + '\''
if not crossrep.isBlank (default_warehouse ):
cuserSQL = cuserSQL + ' default_warehouse=\'' + default_warehouse + '\''
if not crossrep.isBlank (default_namespace ):
cuserSQL = cuserSQL + ' default_namespace=\'' + default_namespace + '\''
if not crossrep.isBlank (default_role ):
cuserSQL = cuserSQL + ' default_role=\'' + default_role + '\''
if crossrep.verbose == True:
print(cuserSQL)
if options == 'samepwd':
cuserSQL = cuserSQL + " password='******' MUST_CHANGE_PASSWORD=TRUE "
elif options == 'randpwd':
pwd = crossrep.genPWD()
cuserSQL = cuserSQL + " password='******' MUST_CHANGE_PASSWORD=TRUE "
ofile.write(cuserSQL+';\n')