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 = 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 = 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:
raise exception.BadRequest("Username %s is not valid: %s" %
(username, ve.message))
with LocalSqlClient(get_engine()) as client:
q = 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("Result: %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 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 = 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 _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 LocalSqlClient(get_engine()) as client:
q = 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):
mysql_db = models.MySQLDatabase()
mysql_db.name = db['table_schema']
user.databases.append(mysql_db.serialize())
def test_limit_2(self):
limit_count = 20
myQuery = query.Query(limit=limit_count)
self.assertEqual('LIMIT 20', myQuery._limit)
def test_limit(self):
myQuery = query.Query(limit=None)
self.assertEqual('', myQuery._limit)
def test_group_by_2(self):
groups = ['deleted=1']
myQuery = query.Query(group=groups)
self.assertEqual('GROUP BY deleted=1', myQuery._group_by)
def test_group_by(self):
myQuery = query.Query(group=None)
self.assertEqual('', myQuery._group_by)
def test_order_2(self):
orders = ['deleted_at', 'updated_at']
myQuery = query.Query(order=orders)
self.assertEqual('ORDER BY deleted_at, updated_at', myQuery._order)
def test_order(self):
myQuery = query.Query(order=None)
self.assertEqual('', myQuery._order)
def test_where_2(self):
conditions = ['cond_A', 'cond_B']
myQuery = query.Query(where=conditions)
self.assertEqual("WHERE cond_A AND cond_B", myQuery._where)
def test_where(self):
myQuery = query.Query(where=None)
self.assertEqual("", myQuery._where)
def test_tables(self):
tables = ['table_A', 'table_B']
myQuery = query.Query(tables=tables)
self.assertEqual("FROM table_A, table_B", myQuery._tables)
def test_columns_2(self):
columns = ["col_A", "col_B"]
myQuery = query.Query(columns=columns)
self.assertEqual("SELECT col_A, col_B", myQuery._columns)
def test_columns(self):
myQuery = query.Query(columns=None)
self.assertEqual("SELECT *", myQuery._columns)
