转:Oracle R12 多组织访问的控制 - MOAC(Multi-Org Access Control)

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了转:Oracle R12 多组织访问的控制 - MOAC(Multi-Org Access Control)，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含12317字，纯文字阅读大概需要18分钟。

内容图文

1. --1.Create Data 
2. create table t (x number); 
3. insert into t values (1); 
4. insert into t values (2); 
5. insert into t values (10001); 
6. insert into t values (10002); 
7. commit; 
8. select * from t; 
9. output: 
10. 1 
11. 2 
12. 10001 
13. 10002 
14.  
15. --2.Create VPD FUNCTION 
16. CREATE OR REPLACE FUNCTION f_limited_query_t (s_schema IN VARCHAR2, 
17.                                               s_object IN VARCHAR2) 
18.    RETURN VARCHAR2 
19. AS 
20. BEGIN 
21.    RETURN ‘X <= 10000‘; 
22. END; 
23.  
24.  
25. --3.Register VPD Policy. 
26. BEGIN 
27.    DBMS_RLS.add_policy (object_schema   => ‘APPS‘, 
28.                         object_name     => ‘T‘, 
29.                         policy_name     => ‘POLICY_LIMITED_QUERY_T‘, 
30.                         function_schema => ‘APPS‘, 
31.                         policy_function => ‘F_LIMITED_QUERY_T‘); 
32. END; 
33.  
34.  
35. select * from t; 
36. output: 
37. 1 
38. 2 

--1.Create Data
create table t (x number);
insert into t values (1);
insert into t values (2);
insert into t values (10001);
insert into t values (10002);
commit;
select * from t;
output:
1
2
10001
10002

--2.Create VPD FUNCTION
CREATE OR REPLACE FUNCTION f_limited_query_t (s_schema IN VARCHAR2,
                                              s_object IN VARCHAR2)
   RETURN VARCHAR2
AS
BEGIN
   RETURN ‘X <= 10000‘;
END;


--3.Register VPD Policy.
BEGIN
   DBMS_RLS.add_policy (object_schema   => ‘APPS‘,
                        object_name     => ‘T‘,
                        policy_name     => ‘POLICY_LIMITED_QUERY_T‘,
                        function_schema => ‘APPS‘,
                        policy_function => ‘F_LIMITED_QUERY_T‘);
END;


select * from t;
output:
1
2

 

对于上边例子，我们对表T使用了VPD技术，引入了表限制Function f_limited_query_t，这样我们通过function限制了对表的查询，查询结果只返回小于10000的数字。

 

如何查看我们是否对某张表使用了VPD技术

SELECT * FROM DBA_POLICIES WHERE object_name = ‘T‘;

查询结果中，其中Pakcage + Function就是我们对于表所加的限制。

 

那么Oracle EBS是如何使用VPD技术来实现多组织的

 

R12里,以PO表为例，PO_HEADERS_ALL是基础表(PO/APPS Scehma)，PO_HEADERS是PO_HEADERS_ALL对应的Synonym对象(Apps Schema),我们对PO_HEADERS应用VPD技术.MO_GLOBAL-Dive into R12 Multi Org Design 有较为详细的说明，

In pre Release 12, you would have had following methodology for PO_HEADERS_ALLa. A table is created in PO Schema, named PO_HEADERS_ALLb. A synonym named PO_HEADERS_ALL is created in APPS schema, referring to PO.PO_HEADERS_ALLc. Create a view PO_HEADERS in APPS schema, as "select * from po_headers_all where org_id=client_info"But now in R12, following will happena. A table is created in PO Schema, named PO_HEADERS_ALLb. A synonym named PO_HEADERS_ALL is created in APPS schema, referring to PO.PO_HEADERS_ALLc. Another synonym named PO_HEADERS is created in APPS, referring to PO_HEADERS_ALLd. A Row Level security is applied to PO_HEADERS, using package function MO_GLOBAL.ORG_SECURITY.This can be double-checked by running SQL select * from all_policies where object_name=‘PO_HEADERS‘e. The effect of this policy is that,whenever you access PO_HEADERS, Oracle RLS will dynamically append WHERE CLAUSE similar to belowSELECT * FROM PO_HEADERS WHERE EXISTS (SELECT 1 FROM mo_glob_org_access_tmp oa WHERE oa.organization_id = org_id)

