Oracle VPD POLICY DBMS_RLS

背景：

    最近某公司在ERP做库存领料时，发现选择物料后报错： 用户退出意外错误（1），来自 Qt_EU.Create 结构树。

    此fORM界面为客户化，错误来自查询现有量信息。

分析问题：

    遂查询该物料现有量，输入物料后发现，现有量是有的，但可用性界面各数量均为空。

    这问题很奇怪，去追查该form数量赋值逻辑，发现调用了现有量查询的标准api。copy参数后通过plql执行该API，报错。

    遂去检查子库存设置，关键点来了，在子库存设置界面，能查询到BATB子库，但无法查询到GCH子库。

    plsql查询子库存信息:

SELECT * FROM inv.mtl_secondary_inventories msi WHERE 1=1 AND msi.organization_id = 101 AND msi.secondary_inventory_name = 'GCH';

    有数据，但为啥前台界面查不到？

    copy前台界面的查询条件，system.last_query， 发现是查询 MTL_SECONDARY_INVENTORIES_FK_V 视图。

    plsql初始化环境变量，查询该视图：

BEGIN
   Fnd_Global.Apps_Initialize(43943, 53249, 401);
   Mo_Global.Init('INV'); -- MOAC 
   Mo_Global.Set_Policy_Context('S', 101);
END;
SELECT * FROM  MTL_SECONDARY_INVENTORIES_FK_V  msi WHERE 1=1 AND msi.organization_id = 101 AND msi.secondary_inventory_name = 'GCH';

    依然是有数据的，为啥？

    突然想到，没有初始化库存组织信息，试一下：

BEGIN
   Fnd_Global.Apps_Initialize(43943, 53249, 401);
   Mo_Global.Init('INV'); -- MOAC 
   Mo_Global.Set_Policy_Context('S', 101);
   fnd_profile.put('MFG_ORGANIZATION_ID',101);
END;

    没有数据了！！！ MTL_SECONDARY_INVENTORIES_FK_V 查不到GCH子库， mtl_secondary_inventories 也查不到GCH。

    将库存组织信息赋值为空:

fnd_profile.put('MFG_ORGANIZATION_ID',null);

    又可以查到GCH了。

TRACE分析：

    在子库存设置界面，做个查询的trace跟踪。

    查询完毕结束trace跟踪，然后去应用服务器取trace文件。

    trace中看到两段客户化代码：

begin :con := "CUX_EAM_UTIL_PKG"."CHECK_SUBINV_SEC"(:sn, :on); end;

begin :con := "CUXPOLICY_MTL_SECONDARY_GCH"(:sn, :on); end;

    打开后发现是function，而function的两个参数 P_Schema,P_Object 让我意识到这是个VPD适用函数。

SELECT * FROM Dba_Policies t WHERE 1 = 1 AND t.Object_Name = 'MTL_SECONDARY_INVENTORIES';

    果然，发现了两条policy，而其中的CUXPOLICY_MTL_SECONDARY_GCH 正是导致初始化库存组织信息后无法查到GCH子库的罪魁祸首。

    可是，为什么查询 inv.mtl_secondary_inventories 也没有结果呢，正常来说，同义词查不到数据可以理解，但基表查不到就不合常理了。

    莫非这个策略是加在基表上的？

    果然，查Dba_Policies表后发现，object_owner是INV而非APPS.

    至此，原因已查明。

VPD&POLICY&DBMS_RLS:

    上文中提到了VPD，这里做个解释。

    VPD(virtual Private database):虚拟私有数据库，通过技术手段实现同一个数据库中对不同对象的数据隔离。

    oracle 通过策略（Oracle Policy）来实现VPD。

    Oracle Policy应用于数据库行访问权限控制时，其作用就是在查询数据表时，自动在查询结果加上一个where子句。假如该查询已有where子句，则在该where子句后面加上and ...

    由Oracle Policy自动添加的where子句，通常由一个函数来实现。而进行数据行级访问权限控制实现的结果，也是通过该函数返回。

    这种在不同用户、不同业务场景查询同一个数据源得出不同数据的技术，控制核心在于行级安全控制RLS（row level security）。

    Oracle给出了DBMS_RLS包实现此功能。

    1. 创建策略

BEGIN
    Dbms_Rls.Add_Policy(Object_Schema         => 'APPS', --数据表(或视图)所在的Schema名称
                        Object_Name           => 'MTL_SECONDARY_INVENTORIES', --数据表(或视图)的名称  
                        Policy_Name           => 'MTL_SECONDARY_INVENTORIES_GCH', --POLICY的名称，主要用于将来对Policy的管理
                        Function_Schema       => 'APPS', --返回Where子句的函数所在Schema名称
                        Policy_Function       => 'CUXPOLICY_MTL_SECONDARY_GCH', --返回Where子句的函数名称
                        Statement_Types       => 'Select,Insert,Update,Delete', --要使用该Policy的DML类型，如'Select,Insert,Update,Delete'  
                        Update_Check          => FALSE, --仅适用于Statement_Type为'Insert,Update'，值为'True'或'False'  
                        ENABLE                => TRUE, --是否启用，值为'True'或'False'
                        Static_Policy         => FALSE, --默认值为FALSE。如果它被设置为TRUE则所有用户启用该策略，sys或特权用户例外。
                        Policy_Type           => NULL, --默认值是null，意味着static_policy的值决定，在这里指定任何策略将覆盖static_policy的值。
                        Long_Predicate        => NULL, --默认值是null，max predicate length 4000 bytes (default) or 32K
                        Sec_Relevant_Cols     => NULL, --默认值是null，敏感的字段名称,
                        Sec_Relevant_Cols_Opt => NULL --默认值是null，设置为dbms_rls.ALL_ROWS来显示所有的行，敏感的列的值为null
                        );
END;

    2. 删除策略

BEGIN
    Dbms_Rls.Drop_Policy(Object_Schema => 'APPS', --数据表(或视图)所在的Schema名称
                         Object_Name   => 'MTL_SECONDARY_INVENTORIES', --数据表(或视图)的名称  
                         Policy_Name   => 'MTL_SECONDARY_INVENTORIES_GCH' --POLICY的名称
                         );
END;

    3. 启用/停用策略

BEGIN
    Dbms_Rls.Enable_Policy(Object_Schema => 'APPS', --数据表(或视图)所在的Schema名称
                           Object_Name   => 'MTL_SECONDARY_INVENTORIES', --数据表(或视图)的名称  
                           Policy_Name   => 'MTL_SECONDARY_INVENTORIES_GCH', --POLICY的名称
                           ENABLE        => TRUE --'默认True,是否启用，True为启用策略，False为禁用策略'
                           );
END;

    以上列出了DBMS_RLS包的常用函数，还有一些不常用的如刷新、策略组等等，请自行研究吧。