Oracle回收角色报ORA-01951 如何校验用户角色状态后再执行回收操作

问题背景 #

• 运维第三方维护的Oracle数据库时，直接执行角色回收语句存在逻辑缺陷：如果目标用户未被授予待回收的自定义角色，操作会直接失败抛出ORA-01951错误，典型报错信息如下：

SQL.sql: Error (4,1): ORA-01951: ROLE 'CUSTOM_MASTER_ROLE' not granted to 'OPS$DOMAIN\USER'

• 由于目标角色的创建逻辑封装在无源码的程序包中，无法通过追溯创建规则判断角色授予状态，需要实现前置校验逻辑：仅当目标用户确实持有对应角色时，才执行回收操作。

实现方案 #

Oracle内置数据字典视图DBA_ROLE_PRIVS存储了全库所有用户被直接授予的角色映射关系，不需要感知角色创建规则，直接查询该视图判断授予关系存在性即可，两种常用实现方式如下：

方式1：匿名PL/SQL块执行（推荐） #

不需要额外创建数据库对象，直接替换参数即可执行：

DECLARE
    v_grant_exists NUMBER;
    -- 按需替换下方两个变量为实际的角色名、目标用户名
    v_target_role VARCHAR2(100) := 'CUSTOM_MASTER_ROLE';
    v_target_user VARCHAR2(100) := 'OPS$DOMAIN\USER';
BEGIN
    -- 校验角色是否直接授予给目标用户
    SELECT COUNT(1)
      INTO v_grant_exists
      FROM dba_role_privs
     WHERE grantee = v_target_user
       AND granted_role = v_target_role;

    -- 仅当授予关系存在时执行回收
    IF v_grant_exists > 0 THEN
        EXECUTE IMMEDIATE 'REVOKE '||v_target_role||' FROM "'||v_target_user||'"';
    END IF;
END;
/

方式2：循环判断单语句执行 #

不需要定义变量，逻辑更简洁：

BEGIN
   FOR rec IN (
       SELECT 1 
         FROM dba_role_privs 
        WHERE grantee = 'OPS$DOMAIN\USER' 
          AND granted_role = 'CUSTOM_MASTER_ROLE'
   )
   LOOP
      EXECUTE IMMEDIATE 'REVOKE CUSTOM_MASTER_ROLE FROM "OPS$DOMAIN\USER"';
   END LOOP;
END;
/

注意事项 #

• 执行上述逻辑的账号需要拥有REVOKE ANY ROLE系统权限；如果账号无DBA角色，需要提前授予SELECT_CATALOG_ROLE角色或SELECT ANY DICTIONARY权限，保证能正常查询数据字典视图。
• Oracle默认存储的用户名、角色名为大写，如果环境中使用双引号创建了大小写敏感的命名对象，需要把查询条件、执行语句里的字符串替换为大小写精确匹配的值；带$、反斜杠的域账号格式用户名，建议用双引号包裹，避免特殊字符解析错误。
• 该校验逻辑仅判断角色的直接授予关系，和原生REVOKE语法的校验规则完全一致，不会误判间接通过其他角色继承的权限，执行逻辑和原生回收操作无差异。

内容的提问来源于stack exchange，提问作者Chris Crowe