看下下边Query的输出

SELECT * FROM DBA_POLICIES WHERE object_name = ‘PO_HEADERS‘;

可以看到，我们对表PO_HEADERS加了MO_GLOBAL.ORG_SECURITY限制，MO_GLOBAL.ORG_SECURITY的作用实际上就是根据你关于MOAC Profiles的设置，然后转换为相应Where条件(组织过滤)，再进行查询。

 

对于VPD表的查询

对于VPD表，简单的查询一般是不返回记录的，如果想查到记录，需要设置一下上下文先

--普通查询VPD表

select * from PO_HEADERS;--No Output

--Single OU Mode

BEGIN  execute mo_global.set_policy_context(‘S‘,204); --204为ORG_ID,S表示Single Org ContextEND;select * from PO_HEADERS;--会输出OU：204下边的所有PO

--Multiple OU Mode(simulate login to a specific responsibility)

a. Call fnd_global.apps_initialize(userid,resp_id,resp_appl_id);

b. call MO_GLOBAL.INIT(p_appl_short_name);This will read the MO profile option values for your responsibility/user, and will initialize the Multi Org Access.

c.select * from po_headers

 

MOAC API

What is the purpose of MO_GLOBAL.ORG_SECURITY?

The purpose of Row-Level-Security is to hide certain data[based on some conditions]. RLS does so by appending a where clause to the secured object.1. MO_GLOBAL.ORG_SECURITY is a function that returns a predicate for the WHERE CLAUSE2. The where clause will be appended to Table/Synonym/View for which Multi Org Row Level security is enabled

What is the purpose of MO_GLOBAL.SET_POLICY_CONTEXT ?

This procedure has two parameters    p_access_mode          Pass a value "S" in case you want your current session to work against Single ORG_ID          Pass a value of "M" in case you want your current session to work against multiple ORG_ID‘s    p_org_id          Only applicable if p_access_mode is passed value of "S"

MOAC相关的查询语句

Security Profile Definiation

 

[sql] view plain copy print?

1. SELECT * 
2. FROM per_security_profiles 
3. WHERE security_profile_name = ‘PTIAN_SECURITY_PROFILE‘; 

SELECT *
FROM per_security_profiles
WHERE security_profile_name = ‘PTIAN_SECURITY_PROFILE‘;

 

check Organization which are related to a profile

 

[sql] view plain copy print?

1. select PPO.* 
2. from PER_SECURITY_PROFILES PPR, 
3.      PER_SECURITY_ORGANIZATIONS PPO 
4. where PPR.security_profile_id = PPO.security_profile_id 
5. and security_profile_name like ‘%PTIAN_SECURITY_PROFILE%‘; 

select PPO.*
from PER_SECURITY_PROFILES PPR,
     PER_SECURITY_ORGANIZATIONS PPO
where PPR.security_profile_id = PPO.security_profile_id
and security_profile_name like ‘%PTIAN_SECURITY_PROFILE%‘;

List Profile Option Values For All Levels

[sql] view plain copy print?

