def list_users(self, limit=None, marker=None, include_marker=False):
LOG.debug(
"List all users for all the databases in an Oracle server instance."
)
user_list = {}
databases, marker = self.list_databases()
for database in databases:
oracle_db = models.OracleSchema.deserialize_schema(database)
with LocalOracleClient(oracle_db.name, service=True) as client:
q = sql_query.Query()
q.columns = ["grantee"]
q.tables = ["dba_role_privs"]
q.where = [
"granted_role = '%s'" % CONF.get(MANAGER).cloud_user_role,
"grantee != 'SYS'"
]
client.execute(str(q))
for row in client:
user_name = row[0]
if user_name in user_list:
user = models.OracleUser.deserialize_user(
user_list.get(user_name))
else:
user = models.OracleUser(user_name)
user.databases = oracle_db.name
user_list.update({user_name: user.serialize()})
return user_list.values(), marker
def list_users(self, limit=None, marker=None, include_marker=False):
LOG.debug("Listing users (limit of %s, marker %s, "
"%s marker)." %
(limit, marker,
('including' if include_marker else 'not including')))
user_names = []
with self.cursor(self.database_name) as cursor:
q = sql_query.Query(columns=['GRANTEE'],
tables=['DBA_ROLE_PRIVS'],
where=[
"GRANTED_ROLE = '%s'" %
self.cloud_role_name, "GRANTEE != 'SYS'"
])
cursor.execute(str(q))
for row in cursor:
user_names.append(row[0])
user_list_page, next_marker = pagination.paginate_list(
list(set(user_names)), limit, marker, include_marker)
users_page = []
for user_name in user_list_page:
user = models.OracleUser(user_name)
user.databases = self.database_name
users_page.append(user.serialize())
LOG.debug("Successfully listed users. "
"Users: %s (next marker %s)." %
(user_list_page, next_marker))
return users_page, marker
def enable_root(self, root_password=None):
"""Create user 'root' with the dba_user role and/or reset the root
user password.
"""
LOG.debug("---Enabling root user---")
if not root_password:
root_password = utils.generate_random_password(PASSWORD_MAX_LEN)
root_user = models.OracleUser(ROOT_USERNAME, root_password)
with LocalOracleClient(CONF.guest_name, service=True) as client:
client.execute("SELECT USERNAME FROM ALL_USERS "
"WHERE USERNAME = upper('%s')" %
root_user.name.upper())
if client.rowcount == 0:
client.execute("CREATE USER %(username)s "
"IDENTIFIED BY %(password)s" % {
'username': root_user.name,
'password': root_user.password
})
else:
client.execute("ALTER USER %(username)s "
"IDENTIFIED BY %(password)s" % {
'username': root_user.name,
'password': root_user.password
})
client.execute("GRANT PDB_DBA TO %s" % ROOT_USERNAME)
return root_user.serialize()
def change_passwords(self, users):
"""Change the passwords of one or more existing users."""
LOG.debug("Changing the password of some users.")
with LocalOracleClient(self.database_name, service=True) as client:
for item in users:
LOG.debug("Changing password for user %s." % item)
user = models.OracleUser(item['name'],
password=item['password'])
q = sql_query.AlterUser(user.name, password=user.password)
client.execute(str(q))
def _find_root_user(self, context, instance_id):
user = guest_models.OracleUser('sys')
# TODO(mvandijk): This should be ultimately using Oracle model
# extensions. MySQL extensions will work for now, but may lead to
# future bugs as it makes use of the 'host' field which
# does not exist/has different meaning in Postgres.
return models.User.load(context,
instance_id,
user.name,
user.host,
root_user=True)
def change_passwords(self, users):
"""Change the passwords of one or more existing users."""
LOG.debug("Changing the password of some user(s).")
with self.cursor(self.database_name) as cursor:
for item in users:
LOG.debug("Changing password for user %s." % item)
user = models.OracleUser(item['name'],
password=item['password'])
cursor.execute(
str(
sql_query.AlterUser.change_password(
user.name, user.password)))
def _get_user(self, username):
with self.cursor(self.database_name) as cursor:
q = sql_query.Query(columns=['USERNAME'],
tables=['ALL_USERS'],
where=["USERNAME = '******'" % username.upper()])
# Check that the user exists
cursor.execute(str(q))
if not cursor.fetchone():
return None
user = models.OracleUser(username)
user.databases = self.database_name
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)
if user_attrs.get('host'):
raise exception.DatastoreOperationNotSupported(
operation='update_attributes:new_host', datastore=MANAGER)
if user_attrs.get('name'):
raise exception.DatastoreOperationNotSupported(
operation='update_attributes:new_name', datastore=MANAGER)
new_password = user_attrs.get('password')
if new_password:
user = models.OracleUser(username, new_password)
self.change_passwords([user])
def list_users(self, limit=None, marker=None, include_marker=False):
"""List users that have access to the database."""
LOG.debug("---Listing Users---")
users = []
with LocalOracleClient(CONF.guest_name, service=True) as client:
# filter out Oracle system users by id
# Oracle docs say that new users are given id's between
# 100 and 60000
client.execute("SELECT USERNAME FROM ALL_USERS "
"WHERE (USER_ID BETWEEN 100 AND 60000) "
"AND USERNAME != '%s'" % ROOT_USERNAME.upper())
for row in client:
oracle_user = models.OracleUser(row[0])
users.append(oracle_user.serialize())
return users, None
def _get_user(self, username, hostname):
LOG.debug("Get details of a given database user %s." % username)
user = models.OracleUser(username)
databases, marker = self.list_databases()
for database in databases:
oracle_db = models.OracleSchema.deserialize_schema(database)
with LocalOracleClient(oracle_db.name, service=True) as client:
q = sql_query.Query()
q.columns = ["username"]
q.tables = ["all_users"]
q.where = ["username = '******'" % username.upper()]
client.execute(str(q))
users = client.fetchall()
if client.rowcount == 1:
user.databases.append(database)
return user
def _get_user(self, username, hostname):
"""Return a single user matching the criteria."""
with LocalOracleClient(CONF.guest_name, service=True) as client:
client.execute("SELECT USERNAME FROM ALL_USERS "
"WHERE USERNAME = '******'" % username.upper())
users = client.fetchall()
if client.rowcount != 1:
return None
try:
user = models.OracleUser(users[0][0])
return user
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
})