def upgrade_password_format(self):
queries = []
if self.password != self.confirm_password:
raise WBSecurityValidationError("The new password and its confirmation don't match. Please re-enter them.")
queries.append("use mysql")
if not self.host:
raise WBSecurityValidationError("Host name must not be blank")
fields = {
"user" : escape_sql_string(self.username) or "NULL",
"host" : escape_sql_string(self.host) or "",
"password" : escape_sql_string(self.password or ""),
}
queries.append("SET old_passwords = 0")
if self._owner.ctrl_be.target_version and self._owner.ctrl_be.target_version.is_supported_mysql_version_at_least(5, 5, 7):
queries.append("UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = '******' AND host = '%(host)s'" % fields)
queries.append("FLUSH PRIVILEGES")
change_pw = CHANGE_PASSWORD_QUERY if self._owner.ctrl_be.target_version and self._owner.ctrl_be.target_version < Version(5,7,6) else CHANGE_PASSWORD_QUERY_576
queries.append(change_pw % fields)
queries.append("FLUSH PRIVILEGES")
action = "changing"
for query in queries:
try:
self._owner.ctrl_be.exec_sql(query)
except QueryError, e:
if e.error == 1142:
raise Exception("Error %s account %s@%s: Insufficient rights to perform operation"%(action, self.username, self.host))
else:
raise Exception("Error %s account %s@%s: %s"%(action, self.username, self.host, e.errortext or e))
except Exception, e:
raise Exception("Error %s account %s@%s: %s"%(action, self.username, self.host, e))
def upgrade_password_format(self):
queries = []
if self.password != self.confirm_password:
raise WBSecurityValidationError("The new password and its confirmation don't match. Please re-enter them.")
queries.append("use mysql")
if not self.host:
raise WBSecurityValidationError("Host name must not be blank")
fields = {
"user" : escape_sql_string(self.username) or "NULL",
"host" : escape_sql_string(self.host) or "",
"password" : escape_sql_string(self.password or ""),
}
queries.append("SET old_passwords = 0")
if self._owner.ctrl_be.target_version and self._owner.ctrl_be.target_version.is_supported_mysql_version_at_least(5, 5, 7):
queries.append("UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = '******' AND host = '%(host)s'" % fields)
queries.append("FLUSH PRIVILEGES")
queries.append("SET PASSWORD FOR '%(user)s'@'%(host)s' = PASSWORD('%(password)s')" % fields)
queries.append("FLUSH PRIVILEGES")
action = "changing"
for query in queries:
try:
self._owner.ctrl_be.exec_sql(query)
except QueryError, e:
if e.error == 1142:
raise Exception("Error %s account %s@%s: Insufficient rights to perform operation"%(action, self.username, self.host))
else:
raise Exception("Error %s account %s@%s: %s"%(action, self.username, self.host, e.errortext or e))
except Exception, e:
raise Exception("Error %s account %s@%s: %s"%(action, self.username, self.host, e))
def load(self, username, hostname):
self.is_commited = True
# Basic stuff from User table
query = GET_ACCOUNT_QUERY % {"user":escape_sql_string(username),"host":escape_sql_string(hostname)}
try:
result = self._owner.ctrl_be.exec_query(query)
except Exception, e:
raise Exception("Error querying security information: %s" % e)
def load(self, username, hostname):
self.is_commited = True
# Basic stuff from User table
query = GET_ACCOUNT_QUERY % {"user":escape_sql_string(username),"host":escape_sql_string(hostname)}
try:
result = self._owner.ctrl_be.exec_query(query)
except Exception, e:
raise Exception("Error querying security information: %s" % e)
def load(self):
# Schema privileges from Db table
query = GET_ACCOUNT_SCHEMA_PRIVS_QUERY % {
"user": escape_sql_string(self._owner.username),
"host": escape_sql_string(self._owner.host)
}
try:
result = self._owner.ctrl_be.exec_query(query)
except Exception, e:
raise Exception("Error querying security information: %s" % e)
def do_delete_account(self, username, host):
query = REMOVE_USER % {"user":escape_sql_string(username), "host":escape_sql_string(host)}
try:
self.ctrl_be.exec_sql("use mysql")
self.ctrl_be.exec_sql(query)
except QueryError, e:
log_error('Error removing account %s@%s:\n%s' % (username, host, str(e)))
if e.error == 1227: # MySQL error code 1227 (ER_SPECIFIC_ACCESS_DENIED_ERROR)
raise Exception('Error removing the account %s@%s:' % (username, host),
'You must have the global CREATE USER privilege or the DELETE privilege for the mysql '
'database')
raise e
def do_delete_account(self, username, host):
query = REMOVE_USER % {"user":escape_sql_string(username), "host":escape_sql_string(host)}
try:
self.ctrl_be.exec_sql("use mysql")
self.ctrl_be.exec_sql(query)
except QueryError, e:
log_error('Error removing account %s@%s:\n%s' % (username, host, str(e)))
if e.error == 1227: # MySQL error code 1227 (ER_SPECIFIC_ACCESS_DENIED_ERROR)
raise Exception('Error removing the account %s@%s:' % (username, host),
'You must have the global CREATE USER privilege or the DELETE privilege for the mysql '
'database')
raise e
def save(self):
# workaround for server bug
self._owner.ctrl_be.exec_sql("use mysql")
for deleted, entry in [(True, e) for e in self._deleted_entries
] + [(False, e) for e in self.entries]:
fields = {
"user": escape_sql_string(self._owner.username),
"host": escape_sql_string(self._owner.host),
"db": entry.db if entry.db != '%' else '*'
}
granted_privs = []
revoked_privs = []
for priv in self.schema_privilege_names:
(priv_name,
description) = PrivilegeInfo.get(priv, (None, None))
if not priv_name:
continue
if (not deleted
and priv in entry.privileges) or not entry.privileges:
granted_privs.append(priv_name)
else:
revoked_privs.append(priv_name)
if granted_privs and not deleted:
fields['granted_privs'] = ', '.join(granted_privs)
try:
self._owner.ctrl_be.exec_sql(
GRANT_SCHEMA_PRIVILEGES_QUERY % fields)
except QueryError, e:
if e.error in [1045, 1044]:
raise Exception(
'Error assigning privileges for %(user)s@%(host)s in schema %(db)s'
% fields,
'You must have the GRANT OPTION privilege, and you must have the privileges that you are granting'
)
raise e
if revoked_privs:
fields['revoked_privs'] = ', '.join(revoked_privs)
try:
self._owner.ctrl_be.exec_sql(
REVOKE_SCHEMA_PRIVILEGES_QUERY % fields)
except QueryError, e:
if e.error in [1045, 1044]:
raise Exception(
'Error revoking privileges for %(user)s@%(host)s in schema %(db)s'
% fields,
'You must have the GRANT OPTION privilege, and you must have the privileges that you are revoking'
)
raise e
def run(self):
if self.run_modal(self.ok, self.cancel):
if self.password.get_string_value(
) != self.confirm.get_string_value():
mforms.Utilities.show_error(
"Reset Password",
"The password and its confirmation do not match. Please try again.",
"OK", "", "")
return self.run()
con = self._conn.shallow_copy()
con.parameterValues["CLIENT_MULTI_STATEMENTS"] = 1
if self.legacy.get_active():
con.parameterValues[
"preInit"] = "SET PASSWORD = PASSWORD('%s')" % escape_sql_string(
self.password.get_string_value())
else:
con.parameterValues[
"preInit"] = "SET PASSWORD = '******'" % escape_sql_string(
self.password.get_string_value())
retry = False
result = 1
c = MySQLConnection(con,
password=self.old_password.get_string_value())
# connect to server so that preInitScript will do the password reset work
try:
log_info(
"About to connecto to MySQL Server to change expired password"
)
c.connect()
mforms.Utilities_store_password(
c.connect_info.hostIdentifier,
c.connect_info.parameterValues.userName,
self.password.get_string_value())
except MySQLError as e:
if mforms.Utilities.show_error("Reset Password", str(e),
"Retry", "Cancel",
"") == mforms.ResultOk:
retry = True
result = 0
except Exception as e:
print("Error handling expired password: %s" % str(e))
if retry:
return self.run()
return result
return 0
def run(self):
if self.run_modal(self.ok, self.cancel):
if self.password.get_string_value() != self.confirm.get_string_value():
mforms.Utilities.show_error("Reset Password", "The password and its confirmation do not match. Please try again.", "OK", "", "")
return self.run()
con = self._conn.shallow_copy()
old_multi_statements = con.parameterValues.get("CLIENT_MULTI_STATEMENTS")
old_script = con.parameterValues.get("preInit")
con.parameterValues["CLIENT_MULTI_STATEMENTS"] = 1
con.parameterValues["preInit"] = "SET PASSWORD = '******'" % escape_sql_string(self.password.get_string_value())
#con.parameterValues["preInit"] = "ALTER USER '%s'@'%s' IDENTIFIED BY '%s'" % (con.parameterValues["userName"], con.hostIdentifier.replace("Mysql@", ""), escape_sql_string(self.password.get_string_value()))
#change_pw = "ALTER USER '%s'@'%s' IDENTIFIED BY '%s'" % (con.parameterValues["userName"], con.hostIdentifier.replace("Mysql@", ""), escape_sql_string(self.password.get_string_value()))
retry = False
result = 1
c = MySQLConnection(con, password=self.old_password.get_string_value())
# connect to server so that preInitScript will do the password reset work
try:
log_info("About to connecto to MySQL Server to change expired password")
c.connect()
except MySQLError, e:
if mforms.Utilities.show_error("Reset Password", str(e), "Retry", "Cancel", "") == mforms.ResultOk:
retry = True
result = 0
if retry:
return self.run()
return result
def expire_password(self):
command = EXPIRE_PASSWORD % {"user":escape_sql_string(self.username),"host":self.host}
try:
self._owner.ctrl_be.exec_sql("use mysql")
self._owner.ctrl_be.exec_sql(command)
except QueryError, e:
raise Exception('Error expiring password for account %s@%s: %s' % (self.username, self.host, e))
def delete_account(self, account):
if account.is_commited:
query = REMOVE_USER % {
"user": escape_sql_string(account.username),
"host": escape_sql_string(account.host)
}
try:
self.ctrl_be.exec_sql("use mysql")
self.ctrl_be.exec_sql(query)
except QueryError, e:
if e.error == 1227:
raise Exception(
'Error removing the account %s@%s:' %
(account.username, account.host),
'You must have the global CREATE USER privilege or the DELETE privilege for the mysql '
'database')
def run(self):
if self.run_modal(self.ok, self.cancel):
if self.password.get_string_value(
) != self.confirm.get_string_value():
mforms.Utilities.show_error(
"Reset Password",
"The password and its confirmation do not match. Please try again.",
"OK", "", "")
return self.run()
con = self._conn
old_multi_statements = con.parameterValues.get(
"CLIENT_MULTI_STATEMENTS")
old_script = con.parameterValues.get("preInit")
con.parameterValues["CLIENT_MULTI_STATEMENTS"] = 1
con.parameterValues[
"preInit"] = "SET PASSWORD = PASSWORD('%s')" % escape_sql_string(
self.password.get_string_value())
retry = False
result = 1
c = MySQLConnection(con,
password=self.old_password.get_string_value())
# connect to server so that preInitScript will do the password reset work
try:
c.connect()
except MySQLError, e:
if mforms.Utilities.show_error("Reset Password", str(e),
"Retry", "Cancel",
"") == mforms.ResultOk:
retry = True
result = 0
finally:
def load(self):
# Schema privileges from Db table
query = GET_ACCOUNT_SCHEMA_PRIVS_QUERY % {"user": escape_sql_string(self._owner.username), "host": escape_sql_string(self._owner.host)}
try:
result = self._owner.ctrl_be.exec_query(query)
except Exception, e:
raise Exception("Error querying security information: %s" % e)
def run(self):
if self.run_modal(self.ok, self.cancel):
if self.password.get_string_value() != self.confirm.get_string_value():
mforms.Utilities.show_error("Reset Password", "The password and its confirmation do not match. Please try again.", "OK", "", "")
return self.run()
con = self._conn.shallow_copy()
old_multi_statements = con.parameterValues.get("CLIENT_MULTI_STATEMENTS")
old_script = con.parameterValues.get("preInit")
con.parameterValues["CLIENT_MULTI_STATEMENTS"] = 1
con.parameterValues["preInit"] = "SET PASSWORD = PASSWORD('%s')" % escape_sql_string(self.password.get_string_value())
#con.parameterValues["preInit"] = "ALTER USER '%s'@'%s' IDENTIFIED BY '%s'" % (con.parameterValues["userName"], con.hostIdentifier.replace("Mysql@", ""), escape_sql_string(self.password.get_string_value()))
#change_pw = "ALTER USER '%s'@'%s' IDENTIFIED BY '%s'" % (con.parameterValues["userName"], con.hostIdentifier.replace("Mysql@", ""), escape_sql_string(self.password.get_string_value()))
retry = False
result = 1
c = MySQLConnection(con, password = self.old_password.get_string_value())
# connect to server so that preInitScript will do the password reset work
try:
log_info("About to connecto to MySQL Server to change expired password")
c.connect()
except MySQLError, e:
if mforms.Utilities.show_error("Reset Password", str(e), "Retry", "Cancel", "") == mforms.ResultOk:
retry = True
result = 0
if retry:
return self.run()
return result
def expire_password(self):
command = EXPIRE_PASSWORD % {"user":escape_sql_string(self.username),"host":self.host}
try:
self._owner.ctrl_be.exec_sql("use mysql")
self._owner.ctrl_be.exec_sql(command)
except QueryError, e:
raise Exception('Error expiring password for account %s@%s: %s' % (self.username, self.host, e))
def getFunctionNames(connection, catalog_name, schema_name):
names = []
version = getServerVersion(connection)
if (version.majorNumber, version.minorNumber, version.releaseNumber) >= (5, 0, 0):
result = execute_query(connection, "SHOW FUNCTION STATUS WHERE Db='%s'" % escape_sql_string(schema_name))
while result and result.nextRow():
names.append(result.stringByName("Name"))
return names
def getFunctionNames(connection, catalog_name, schema_name):
names = []
version = getServerVersion(connection)
if (version.majorNumber, version.minorNumber, version.releaseNumber) >= (5, 0, 0):
result = execute_query(connection, "SHOW FUNCTION STATUS WHERE Db='%s'" % escape_sql_string(schema_name))
while result and result.nextRow():
names.append(result.stringByName("Name"))
return names
def save(self):
# workaround for server bug
self._owner.ctrl_be.exec_sql("use mysql")
handled_entries = set()
for deleted, entry in [(True, e) for e in self._deleted_entries] + [(False, e) for e in self.entries]:
fields = { "user":escape_sql_string(self._owner.username),
"host":escape_sql_string(self._owner.host),
"db":entry.db if entry.db != '%' else '*'
}
granted_privs = []
revoked_privs = []
for priv in self.schema_privilege_names:
(priv_name, description) = PrivilegeInfo.get(priv, (None,None))
if not priv_name:
continue
if not deleted and priv in entry.privileges:
granted_privs.append(priv_name)
else:
revoked_privs.append(priv_name)
if granted_privs and not deleted:
fields['granted_privs'] = ', '.join(granted_privs)
try:
self._owner.ctrl_be.exec_sql(GRANT_SCHEMA_PRIVILEGES_QUERY % fields)
except QueryError, e:
if e.error == 1045:
raise Exception('Error assigning privileges for %(user)s@%(host)s in schema %(db)s' % fields,
'You must have the GRANT OPTION privilege, and you must have the privileges that you are granting')
raise e
if revoked_privs:
fields['revoked_privs'] = ', '.join(revoked_privs)
try:
self._owner.ctrl_be.exec_sql(REVOKE_SCHEMA_PRIVILEGES_QUERY % fields)
except QueryError, e:
if e.error == 1044:
raise Exception('Error revoking privileges for %(user)s@%(host)s in schema %(db)s' % fields,
'You must have the GRANT OPTION privilege, and you must have the privileges that you are revoking')
raise e
def delete_account(self, account):
if account.is_commited:
query = REMOVE_USER % {"user":escape_sql_string(account.username), "host":escape_sql_string(account.host)}
try:
self.ctrl_be.exec_sql("use mysql")
self.ctrl_be.exec_sql(query)
except QueryError, e:
if e.error == 1227:
raise Exception('Error removing the account %s@%s:' % (account.username, account.host),
'You must have the global CREATE USER privilege or the DELETE privilege for the mysql '
'database')
def revoke_all(self):
command = REVOKE_ALL % {"user":escape_sql_string(self.username),"host":self.host}
try:
self._owner.ctrl_be.exec_sql("use mysql")
self._owner.ctrl_be.exec_sql(command)
except QueryError, e:
if e.error == 1227:
raise Exception('Error revoking privileges for the account %s@%s:' % (self.username, self.host),
'You must have the global CREATE USER privilege or the UPDATE privilege for the mysql '
'database')
else:
raise
def revoke_all(self):
command = REVOKE_ALL % {"user":escape_sql_string(self.username),"host":self.host}
try:
self._owner.ctrl_be.exec_sql("use mysql")
self._owner.ctrl_be.exec_sql(command)
except QueryError, e:
if e.error == 1227:
raise Exception('Error revoking privileges for the account %s@%s:' % (self.username, self.host),
'You must have the global CREATE USER privilege or the UPDATE privilege for the mysql '
'database')
else:
raise
def run(self):
if self.run_modal(self.ok, self.cancel):
con = self._conn
old_multi_statements = con.parameterValues.get(
"CLIENT_MULTI_STATEMENTS")
old_script = con.parameterValues.get("preInit")
con.parameterValues["CLIENT_MULTI_STATEMENTS"] = 1
con.parameterValues[
"preInit"] = "SET PASSWORD = PASSWORD('%s')" % escape_sql_string(
self.password.get_string_value())
retry = False
result = 1
c = MySQLConnection(con,
password=self.old_password.get_string_value())
# connect to server so that preInitScript will do the password reset work
try:
c.connect()
except MySQLError, e:
if mforms.Utilities.show_error("Reset Password", str(e),
"Retry", "Cancel",
"") == mforms.ResultOk:
retry = True
result = 0
if old_script is not None:
con.parameterValues["preInit"] = old_script
else:
del con.parameterValues["preInit"]
if old_multi_statements is not None:
con.parameterValues[
"CLIENT_MULTI_STATEMENTS"] = old_multi_statements
else:
del con.parameterValues["CLIENT_MULTI_STATEMENTS"]
if retry:
return self.run()
return result
def run(self):
if self.run_modal(self.ok, self.cancel):
if self.password.get_string_value() != self.confirm.get_string_value():
mforms.Utilities.show_error("Reset Password", "The password and its confirmation do not match. Please try again.", "OK", "", "")
return self.run()
con = self._conn
old_multi_statements = con.parameterValues.get("CLIENT_MULTI_STATEMENTS")
old_script = con.parameterValues.get("preInit")
con.parameterValues["CLIENT_MULTI_STATEMENTS"] = 1
con.parameterValues["preInit"] = "SET PASSWORD = PASSWORD('%s')" % escape_sql_string(self.password.get_string_value())
retry = False
result = 1
c = MySQLConnection(con, password = self.old_password.get_string_value())
# connect to server so that preInitScript will do the password reset work
try:
c.connect()
except MySQLError, e:
if mforms.Utilities.show_error("Reset Password", str(e), "Retry", "Cancel", "") == mforms.ResultOk:
retry = True
result = 0
if old_script is not None:
con.parameterValues["preInit"] = old_script
else:
del con.parameterValues["preInit"]
if old_multi_statements is not None:
con.parameterValues["CLIENT_MULTI_STATEMENTS"] = old_multi_statements
else:
del con.parameterValues["CLIENT_MULTI_STATEMENTS"]
if retry:
return self.run()
return result
def is_zombie(self, user, host):
return self._zombie_privs.has_key((user,host))
def get_zombie_privs(self, user, host):
return self._zombie_privs.get((user,host), None)
def wipe_zombie(self, user, host):
try:
self.ctrl_be.exec_query(DROP_ZOMBIE_SCHEMA_PRIVS_QUERY % {'user':escape_sql_string(user), 'host':escape_sql_string(host)})
except Exception, e:
log_error(_this_file, "Could not delete schema privileges for %s@%s: %s\n" % (user, host, e))
try:
self.ctrl_be.exec_query(DROP_ZOMBIE_TABLE_PRIVS_QUERY % {'user':escape_sql_string(user), 'host':escape_sql_string(host)})
except Exception, e:
log_error(_this_file, "Could not delete table privileges for %s@%s: %s\n" % (user, host, e))
try:
self.ctrl_be.exec_query(DROP_ZOMBIE_COLUMN_PRIVS_QUERY % {'user':escape_sql_string(user), 'host':escape_sql_string(host)})
except Exception, e:
log_error(_this_file, "Could not delete column privileges for %s@%s: %s\n" % (user, host, e))
try:
self.ctrl_be.exec_query(DROP_ZOMBIE_PROCS_PRIVS_QUERY % {'user':escape_sql_string(user), 'host':escape_sql_string(host)})
except Exception, e:
log_error(_this_file, "Could not delete procs privileges for %s@%s: %s\n" % (user, host, e))
def wipe_zombie(self, user, host):
try:
self.ctrl_be.exec_query(DROP_ZOMBIE_SCHEMA_PRIVS_QUERY % {'user':escape_sql_string(user), 'host':escape_sql_string(host)})
except Exception, e:
log_error(_this_file, "Could not delete schema privileges for %s@%s: %s\n" % (user, host, e))
def save(self):
queries = []
if self.password != self.confirm_password:
raise WBSecurityValidationError("The new password and its confirmation don't match. Please re-enter them.")
# workaround for server bug with replication #14358854
queries.append("use mysql")
#if not self.username:
# raise WBSecurityValidationError("Username must not be blank")
if not self.host:
raise WBSecurityValidationError("Host name must not be blank")
# check if username + host is duplicated
if self.is_commited and (self.username != self._orig_username or self.host != self._orig_host):
if (self.username, self.host) in self._owner.account_names:
raise WBSecurityValidationError("The '%s' account already exists and cannot be saved." % (self.formatted_name()))
elif not self.is_commited:
if self._owner.account_names.count((self.username, self.host)) > 1:
raise WBSecurityValidationError("The '%s' account already exists and cannot be saved." % (self.formatted_name()))
fields = {
"old_user" : escape_sql_string(self._orig_username) if self._orig_username else self._orig_username,
"old_host" : escape_sql_string(self._orig_host) if self._orig_host else self._orig_host,
"user" : escape_sql_string(self.username) or "NULL",
"host" : escape_sql_string(self.host) or "",
"password" : escape_sql_string(self.password or ""),
"auth_plugin" : escape_sql_string(self.auth_plugin) if self.auth_plugin else None,
"auth_string" : escape_sql_string(self.auth_string) if self.auth_string else None
}
password_already_set = False
if not self.is_commited: # This is a new account
if self.auth_plugin and self.auth_plugin != 'mysql_native_password':
if self.auth_string is None:
create_query = CREATE_USER_QUERY_PLUGIN
else:
create_query = CREATE_USER_QUERY_PLUGIN_AUTH_STRING
else:
create_query = CREATE_USER_QUERY
password_already_set = True
queries[:0] = [ create_query % fields ] # This query should be the first in the batch
# WARNING: Now the pwd is sent in clear text
else: # The account already exists
assert self._orig_username is not None and self._orig_host is not None
if self._orig_username != self.username or self._orig_host != self.host: # Rename the user
queries[:0] = [ RENAME_USER_QUERY % fields ] # This query should be the first in the batch
names = ["MAX_QUERIES_PER_HOUR", "MAX_UPDATES_PER_HOUR", "MAX_CONNECTIONS_PER_HOUR"] + (self._owner.has_max_user_connections and ["MAX_USER_CONNECTIONS"] or [])
values = [str(s) for s in [self.max_questions, self.max_updates, self.max_connections] + (self._owner.has_max_user_connections and [self.max_user_connections] or [])]
account_limits = dict(zip(names, values))
limits_changed = account_limits != self._orig_account_limits
is_normal_priv = lambda priv: ( PrivilegeInfo.get(priv, (None,None))[0] and
PrivilegeInfo.get(priv, (None,None))[0][0] != '*'
)
all_normal_privs = set( priv for priv in self._owner.global_privilege_names if is_normal_priv(priv) )
new_granted_privs = (self._global_privs - self._orig_global_privs) & all_normal_privs
orig_revoked_privs = all_normal_privs - self._orig_global_privs
new_revoked_privs = all_normal_privs - self._global_privs - orig_revoked_privs
if new_granted_privs or limits_changed:
if 'Grant_priv' in new_granted_privs:
account_limits['GRANT'] = 'OPTION'
new_granted_privs.remove('Grant_priv')
if (all_normal_privs - new_granted_privs) <= set(['Grant_priv']):
priv_list = ['ALL PRIVILEGES']
else:
priv_list = [ PrivilegeInfo[priv][0] for priv in new_granted_privs ]
fields['granted_privs'] = ', '.join(priv_list) or 'USAGE'
grant_query = GRANT_GLOBAL_PRIVILEGES_QUERY % fields
with_clause = ''
for key, value in account_limits.iteritems(): #name, value in zip(names, values):
if value != self._orig_account_limits.get(key):
if not with_clause:
with_clause = ' WITH '
with_clause += "%s %s "%(key, value)
queries.append(grant_query + with_clause)
if new_revoked_privs:
if all_normal_privs - new_revoked_privs: #set(self._owner.global_privilege_names) - revoked_privs_set: # Revoke a subset of all privs
priv_list = [ PrivilegeInfo[priv][0] for priv in new_revoked_privs ]
fields['revoked_privs'] = ', '.join(priv_list)
queries.append(REVOKE_GLOBAL_PRIVILEGES_QUERY % fields)
else: # All privs are to be revoked so use the revoke all query
queries.append(REVOKE_ALL % fields)
if self.password != self._orig_password and not password_already_set:
change_pw = CHANGE_PASSWORD_QUERY if self._owner.ctrl_be.target_version and self._owner.ctrl_be.target_version < Version(5,7,6) else CHANGE_PASSWORD_QUERY_576
blank_pw = BLANK_PASSWORD_QUERY if self._owner.ctrl_be.target_version and self._owner.ctrl_be.target_version < Version(5,7,6) else BLANK_PASSWORD_QUERY
# special hack required by server to handle sha256 password accounts
if self.auth_plugin == "sha256_password":
queries.append("SET old_passwords = 2")
else:
queries.append("SET old_passwords = 0")
if fields["password"]:
queries.append(change_pw % fields)
else:
queries.append(blank_pw % fields)
action = "changing" if self.is_commited else "creating"
for query in queries:
try:
self._owner.ctrl_be.exec_sql(query)
except QueryError, e:
if e.error == 1142:
raise Exception("Error %s account %s@%s: Insufficient rights to perform operation"%(action, self.username, self.host))
else:
raise Exception("Error %s account %s@%s: %s"%(action, self.username, self.host, e.errortext or e))
except Exception, e:
raise Exception("Error %s account %s@%s: %s"%(action, self.username, self.host, e))
if result < 0:
raise Exception("Error querying information_schema table: "+modules.DbMySQLQuery.lastError())
try:
is_privs = []
while modules.DbMySQLQuery.resultNextRow(result):
table = result.stringByName("Table_name")
table_privs = result.stringByName("Table_priv")
is_privs.append((table, table_priv and table_privs.split(",") or []))
finally:
modules.DbMySQLQuery.closeResult(result)
"""
# privs from mysql tables
query = GET_ACCOUNT_MYSQL_TABLE_PRIVS_QUERY % {
"user": escape_sql_string(username),
"host": escape_sql_string(hostname)
}
try:
result = self._owner.ctrl_be.exec_query(query)
except Exception, e:
raise Exception("Error querying mysql table: %s" % e)
mysql_privs = {}
while result.nextRow():
table = result.stringByName("Table_name")
table_privs = result.stringByName("Table_priv")
mysql_privs[table] = table_privs and table_privs.split(",") or []
# interpret the privileges
for name, (db, tables, required_privs, grant,
result = modules.DbMySQLQuery.executeQuery(self._owner._connection, query)
if result < 0:
raise Exception("Error querying information_schema table: "+modules.DbMySQLQuery.lastError())
try:
is_privs = []
while modules.DbMySQLQuery.resultNextRow(result):
table = result.stringByName("Table_name")
table_privs = result.stringByName("Table_priv")
is_privs.append((table, table_priv and table_privs.split(",") or []))
finally:
modules.DbMySQLQuery.closeResult(result)
"""
# privs from mysql tables
query = GET_ACCOUNT_MYSQL_TABLE_PRIVS_QUERY % {"user":escape_sql_string(username),"host":escape_sql_string(hostname)}
try:
result = self._owner.ctrl_be.exec_query(query)
except Exception, e:
raise Exception("Error querying mysql table: %s" % e)
mysql_privs = {}
while result.nextRow():
table = result.stringByName("Table_name")
table_privs = result.stringByName("Table_priv")
mysql_privs[table] = table_privs and table_privs.split(",") or []
# interpret the privileges
for name, (db, tables, required_privs, grant, revoke) in AdminAttributes.items():
if db == "mysql":
ok = True
def refresh_mdl_list(self):
self.mdl_list_held.clear()
self.mdl_blocked_icon.show(False)
waiting_label_text = "This connection is not waiting for any locks."
try:
nodes = self.connection_list.get_selection()
if nodes and len(nodes) == 1:
thread_id = nodes[0].get_long(7)
result = self.ctrl_be.exec_query("SELECT * FROM performance_schema.metadata_locks WHERE owner_thread_id = %s" % thread_id)
if result is not None:
while result.nextRow():
lock_status = result.stringByName("LOCK_STATUS")
if lock_status == "PENDING":
otype = result.stringByName("OBJECT_TYPE")
oschema = result.stringByName("OBJECT_SCHEMA")
oname = result.stringByName("OBJECT_NAME")
obj_name = [oschema, oname]
if otype == "GLOBAL":
obj_name = "<global>"
else:
obj_name = ".".join([o for o in obj_name if o is not None])
self.mdl_blocked_icon.show(True)
sub_expr = "OBJECT_TYPE = '%s'" % otype
if oschema:
sub_expr += " AND OBJECT_SCHEMA = '%s'" % escape_sql_string(oschema)
if oname:
sub_expr += " AND OBJECT_NAME = '%s'" % escape_sql_string(oname)
lock_type = result.stringByName("LOCK_TYPE")
lock_duration = result.stringByName("LOCK_DURATION")
subresult = self.ctrl_be.exec_query("""SELECT *
FROM performance_schema.metadata_locks
WHERE %s AND LOCK_STATUS = 'GRANTED'""" % sub_expr)
owners = []
while subresult and subresult.nextRow():
owners.append(subresult.intByName("OWNER_THREAD_ID"))
owner_list = ", ".join([str(i) for i in owners])
if len(owners) == 1:
waiting_label_text = "The connection is waiting for a lock on\n%s %s,\nheld by thread %s." % (otype.lower(), obj_name, owner_list)
else:
waiting_label_text = "The connection is waiting for a lock on\n%s %s,\nheld by threads %s" % (otype.lower(), obj_name, owner_list)
waiting_label_text += "\nType: %s\nDuration: %s" % (lock_type, lock_duration)
elif lock_status == "GRANTED":
node = self.mdl_list_held.add_node()
otype = result.stringByName("OBJECT_TYPE")
oschema = result.stringByName("OBJECT_SCHEMA")
oname = result.stringByName("OBJECT_NAME")
obj_name = [oschema, oname]
if otype == "GLOBAL":
node.set_string(0, "<global>")
else:
node.set_string(0, ".".join([o for o in obj_name if o is not None]))
node.set_icon_path(0, self.icon_for_object_type.get(otype))
sub_expr = "OBJECT_TYPE = '%s'" % otype
if oschema:
sub_expr += " AND OBJECT_SCHEMA = '%s'" % escape_sql_string(oschema)
if oname:
sub_expr += " AND OBJECT_NAME = '%s'" % escape_sql_string(oname)
node.set_string(1, result.stringByName("LOCK_TYPE"))
node.set_string(2, result.stringByName("LOCK_DURATION"))
subresult = self.ctrl_be.exec_query("""SELECT OWNER_THREAD_ID, LOCK_TYPE, LOCK_DURATION
FROM performance_schema.metadata_locks
WHERE %s AND LOCK_STATUS = 'PENDING'""" % sub_expr)
while subresult and subresult.nextRow():
subnode = node.add_child()
subnode.set_string(0, "thread %s" % subresult.intByName("OWNER_THREAD_ID"))
subnode.set_string(1, subresult.stringByName("LOCK_TYPE"))
subnode.set_string(2, subresult.stringByName("LOCK_DURATION"))
else:
waiting_label_text = ""
except Exception, e:
import traceback
log_error("Error looking up metadata lock information: %s\n" % traceback.format_exc())
mforms.Utilities.show_error("Lookup Metadata Locks", "Error looking up metadata lock information: %s" % e, "OK", "", "")
def save(self):
queries = []
if self.password != self.confirm_password:
raise WBSecurityValidationError("The new password and its confirmation don't match. Please re-enter them.")
# workaround for server bug with replication #14358854
queries.append("use mysql")
#if not self.username:
# raise WBSecurityValidationError("Username must not be blank")
if not self.host:
raise WBSecurityValidationError("Host name must not be blank")
# check if username + host is duplicated
if self.is_commited and (self.username != self._orig_username or self.host != self._orig_host):
if (self.username, self.host) in self._owner.account_names:
raise WBSecurityValidationError("The '%s' account already exists and cannot be saved." % (self.formatted_name()))
elif not self.is_commited:
if (self.username, self.host, True) in self._owner.account_names:
raise WBSecurityValidationError("The '%s' account already exists and cannot be saved." % (self.formatted_name()))
fields = {
"old_user" : escape_sql_string(self._orig_username) if self._orig_username else self._orig_username,
"old_host" : escape_sql_string(self._orig_host) if self._orig_host else self._orig_host,
"user" : escape_sql_string(self.username) or "NULL",
"host" : escape_sql_string(self.host) or "",
"password" : escape_sql_string(self.password or ""),
"auth_plugin" : escape_sql_string(self.auth_plugin) if self.auth_plugin else None,
"auth_string" : escape_sql_string(self.auth_string) if self.auth_string else None
}
password_already_set = False
if not self.is_commited: # This is a new account
if self.auth_plugin:
if self.auth_string is None:
create_query = CREATE_USER_QUERY_PLUGIN
elif self.auth_plugin == 'mysql_native_password':
if (self._owner.ctrl_be.target_version and self._owner.ctrl_be.target_version < Version(5, 7, 0)):
create_query = CREATE_USER_QUERY_PLUGIN
else:
create_query = CREATE_USER_QUERY_PLUGIN_AUTH_NATIVE
elif self.auth_plugin == 'caching_sha2_password':
create_query = CREATE_USER_QUERY_PLUGIN_AUTH_CACHING
else:
create_query = CREATE_USER_QUERY_PLUGIN_AUTH_STRING
else:
create_query = CREATE_USER_QUERY
password_already_set = True
queries[:0] = [ create_query % fields ] # This query should be the first in the batch
# WARNING: Now the pwd is sent in clear text
else: # The account already exists
assert self._orig_username is not None and self._orig_host is not None
if self._orig_username != self.username or self._orig_host != self.host: # Rename the user
queries[:0] = [ RENAME_USER_QUERY % fields ] # This query should be the first in the batch
names = ["MAX_QUERIES_PER_HOUR", "MAX_UPDATES_PER_HOUR", "MAX_CONNECTIONS_PER_HOUR"] + (self._owner.has_max_user_connections and ["MAX_USER_CONNECTIONS"] or [])
values = [str(s) for s in [self.max_questions, self.max_updates, self.max_connections] + (self._owner.has_max_user_connections and [self.max_user_connections] or [])]
account_limits = dict(zip(names, values))
limits_changed = account_limits != self._orig_account_limits
is_normal_priv = lambda priv: ( PrivilegeInfo.get(priv, (None,None))[0] and
PrivilegeInfo.get(priv, (None,None))[0][0] != '*'
)
all_normal_privs = set( priv for priv in self._owner.global_privilege_names if is_normal_priv(priv) )
new_granted_privs = (self._global_privs - self._orig_global_privs) & all_normal_privs
orig_revoked_privs = all_normal_privs - self._orig_global_privs
new_revoked_privs = all_normal_privs - self._global_privs - orig_revoked_privs
if new_granted_privs or limits_changed:
if 'Grant_priv' in new_granted_privs:
account_limits['GRANT'] = 'OPTION'
new_granted_privs.remove('Grant_priv')
if (all_normal_privs - new_granted_privs) <= set(['Grant_priv']):
priv_list = ['ALL PRIVILEGES']
else:
priv_list = [ PrivilegeInfo[priv][0] for priv in new_granted_privs ]
fields['granted_privs'] = ', '.join(priv_list) or 'USAGE'
grant_query = GRANT_GLOBAL_PRIVILEGES_QUERY % fields
with_clause = ''
for key, value in account_limits.iteritems(): #name, value in zip(names, values):
if value != self._orig_account_limits.get(key):
if key == "GRANT" and value == "OPTION":
queries.append(grant_query + "WITH GRANT OPTION")
continue
if not with_clause:
with_clause = ' WITH '
with_clause += "%s %s "%(key, value)
if self._owner.ctrl_be.target_version and self._owner.ctrl_be.target_version >= Version(8, 0, 5):
queries.append(grant_query)
queries.append((ALTER_USER_RESOURCES % fields) + with_clause)
else:
queries.append(grant_query + with_clause)
if new_revoked_privs:
if all_normal_privs - new_revoked_privs: #set(self._owner.global_privilege_names) - revoked_privs_set: # Revoke a subset of all privs
priv_list = [ PrivilegeInfo[priv][0] for priv in new_revoked_privs ]
fields['revoked_privs'] = ', '.join(priv_list)
queries.append(REVOKE_GLOBAL_PRIVILEGES_QUERY % fields)
else: # All privs are to be revoked so use the revoke all query
queries.append(REVOKE_ALL % fields)
if self.password != self._orig_password and not password_already_set:
change_pw = CHANGE_PASSWORD_QUERY if self._owner.ctrl_be.target_version and self._owner.ctrl_be.target_version < Version(5,7,6) else CHANGE_PASSWORD_QUERY_576
blank_pw = BLANK_PASSWORD_QUERY if self._owner.ctrl_be.target_version and self._owner.ctrl_be.target_version < Version(5,7,6) else BLANK_PASSWORD_QUERY
# special hack required by server to handle sha256 password accounts
if self._owner.ctrl_be.target_version and self._owner.ctrl_be.target_version < Version(8, 0, 5):
if self.auth_plugin == "sha256_password":
queries.append("SET old_passwords = 2")
else:
queries.append("SET old_passwords = 0")
if fields["password"]:
queries.append(change_pw % fields)
else:
queries.append(blank_pw % fields)
action = "changing" if self.is_commited else "creating"
for query in queries:
try:
self._owner.ctrl_be.exec_sql(query)
except QueryError, e:
if e.error == 1142:
raise Exception("Error %s account %s@%s: Insufficient rights to perform operation"%(action, self.username, self.host))
else:
raise Exception("Error %s account %s@%s: %s"%(action, self.username, self.host, e.errortext or e))
except Exception, e:
raise Exception("Error %s account %s@%s: %s"%(action, self.username, self.host, e))
def refresh_mdl_list(self):
self.mdl_list_held.clear()
self.mdl_blocked_icon.show(False)
waiting_label_text = "This connection is not waiting for any locks."
try:
nodes = self.connection_list.get_selection()
if nodes and len(nodes) == 1:
thread_id = nodes[0].get_long(7)
result = self.ctrl_be.exec_query("SELECT * FROM performance_schema.metadata_locks WHERE owner_thread_id = %s" % thread_id)
if result is not None:
while result.nextRow():
lock_status = result.stringByName("LOCK_STATUS")
if lock_status == "PENDING":
otype = result.stringByName("OBJECT_TYPE")
oschema = result.stringByName("OBJECT_SCHEMA")
oname = result.stringByName("OBJECT_NAME")
obj_name = [oschema, oname]
if otype == "GLOBAL":
obj_name = "<global>"
else:
obj_name = ".".join([o for o in obj_name if o is not None])
self.mdl_blocked_icon.show(True)
sub_expr = "OBJECT_TYPE = '%s'" % otype
if oschema:
sub_expr += " AND OBJECT_SCHEMA = '%s'" % escape_sql_string(oschema)
if oname:
sub_expr += " AND OBJECT_NAME = '%s'" % escape_sql_string(oname)
lock_type = result.stringByName("LOCK_TYPE")
lock_duration = result.stringByName("LOCK_DURATION")
subresult = self.ctrl_be.exec_query("""SELECT *
FROM performance_schema.metadata_locks
WHERE %s AND LOCK_STATUS = 'GRANTED'""" % sub_expr)
owners = []
while subresult and subresult.nextRow():
owners.append(subresult.intByName("OWNER_THREAD_ID"))
owner_list = ", ".join([str(i) for i in owners])
if len(owners) == 1:
waiting_label_text = "The connection is waiting for a lock on\n%s %s,\nheld by thread %s." % (otype.lower(), obj_name, owner_list)
else:
waiting_label_text = "The connection is waiting for a lock on\n%s %s,\nheld by threads %s" % (otype.lower(), obj_name, owner_list)
waiting_label_text += "\nType: %s\nDuration: %s" % (lock_type, lock_duration)
elif lock_status == "GRANTED":
node = self.mdl_list_held.add_node()
otype = result.stringByName("OBJECT_TYPE")
oschema = result.stringByName("OBJECT_SCHEMA")
oname = result.stringByName("OBJECT_NAME")
obj_name = [oschema, oname]
if otype == "GLOBAL":
node.set_string(0, "<global>")
else:
node.set_string(0, ".".join([o for o in obj_name if o is not None]))
node.set_icon_path(0, self.icon_for_object_type.get(otype))
sub_expr = "OBJECT_TYPE = '%s'" % otype
if oschema:
sub_expr += " AND OBJECT_SCHEMA = '%s'" % escape_sql_string(oschema)
if oname:
sub_expr += " AND OBJECT_NAME = '%s'" % escape_sql_string(oname)
node.set_string(1, result.stringByName("LOCK_TYPE"))
node.set_string(2, result.stringByName("LOCK_DURATION"))
subresult = self.ctrl_be.exec_query("""SELECT OWNER_THREAD_ID, LOCK_TYPE, LOCK_DURATION
FROM performance_schema.metadata_locks
WHERE %s AND LOCK_STATUS = 'PENDING'""" % sub_expr)
while subresult and subresult.nextRow():
subnode = node.add_child()
subnode.set_string(0, "thread %s" % subresult.intByName("OWNER_THREAD_ID"))
subnode.set_string(1, subresult.stringByName("LOCK_TYPE"))
subnode.set_string(2, subresult.stringByName("LOCK_DURATION"))
else:
waiting_label_text = ""
except Exception, e:
import traceback
log_error("Error looking up metadata lock information: %s\n" % traceback.format_exc())
mforms.Utilities.show_error("Lookup Metadata Locks", "Error looking up metadata lock information: %s" % e, "OK", "", "")
result = modules.DbMySQLQuery.executeQuery(self._owner._connection, query)
if result < 0:
raise Exception("Error querying information_schema table: "+modules.DbMySQLQuery.lastError())
try:
is_privs = []
while modules.DbMySQLQuery.resultNextRow(result):
table = result.stringByName("Table_name")
table_privs = result.stringByName("Table_priv")
is_privs.append((table, table_priv and table_privs.split(",") or []))
finally:
modules.DbMySQLQuery.closeResult(result)
"""
# privs from mysql tables
query = GET_ACCOUNT_MYSQL_TABLE_PRIVS_QUERY % {"user":escape_sql_string(username),"host":escape_sql_string(hostname)}
try:
result = self._owner.ctrl_be.exec_query(query)
except Exception, e:
raise Exception("Error querying mysql table: %s" % e)
mysql_privs = {}
while result.nextRow():
table = result.stringByName("Table_name")
table_privs = result.stringByName("Table_priv")
mysql_privs[table] = table_privs and table_privs.split(",") or []
# interpret the privileges
for name, (db, tables, required_privs, grant, revoke) in AdminAttributes.items():
if db == "mysql":
ok = True
