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.debug("---Listing Users---")
users = []
with LocalSqlClient(get_engine()) as client:
mysql_user = models.MySQLUser()
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'"]
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
LOG.debug("result = " + str(result))
for count, row in enumerate(result):
if count >= limit:
break
LOG.debug("user = "******"users = " + str(users))
return users, next_marker
def _get_user(self, username, hostname):
"""Return a single user matching the criteria."""
user = models.MySQLUser()
try:
user.name = username # Could possibly throw a BadRequest here.
except exception.ValueError as ve:
LOG.exception(_("Error Getting user information"))
raise exception.BadRequest(_("Username %(user)s is not valid"
": %(reason)s") %
{'user': username, 'reason': ve.message}
)
with LocalSqlClient(get_engine()) as client:
q = sql_query.Query()
q.columns = ['User', 'Host', 'Password']
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.password = found_user['Password']
user.host = found_user['Host']
self._associate_dbs(user)
return user
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 self.local_sql_client(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 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)
db_access = set()
grantee = set()
with LocalSqlClient(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:
grantee.add(db['grantee'])
if db['grantee'] == "'%s'@'%s'" % (user.name, user.host):
db_name = db['table_schema']
db_access.add(db_name)
with LocalSqlClient(get_engine()) as client:
uu = sql_query.UpdateUser(user.name, host=user.host,
clear=user_attrs.get('password'),
new_user=user_attrs.get('name'),
new_host=user_attrs.get('host'))
t = text(str(uu))
client.execute(t)
uname = user_attrs.get('name') or username
host = user_attrs.get('host') or hostname
find_user = "******" % (uname, host)
if find_user not in grantee:
self.grant_access(uname, host, db_access)
def _associate_dbs(self, user):
"""Internal. Given a MySQLUser, populate its databases attribute."""
LOG.debug("Associating dbs to user %s at %s." % (user.name, user.host))
with self.local_sql_client(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 list_databases(self, limit=None, marker=None, include_marker=False):
"""List databases the user created on this mysql instance."""
LOG.debug("---Listing Databases---")
databases = []
with LocalSqlClient(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 ("
"'mysql', 'information_schema', "
"'lost+found', '#mysql50#lost+found'"
")"
]
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
LOG.debug("database_names = %r." % database_names)
for count, database in enumerate(database_names):
if count >= limit:
break
LOG.debug("database = %s." % str(database))
mysql_db = models.MySQLDatabase()
mysql_db.name = database[0]
next_marker = mysql_db.name
mysql_db.character_set = database[1]
mysql_db.collate = database[2]
databases.append(mysql_db.serialize())
LOG.debug("databases = " + str(databases))
if database_names.rowcount <= limit:
next_marker = None
return databases, next_marker
def list_databases(self, limit=None, marker=None, include_marker=False):
"""List databases the user created on this mysql instance."""
LOG.debug("---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 self.local_sql_client(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
LOG.debug("database_names = %r.", database_names)
for count, database in enumerate(database_names):
if limit is not None and count >= limit:
break
LOG.debug("database = %s.", str(database))
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.debug("databases = %s", str(databases))
if limit is not None and database_names.rowcount <= limit:
next_marker = None
return databases, next_marker
def list_databases(self, limit=None, marker=None, include_marker=False):
"""List databases the user created on this mysql instance."""
LOG.debug("---Listing Databases---")
ignored_database_names = "'%s'" % "', '".join(CONF.ignore_dbs)
LOG.debug("The following database names are on ignore list and will "
"be omitted from the listing: %s" % ignored_database_names)
databases = []
with LocalSqlClient(get_engine()) as client:
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
LOG.debug("database_names = %r." % database_names)
for count, database in enumerate(database_names):
if count >= limit:
break
LOG.debug("database = %s." % str(database))
mysql_db = models.MySQLDatabase()
mysql_db.name = database[0]
next_marker = mysql_db.name
mysql_db.character_set = database[1]
mysql_db.collate = database[2]
databases.append(mysql_db.serialize())
LOG.debug("databases = " + str(databases))
if database_names.rowcount <= limit:
next_marker = None
return databases, next_marker
def verify_slave_status():
actual_status = client.execute(
"SHOW GLOBAL STATUS like 'slave_running'").first()
if actual_status:
return actual_status[1].upper() == status.upper()
# The slave_running status is no longer available in MySql 5.7
# Need to query the performance_schema instead.
LOG.debug("slave_running global status doesn't exist, checking "
"service_state in performance_schema instead.")
q = sql_query.Query()
q.columns = ["a.service_state", "c.service_state"]
q.tables = ["performance_schema.replication_applier_status a",
"performance_schema.replication_connection_status c"]
q.where = ["a.channel_name = ''", "c.channel_name = ''"]
t = text(str(q))
actual_status = client.execute(t).first()
if (actual_status and actual_status[0].upper() == 'ON' and
actual_status[1].upper() == 'ON'):
actual_status_str = 'ON'
else:
actual_status_str = 'OFF'
return actual_status_str == status.upper()
def test_group_by_2(self):
groups = ['deleted=1']
myQuery = sql_query.Query(group=groups)
self.assertEqual('GROUP BY deleted=1', myQuery._group_by)
def test_columns_2(self):
columns = ["col_A", "col_B"]
myQuery = sql_query.Query(columns=columns)
self.assertEqual("SELECT col_A, col_B", myQuery._columns)
def test_tables(self):
tables = ['table_A', 'table_B']
myQuery = sql_query.Query(tables=tables)
self.assertEqual("FROM table_A, table_B", myQuery._tables)
def test_group_by(self):
myQuery = sql_query.Query(group=None)
self.assertEqual('', myQuery._group_by)
def test_order_2(self):
orders = ['deleted_at', 'updated_at']
myQuery = sql_query.Query(order=orders)
self.assertEqual('ORDER BY deleted_at, updated_at', myQuery._order)
def test_order(self):
myQuery = sql_query.Query(order=None)
self.assertEqual('', myQuery._order)
def test_where_2(self):
conditions = ['cond_A', 'cond_B']
myQuery = sql_query.Query(where=conditions)
self.assertEqual("WHERE cond_A AND cond_B", myQuery._where)
def test_where(self):
myQuery = sql_query.Query(where=None)
self.assertEqual("", myQuery._where)
def test_limit(self):
myQuery = sql_query.Query(limit=None)
self.assertEqual('', myQuery._limit)
def test_limit_2(self):
limit_count = 20
myQuery = sql_query.Query(limit=limit_count)
self.assertEqual('LIMIT 20', myQuery._limit)
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.debug("---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 self.local_sql_client(self.mysql_app.get_engine()) 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
LOG.debug("result = %s", str(result))
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.debug("users = %s", str(users))
return users, next_marker
def test_columns(self):
myQuery = sql_query.Query(columns=None)
self.assertEqual("SELECT *", myQuery._columns)
