使用 VPD（DBMS_RLS）添加行级访问策略

命令

-- 1) Policy function returning the predicate:
CREATE OR REPLACE FUNCTION customer_app.customer_vpd_fn (
  p_schema IN VARCHAR2,
  p_object IN VARCHAR2
) RETURN VARCHAR2
AS
BEGIN
  -- The schema owner sees all rows (NULL = no restriction)
  IF SYS_CONTEXT('USERENV', 'SESSION_USER') = 'CUSTOMER_APP' THEN
    RETURN NULL;
  END IF;
  -- Other users see only their own city
  RETURN 'city = SYS_CONTEXT(''USERENV'', ''CLIENT_IDENTIFIER'')';
END;
/

-- 2) Attach the policy to the table:
BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema   => 'CUSTOMER_APP',
    object_name     => 'CUSTOMER',
    policy_name     => 'CUSTOMER_CITY_VPD',
    function_schema => 'CUSTOMER_APP',
    policy_function => 'CUSTOMER_VPD_FN',
    statement_types => 'SELECT, UPDATE, DELETE',
    update_check    => TRUE,
    policy_type     => DBMS_RLS.CONTEXT_SENSITIVE);
END;
/

-- 3) Check:
SELECT object_owner, object_name, policy_name, function,
       sel, upd, del, chk_option, enable, policy_type
FROM   dba_policies WHERE object_owner = 'CUSTOMER_APP';
-- Test (as the application user): EXEC DBMS_SESSION.SET_IDENTIFIER('Ankara');

-- 4) Temporarily disable / drop:
EXEC DBMS_RLS.ENABLE_POLICY('CUSTOMER_APP', 'CUSTOMER', 'CUSTOMER_CITY_VPD', FALSE);
EXEC DBMS_RLS.DROP_POLICY('CUSTOMER_APP', 'CUSTOMER', 'CUSTOMER_CITY_VPD');

作用

Virtual Private Database 会自动将策略函数返回的 WHERE 谓词附加到针对该表的每条 SQL 上，使同一张表的用户只能查看/修改其有权访问的行。update_check=>TRUE 可防止用户将某行更新为其自身无法看到的值。

适用场景

用于多租户应用、基于区域/分支机构的数据隔离，以及在不修改应用代码的情况下增加行级安全。

注意

包含在 Enterprise Edition 中，无需额外许可。CLIENT_IDENTIFIER 可由客户端随意设置；在生产环境中请使用由受信任程序包设置的应用上下文（CREATE CONTEXT ... USING）。SYS 和拥有 EXEMPT ACCESS POLICY 的用户会绕过策略；没有该权限的 Data Pump 导出会静默地只导出部分数据。函数中的错误会以 ORA-28112 阻止所有访问。错误的 policy_type（例如 STATIC）会缓存谓词并返回错误结果。