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 DBA_AUDIT_TRAIL view.

| improve this answer | |

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;
| improve this answer | |

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.