如何使用SQL在Oracle中创建VPD实现同部门访问及薪资掩码

你要的功能可以通过VPD行级安全策略+列级掩码策略组合实现，不需要复杂的自定义包，搭配Oracle自带的USERENV上下文就可以满足需求，是性能和安全性最优的实现方式。

前提说明 #

我们默认你的应用系统在用户登录后，会将当前登录员工的ID存入Oracle标准的CLIENT_IDENTIFIER上下文（可以通过dbms_session.set_identifier(当前员工ID)设置），如果你的场景是员工用独立数据库账号登录，只需要把下面获取当前员工信息的逻辑替换为读取SESSION_USER匹配员工表的姓名字段即可。

实现步骤 #

1. 创建同部门行过滤策略函数 #

该函数负责返回行过滤条件，限制用户只能看到所属部门的所有员工记录：

CREATE OR REPLACE FUNCTION vpd_employee_dept_filter(
    p_schema VARCHAR2,
    p_table VARCHAR2
) RETURN VARCHAR2
IS
    v_current_emp_id NUMBER;
    v_current_dept VARCHAR2(25);
BEGIN
    -- 获取当前登录员工ID
    v_current_emp_id := TO_NUMBER(SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER'));
    
    -- 查询当前员工所属部门
    SELECT DEPT INTO v_current_dept 
    FROM EMPLOYEE 
    WHERE ID = v_current_emp_id;
    
    -- 返回行过滤规则
    RETURN 'DEPT = '''|| v_current_dept ||'''';
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- 匹配不到员工身份时禁止访问所有数据
        RETURN '1=2';
END;
/

2. 绑定行级安全策略到Employee表 #

BEGIN
    DBMS_RLS.ADD_POLICY(
        object_schema => '你的表所属Schema名称', -- 替换为实际Schema名
        object_name => 'EMPLOYEE',
        policy_name => 'EMPLOYEE_DEPT_ACCESS_POLICY',
        function_schema => '你的策略函数所属Schema名称', -- 替换为实际Schema名
        policy_function => 'vpd_employee_dept_filter',
        statement_types => 'SELECT', -- 仅对查询生效，可按需添加INSERT/UPDATE/DELETE
        update_check => TRUE
    );
END;
/

3. 创建非本人薪资掩码策略函数 #

该函数负责判断薪资字段是否可见，非本人记录的薪资返回NULL：

CREATE OR REPLACE FUNCTION vpd_employee_salary_mask(
    p_schema VARCHAR2,
    p_table VARCHAR2
) RETURN VARCHAR2
IS
    v_current_emp_id NUMBER;
BEGIN
    v_current_emp_id := TO_NUMBER(SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER'));
    -- 只有记录ID等于当前登录员工ID时，薪资才可见
    RETURN 'ID = '|| v_current_emp_id;
END;
/

4. 绑定列级掩码策略到Employee表的SALARY字段 #

BEGIN
    DBMS_RLS.ADD_POLICY(
        object_schema => '你的表所属Schema名称', -- 替换为实际Schema名
        object_name => 'EMPLOYEE',
        policy_name => 'EMPLOYEE_SALARY_MASK_POLICY',
        function_schema => '你的策略函数所属Schema名称', -- 替换为实际Schema名
        policy_function => 'vpd_employee_salary_mask',
        statement_types => 'SELECT',
        sec_relevant_cols => 'SALARY', -- 仅对SALARY字段生效
        sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS -- 不符合条件的行仅掩码对应字段，不过滤行
    );
END;
/

效果验证 #

假设当前登录员工ID为1，所属部门为研发部，研发部有2名员工：
查询SELECT ID, NAME, DEPT, SALARY FROM EMPLOYEE返回结果如下：

注意事项 #

• 如果你的系统需要存储更多当前用户属性，也可以自定义应用上下文命名空间存储员工信息，替换USERENV即可
• 确保策略函数有对应Employee表的查询权限，访问数据库的业务账号有两个策略函数的执行权限
• 两个策略分开维护更灵活，后续修改部门过滤规则或掩码规则不需要互相影响

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