Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 2 down vote accepted

The roles in Oracle Database have no owner as well as directories have no owner. But whenever the new role is created (by user with SYSDBA role or CREATE ANY ROLE prvilege) it is automatically assigned to the user who created it. 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.

share|improve this answer
    
Oops, actually it's CREATE ROLE privilege, and when the user with this privilege 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. Also, you cannot create the user if the role with the same name already exists, and vice versa. 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. –  Yasir Arsanukaev Apr 5 '13 at 12:47

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;
share|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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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