Suppose that I have logged in to oracle database using user X, then user X creates some roles using
create role command. I want to know that is user X the owner of the roles? Can I select all roles created by user X?
The roles in Oracle Database have no owner as well as directories have no owner.
When the user with
CREATE ROLE or
SYSDBAprivilege creates the role, he or she is automatically assigned that role
WITH ADMIN OPTION which means that the user can subsequently grant that role to other users even if the privilege
CREATE ROLE is revoked from the user.
You can see what roles are assigned to which users querying
DBA_ROLE_PRIVS view, or
USER_ROLE_PRIVS if you want to know what roles are assigned to the current user.
Also, a user cannot create a role if the role with the same name already exists. However you can audit role creation and granting. You can do that with AUDIT ROLE statement (if it was for some reason disabled with
NOAUDIT ROLE), and then querying
If you have the admin option over a role you can drop that role, even if you don't possess the drop role privilege or "own" the role, as in, you didn't create the role.
Although a role does not have an owner, dba_role_privs shows both users and roles granted to a privilege.
--This lists both users and roles SELECT * FROM dba_role_privs WHERE admin_option = 'YES' order by grantee, granted_role;
Inner Join to dba_users to see only users with admin option on roles.
--This lists only users with admin option on roles SELECT rp.grantee, rp.granted_role, rp.admin_option FROM dba_role_privs rp JOIN dba_users u ON rp.grantee = u.username WHERE admin_option = 'YES' ORDER BY grantee, granted_role;