File: //lib/python2.7/site-packages/lwdbadmin/mysql/libmysql.py
import logging
import MySQLdb
import ConfigParser
import warnings
import os
SYSTEMDBS = ['mysql', 'information_schema', 'performance_schema', 'administration_schema','sys']
logger = logging.getLogger('libmysql')
DESC_PATH = '/etc/default/locaweb/description'
class LibMySQLException(Exception): pass
class DatabaseNotExists(LibMySQLException): pass
#This is a high level API. Prefer use that'
def config_file(osuser=None):
if osuser and os.path.exists('%s/reseller' % DESC_PATH):
logger.debug("Loading config file for OSUSER: %s" % osuser)
return '/etc/locaweb/lwdbadmin/{}.cnf'.format(osuser)
else:
logger.debug("Loading default mysqld config file")
return '/etc/locaweb/lwdbadmin/mysql.cnf'
def createdatabase(database, login, password, host='%', osuser=None):
mysql = controller(config_file(osuser))
mysql.createDatabase(database)
# User has a strictier schema than database, so we can fail now
# We could invert these method calls, but then we need to change
# some internal lib rules inside userAdd method.
# Given the fact that we reach here, we know that we created this
# database and we can remove it safely
try:
mysql.addUser(database, login, password, host, osuser)
except Exception, e:
mysql.rmDatabase(database)
raise e
def removedatabase(database, osuser=None):
mysql = controller(config_file(osuser))
mysql.exists(database)
users = mysql.getUsersHost(database)
for user, host in users:
mysql.rmUser(database, user, host)
mysql.rmDatabase(database)
def activatedatabase(database, osuser=None):
mysql = controller(config_file(osuser))
mysql.exists(database)
mysql.startDatabase(database)
def deactivedatabase(database, osuser=None):
mysql = controller(config_file(osuser))
mysql.exists(database)
mysql.stopDatabase(database)
mysql.killQueries(database)
def setpassword(login, password, osuser=None):
mysql = controller(config_file(osuser))
mysql.exists(login)
mysql.setPassword(login, password)
def isactive(database, osuser=None):
mysql = controller(config_file(osuser))
mysql.exists(database)
return mysql.isActive(database)
def killqueries(database, osuser=None):
mysql = controller(config_file(osuser))
mysql.exists(database)
x = mysql.killQueries(database)
return x
class Version(object):
def __init__(self, version):
'''receive a X.Y.Z version schema'''
major, minor, release = version.split(".")
self.major = int(major)
self.minor = int(minor)
self.release = int(release)
def __cmp__(self, other):
major = self.major - other.major
if major != 0:
return major
minor = self.minor - other.minor
if minor != 0:
return minor
return self.release - other.release
class controller(object):
""" PaaS controller class provides basic MySQL operations """
def __init__(self, cfile=config_file()):
self.config = ConfigParser.ConfigParser()
self.cfgfile = self.config.readfp(open(cfile))
try:
self.host = self.config.get('MySQL', 'host')
except ConfigParser.NoOptionError as e:
self.host = None
logger.exception('{}. Setting "None" to variable'.format(e))
try:
self.socket = self.config.get('MySQL', 'socket')
except ConfigParser.NoOptionError as e:
self.socket = None
logger.exception('{}. Setting "None" to variable'.format(e))
try:
self.userc = self.config.get('MySQL', 'user')
except ConfigParser.NoOptionError as e:
self.userc = None
logger.exception('{}. Setting "None" to variable'.format(e))
try:
self.passwordc = self.config.get('MySQL', 'pass')
except ConfigParser.NoOptionError as e:
self.passwordc = None
logger.exception('{}. Setting "None" to variable'.format(e))
self.connect()
version_57 = Version("5.7.0")
version_80 = Version("8.0.0")
version_database = self.version()
if version_database < version_57:
self.impl = MySQL51(self.conn)
elif version_database < version_80:
self.impl = MySQL57(self.conn)
elif version_database >= version_80 and os.path.exists('%s/reseller' % DESC_PATH):
self.impl = MySQL80Reseller(self.conn)
def connect(self):
""" Connect to local database server """
try:
if self.socket:
logger.debug('Connecting to MySQL server socket %s with user %s' % (self.socket, self.userc))
self.conn = MySQLdb.connect(unix_socket=self.socket, user=self.userc, passwd=self.passwordc)
else:
logger.debug('Connecting to MySQL server %s with user %s' % (self.host, self.userc))
self.conn = MySQLdb.connect(host=self.host, user=self.userc, passwd=self.passwordc)
except Exception, e:
logger.error('Could not connect to mysql service: %s' % e)
raise LibMySQLException('Could not connect to MySQL server')
else:
self.cursor = self.conn.cursor()
def version(self):
""" Get MySQL Version """
cursor = self.conn.cursor()
cursor.execute("SELECT VERSION()")
version = cursor.fetchone()[0]
return Version(version.split("-")[0])
def databaseStatus(self, database):
return self.impl.databaseStatus(database)
def getUsers(self, database):
return self.impl.getUsers(database)
def getUsersHost(self, database):
return self.impl.getUsersHost(database)
def addUser(self, database, username, password, host='%', osuser=None):
self.impl.addUser(database, username, password, host, osuser)
def rmUser(self, database, username, host='%'):
self.impl.rmUser(database, username, host)
def setDefiner(self, database, username, host='%'):
return self.impl.setDefiner(database, username, host)
def setPermission(self, database, username, host='%'):
return self.impl.setPermission(database, username, host)
def createDatabase(self, database):
self.impl.createDatabase(database)
def rmDatabase(self, database):
self.impl.rmDatabase(database)
def stopDatabase(self, database):
self.impl.stopDatabase(database)
def startDatabase(self, database):
self.impl.startDatabase(database)
def isActive(self, database):
return self.impl.isActive(database)
def setPassword(self, user, password, host='%'):
self.impl.setPassword(user,password, host)
def userStatus(self, user):
return self.impl.userStatus(user)
def killQueries(self, username):
return self.impl.killQueries(username)
def exists(self, database):
self.impl.exists(database)
class MySQL51(object):
def __init__(self, connection):
self.conn = connection
def databaseStatus(self, database):
""" Get database status (exists / doesnt exists) """
try:
self.conn.select_db(database)
except Exception, e:
return False
else:
return True
def getUsers(self, database):
""" Get users associated with a database """
cursor = self.conn.cursor()
cursor.execute("SELECT User FROM mysql.db WHERE Db = %s AND User <> 'root' GROUP BY User", (database,))
users = []
for user in cursor.fetchall():
users.append(user[0])
return users
def getUsersHost(self, database):
""" Get (user, hostname) tuples associated with a database """
self.conn.select_db('mysql')
cursor = self.conn.cursor()
cursor.execute("SELECT User, Host FROM db WHERE Db = %s", (database,))
users = []
for user in cursor:
users.append(user)
return users
def addUser(self, database, username, password, host='%', osuser=None):
""" Create new user to database"""
if not self.databaseStatus(database):
logger.error('Could not create new user, database %s does not exists' % database)
raise DatabaseNotExists('Database {0} does not exists'.format(database))
cursor = self.conn.cursor()
if database == username:
cursor.execute("GRANT ALL ON {0}.* TO %s@%s IDENTIFIED BY %s WITH GRANT OPTION".format(database), (username, host, password))
else:
cursor.execute("GRANT ALL ON {0}.* TO %s@%s IDENTIFIED BY %s".format(database), (username, host, password))
logger.debug('Database %s created' % database)
def rmUser(self, database, username, host='%'):
""" Remove user from database """
cursor = self.conn.cursor()
cursor.execute("DROP USER %s@%s", (username, host))
def setDefiner(self, database, username, host='%'):
cursor = self.conn.cursor()
definer = "%s@%s" % (username, host)
cursor.execute("UPDATE mysql.proc SET definer=%s WHERE db=%s", (definer, database))
cursor.execute('FLUSH PRIVILEGES')
return True
def setPermission(self, database, username, host='%'):
cursor = self.conn.cursor()
if database == username:
cursor.execute("GRANT GRANT OPTION ON {0}.* TO %s@%s".format(database), (username, host))
else:
cursor.execute("REVOKE GRANT OPTION ON {0}.* FROM %s@%s".format(database), (username, host))
return True
def createDatabase(self, database):
""" Create new database on server """
if self.databaseStatus(database):
raise LibMySQLException('Database already exists')
cursor = self.conn.cursor()
cursor.execute("CREATE DATABASE %s COLLATE LATIN1_GENERAL_CI" % database)
logger.info('Database %s created' % database)
def rmDatabase(self, database):
""" Remove database from server """
if database in SYSTEMDBS:
raise LibMySQLException('Cannot remove {0}: system database'.format(database))
if not self.databaseStatus(database):
raise LibMySQLException('Database not found')
cursor = self.conn.cursor()
cursor.execute("DROP DATABASE %s" % database)
logger.info('Database %s removed' % database)
def stopDatabase(self, database):
""" Stop database from server """
if not self.isActive(database):
return
# We have 2 ways to stop a database. The new way is to change only the password
# Now, we control users, not databases
# Changing databases can cause MySQL crashes
cursor = self.conn.cursor()
cursor.execute("""SELECT User, Host, Password
FROM mysql.user
WHERE User = %s""",
(database,))
cursor2 = self.conn.cursor()
for user, host, password in cursor:
if len(password) == 41 and password[0] == '*':
# password normal
new_password = password[1:] + '#'
elif len(password) == 16:
# old password
new_password = password + '#########################'
else:
logger.info('Database %s already deactivated' % database)
return
cursor2.execute("""UPDATE mysql.user
SET Password = %s
WHERE User = %s
AND Host = %s""",
(new_password, user, host))
cursor.execute('FLUSH PRIVILEGES')
logger.info('Database %s deactivated' % database)
def startDatabase(self, database):
""" Start database from server """
if self.isActive(database):
return
cursor = self.conn.cursor()
# Old method
with warnings.catch_warnings():
warnings.simplefilter("ignore")
cursor.execute("""UPDATE mysql.db
SET db = %s
WHERE db in (password(%s), old_password(%s), md5(%s))
AND user != 'admin'""", [database] * 4)
# New method
cursor.execute("""SELECT User, Host, Password
FROM mysql.user
WHERE user = %s""", (database,))
cursor2 = self.conn.cursor()
for user, host, password in cursor:
if password[-1] == '#':
new_password = password.strip('#')
if len(new_password) == 40:
new_password = '*' + new_password
cursor2.execute("""UPDATE mysql.user
SET Password = %s
WHERE User = %s AND Host = %s""",
(new_password, user, host))
self.setPermission(database, user, host)
cursor.execute('FLUSH PRIVILEGES')
logger.info('Database %s activated' % database)
def isActive(self, database):
"Given a database, check if its started or stopped"
if not self.databaseStatus(database):
raise DatabaseNotExists('Database {0} does not exists'.format(database))
cursor = self.conn.cursor()
# check old method first
with warnings.catch_warnings():
warnings.simplefilter("ignore")
cursor.execute("""SELECT 1
FROM mysql.db
WHERE Db in (password(%s), old_password(%s), md5(%s))""",
[database] * 3)
if cursor.rowcount:
return False
# New method, by user, not database
cursor.execute("""SELECT Host, User, Password
FROM mysql.user
WHERE User = %s""",
(database,))
# We reach a problem, the user does not exists
if cursor.rowcount == 0:
raise LibMySQLException("User {0} not exists in mysql.db".format(database))
for user, host, password in cursor:
if password[-1] == '#':
return False
return True
def setPassword(self, user, password, host='%'):
""" Set a new password for user associated with a database """
if not self.isActive(user):
raise Exception('User %s is locked because database is disabled' % user)
cursor = self.conn.cursor()
cursor.execute("UPDATE mysql.user SET plugin ='mysql_native_password' WHERE user = '%s'" % user)
cursor.execute("FLUSH PRIVILEGES")
cursor.execute("SET PASSWORD FOR '%s'@'%s' = password('%s')" % (user, host, password))
logger.info('Password for %s changed' % user)
def userStatus(self, user):
""" Verify if the user is valid """
self.conn.select_db('mysql')
cursor = self.conn.cursor()
output = cursor.execute("SELECT User FROM user WHERE User = '%s'" % user)
if output == 0:
return False
else:
return True
def killQueries(self, username):
cursor = self.conn.cursor()
with warnings.catch_warnings():
warnings.simplefilter("ignore")
cursor.execute('SELECT id FROM information_schema.processlist WHERE User = %s', (username,))
cursor2 = self.conn.cursor()
x = 0
for id_ in cursor:
# filter terminate queries
try:
cursor2.execute('KILL %s' % id_)
x += 1
except Exception, e:
pass
return x
def exists(self, database):
cursor = self.conn.cursor()
cursor.execute("SHOW DATABASES LIKE %s", (database,))
if not cursor.rowcount:
raise DatabaseNotExists(database)
class MySQL57(object):
def __init__(self, connection):
self.conn = connection
def databaseStatus(self, database):
""" Get database status (exists / doesnt exists) """
try:
self.conn.select_db(database)
except Exception, e:
return False
else:
return True
def getUsersHost(self, database):
""" Get (user, hostname) tuples associated with a database """
cursor = self.conn.cursor()
cursor.execute("SELECT User, Host FROM mysql.db WHERE Db = %s", (database,))
users = []
for user in cursor.fetchall():
users.append(user)
return users
def addUser(self, database, username, password, host='%', osuser=None):
""" Create new user to database"""
if not self.databaseStatus(database):
logger.error('Could not create new user, database %s does not exists' % database)
raise DatabaseNotExists('Database {0} does not exists'.format(database))
cursor = self.conn.cursor()
cursor.execute("CREATE USER %s@%s IDENTIFIED BY %s", (username, host, password))
cursor2 = self.conn.cursor()
cursor2.execute("GRANT ALL ON {0}.* TO %s@%s".format(database), (username, host,))
logger.debug('User %s created' % database)
def rmUser(self,database, username, host='%'):
""" Remove user from database """
cursor = self.conn.cursor()
cursor.execute("DROP USER %s@%s", (username, host))
def setDefiner(self, database, username, host='%'):
cursor = self.conn.cursor()
definer = "%s@%s" % (username, host)
cursor.execute("UPDATE mysql.proc SET definer=%s WHERE db=%s", (definer, database))
cursor.execute('FLUSH PRIVILEGES')
return True
def setPermission(self, database, username, host='%'):
cursor = self.conn.cursor()
if database == username:
cursor.execute("GRANT GRANT OPTION ON {0}.* TO %s@%s".format(database), (username, host))
else:
cursor.execute("REVOKE GRANT OPTION ON {0}.* FROM %s@%s".format(database), (username, host))
return True
def createDatabase(self, database):
""" Create new database on server """
if self.databaseStatus(database):
raise LibMySQLException('Database already exists')
cursor = self.conn.cursor()
cursor.execute("CREATE DATABASE %s COLLATE LATIN1_GENERAL_CI" % database)
logger.info('Database %s created' % database)
def rmDatabase(self, database):
""" Remove database from server """
if database in SYSTEMDBS:
raise LibMySQLException('Cannot remove {0}: system database'.format(database))
if not self.databaseStatus(database):
raise LibMySQLException('Database not found')
cursor = self.conn.cursor()
cursor.execute("DROP DATABASE %s" % database)
logger.info('Database %s removed' % database)
def stopDatabase(self, database):
""" Stop database from server """
if not self.isActive(database):
return
# Now mysql lock account
cursor = self.conn.cursor()
cursor.execute("SELECT User, Host FROM mysql.user WHERE user = %s", (database,))
hosts = cursor.fetchall()
for database, host in hosts:
cursor.execute("ALTER USER %s@%s ACCOUNT LOCK", (database, host,))
logger.info('Database %s deactivated' % database)
def startDatabase(self, database):
""" Start database from server """
if self.isActive(database):
return
# Now mysql lock account
cursor = self.conn.cursor()
cursor.execute("SELECT User, Host FROM mysql.user WHERE user = %s", (database,))
hosts = cursor.fetchall()
for database, host in hosts:
cursor.execute("ALTER USER %s@%s ACCOUNT UNLOCK", (database, host,))
logger.info('Database %s activated' % database)
def isActive(self, database):
"Given a database, check if its started or stopped"
if not self.databaseStatus(database):
raise DatabaseNotExists('Database {0} does not exists'.format(database))
cursor = self.conn.cursor()
# New method, by user, not database
cursor.execute("SELECT Db FROM mysql.db WHERE User = %s", (database,))
# We reach a problem, the user does not exists
if cursor.rowcount == 0:
raise LibMySQLException("User {0} not exists in mysql.db".format(database))
cursor.execute("SELECT account_locked FROM mysql.user WHERE User = %s", (database,))
result = cursor.fetchone()
if 'N' not in result:
return False
return True
def setPassword(self, user, password, host='%'):
""" Set a new password for user associated with a database """
if not self.isActive(user):
raise Exception('User %s is locked because database is disabled' % user)
cursor = self.conn.cursor()
cursor.execute("ALTER USER '%s'@'%s' IDENTIFIED BY '%s'" % (user, host, password))
logger.info('Password for %s changed' % user)
def userStatus(self, user):
""" Verify if the user is valid """
self.conn.select_db('mysql')
cursor = self.conn.cursor()
output = cursor.execute("SELECT User FROM user WHERE User = '%s'" % user)
if output == 0:
return False
else:
return True
def killQueries(self, username):
cursor = self.conn.cursor()
with warnings.catch_warnings():
warnings.simplefilter("ignore")
cursor.execute('SELECT id FROM information_schema.processlist WHERE User = %s', (username,))
cursor2 = self.conn.cursor()
x = 0
for id_ in cursor:
# filter terminate queries
try:
cursor2.execute('KILL %s' % id_)
x += 1
except Exception, e:
pass
return x
def exists(self, database):
cursor = self.conn.cursor()
cursor.execute("SHOW DATABASES LIKE %s", (database,))
if not cursor.rowcount:
raise DatabaseNotExists(database)
class MySQL80Reseller(object):
def __init__(self, connection):
config = ConfigParser.ConfigParser()
cfgfile = config.readfp(open("/etc/locaweb/lwdbadmin/proxysql.cnf"))
try:
proxy_host = config.get('proxysql', 'host')
except ConfigParser.NoOptionError as e:
logger.exception('{}. Unable to set variable'.format(e))
try:
proxy_admin_user = config.get('proxysql', 'user')
except ConfigParser.NoOptionError as e:
logger.exception('{}. Unable to set variable'.format(e))
try:
proxy_admin_password = config.get('proxysql', 'password')
except ConfigParser.NoOptionError as e:
logger.exception('{}. Unable to set variable'.format(e))
self.proxyconn = MySQLdb.connect(host=proxy_host, user=proxy_admin_user, passwd=proxy_admin_password, port=6032)
self.conn = connection
def _proxyuserhash(self):
'''
This actions below are necessary to hash passwords whenever theres an insert or update
Source: https://proxysql.com/documentation/password-management/
'''
proxycursor = self.proxyconn.cursor()
script = "LOAD MYSQL USERS TO RUNTIME"
logger.debug("Running script: {}".format(script))
proxycursor.execute(script)
script = "SAVE MYSQL USERS FROM RUNTIME"
logger.debug("Running script: {}".format(script))
proxycursor.execute(script)
script = "SAVE MYSQL USERS TO DISK"
logger.debug("Running script: {}".format(script))
proxycursor.execute(script)
def databaseStatus(self, database):
""" Get database status (exists / doesnt exists) """
try:
self.conn.select_db(database)
except Exception, e:
return False
else:
return True
def getUsersHost(self, database):
""" Get (user, hostname) tuples associated with a database """
cursor = self.conn.cursor()
cursor.execute("SELECT User, Host FROM mysql.db WHERE Db = %s", (database,))
users = []
for user in cursor.fetchall():
users.append(user)
return users
def addUser(self, database, username, password, host='%', osuser=None):
""" Create new user to database and proxysql"""
if not self.databaseStatus(database):
logger.error('Could not create new user, database %s does not exists' % database)
raise DatabaseNotExists('Database {} does not exists'.format(database))
cursor = self.conn.cursor()
script = "CREATE USER '{}'@'{}' IDENTIFIED BY '{}'".format(username, host, password)
logger.debug("Running script: {}".format(script))
cursor.execute(script)
cursor2 = self.conn.cursor()
script = "GRANT ALL ON {}.* TO '{}'@'{}'".format(database, username, host)
logger.debug("Running script: {}".format(script))
cursor2.execute(script)
proxycursor = self.proxyconn.cursor()
script = "INSERT OR REPLACE INTO mysql_users (username, password, active, default_hostgroup, comment) VALUES ('{username}', '{password}', 1, (select hostgroup_id from mysql_servers where hostname LIKE '/var/lib/mysql-clientes/{osuser}/run/mysql_{osuser}.sock' LIMIT 1), 'MySQL credentials for user {osuser}')".format(**locals())
logger.debug("Running script: {}".format(script))
proxycursor.execute(script)
self._proxyuserhash()
logger.debug('User %s created' % database)
def rmUser(self,database, username, host='%'):
""" Remove user from database """
cursor = self.conn.cursor()
cursor.execute("DROP USER %s@%s", (username, host))
proxycursor = self.proxyconn.cursor()
script = "DELETE FROM mysql_users WHERE username = '{username}'".format(**locals())
logger.debug("Running script: {}".format(script))
proxycursor.execute(script)
self._proxyuserhash()
def setDefiner(self, database, username, host='%'):
cursor = self.conn.cursor()
definer = "%s@%s" % (username, host)
cursor.execute("UPDATE mysql.proc SET definer=%s WHERE db=%s", (definer, database))
cursor.execute('FLUSH PRIVILEGES')
return True
def setPermission(self, database, username, host='%'):
cursor = self.conn.cursor()
if database == username:
cursor.execute("GRANT GRANT OPTION ON {0}.* TO %s@%s".format(database), (username, host))
else:
cursor.execute("REVOKE GRANT OPTION ON {0}.* FROM %s@%s".format(database), (username, host))
return True
def createDatabase(self, database):
""" Create new database on server """
if self.databaseStatus(database):
raise LibMySQLException('Database already exists')
cursor = self.conn.cursor()
cursor.execute("CREATE DATABASE %s COLLATE LATIN1_GENERAL_CI" % database)
logger.info('Database %s created' % database)
def rmDatabase(self, database):
""" Remove database from server """
if database in SYSTEMDBS:
raise LibMySQLException('Cannot remove {0}: system database'.format(database))
if not self.databaseStatus(database):
raise LibMySQLException('Database not found')
cursor = self.conn.cursor()
cursor.execute("DROP DATABASE %s" % database)
logger.info('Database %s removed' % database)
def stopDatabase(self, database):
""" Stop database from server """
if not self.isActive(database):
return
# Now mysql lock account
cursor = self.conn.cursor()
cursor.execute("SELECT User, Host FROM mysql.user WHERE user = %s", (database,))
hosts = cursor.fetchall()
for database, host in hosts:
cursor.execute("ALTER USER %s@%s ACCOUNT LOCK", (database, host,))
proxycursor = self.proxyconn.cursor()
script = "UPDATE mysql_users SET active = 0 WHERE username = '{database}'".format(**locals())
logger.debug("Running script: {}".format(script))
proxycursor.execute(script)
self._proxyuserhash()
logger.info('Database %s deactivated' % database)
def startDatabase(self, database):
""" Start database from server """
if self.isActive(database):
return
# Now mysql lock account
cursor = self.conn.cursor()
cursor.execute("SELECT User, Host FROM mysql.user WHERE user = %s", (database,))
hosts = cursor.fetchall()
for database, host in hosts:
cursor.execute("ALTER USER %s@%s ACCOUNT UNLOCK", (database, host,))
proxycursor = self.proxyconn.cursor()
script = "UPDATE mysql_users SET active = 1 WHERE username = '{database}'".format(**locals())
logger.debug("Running script: {}".format(script))
proxycursor.execute(script)
self._proxyuserhash()
logger.info('Database %s activated' % database)
def isActive(self, database):
"Given a database, check if its started or stopped"
if not self.databaseStatus(database):
raise DatabaseNotExists('Database {0} does not exists'.format(database))
cursor = self.conn.cursor()
# New method, by user, not database
cursor.execute("SELECT Db FROM mysql.db WHERE User = %s", (database,))
# We reach a problem, the user does not exists
if cursor.rowcount == 0:
raise LibMySQLException("User {0} not exists in mysql.db".format(database))
cursor.execute("SELECT account_locked FROM mysql.user WHERE User = %s", (database,))
result = cursor.fetchone()
proxycursor = self.proxyconn.cursor()
script = "SELECT active FROM mysql_users WHERE username = '{database}'".format(**locals())
logger.debug("Running script: {}".format(script))
proxycursor.execute(script)
proxyresult = proxycursor.fetchall()
if 'N' not in result and '0' in [ row[0] for row in proxyresult]:
return False
return True
def setPassword(self, user, password, host='%'):
""" Set a new password for user associated with a database """
if not self.isActive(user):
raise Exception('User %s is locked because database is disabled' % user)
cursor = self.conn.cursor()
cursor.execute("ALTER USER '%s'@'%s' IDENTIFIED BY '%s'" % (user, host, password))
proxycursor = self.proxyconn.cursor()
script = "UPDATE mysql_users SET password = '{password}' WHERE username = '{user}'".format(**locals())
logger.debug("Running script: {}".format(script))
proxycursor.execute(script)
self._proxyuserhash()
logger.info('Password for %s changed' % user)
def userStatus(self, user):
""" Verify if the user is valid """
self.conn.select_db('mysql')
cursor = self.conn.cursor()
output = cursor.execute("SELECT User FROM user WHERE User = '%s'" % user)
if output == 0:
return False
else:
return True
def killQueries(self, username):
cursor = self.conn.cursor()
with warnings.catch_warnings():
warnings.simplefilter("ignore")
cursor.execute('SELECT id FROM information_schema.processlist WHERE User = %s', (username,))
cursor2 = self.conn.cursor()
x = 0
for id_ in cursor:
# filter terminate queries
try:
cursor2.execute('KILL %s' % id_)
x += 1
except Exception, e:
pass
return x
def exists(self, database):
cursor = self.conn.cursor()
cursor.execute("SHOW DATABASES LIKE %s", (database,))
if not cursor.rowcount:
raise DatabaseNotExists(database)