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)