求助：如何删除Oracle中无用户拥有ADMIN OPTION权限的角色

First off, let's fix a gap in your current admin privilege check: your query joins with DBA_USERS, which means it only looks at user grantees. Roles can also be granted the ADMIN OPTION on other roles, so you're missing that subset entirely. Run this modified query to see if any role holds admin rights over your target role:

SELECT rp.grantee, rp.granted_role, rp.admin_option 
FROM dba_role_privs rp 
WHERE rp.granted_role = 'roleName' 
  AND rp.admin_option = 'YES' 
ORDER BY grantee;

If this returns a role, you can trace further to see if that role is granted to any user with admin option—this would give you a valid path to manage the role through that user/role chain.

If No Admin Option Grantees Exist (Users or Roles) #

Even if the role is "orphaned" without any grantee holding ADMIN OPTION, you can still delete it if you have the right system privileges. Here's how to proceed:

1. Verify your deletion privileges: Check if your current user has the DROP ANY ROLE privilege required to delete the role:

   SELECT * FROM dba_sys_privs 
   WHERE grantee = USER 
     AND privilege = 'DROP ANY ROLE';
   

   If you're logged in as SYS, SYSTEM, or another DBA-level user, you’ll have this access by default.

2. Assess impact (optional but recommended): Before deleting, see which users/roles have been granted the role (even without admin rights) to understand what will be revoked:

   SELECT grantee FROM dba_role_privs 
   WHERE granted_role = 'roleName';
   

3. Delete the role: If you’re ready to proceed, execute the drop command:

   DROP ROLE roleName;
   

Why Does This Happen? #

A few common scenarios lead to this "orphaned" role state:

• The original user who created the role (and held ADMIN OPTION by default) was dropped from the database. Oracle doesn’t automatically clean up role privileges when a user is deleted, so the admin right disappears along with the user.
• The ADMIN OPTION was explicitly revoked from all users and roles that previously held it, but the role itself wasn’t removed.
• The role was created via a script or automated process that intentionally omitted assigning the admin option to any user/role (though this is unusual, since creating a role grants you admin rights unless you specify NOADMIN).

Content of the question originates from Stack Exchange, question author Martin