Oracle中如何创建继承现有角色权限但排除指定表访问的新角色？

首先明确：直接继承现有角色后撤销部分表权限的方案不可行。Oracle的权限模型是累加制——如果新角色继承了原角色的权限，即使你从新角色中撤销某张表的访问权限，原角色的权限依然会生效，用户还是能访问该表。

以下是两种无需逐个手动授权的可行方案：

方案一：批量生成授权语句 #

通过SQL脚本自动生成除排除表外的所有主模式表权限授权语句，一次性执行即可：

1. 创建新角色：

CREATE ROLE new_restricted_role;

2. 执行以下SQL生成授权语句（替换占位符为你的实际信息）：

SELECT 'GRANT SELECT ON MAIN_SCHEMA.' || table_name || ' TO new_restricted_role;'
FROM all_tables
WHERE owner = 'MAIN_SCHEMA'
  AND table_name NOT IN ('EXCLUDE_TABLE_1', 'EXCLUDE_TABLE_2'); -- 填入要排除的表名

3. 将查询结果中的所有SQL语句复制执行，完成权限授予。

• 后续主模式新增表时，只需重新执行上述脚本即可同步权限。

方案二：用虚拟专用数据库（VPD）动态过滤 #

如果希望自动适配主模式的表变更，无需每次手动更新权限，可以用VPD实现基于角色的对象级访问控制：

1. 创建权限控制函数（需有CREATE PROCEDURE权限）：

CREATE OR REPLACE FUNCTION restrict_table_access(p_schema IN VARCHAR2, p_table IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
  -- 检查当前会话是否持有受限角色
  IF SYS_CONTEXT('USERENV', 'CURRENT_ROLE') = 'NEW_RESTRICTED_ROLE' THEN
    -- 对指定排除表返回永远为假的条件，禁止访问
    IF p_table IN ('EXCLUDE_TABLE_1', 'EXCLUDE_TABLE_2') THEN
      RETURN '1=0';
    END IF;
  END IF;
  -- 其他情况允许访问
  RETURN '1=1';
END;
/

2. 为需要控制的表添加VPD策略（需主模式拥有DBMS_RLS权限）：

BEGIN
  -- 为排除表添加策略
  DBMS_RLS.ADD_POLICY(
    object_schema => 'MAIN_SCHEMA',
    object_name => 'EXCLUDE_TABLE_1',
    policy_name => 'RESTRICT_EXCLUDE1',
    function_schema => 'YOUR_ADMIN_SCHEMA', -- 上述函数所在的模式
    policy_function => 'restrict_table_access',
    statement_types => 'SELECT,INSERT,UPDATE,DELETE' -- 根据需要调整允许的操作
  );
  
  DBMS_RLS.ADD_POLICY(
    object_schema => 'MAIN_SCHEMA',
    object_name => 'EXCLUDE_TABLE_2',
    policy_name => 'RESTRICT_EXCLUDE2',
    function_schema => 'YOUR_ADMIN_SCHEMA',
    policy_function => 'restrict_table_access',
    statement_types => 'SELECT,INSERT,UPDATE,DELETE'
  );
END;
/

• 此方案的优势是：主模式新增表时无需额外操作，只有指定的排除表会被限制访问。

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