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 list_users(self, limit=None, marker=None, include_marker=False):
"""List users that have access to the database"""
LOG.debug(_("---Listing Users---"))
users = []
client = LocalSqlClient(get_engine())
with client:
mysql_user = models.MySQLUser()
q = Query()
q.columns = ['User']
q.tables = ['mysql.user']
q.where = ["host != 'localhost'"]
q.order = ['User']
if marker:
q.where.append(
"User %s '%s'" %
(INCLUDE_MARKER_OPERATORS[include_marker], marker))
if limit:
q.limit = limit + 1
t = text(str(q))
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 = "******"^'(.+)'@", db['grantee'])
if (matches is not None
and matches.group(1) == mysql_user.name):
mysql_db = models.MySQLDatabase()
mysql_db.name = db['table_schema']
mysql_user.databases.append(mysql_db.serialize())
users.append(mysql_user.serialize())
if result.rowcount <= limit:
next_marker = None
LOG.debug("users = " + str(users))
return users, next_marker
def enable_root(self):
"""Enable the root user global access and/or reset the root password"""
user = models.MySQLUser()
user.name = "root"
user.host = "%"
user.password = generate_random_password()
with LocalSqlClient(get_engine()) as client:
try:
cu = query.CreateUser(user.name, host=user.host)
t = text(str(cu))
client.execute(t, **cu.keyArgs)
except exc.OperationalError as err:
# Ignore, user is already created, just reset the password
# TODO(rnirmal): More fine grained error checking later on
LOG.debug(err)
with LocalSqlClient(get_engine()) as client:
uu = query.UpdateUser(user.name,
host=user.host,
clear=user.password)
t = text(str(uu))
client.execute(t)
LOG.debug("CONF.root_grant: %s CONF.root_grant_option: %s" %
(CONF.root_grant, CONF.root_grant_option))
g = query.Grant(permissions=CONF.root_grant,
user=user.name,
host=user.host,
grant_option=CONF.root_grant_option,
clear=user.password)
t = text(str(g))
client.execute(t)
return user.serialize()
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 update(self, req, body, tenant_id, instance_id):
"""Change the password of one or more users."""
LOG.info(_("Updating user passwords for instance '%s'") % instance_id)
LOG.info(_("req : '%s'\n\n") % req)
context = req.environ[wsgi.CONTEXT_KEY]
self.validate(body)
users = body['users']
model_users = []
for user in users:
try:
mu = guest_models.MySQLUser()
mu.name = user['name']
mu.host = user.get('host')
mu.password = user['password']
found_user = models.User.load(context, instance_id, mu.name,
mu.host)
if not found_user:
user_and_host = mu.name
if mu.host:
user_and_host += '@' + mu.host
raise exception.UserNotFound(uuid=user_and_host)
model_users.append(mu)
except (ValueError, AttributeError) as e:
raise exception.BadRequest(msg=str(e))
models.User.change_password(context, instance_id, model_users)
return wsgi.Result(None, 202)
def delete_user(self, user):
"""Delete the specified users"""
client = LocalSqlClient(get_engine())
with client:
mysql_user = models.MySQLUser()
mysql_user.deserialize(user)
t = text("""DROP USER `%s`""" % mysql_user.name)
client.execute(t)
def delete_user(self, user):
"""Delete the specified users"""
with LocalSqlClient(get_engine()) as client:
mysql_user = models.MySQLUser()
mysql_user.deserialize(user)
du = query.DropUser(mysql_user.name, host=mysql_user.host)
t = text(str(du))
client.execute(t)
def is_root_enabled(self):
"""Return True if root access is enabled; False otherwise."""
client = LocalSqlClient(get_engine())
with client:
mysql_user = models.MySQLUser()
t = text("""SELECT User FROM mysql.user where User = '******'
and host != 'localhost';""")
result = client.execute(t)
LOG.debug("result = " + str(result))
return result.rowcount != 0
def delete(self, req, tenant_id, instance_id, id):
LOG.info(_("Deleting user for instance '%s'") % instance_id)
LOG.info(_("req : '%s'\n\n") % req)
context = req.environ[wsgi.CONTEXT_KEY]
try:
user = guest_models.MySQLUser()
user.name = id
models.User.delete(context, instance_id, user.serialize())
except ValueError as ve:
raise exception.BadRequest(ve.message)
return wsgi.Result(None, 202)
def populate_users(users):
"""Create a serializable request containing users"""
users_data = []
for user in users:
u = guest_models.MySQLUser()
u.name = user.get('name', '')
u.host = user.get('host')
u.password = user.get('password', '')
dbs = user.get('databases', '')
if dbs:
for db in dbs:
u.databases = db.get('name', '')
users_data.append(u.serialize())
return users_data
def load(cls, context, instance_id, username, hostname):
load_and_verify(context, instance_id)
validate = guest_models.MySQLUser()
validate.name = username
validate.host = hostname
client = create_guest_client(context, instance_id)
found_user = client.get_user(username=username, hostname=hostname)
if not found_user:
return None
database_names = [{
'name': db['_name']
} for db in found_user['_databases']]
return cls(found_user['_name'], found_user['_host'],
found_user['_password'], database_names)
def update(self, req, body, tenant_id, instance_id):
"""Change the password of one or more users."""
LOG.info(_("Updating user passwords for instance '%s'") % instance_id)
LOG.info(_("req : '%s'\n\n") % req)
context = req.environ[wsgi.CONTEXT_KEY]
self.validate(body)
users = body['users']
model_users = []
for user in users:
mu = guest_models.MySQLUser()
mu.name = user['name']
mu.host = user.get('host')
mu.password = user['password']
model_users.append(mu)
models.User.change_password(context, instance_id, model_users)
return wsgi.Result(None, 202)
def populate_users(users):
"""Create a serializable request containing users"""
try:
users_data = []
for user in users:
u = guest_models.MySQLUser()
u.name = user.get('name', '')
u.password = user.get('password', '')
dbs = user.get('databases', '')
if dbs:
for db in dbs:
u.databases = db.get('name', '')
users_data.append(u.serialize())
return users_data
except ValueError as ve:
raise exception.BadRequest(ve.message)
def delete(self, req, tenant_id, instance_id, id):
LOG.info(_("Deleting user for instance '%s'") % instance_id)
LOG.info(_("req : '%s'\n\n") % req)
context = req.environ[wsgi.CONTEXT_KEY]
username, host = unquote_user_host(id)
user = None
try:
user = guest_models.MySQLUser()
user.name = username
user.host = host
found_user = models.User.load(context, instance_id, username, host)
except (ValueError, AttributeError) as e:
raise exception.BadRequest(msg=e)
if not user:
raise exception.UserNotFound(uuid=id)
models.User.delete(context, instance_id, user.serialize())
return wsgi.Result(None, 202)
def load_with_client(cls, client, limit, marker, include_marker):
user_list, next_marker = client.list_users(
limit=limit, marker=marker, include_marker=include_marker)
model_users = []
ignore_users = CONFIG.get_list('ignore_users', [])
for user in user_list:
mysql_user = guest_models.MySQLUser()
mysql_user.deserialize(user)
if mysql_user.name in ignore_users:
continue
# TODO(hub-cap): databases are not being returned in the
# reference agent
dbs = []
for db in mysql_user.databases:
dbs.append({'name': db['_name']})
model_users.append(User(mysql_user.name, mysql_user.password, dbs))
return model_users, next_marker
def change_passwords(self, users):
"""Change the passwords of one or more existing users."""
LOG.debug("Changing the password of some users." "")
LOG.debug("Users is %s" % users)
with LocalSqlClient(get_engine()) as client:
for item in users:
LOG.debug("\tUser: %s" % item)
user_dict = {
'_name': item['name'],
'_host': item['host'],
'_password': item['password'],
}
user = models.MySQLUser()
user.deserialize(user_dict)
LOG.debug("\tDeserialized: %s" % user.__dict__)
uu = query.UpdateUser(user.name,
host=user.host,
clear=user.password)
t = text(str(uu))
client.execute(t)
def create_user(self, users):
"""Create users and grant them privileges for the
specified databases"""
host = "%"
client = LocalSqlClient(get_engine())
with client:
for item in users:
user = models.MySQLUser()
user.deserialize(item)
# TODO(cp16net):Should users be allowed to create users
# 'os_admin' or 'debian-sys-maint'
t = text("""GRANT USAGE ON *.* TO '%s'@\"%s\"
IDENTIFIED BY '%s';""" %
(user.name, host, user.password))
client.execute(t)
for database in user.databases:
mydb = models.MySQLDatabase()
mydb.deserialize(database)
t = text("""
GRANT ALL PRIVILEGES ON `%s`.* TO `%s`@:host;
""" % (mydb.name, user.name))
client.execute(t, host=host)
def enable_root(self):
"""Enable the root user global access and/or reset the root password"""
host = "%"
user = models.MySQLUser()
user.name = "root"
user.password = generate_random_password()
client = LocalSqlClient(get_engine())
with client:
try:
t = text("""CREATE USER :user@:host;""")
client.execute(t, user=user.name, host=host, pwd=user.password)
except exc.OperationalError as err:
# Ignore, user is already created, just reset the password
# TODO(rnirmal): More fine grained error checking later on
LOG.debug(err)
with client:
t = text("""UPDATE mysql.user SET Password=PASSWORD(:pwd)
WHERE User=:user;""")
client.execute(t, user=user.name, pwd=user.password)
t = text("""GRANT ALL PRIVILEGES ON *.* TO :user@:host
WITH GRANT OPTION;""")
client.execute(t, user=user.name, host=host)
return user.serialize()
def create_user(self, users):
"""Create users and grant them privileges for the
specified databases"""
with LocalSqlClient(get_engine()) as client:
for item in users:
user = models.MySQLUser()
user.deserialize(item)
# TODO(cp16net):Should users be allowed to create users
# 'os_admin' or 'debian-sys-maint'
g = query.Grant(user=user.name,
host=user.host,
clear=user.password)
t = text(str(g))
client.execute(t)
for database in user.databases:
mydb = models.MySQLDatabase()
mydb.deserialize(database)
g = query.Grant(permissions='ALL',
database=mydb.name,
user=user.name,
host=user.host,
clear=user.password)
t = text(str(g))
client.execute(t)
def setUp(self):
super(IsValidUsernameTest, self).setUp()
self.mysqlUser = dbmodels.MySQLUser()
self.origin_is_valid = self.mysqlUser._is_valid
self.origin_ignore_users = self.mysqlUser._ignore_users
self.mysqlUser._ignore_users = ["king"]
def setUp(self):
super(MySQLUserTest, self).setUp()
self.mysqlUser = dbmodels.MySQLUser()
