如何查询拥有指定权限（如SESSION）的数据库用户？

我需要查询拥有特定权限（如SESSION、CREATE TABLE等）的数据库用户，要一次性检索所有相关表，包含用户直接拥有的权限，以及通过角色间接获得的权限。比如用户'user01'可能没有直接表权限，但可通过角色间接获得。我编写了如下存储过程，不确定是否正确：

set serveroutput on;

CREATE OR REPLACE PROCEDURE SP_WHO_HAS_PRIVILEGE(arg_priv varchar2)
IS

CURSOR grantee_cur IS

SELECT   GRANTEE FROM DBA_SYS_PRIVS WHERE PRIVILEGE=arg_priv
UNION
SELECT   DISTINCT GRANTEE FROM DBA_TAB_PRIVS WHERE PRIVILEGE=arg_priv
UNION
SELECT   GRANTEE FROM DBA_ROLE_PRIVS WHERE GRANTED_ROLE IN
         ( SELECT ROLE FROM ROLE_SYS_PRIVS WHERE PRIVILEGE = arg_priv)
UNION
SELECT   GRANTEE FROM DBA_ROLE_PRIVS WHERE GRANTED_ROLE IN
         (SELECT ROLE FROM ROLE_TAB_PRIVS WHERE PRIVILEGE = arg_priv);

BEGIN
DBMS_OUTPUT.PUT_LINE('Grantees: ');
FOR rec IN grantee_cur LOOP
        DBMS_OUTPUT.PUT_LINE(rec.grantee);
END LOOP;
END;
/

BEGIN
SP_WHO_HAS_PRIVILEGE(&priv);
END;

/

对该存储过程的分析 #

正确的部分 #

这个存储过程的核心思路覆盖了权限授予的主要场景：

• 直接赋予用户的系统权限（从DBA_SYS_PRIVS查询）
• 直接赋予用户的表级权限（从DBA_TAB_PRIVS查询）
• 用户通过角色获得的系统权限（关联DBA_ROLE_PRIVS和ROLE_SYS_PRIVS）
• 用户通过角色获得的表级权限（关联DBA_ROLE_PRIVS和ROLE_TAB_PRIVS）
• 使用UNION自动去重，避免重复输出同一用户

需要优化的点 #

1. 冗余的DISTINCT：DBA_TAB_PRIVS查询里的DISTINCT可以去掉，因为UNION会自动对所有结果去重，保留DISTINCT只会增加不必要的计算开销。
2. 未处理角色嵌套：当前查询只能处理一层角色授权（用户→角色→权限），如果存在多层嵌套（用户→角色A→角色B→权限），则无法递归识别这类间接权限。要解决这个问题，需要使用递归查询（比如Oracle的CONNECT BY语法）来遍历所有角色层级。
3. 权限名称大小写问题：Oracle数据库中权限名称默认以大写存储，如果输入的arg_priv是小写或混合大小写，会导致匹配失败。建议在查询中统一转换为大写，例如将PRIVILEGE=arg_priv改为UPPER(PRIVILEGE) = UPPER(arg_priv)，提升鲁棒性。
4. 输出方式限制：使用DBMS_OUTPUT输出结果，当用户数量较多时可能超出缓冲区大小导致内容截断。可以考虑改为返回游标（如使用REF CURSOR），让调用方可以直接获取结果集，更适合批量数据场景。

内容的提问来源于stack exchange，提问作者Moudhaffer Bouallegui