如何在Oracle中创建角色并批量分配权限

Oracle创建角色必须显式指定IDENTIFIED BY或NOT IDENTIFIED；未认证角色无法后期添加密码，需重建；批量授权应通过DBA_OBJECTS生成GRANT语句而非SELECT ANY TABLE；WITH ADMIN OPTION存在权限扩散风险；角色嵌套超2层会触发ORA-01927；权限变更需重新连接或SET ROLE生效。

CREATE ROLE 语句必须显式指定 IDENTIFIED BY 或 NOT IDENTIFIED

Oracle 中创建角色时，CREATE ROLE 默认创建的是“非认证角色”（即 NOT IDENTIFIED），这种角色不能被密码保护，也不能用于外部身份验证。如果你后续想用 SET ROLE role_name IDENTIFIED BY ... 切换角色，就必须在建角色时加上 IDENTIFIED BY 子句。

常见错误是漏写该子句，导致后续无法以密码方式激活角色：

CREATE ROLE app_reader; -- ✅ 非认证角色，可直接 GRANT/REVOKE

CREATE ROLE app_reader IDENTIFIED BY "R3ad@2026"; -- ✅ 支持密码切换

若已建好未认证角色，无法后期 ALTER 添加密码 —— 只能 DROP ROLE 后重建。

批量授对象权限：别依赖 SELECT ANY TABLE，用 DBA_OBJECTS 动态生成 GRANT

给角色批量授予某 schema 下所有表的 SELECT 权限，最安全的做法不是授 SELECT ANY TABLE（高危，绕过所有权控制），而是查 DBA_OBJECTS 生成语句。

在 SYS 或具有 SELECT_CATALOG_ROLE 的用户下执行：

SELECT 'GRANT SELECT ON ' || owner || '.' || object_name || ' TO app_reader;' FROM dba_objects WHERE owner = 'HR' AND object_type = 'TABLE';

1. 输出结果是一堆 GRANT SELECT ON hr.employees TO app_reader; 类语句，复制执行即可
2. 注意：owner 必须大写（如 'HR'），否则可能漏匹配
3. 如果目标 schema 有视图、序列等，需额外加 OR object_type IN ('VIEW', 'SEQUENCE')

GRANT 系统权限时 WITH ADMIN OPTION 的风险要盯住

给角色授系统权限（如 CREATE SESSION、CREATE TABLE）时，加 WITH ADMIN OPTION 意味着该角色持有者可以再把这权限转授给别人——这会脱离 DBA 控制面。

典型误用场景：

1. GRANT CREATE TABLE TO app_dev WITH ADMIN OPTION; → app_dev 用户可自行 GRANT CREATE TABLE TO attacker_user;
2. 一旦角色被恶意用户获取，权限链就不可追溯
3. 生产环境除非明确需要 delegation（如中间件部署账号），否则一律不加 WITH ADMIN OPTION

查谁有转授权：运行 SELECT * FROM dba_sys_privs WHERE admin_option = 'YES';

角色嵌套深度超过 2 层可能触发 ORA-01927

Oracle 允许角色 A 授予角色 B，B 再授予角色 C，但嵌套层级默认上限为 2（即 A→B→C 是合法的，A→B→C→D 就不行）。超限时报错：ORA-01927: cannot revoke privileges you did not grant 或登录后权限不生效。

排查方法：

1. 查当前用户所有有效角色：SELECT * FROM session_roles;
2. 查某角色包含哪些角色：SELECT granted_role FROM dba_role_privs WHERE grantee = 'APP_READER';
3. 避免三层以上嵌套；如需复杂权限组合，优先合并到一个角色里，而不是靠链式授予

真正容易被忽略的是：角色继承关系在用户会话建立时固化，改完角色权限后，已有连接不会自动刷新 —— 必须让用户重新 CONNECT 或执行 SET ROLE 才生效。