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 delete(self, req, tenant_id, instance_id, id):
LOG.info(
_("Delete instance '%(id)s'\n"
"req : '%(req)s'\n\n") % {
"id": instance_id,
"req": req
})
context = req.environ[wsgi.CONTEXT_KEY]
id = correct_id_with_req(id, req)
username, host = unquote_user_host(id)
context.notification = notification.DBaaSUserDelete(context,
request=req)
with StartNotification(context,
instance_id=instance_id,
username=username):
user = None
try:
user = guest_models.MySQLUser()
user.name = username
user.host = host
found_user = models.User.load(context, instance_id, username,
host)
if not found_user:
user = None
except (ValueError, AttributeError) as e:
raise exception.BadRequest(msg=str(e))
if not user:
raise exception.UserNotFound(uuid=id)
models.User.delete(context, instance_id, user.serialize())
return wsgi.Result(None, 202)
def update_attributes(cls, context, instance_id, username, hostname,
user_attrs):
load_and_verify(context, instance_id)
client = create_guest_client(context, instance_id)
user_changed = user_attrs.get('name')
host_changed = user_attrs.get('host')
validate = guest_models.MySQLUser()
if host_changed:
validate.host = host_changed
if user_changed:
validate.name = user_changed
user = user_changed or username
host = host_changed or hostname
userhost = "%s@%s" % (user, host)
if user_changed or host_changed:
existing_users, _nadda = Users.load_with_client(
client,
limit=1,
marker=userhost,
include_marker=True)
if (len(existing_users) > 0 and
existing_users[0].name == user and
existing_users[0].host == host):
raise exception.UserAlreadyExists(name=user,
host=host)
client.update_attributes(username, hostname, user_attrs)
def update_all(self, req, body, tenant_id, instance_id):
"""Change the password of one or more users."""
LOG.info(
_("Updating user password for instance '%(id)s'\n"
"req : '%(req)s'\n\n") % {
"id": instance_id,
"req": strutils.mask_password(req)
})
context = req.environ[wsgi.CONTEXT_KEY]
context.notification = notification.DBaaSUserChangePassword(
context, request=req)
users = body['users']
with StartNotification(context,
instance_id=instance_id,
username="******".join(
[user['name'] for user in 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 create_user(self, users):
"""Create users and grant them privileges for the
specified databases.
"""
with self.local_sql_client(self.mysql_app.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 = sql_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.ValidatedMySQLDatabase()
mydb.deserialize(database)
g = sql_query.Grant(permissions='ALL',
database=mydb.name,
user=user.name,
host=user.host,
clear=user.password)
t = text(str(g))
client.execute(t)
def _create_replication_user(self):
replication_user = None
replication_password = utils.generate_random_password(16)
mysql_user = models.MySQLUser()
mysql_user.password = replication_password
retry_count = 0
while replication_user is None:
try:
mysql_user.name = 'slave_' + str(uuid.uuid4())[:8]
MySqlAdmin().create_user([mysql_user.serialize()])
LOG.debug("Trying to create replication user " +
mysql_user.name)
replication_user = {
'name': mysql_user.name,
'password': replication_password
}
except Exception:
retry_count += 1
if retry_count > 5:
LOG.error(_("Replication user retry count exceeded"))
raise
return replication_user
def _get_user(self, username, hostname):
LOG.debug("Get details of a given database user %s." % username)
user = models.MySQLUser()
user.name = username
databases, marker = self.list_databases()
out = None
for database in databases:
db2_db = models.MySQLDatabase()
db2_db.deserialize(database)
try:
out, err = run_command(system.LIST_DB_USERS %
{'dbname': db2_db.name})
except exception.ProcessExecutionError:
LOG.debug(
"Error while trying to get the users for database: %s." %
db2_db.name)
continue
for item in out.split('\n'):
user_access = item.split() if item != "" else None
if (user_access is not None
and user_access[0].lower() == username.lower()
and user_access[1] == 'Y'):
user.databases = db2_db.name
break
return user
def update_all(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(logging.mask_password(_("req : '%s'\n\n") % req))
context = req.environ[wsgi.CONTEXT_KEY]
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 create_user(self, users):
LOG.debug("Creating user(s) for accessing DB2 database(s).")
try:
for item in users:
user = models.MySQLUser()
user.deserialize(item)
try:
LOG.debug("Creating OS user: %s." % user.name)
utils.execute_with_timeout(
system.CREATE_USER_COMMAND % {
'login': user.name, 'login': user.name,
'passwd': user.password}, shell=True)
except exception.ProcessExecutionError as pe:
LOG.exception(_("Error creating user: %s.") % user.name)
continue
for database in user.databases:
mydb = models.ValidatedMySQLDatabase()
mydb.deserialize(database)
try:
LOG.debug("Granting user: %s access to database: %s."
% (user.name, mydb.name))
run_command(system.GRANT_USER_ACCESS % {
'dbname': mydb.name, 'login': user.name})
except exception.ProcessExecutionError as pe:
LOG.debug(
"Error granting user: %s access to database: %s."
% (user.name, mydb.name))
LOG.debug(pe)
pass
except exception.ProcessExecutionError as pe:
LOG.exception(_("An error occured creating users: %s.") %
pe.message)
pass
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 enable_root(cls):
"""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:
print(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:
print(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 delete_user(self, user):
"""Delete the specified users"""
with LocalSqlClient(get_engine()) as client:
mysql_user = models.MySQLUser()
mysql_user.deserialize(user)
du = sql_query.DropUser(mysql_user.name, host=mysql_user.host)
t = text(str(du))
client.execute(t)
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 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 change_passwords(self, users):
"""Change the passwords of one or more existing users."""
LOG.debug("Changing the password of some users.")
with LocalSqlClient(get_engine()) as client:
for item in users:
LOG.debug("Changing password for user %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 = sql_query.UpdateUser(user.name, host=user.host,
clear=user.password)
t = text(str(uu))
client.execute(t)
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 = []
for user in user_list:
mysql_user = guest_models.MySQLUser()
mysql_user.deserialize(user)
if mysql_user.name in cfg.get_ignored_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.host, mysql_user.password,
dbs))
return model_users, next_marker
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)
if not found_user:
user = None
except (ValueError, AttributeError) as e:
raise exception.BadRequest(msg=str(e))
if not user:
raise exception.UserNotFound(uuid=id)
models.User.delete(context, instance_id, user.serialize())
return wsgi.Result(None, 202)
def delete_user(self, user):
LOG.debug("Delete a given user.")
db2_user = models.MySQLUser()
db2_user.deserialize(user)
userName = db2_user.name
user_dbs = db2_user.databases
LOG.debug("For user %s, databases to be deleted = %r." %
(userName, user_dbs))
if len(user_dbs) == 0:
databases = self.list_access(db2_user.name, None)
else:
databases = user_dbs
LOG.debug("databases for user = %r." % databases)
for database in databases:
mydb = models.ValidatedMySQLDatabase()
mydb.deserialize(database)
try:
run_command(system.REVOKE_USER_ACCESS % {
'dbname': mydb.name,
'login': userName
})
LOG.debug("Revoked access for user:%s on database:%s." %
(userName, mydb.name))
except exception.ProcessExecutionError as pe:
LOG.debug("Error occurred while revoking access to %s." %
mydb.name)
pass
try:
utils.execute_with_timeout(system.DELETE_USER_COMMAND %
{'login': db2_user.name.lower()},
shell=True)
except exception.ProcessExecutionError as pe:
LOG.exception(
_("There was an error while deleting user: %s.") % pe)
raise exception.GuestError(
original_message=_("Unable to delete user: %s.") %
userName)
def populate_users(users, initial_databases=None):
"""Create a serializable request containing users"""
users_data = []
unique_identities = set()
for user in users:
u = guest_models.MySQLUser()
u.name = user.get('name', '')
u.host = user.get('host', '%')
user_identity = (u.name, u.host)
if user_identity in unique_identities:
raise exception.DatabaseInitialUserDuplicateError()
unique_identities.add(user_identity)
u.password = user.get('password', '')
user_dbs = user.get('databases', '')
# user_db_names guaranteed unique and non-empty by apischema
user_db_names = [user_db.get('name', '') for user_db in user_dbs]
for user_db_name in user_db_names:
if (initial_databases is not None and user_db_name not in
initial_databases):
raise exception.DatabaseForUserNotInDatabaseListError(
user=u.name, database=user_db_name)
u.databases = user_db_name
users_data.append(u.serialize())
return users_data
def setUp(self):
super(MySQLUserTest, self).setUp()
self.mysqlUser = dbmodels.MySQLUser()
def delete_user(self, user):
"""Delete the specified user."""
mysql_user = models.MySQLUser()
mysql_user.deserialize(user)
self.delete_user_by_name(mysql_user.name, mysql_user.host)
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(IsValidHostnameTest, self).setUp()
self.mysqlUser = dbmodels.MySQLUser()
def list_users(self, limit=None, marker=None, include_marker=False):
LOG.debug(
"List all users for all the databases in a DB2 server instance.")
users = []
user_map = {}
next_marker = None
count = 0
databases, marker = self.list_databases()
for database in databases:
db2_db = models.MySQLDatabase()
db2_db.deserialize(database)
out = None
try:
out, err = run_command(system.LIST_DB_USERS %
{'dbname': db2_db.name})
except exception.ProcessExecutionError:
LOG.debug(
"There was an error while listing users for database: %s."
% db2_db.name)
continue
userlist = []
for item in out.split('\n'):
LOG.debug("item = %r" % item)
user = item.split() if item != "" else None
LOG.debug("user = %r" % (user))
if user is not None and user[0] not in IGNORE_USERS_LIST \
and user[1] == 'Y':
userlist.append(user[0])
result = iter(userlist)
if marker is not None:
try:
item = result.next()
while item != marker:
item = result.next()
if item == marker:
marker = None
except StopIteration:
pass
try:
item = result.next()
db2db = models.MySQLDatabase()
db2db.name = db2_db.name
while item:
'''
Check if the user has already been discovered. If so,
add this database to the database list for this user.
'''
if item in user_map:
db2user = user_map.get(item)
db2user.databases.append(db2db.serialize())
item = result.next()
continue
'''
If this user was not previously discovered, then add
this to the user's list.
'''
count = count + 1
if (limit and count <= limit) or limit is None:
db2_user = models.MySQLUser()
db2_user.name = item
db2_user.databases.append(db2db.serialize())
users.append(db2_user.serialize())
user_map.update({item: db2_user})
item = result.next()
else:
next_marker = None
break
except StopIteration:
next_marker = None
if count == limit:
break
return users, next_marker
