def enable_root(self, root_password=None):
"""Enable the root user global access and/or
reset the root password.
"""
user = models.MySQLUser.root(password=root_password)
with mysql_util.SqlClient(self.mysql_app.get_engine()) as client:
try:
cu = sql_query.CreateUser(user.name, host=user.host)
t = text(str(cu))
client.execute(t, **cu.keyArgs)
except (exc.OperationalError, exc.InternalError) as err:
# Ignore, user is already created, just reset the password
# TODO(rnirmal): More fine grained error checking later on
LOG.debug(err)
with mysql_util.SqlClient(self.mysql_app.get_engine()) as client:
uu = sql_query.SetPassword(
user.name, host=user.host, new_password=user.password,
ds=CONF.datastore_manager, ds_version=CONF.datastore_version
)
t = text(str(uu))
client.execute(t)
LOG.debug("CONF.root_grant: %(grant)s CONF.root_grant_option: "
"%(grant_option)s.",
{'grant': CONF.root_grant,
'grant_option': CONF.root_grant_option})
g = sql_query.Grant(permissions=CONF.root_grant,
user=user.name,
host=user.host,
grant_option=CONF.root_grant_option)
t = text(str(g))
client.execute(t)
return user.serialize()
def update_attributes(self, username, hostname, user_attrs):
"""Change the attributes of an existing user."""
LOG.debug("Changing user attributes for user %s.", username)
user = self._get_user(username, hostname)
new_name = user_attrs.get('name')
new_host = user_attrs.get('host')
new_password = user_attrs.get('password')
if new_name or new_host or new_password:
with mysql_util.SqlClient(self.mysql_app.get_engine(),
use_flush=True) as client:
if new_password is not None:
uu = sql_query.SetPassword(
user.name,
host=user.host,
new_password=new_password,
ds=CONF.datastore_manager,
ds_version=CONF.datastore_version)
t = text(str(uu))
client.execute(t)
if new_name or new_host:
uu = sql_query.RenameUser(user.name,
host=user.host,
new_user=new_name,
new_host=new_host)
t = text(str(uu))
client.execute(t)
def create_users(self, users):
"""Create users and grant them privileges for the
specified databases.
"""
with mysql_util.SqlClient(self.mysql_app.get_engine(),
use_flush=True) as client:
for item in users:
user = models.MySQLUser.deserialize(item)
user.check_create()
cu = sql_query.CreateUser(user.name,
host=user.host,
clear=user.password)
t = text(str(cu))
client.execute(t, **cu.keyArgs)
for database in user.databases:
mydb = models.MySQLSchema.deserialize(database)
g = sql_query.Grant(permissions='ALL',
database=mydb.name,
user=user.name,
host=user.host)
t = text(str(g))
LOG.debug('Creating user, command: %s', str(g))
client.execute(t)
def is_root_enabled(self):
"""Return True if root access is enabled; False otherwise."""
with mysql_util.SqlClient(self.mysql_app.get_engine()) as client:
t = text(sql_query.ROOT_ENABLED)
result = client.execute(t)
LOG.debug("Found %s with remote root access.", result.rowcount)
return result.rowcount != 0
def delete_user_by_name(self, name, host='%'):
with mysql_util.SqlClient(self.mysql_app.get_engine(),
use_flush=True) as client:
du = sql_query.DropUser(name, host=host)
t = text(str(du))
LOG.debug("delete_user_by_name: %s", t)
client.execute(t)
def grant_access(self, username, hostname, databases):
"""Grant a user permission to use a given database."""
user = self._get_user(username, hostname)
mydb = None # cache the model as we just want name validation
with mysql_util.SqlClient(self.mysql_app.get_engine(),
use_flush=True) as client:
for database in databases:
try:
if mydb:
mydb.name = database
else:
mydb = models.MySQLSchema(name=database)
mydb.check_reserved()
except ValueError:
LOG.exception("Error granting access")
raise exception.BadRequest(
_("Grant access to %s is not allowed") % database)
g = sql_query.Grant(permissions='ALL',
database=mydb.name,
user=user.name,
host=user.host,
hashed=user.password)
t = text(str(g))
client.execute(t)
def _get_user(self, username, hostname):
"""Return a single user matching the criteria."""
user = None
try:
# Could possibly throw a ValueError here.
user = models.MySQLUser(name=username)
user.check_reserved()
except ValueError as ve:
LOG.exception("Error Getting user information")
err_msg = encodeutils.exception_to_unicode(ve)
raise exception.BadRequest(
_("Username %(user)s is not valid"
": %(reason)s") % {
'user': username,
'reason': err_msg
})
with mysql_util.SqlClient(self.mysql_app.get_engine()) as client:
q = sql_query.Query()
q.columns = ['User', 'Host']
q.tables = ['mysql.user']
q.where = [
"Host != 'localhost'",
"User = '******'" % username,
"Host = '%s'" % hostname
]
q.order = ['User', 'Host']
t = text(str(q))
result = client.execute(t).fetchall()
LOG.debug("Getting user information %s.", result)
if len(result) != 1:
return None
found_user = result[0]
user.host = found_user['Host']
self._associate_dbs(user)
return user
def delete_database(self, database):
"""Delete the specified database."""
with mysql_util.SqlClient(self.mysql_app.get_engine()) as client:
mydb = models.MySQLSchema.deserialize(database)
mydb.check_delete()
dd = sql_query.DropDatabase(mydb.name)
t = text(str(dd))
client.execute(t)
def revoke_access(self, username, hostname, database):
"""Revoke a user's permission to use a given database."""
user = self._get_user(username, hostname)
with mysql_util.SqlClient(self.mysql_app.get_engine()) as client:
r = sql_query.Revoke(database=database,
user=user.name,
host=user.host)
t = text(str(r))
client.execute(t)
def get_service_status(self):
try:
with mysql_util.SqlClient(self.app.get_engine()) as client:
cmd = "SELECT 1;"
client.execute(cmd)
LOG.debug("Database service check: database query is responsive")
return service_status.ServiceStatuses.HEALTHY
except Exception:
return super(MySqlManager, self).get_service_status()
def secure(self):
LOG.info("Securing MySQL now.")
root_pass = self.get_auth_password(file="root.cnf")
admin_password = utils.generate_random_password()
engine = sqlalchemy.create_engine(
CONNECTION_STR_FORMAT % ('root', root_pass), echo=True)
with mysql_util.SqlClient(engine, use_flush=False) as client:
self._create_admin_user(client, admin_password)
engine = sqlalchemy.create_engine(
CONNECTION_STR_FORMAT % (ADMIN_USER_NAME,
urllib.parse.quote(admin_password)),
echo=True)
with mysql_util.SqlClient(engine) as client:
self._remove_anonymous_user(client)
self.save_password(ADMIN_USER_NAME, admin_password)
LOG.info("MySQL secure complete.")
def grant_replication_privilege(self, replication_user):
LOG.info("Granting replication slave privilege for %s",
replication_user['name'])
with mysql_util.SqlClient(self.get_engine(), use_flush=True) as client:
g = sql_query.Grant(permissions=['REPLICATION SLAVE'],
user=replication_user['name'],
clear=replication_user['password'])
t = text(str(g))
client.execute(t)
def create_databases(self, databases):
"""Create the list of specified databases."""
with mysql_util.SqlClient(self.mysql_app.get_engine()) as client:
for item in databases:
mydb = models.MySQLSchema.deserialize(item)
mydb.check_create()
cd = sql_query.CreateDatabase(mydb.name, mydb.character_set,
mydb.collate)
t = text(str(cd))
LOG.debug('Creating database, command: %s', str(cd))
client.execute(t)
def apply_overrides(self, overrides):
with mysql_util.SqlClient(self.get_engine()) as client:
for k, v in overrides.items():
byte_value = guestagent_utils.to_bytes(v)
q = sql_query.SetServerVariable(key=k, value=byte_value)
t = text(str(q))
try:
client.execute(t)
except exc.OperationalError:
output = {'key': k, 'value': byte_value}
LOG.error("Unable to set %(key)s with value %(value)s.",
output)
def stop_slave(self, for_failover):
LOG.info("Stopping slave replication.")
replication_user = None
with mysql_util.SqlClient(self.get_engine()) as client:
result = client.execute('SHOW SLAVE STATUS')
replication_user = result.first()['Master_User']
client.execute('STOP SLAVE')
client.execute('RESET SLAVE ALL')
self.wait_for_slave_status('OFF', client, 180)
if not for_failover:
client.execute('DROP USER IF EXISTS ' + replication_user)
return {'replication_user': replication_user}
def _associate_dbs(self, user):
"""Internal. Given a MySQLUser, populate its databases attribute."""
LOG.debug("Associating dbs to user %(name)s at %(host)s.",
{'name': user.name, 'host': user.host})
with mysql_util.SqlClient(self.mysql_app.get_engine()) as client:
q = sql_query.Query()
q.columns = ["grantee", "table_schema"]
q.tables = ["information_schema.SCHEMA_PRIVILEGES"]
q.group = ["grantee", "table_schema"]
q.where = ["privilege_type != 'USAGE'"]
t = text(str(q))
db_result = client.execute(t)
for db in db_result:
LOG.debug("\t db: %s.", db)
if db['grantee'] == "'%s'@'%s'" % (user.name, user.host):
user.databases = db['table_schema']
def change_passwords(self, users):
"""Change the passwords of one or more existing users."""
LOG.debug("Changing the password of some users.")
with mysql_util.SqlClient(self.mysql_app.get_engine()) as client:
for item in users:
LOG.debug("Changing password for user %s.", item)
user_dict = {'_name': item['name'],
'_host': item['host'],
'_password': item['password']}
user = models.MySQLUser.deserialize(user_dict)
uu = sql_query.SetPassword(user.name, host=user.host,
new_password=user.password,
ds=CONF.datastore_manager,
ds_version=CONF.datastore_version)
t = text(str(uu))
client.execute(t)
def list_databases(self, limit=None, marker=None, include_marker=False):
"""List databases on this mysql instance."""
LOG.info("Listing Databases")
ignored_database_names = "'%s'" % "', '".join(cfg.get_ignored_dbs())
LOG.debug(
"The following database names are on ignore list and will "
"be omitted from the listing: %s", ignored_database_names)
databases = []
with mysql_util.SqlClient(self.mysql_app.get_engine()) as client:
# If you have an external volume mounted at /var/lib/mysql
# the lost+found directory will show up in mysql as a database
# which will create errors if you try to do any database ops
# on it. So we remove it here if it exists.
q = sql_query.Query()
q.columns = [
'schema_name as name',
'default_character_set_name as charset',
'default_collation_name as collation',
]
q.tables = ['information_schema.schemata']
q.where = ["schema_name NOT IN (" + ignored_database_names + ")"]
q.order = ['schema_name ASC']
if limit:
q.limit = limit + 1
if marker:
q.where.append(
"schema_name %s '%s'" %
(INCLUDE_MARKER_OPERATORS[include_marker], marker))
t = text(str(q))
database_names = client.execute(t)
next_marker = None
for count, database in enumerate(database_names):
if limit is not None and count >= limit:
break
mysql_db = models.MySQLSchema(name=database[0],
character_set=database[1],
collate=database[2])
next_marker = mysql_db.name
databases.append(mysql_db.serialize())
LOG.info("databases = %s", str(databases))
if limit is not None and database_names.rowcount <= limit:
next_marker = None
return databases, next_marker
def _get_gtid_executed(self):
with mysql_util.SqlClient(self.get_engine()) as client:
return client.execute('SELECT @@global.gtid_binlog_pos').first()[0]
def wait_for_txn(self, txn):
cmd = "SELECT MASTER_GTID_WAIT('%s')" % txn
with mysql_util.SqlClient(self.get_engine()) as client:
client.execute(cmd)
def wait_for_txn(self, txn):
with mysql_util.SqlClient(self.get_engine()) as client:
client.execute("SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('%s')" %
txn)
def _get_slave_status(self):
with mysql_util.SqlClient(self.get_engine()) as client:
return client.execute('SHOW SLAVE STATUS').first()
def stop_master(self):
LOG.info("Stopping replication master.")
with mysql_util.SqlClient(self.get_engine()) as client:
client.execute('RESET MASTER')
def make_read_only(self, read_only):
with mysql_util.SqlClient(self.get_engine()) as client:
q = "set global read_only = %s" % read_only
client.execute(text(str(q)))
def list_users(self, limit=None, marker=None, include_marker=False):
"""List users that have access to the database."""
'''
SELECT
User,
Host,
Marker
FROM
(SELECT
User,
Host,
CONCAT(User, '@', Host) as Marker
FROM mysql.user
ORDER BY 1, 2) as innerquery
WHERE
Marker > :marker
ORDER BY
Marker
LIMIT :limit;
'''
LOG.info("Listing Users")
ignored_user_names = "'%s'" % "', '".join(cfg.get_ignored_users())
LOG.debug(
"The following user names are on ignore list and will "
"be omitted from the listing: %s", ignored_user_names)
users = []
with mysql_util.SqlClient(self.mysql_app.get_engine(),
use_flush=True) as client:
iq = sql_query.Query() # Inner query.
iq.columns = ['User', 'Host', "CONCAT(User, '@', Host) as Marker"]
iq.tables = ['mysql.user']
iq.order = ['User', 'Host']
innerquery = str(iq).rstrip(';')
oq = sql_query.Query() # Outer query.
oq.columns = ['User', 'Host', 'Marker']
oq.tables = ['(%s) as innerquery' % innerquery]
oq.where = [
"Host != 'localhost'",
"User NOT IN (" + ignored_user_names + ")"
]
oq.order = ['Marker']
if marker:
oq.where.append(
"Marker %s '%s'" %
(INCLUDE_MARKER_OPERATORS[include_marker], marker))
if limit:
oq.limit = limit + 1
t = text(str(oq))
result = client.execute(t)
next_marker = None
for count, row in enumerate(result):
if limit is not None and count >= limit:
break
LOG.debug("user = %s", str(row))
mysql_user = models.MySQLUser(name=row['User'],
host=row['Host'])
mysql_user.check_reserved()
self._associate_dbs(mysql_user)
next_marker = row['Marker']
users.append(mysql_user.serialize())
if limit is not None and result.rowcount <= limit:
next_marker = None
LOG.info("users = %s", str(users))
return users, next_marker
def start_slave(self):
LOG.info("Starting slave replication.")
with mysql_util.SqlClient(self.get_engine()) as client:
client.execute('START SLAVE')
self.wait_for_slave_status("ON", client, 180)
def get_port(self):
with mysql_util.SqlClient(self.get_engine()) as client:
result = client.execute('SELECT @@port').first()
return result[0]
def execute_sql(self, sql_statement, use_flush=False):
LOG.debug("Executing SQL: %s", sql_statement)
with mysql_util.SqlClient(self.get_engine(),
use_flush=use_flush) as client:
return client.execute(sql_statement)
def secure_root(self):
with mysql_util.SqlClient(self.get_engine(), use_flush=True) as client:
self._remove_remote_root_access(client)