1. set long 10000   
2. set pagesize 500   
3. set linesize 160   
4. column SHORT_NAME format a30   
5. column NAME format a40   
6. column LEVEL_SET format a15   
7. column CONTEXT format a30   
8. column VALUE format a40   
9. select p.profile_option_name SHORT_NAME,   
10. n.user_profile_option_name NAME,   
11. decode(v.level_id,   
12. 10001, ‘Site‘,   
13. 10002, ‘Application‘,   
14. 10003, ‘Responsibility‘,   
15. 10004, ‘User‘,   
16. 10005, ‘Server‘,   
17. 10006, ‘Org‘,   
18. 10007, decode(to_char(v.level_value2), ‘-1‘, ‘Responsibility‘,   
19. decode(to_char(v.level_value), ‘-1‘, ‘Server‘,   
20. ‘Server+Resp‘)),   
21. ‘UnDef‘) LEVEL_SET,   
22. decode(to_char(v.level_id),   
23. ‘10001‘, ‘‘,   
24. ‘10002‘, app.application_short_name,   
25. ‘10003‘, rsp.responsibility_key,   
26. ‘10004‘, usr.user_name,   
27. ‘10005‘, svr.node_name,   
28. ‘10006‘, org.name,   
29. ‘10007‘, decode(to_char(v.level_value2), ‘-1‘, rsp.responsibility_key,   
30. decode(to_char(v.level_value), ‘-1‘,   
31. (select node_name from fnd_nodes   
32. where node_id = v.level_value2),   
33. (select node_name from fnd_nodes   
34. where node_id = v.level_value2)||‘-‘||rsp.responsibility_key)),   
35. ‘UnDef‘) "CONTEXT",   
36. v.profile_option_value VALUE   
37. from fnd_profile_options p,   
38. fnd_profile_option_values v,   
39. fnd_profile_options_tl n,   
40. fnd_user usr,   
41. fnd_application app,   
42. fnd_responsibility rsp,   
43. fnd_nodes svr,   
44. hr_operating_units org   
45. where p.profile_option_id = v.profile_option_id (+)   
46. and p.profile_option_name = n.profile_option_name   
47. and upper(p.profile_option_name) in ( select profile_option_name   
48. from fnd_profile_options_tl   
49. where upper(user_profile_option_name)   
50. like upper(‘%MO: Security Profile%‘))   
51. and usr.user_id (+) = v.level_value   
52. and rsp.application_id (+) = v.level_value_application_id   
53. and rsp.responsibility_id (+) = v.level_value   
54. and app.application_id (+) = v.level_value   
55. and svr.node_id (+) = v.level_value   
56. and org.organization_id (+) = v.level_value  
57. order BY  short_name, user_profile_option_name, level_id, level_set;   

    set long 10000  
    set pagesize 500  
    set linesize 160  
    column SHORT_NAME format a30  
    column NAME format a40  
    column LEVEL_SET format a15  
    column CONTEXT format a30  
    column VALUE format a40  
    select p.profile_option_name SHORT_NAME,  
    n.user_profile_option_name NAME,  
    decode(v.level_id,  
    10001, ‘Site‘,  
    10002, ‘Application‘,  
    10003, ‘Responsibility‘,  
    10004, ‘User‘,  
    10005, ‘Server‘,  
    10006, ‘Org‘,  
    10007, decode(to_char(v.level_value2), ‘-1‘, ‘Responsibility‘,  
    decode(to_char(v.level_value), ‘-1‘, ‘Server‘,  
    ‘Server+Resp‘)),  
    ‘UnDef‘) LEVEL_SET,  
    decode(to_char(v.level_id),  
    ‘10001‘, ‘‘,  
    ‘10002‘, app.application_short_name,  
    ‘10003‘, rsp.responsibility_key,  
    ‘10004‘, usr.user_name,  
    ‘10005‘, svr.node_name,  
    ‘10006‘, org.name,  
    ‘10007‘, decode(to_char(v.level_value2), ‘-1‘, rsp.responsibility_key,  
    decode(to_char(v.level_value), ‘-1‘,  
    (select node_name from fnd_nodes  
    where node_id = v.level_value2),  
    (select node_name from fnd_nodes  
    where node_id = v.level_value2)||‘-‘||rsp.responsibility_key)),  
    ‘UnDef‘) "CONTEXT",  
    v.profile_option_value VALUE  
    from fnd_profile_options p,  
    fnd_profile_option_values v,  
    fnd_profile_options_tl n,  
    fnd_user usr,  
    fnd_application app,  
    fnd_responsibility rsp,  
    fnd_nodes svr,  
    hr_operating_units org  
    where p.profile_option_id = v.profile_option_id (+)  
    and p.profile_option_name = n.profile_option_name  
    and upper(p.profile_option_name) in ( select profile_option_name  
    from fnd_profile_options_tl  
    where upper(user_profile_option_name)  
    like upper(‘%MO: Security Profile%‘))  
    and usr.user_id (+) = v.level_value  
    and rsp.application_id (+) = v.level_value_application_id  
    and rsp.responsibility_id (+) = v.level_value  
    and app.application_id (+) = v.level_value  
    and svr.node_id (+) = v.level_value  
    and org.organization_id (+) = v.level_value 
    order BY  short_name, user_profile_option_name, level_id, level_set;  

 

