如何检查用户/角色对系统表/视图的权限及授权问题处理

1. 检查哪些用户/角色拥有DBA_ROLE_PRIVS的SELECT权限 #

要确认谁能访问DBA_ROLE_PRIVS，可以通过以下SQL查询：

方式1：直接查询对象的授权记录 #

SELECT grantee, privilege, grantable
FROM dba_tab_privs
WHERE table_name = 'DBA_ROLE_PRIVS'
  AND privilege = 'SELECT';

这里的grantee可能是用户或角色，能直接看到谁被授予了该对象的SELECT权限。

方式2：检查通过角色继承的权限 #

如果权限是通过角色（比如SELECT_CATALOG_ROLE）授予的，可查询哪些用户拥有该角色：

SELECT grantee, granted_role, admin_option
FROM dba_role_privs
WHERE granted_role = 'SELECT_CATALOG_ROLE';

方式3：确认特权用户 #

SYSDBA、SYSOPER这类特权用户默认能访问所有数据字典，可通过以下查询确认：

SELECT username, granted_role
FROM dba_role_privs
WHERE granted_role IN ('SYSDBA', 'SYSOPER');

那些没被授予SELECT_CATALOG_ROLE却能访问的用户，大概率是拥有这类特权，或者被直接授予了DBA_ROLE_PRIVS的SELECT权限。

2. 为DBA_ROLE_PRIVS授予SELECT权限 #

只有SYS用户或拥有GRANT ANY OBJECT PRIVILEGE系统权限的用户才能执行以下操作：

直接授予用户 #

GRANT SELECT ON dba_role_privs TO target_user;

授予角色（推荐，便于批量管理） #

如果要给多个用户授权，建议先授予角色，再分配角色给用户：

-- 给现有角色（如SELECT_CATALOG_ROLE）授权
GRANT SELECT ON dba_role_privs TO SELECT_CATALOG_ROLE;

-- 或者创建自定义角色后授权
CREATE ROLE catalog_viewer;
GRANT SELECT ON dba_role_privs TO catalog_viewer;
GRANT catalog_viewer TO target_user1, target_user2;

注意：授予角色后，如果用户仍无法访问，需让用户重新登录，或执行SET ROLE SELECT_CATALOG_ROLE;手动启用角色（若角色未设置为默认启用）。

排查权限问题的实用技巧 #

• 检查当前会话的权限状态：切换到问题用户，执行以下SQL确认自身权限：

  -- 查看当前启用的角色
  SELECT * FROM session_roles;
  -- 查看当前用户的直接对象权限
  SELECT * FROM user_tab_privs WHERE table_name = 'DBA_ROLE_PRIVS';
  -- 查看当前用户的系统权限
  SELECT * FROM user_sys_privs;
  

• 区分直接授权与角色继承：dba_tab_privs中grantee为用户时是直接授权，为角色时是通过角色继承的权限。
• 验证角色是否启用：部分角色需要手动启用，可通过SET ROLE 角色名;临时启用，或通过ALTER USER 用户名 DEFAULT ROLE 角色名;设置为默认启用。
• 检查权限传递性：如果dba_tab_privs中的grantable为YES，说明该用户/角色可将权限转授给其他用户/角色。
• 确认对象所有者：DBA_ROLE_PRIVS的所有者是SYS，确保操作时使用的是正确的对象名（Oracle默认对象名为大写）。

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