Oracle EBS R12多組織訪問架構

關於R12的新特性Multi-Org Access Control(MOAC).Oracle宣傳的好處主要有:
1.enable users to access to secured data in one or more Operating Units from a single responsibility
2.End-Users can access/transact data within several operating units based on Security Profile attached to a responsibility.
3.Profile 'MO:Security Profile' will ensure access to multiple operating units from single responsibility

具體為什麼會改成這樣的原因可以從R12的宣傳語看出:"The Global Business Release" "R12 Enables You To Think Globally,Work Globally,Manage Systems Globally "
從技術角度的一些拾零記錄
1.在R12之前的版本中,組織控制是通過View來實現,比如說AP_INVOICES是定義在AP_INVOICES_ALL上面的View,而View一般都是通過在ORG_ID加條件來限制資料訪問.
從R12開始,這樣的View被取消了,取而代之的是同義字(synonyms),www.bkjia.com比如說AP_INVOICES就是AP_INVOICES_ALL的同義字(synonyms).
在R12裡可以通過下面的SQL語句來查詢有這樣關係的表
---
select *
from dba_synonyms syn
where syn.synonym_name || '_ALL' = syn.table_name
---
2.R12中的組織訪問限制是如何?的呢?
是通過資料庫安全方面的新特性virtual private database (VPD) policy來實現的,具體就是給_ALL表的同義字(比如說AP_INVOICES),添加對應的Policy.
這樣在在查詢的時候,資料庫會根據Policy的來產生對應的條件(where)語句,來限制我們對資料的訪問.
通過select * from dba_policies where policy_name = 'ORG_SEC'我們可以查詢到那些表添加了Policy,以及是通過那個具體的Function來產生要添加的where條件
我們通過查詢可以發現,比較具體的一個例子
Policy_name: ORG_SEC
Policy_group: SYS_DEFAULT
Package: MO_GLOBAL
Function: ORG_SECURITY
通過查看MO_GLOBAL.MO_GLOBAL,我們可以看到具體的產生限制語句的邏輯.其中Multiple OU Access是通過GLOBAL TEMPORARY TABLE MO_GLOB_ORG_ACCESS_TMP來實現的.
可以參看Note462383.1來看具體的每種情況會產生什麼樣的Where條件(a WHERE clause).
3.可以通過表FND_MO_PRODUCT_INIT中的STATUS來判斷具體的某個Application是否支援MOAC.
4.通過表FND_MO_SP_PREFERENCES的User_ID, Resp_ID, Security_Profile_ID可以查看預設的組織(Default Org_ID).相關聯的Profile是MO: Default OU
5.MOAC定義的步驟
1.Define Operating Units(Optional)
2.Define Security Profile Enter the Operating Units for which you want access
3.Run concurrent program "Security List Maintenance Program"
4.Assign the security profile to MO: Security Profile profile option for your responsibility or user
5.Assign MO: Default Operating Unit(Optional)
6.Assign MO: Operating Unit(Mandatory for only Single Org or if MO: Security Profile is not defined)
6.MOAC在應用中初始化的基本流程
1.MO_GLOBAL.init
2.Set_Org_Access -- MO_GLOB_ORG_ACCESS_TMP
3.Check FND_MO_PRODUCT_INIT
4.If MO: Security Profile Else MO: Operating Unit
5.Set Policy Context "A", "S", "M" and org_id
注:這裡的Policy Context的含義如下
Restrict the data to eliminate only seed data rows is the access mode is All
Restrict the data to a single operating unit if the access mode is Single
Restrict the data to multiple operating units if the access mode is Multiple
Restrict the data to not return any rows if the access mode is None
Reference:
Doc ID:462383.1 SQL Queries and Multi-Org Architecture in Release 12