支持MOAC功能的Form开发步骤

这部分摘自:http://bbs.erp100.com/thread-103395-1-1.html

在R12版本中，OU的控制采取了MOAC的方式，使用户的操作得到了改善。 而如果客户化的Form能够支持MOAC的功能，需要在界面上提供当前用户可以选择的OU字段供用户选择。
功能展示如下图：

这样在Form的开发过程中需要如下的开发步骤：

1,PRE-FORM 触发器初始化MOAC配置环境  

添加如下代码：  

MO_GLOBAL.init(‘ONT’);  

— global.mo_ou_count  

— global.mo_default_org_id  

— global.mo_default_ou_name  

IF l_default_org_id IS NOT NULL THEN

— default org id not null    

MO_GLOBAL.SET_POLICY_CONTEXT(‘S’,l_default_org_id);  

ELSE    

MO_GLOBAL.SET_POLICY_CONTEXT(‘M’,null);  

END IF;

— default org id not null   

这段代码的作用是根据预制文件的设置，初始化OU的信息，将用户可以访问的OU信息插入到mo_glob_org_access_tmp表中，  

同时将默认的OU ID、OU Name和OU Count分别写到global.mo_default_org_id, global.mo_default_org_id, global.mo_default_ou_name   具体细节可以查看数据库包：mo_global
2,WHEN-CREATE-RECORD触发器中拷贝OU默认值  

在此触发器中将默认的OU ID和OU Name拷贝给Form界面上对应的字段，实现创建记录的时候默认带出默认OU信息。  

copy(name_in(‘global.mo_default_org_id’),’’);   copy(name_in(‘global.mo_default_ou_name’),’’);
3,创建OU的LOV  

Form界面上的OU 名称字段创建一个LOV，LOV对应记录组的SQL语句如下：  

SELECT hr.organization_id organization_id, hr.NAME organization_name    

FROM hr_operating_units hr   

WHERE mo_global.check_access(hr.organization_id) = ‘Y’   

ORDER BY organization_name
其它没有特殊的步骤。

MindMap

 

参考：

Oracle Applications Multiple Organizations Implementation Guide

EBS R12 MOAC(Multi-Org Access Control)原理探索

MO_GLOBAL-Dive into R12 Multi Org Design

FAQ - Multiple Organizations Architechure (Multi-Org) (Doc ID 165042.1)

 

•Note: 420787.1 Oracle Applications Multiple Organizations Access Control for Custom Code •Note: 462383.1 SQL Queries and Multi-Org Architecture in Release 12 •Note: 396750.1 Oracle Applications Multiple Organizations Release 12 Roadmap Document

 

Note 745420.1 -How To Setup And Check MO / MOAC Setup In APPS Instance At R12 Level - Precedence of MO - MOAC Profile Options Best Practices for Securing the E-Business Suite [ID 189367.1] Best Practices For Securing Oracle E-Business Suite Release 12 [ID 403537.1] Understanding and Using HRMS Security in Oracle HRMS [ID 394083.1] Security List Maintenance for All Profiles Is Excluding Employees [ID 755410.1] Effect Of Security List Maintenance Concurrent Request within the Oracle HRMS Module [ID 457629.1]

 

转载请注明出处：http://blog.csdn.net/pan_tian/article/details/7774715

===EOF===

转:Oracle R12 多组织访问的控制 - MOAC(Multi-Org Access Control)

标签：

本文系统来源：http://www.cnblogs.com/pompeii2008/p/5688897.html

内容总结

以上是互联网集市为您收集整理的转:Oracle R12 多组织访问的控制 - MOAC(Multi-Org Access Control)全部内容，希望文章能够帮你解决转:Oracle R12 多组织访问的控制 - MOAC(Multi-Org Access Control)所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---