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)