别的就算您要时常拍卖中中原人民共和国OU下的事体,开发时打开Form中有集体LOV采取达成格局

2014-05-30 Created By BaoXinjian

2014-05-31 Created By BaoXinjian

什么是MOAC

MOAC(Multi-Org Access Control)为多协会访问控制,是Oracle EBS
途达12的重中之重新职能,它能够完结在一个Responsibility下对八个Operation
Unit(OU)进行操作。MOAC允许用户在不切换responsibility的情景下,在2个responsibility下拍卖四个OU协会的业务。

User –> Responsibilities –> Single Operation Unit Mode  / 
Multiple Operation Unit Mode

ca88亚洲城网站 1

多个使用场景:

某公司下面首要分为四个区域(北美,北美洲,亚太地区),亚洲印度洋地区你是一买卖部首席营业官,负责全数八个Operation
Unit。 那种状态下,系统一管理理员能够创建二个security profile,这么些security
profile设置成能够访问那三个亚太地区协会,并把那个security
profile赋予到您的responsibility下,那样您就能在同一个职务下访问那三个OU了,就不用不停地切换任务来走访分化OU了。

别的要是您要时不时处理中中原人民共和国OU下的事务,那么你能够安装Profile:MO: Default
Operating Unit到中中原人民共和国,那么业务私下认可的OU就是炎黄了。

 

ca88亚洲城网站 2

ca88亚洲城网站 3一、
介绍

ca88亚洲城网站 4ca88亚洲城网站,一、摘要

MOAC相关的Profile

有多个重点的Profile

MO: Security Profile :provides access to multiple operating units
from a single responsibility.If the MO: Security Profile is set, then
the MO: Operating Unit profile will be ignored.

MO: Default Operating Unit :If you set the MO: Security Profile
profile option, you can also set an operating unit as the default
operating unit using the MO: Default Operating Unit profile option. This
is useful when you transact in multiple operating unit but frequently
transact in one operating unit.

MO:  Operating Unit :MO: Operating Unit profile option only provides
access to one operating unit.

ca88亚洲城网站 5

 



MOAC profiles rules

1) If the profile  option “MO: Security Profile” is not set, then “MO: 
Operating Unit”  value is used  as the default Operating Unit even if
“MO:  Default Operating Unit” profile is set to a different value.

2) If the profile option “MO: Security Profile” is set and gives access
to only one Operating Unit, the default Operating Unit will return this 
value even if “MO: Default Operating Unit” is set to a different  value.

3) If the profile option “MO: Security  Profile” is set and gives access
to  multiple Operating Units :      

–  If the profile value “MO: Default Operating Unit”  is set,  it  is
validated  against the list of Operating Units in “MO: Security
Profile”.         

+ If  the Operating Unit is included in the security profile then it
is  returned as the default value.          

+ Else there is no defaulted Operating Unit .     

–  If the Profile Option “MO: Default Operating Unit”  is not  set, 
then there is zero (no)  default Operating Unit.

以前一贯存在对Oracle PAJERO12
多协会走访的一些质疑,所以查询了一些城门失火资料,并介绍完结景逸SUV12 MOAC的Oracle
VPD技术

路虎极光12 Form
也许其它一回开发时,很多景观下会涉及Tucson12
MOAC多组织开发,以下介绍了四个广大的选择,如有遗漏还请同学继续补充

单组织的案例

单组织即一个Responsibility只访问二个OU,设置情形大概如下,对于任务:马努facturing
and Distribution Manager,

MO: Operating Unit=Vision Operations

MO: Security Profile=空

ca88亚洲城网站 6

那般我们在应用职务:马努facturing and Distribution
Manager的时候,就只可以访问到Vision
Operations的多寡,比如创设的PO,那个PO只可以处于OU:Vision
Operations下,别的所采取的供货商也不得不是Vision Operations下的。

ca88亚洲城网站 7

 

Oracle VPD全称Virtual Private Database,
Oracle的一种控制数据访问的在这之中安全策略之一

1.
开发时打开Form自动弹出组织挑选达成形式(扩展Choose O卡宴G功效)

Oracle数据安全策略访问实现方式

2.
开发时打开Form中有协会LOV采取完结格局

多协会的案例

先是定义2个Security Profile,路径:HOdysseyMS Super User Responsibility >
Security > Define Security Profile,Business Group:Vision
Corporation,’Security Type’选择’Secure organizations by organization
hierarchy and/or organization list’,并且大家把在Organization
Name列表中添加四个OU:Vision Operations,Vision Corporation ,Vision
Services.

ca88亚洲城网站 8

封存定义的Security Profile,然后在HPAJERO任务下,运营“Security List
Maintenance” program,“Generate lists for”= One Named Security Profile
,Security Profile是刚刚定义的’PTIAN_SECURITY_PROFILE’。

Security List Maintenance的效应是让您定义的Security
Profile生效,能够设置到Profile “MO: Security Profile”(The Security List
Maintenance concurrent program must be run each time you add or change
Security Profiles.)

ca88亚洲城网站 9

System Admin任务下,Profile > System,设置MO: Security
Profile为刚刚定义的’PTIAN_SECURITY_PROFILE’

ca88亚洲城网站 10

再切回马努facturing and Distribution
Manager职责,打开PO,那几个时候,你就可以不切换职责的事态,定义多个OU下的购置订单了。

ca88亚洲城网站 11

  •  Role-based security
    • create role cust_role;
    • grant select on customer to cust_role;
    • grant select, update on orders to cust_role;
    • grant cust_role to scott;
  •  Grant-execute security
    • grant execute on procedure to cust_role;
  •  Virtual private databases
    • 以下为详细介绍
  1. 兰德CR-V12多组织的技能达成方式-VPD技术

  2. VPD技术详解,数据库安全策略 

 

下文对那6个状态做一些用法和案例做一下介绍

MOAC的兑现原理-VPD技术

MOAC的得以实现是因此Oracle数据库的VPD(Virtual
Private
Database)技术来贯彻的。VPD技术提供了数据库对象(表,同义词,视图)行级别访问的决定。使用VPD技术能够有效地限制用户获取数据的界定。

Secooler 的一篇作品 使用Oracle VPD(Virtual Private
Database)限制用户获取数据的界定
 
讲VPD,里边的例子很是不难通晓.

[sql] view
plain

copy
print?

  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. 10001 
  11. 10002 
  12.  
  13. –2.Create VPD FUNCTION 
  14. CREATE OR REPLACE FUNCTION
    f_limited_query_t (s_schema IN VARCHAR2, 

  15.                                               s_object IN VARCHAR2) 

  16.    RETURN VARCHAR2 
  17. AS 
  18. BEGIN 
  19.    RETURN ‘X <=
    10000’; 
  20. END; 
  21.  
  22.  
  23. –3.Register VPD Policy. 
  24. BEGIN 
  25.    DBMS_RLS.add_policy (object_schema   => ‘APPS’, 
  26.                         object_name     => ‘T’, 
  27.                         policy_name     => ‘POLICY_LIMITED_QUERY_T’, 
  28.                         function_schema => ‘APPS’, 
  29.                         policy_function => ‘F_LIMITED_QUERY_T’); 
  30. END; 
  31.  
  32.  
  33. select * from t; 

  34. output: 

对此下面例子,大家对表T使用了VPD技术,引入了表限制Function
f_limited_query_t,那样咱们经过function限制了对表的询问,查询结果只回去小于一千0的数字。

 

怎么着查看我们是不是对某张表使用了VPD技术

SELECT * FROM DBA_POLICIES WHERE object_name = ‘T’;

ca88亚洲城网站 12

询问结果中,个中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_ALL

a. A table is created in PO Schema, named PO_HEADERS_ALL

b. A synonym named PO_HEADERS_ALL is created in APPS schema, referring
to PO.PO_HEADERS_ALL

c. 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 happen

a. A table is created in PO Schema, named PO_HEADERS_ALL

b. A synonym named PO_HEADERS_ALL is created in APPS schema, referring
to PO.PO_HEADERS_ALL

c. Another synonym named PO_HEADERS is created in APPS, referring to
PO_HEADERS_ALL

d. 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 below

SELECT * FROM PO_HEADERS WHERE EXISTS (SELECT 1 FROM
mo_glob_org_access_tmp oa WHERE oa.organization_id = org_id)

ca88亚洲城网站 13

看下上面Query的输出

SELECT * FROM DBA_POLICIES WHERE object_name =
‘PO_HEADERS’;ca88亚洲城网站 14

能够看来,大家对表PO_HEADERS加了MO_GLOBAL.ORG_SECURITY限制,MO_GLOBAL.ORG_SECU中华VITY的机能实际上就是基于你至于MOAC
Profiles的安装,然后转换为相应Where条件(组织过滤),再开始展览询问。

 

ca88亚洲城网站 15② 、案例 – 达成模仿卡宴12
MOAC对客制化的表添加标准多组织访问控制安全策略

 

对于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 Context

END;

select * from PO_HEADE智跑S;–会输出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

 


ca88亚洲城网站 16二、案例

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”

例如, ap_invoices_all 和 
ap_inovices访问的分化


MOAC相关的询问语句

Security Profile Definiation

 

[sql] view
plain

copy
print?

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

ca88亚洲城网站 17

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%’; 

ca88亚洲城网站 18

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;   

ca88亚洲城网站 19

    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;  

 

当没有开首化mo_global.set_policy_context之前,ap_invoices_all
是足以访问具有的数额, 而ap_invoices是未曾其余数据再次回到的

案例① 、开发时打开Form自动弹出组织选择完结格局(扩充Choose
O科雷傲G成效)

支撑MOAC功用的Form开发步骤

这有个别摘自:http://bbs.erp100.com/thread-103395-1-1.html

在瑞虎12本子中,OU的决定采用了MOAC的方法,使用户的操作得到了改正。
而一旦客户化的Form能够协理MOAC的效果,必要在界面上提供当前用户能够挑选的OU字段供用户采用。
功能显示如下图:

ca88亚洲城网站 20

那般在Form的开支进程中需求如下的支付步骤:

1,PRE-FOCRUISERM 触发器先河化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-RECOLX570D触发器中拷贝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
任何没有特别的手续。

当下始化mo_global.set_policy_context之后,ap_invoices_all
还能访问具有的数额,
而ap_invoices能够回来对应协会的记录,而此外团队的记录并不曾被重临

    (1). 新增4个Paramters:
Chart_Of_Account_Id, Org_Name, Org_Code, Org_id七个参数;

MindMap

ca88亚洲城网站 21

 

参考:

Oracle Applications Multiple Organizations Implementation
Guide

EBS GL45012 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===

原因是,在视图ap_invoices添加了3个安全性策略,
mo_golbal.org_security

    (2). 在Form
Level中Pre-Form中新增fnd_org.choose_org;

ca88亚洲城网站 22

    (3). 在Form
Level中When-New-Form-Instance中新增app_window.set_title(‘bxj_windows’,
:parameter.org_code);

就此在模拟标准的MOAC功用建立对客制化表的多协会走访,可以参考上述例子

 

成立二个基表(bxj_vpd_invoices_b),
用以存放全部数据,不管有没有通过mo_global初始化session都能够回去数据,
类似于基表ap_invoices_all的功能

案例② 、开发时打开Form中有团体LOV选择达成格局

建立3个视图(bxj_vpd_invoices_v),
此视图建立在基表之上,再拓展添加安全性策略,达成多组织走访,类似于视图ap_invoices的功能

    (1).
Pre-Form触发器起先化MOAC配置环境

Step1. 成立基表

        MO_GLOBAL.init(‘ONT’);

CREATE TABLE
bxj_vpd_invoices_b(

        IF l_default_org_id IS NOT
NULL THEN — default org id not null

  invoice_id NUMBER,

            MO_GLOBAL.SET_POLICY_CONTEXT(‘S’,l_default_org_id);
        ELSE

  invoice_num VARCHAR(100),

            MO_GLOBAL.SET_POLICY_CONTEXT(‘M’,null);
        END IF;

  invoice_description
VARCHAR(240),

    (2).
When-Create-Record触发器中复制OU暗中同意值

  org_id NUMBER

        在此触发器大校暗中认可的OU ID和OU
Name拷贝给Form界面上相应的机关,达成创制记录的时候私下认可带出暗许OU消息。

)

        copy(name_in(‘global.mo_default_org_id’),’’);

 Step2. 创建视图

        copy(name_in(‘global.mo_default_ou_name’),’’);

CREATE VIEW bxj_vpd_invoices_v
AS

    (3). 创建OU的LOV

  SELECT invoice_id, invoice_num,
invoice_description, org_id

        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

FROM bxj_vpd_invoices_b;

 

 Step3. 准备测试资料,一笔的org为204,
一笔的org为889

案例③ 、Tiggo12多组织的技术完毕格局-VPD技术

INSERT INTO bxj_vpd_invoices_b VALUES
(1, ‘BXJ_20140520_01′,’BXJ_VPD_Show_With ORG204’, 204)

    (1). 三个Profile<MO: Security
Profile、MO: Default Operating Unit、MO: Operating Unit>

INSERT INTO bxj_vpd_invoices_b VALUES
(2, ‘BXJ_20140520_02′,’BXJ_VPD_Show_With ORG889’, 889)

    (2). ap_invoices
定义在ap_invoices_all基础之上

Step4.
查询基表和视图都以足以回来全体两条记下

        a.
对于VPD<ap_invoices>表,简单的查询一般是不回去记录的,假设想查到记录,必要安装一下上下文先

 ca88亚洲城网站 23

        b. 普通查询VPD表

Step5.
最重视的一步,对客制化视图bxj_vpd_invoics_v添加安全性策略dbms_rls.add_policy

            select * from ap_invoices;
–>No Output

ca88亚洲城网站 24

        c. Single OU Mode

Step6. 产看安全性策略是或不是创设成功

            BEGIN  execute
mo_global.set_policy_context(‘S’,204);

ca88亚洲城网站 25

            –204为ORG_ID,S表示Single
Org ContextEND;select * from
PO_HEADEWranglerS;–会输出OU:204底下的保有PO

Test1. 方针建立成功,举办测试,直接select
table bxj_vpd_invoics_v, 查询未回来任何结果

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

ca88亚洲城网站 26

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

Test2. 对session进行初阶化

        d. call
MO_GLOBAL.INIT(p_appl_short_name);This will read the MO profile
option values for your responsibility/user,

ca88亚洲城网站 27

            and will initialize the Multi
Org Access.

Test3. 再度直接select table
bxj_vpd_invoics_v,查询只回去一条O凯雷德G204的笔录,而O宝马X3G889的笔录则并没突显,达成了对客制化表的多协会走访

        e.select * from po_headers;
–>Output
    (3).
MO_GLOBAL.ORG_SECU凯雷德ITY的意义实际上就是依照你关于MOAC
Profiles的装置,然后转换为对应Where条件(组织过滤),再举行查询

ca88亚洲城网站 28

            ca88亚洲城网站 29

 

 

ca88亚洲城网站 30三 、案例 – 完毕3个简单易行的客制的VPD安全策略对表数据访问的管理控制

案例肆 、 VPD技术详解,数据库安全策略 


(1). 创建Table Or
View的数据<bxj_invoices_policy_v>

须求:
对数码表bxj_invoice_total不显示invoice total > 1000的记录

 1     CREATE OR REPLACE VIEW apps.bxj_invoices_policy_v AS
 2 
 3         SELECT a.invoice_id,
 4 
 5                        b.vendor_name,
 6 
 7                        c.vendor_site_code,
 8 
 9                        a.invoice_num,
10 
11                        a.invoice_amount,
12 
13                        a.invoice_currency_code
14 
15             FROM ap_invoices_all a, ap_suppliers b, ap_supplier_sites_all c
16 
17           WHERE a.vendor_id = b.vendor_id AND a.vendor_site_id = c.vendor_site_id
18 
19                AND ROWNUM <= 10 

Step1.创设三个回顾的表,并预备4笔测试数据

(2). 创建Policy Function

ca88亚洲城网站 31

 1     CREATE OR REPLACE FUNCTION bxj_fun_invoices_policy (s_schema IN VARCHAR2,
 2 
 3                                                                                                     s_object IN VARCHAR2)
 4 
 5         RETURN VARCHAR2
 6 
 7     AS
 8 
 9     BEGIN
10 
11         RETURN 'invoice_currency_code = ' || '''EUR''';
12 
13     END; 

Step2. 查看原始数据,有4笔资料

(3). 注册VPD Policy

ca88亚洲城网站 32

 1     BEGIN
 2 
 3         DBMS_RLS.add_policy (object_schema => 'APPS',
 4 
 5                                                 object_name => 'BXJ_INVOICES_POLICY_V',
 6 
 7                                                 policy_name => 'POLICY_LIMITED_QUERY_INVOICES',
 8 
 9                                                 function_schema => 'APPS',
10 
11                                                 policy_function => 'BXJ_FUN_INVOICES_POLICY');
12 
13     END; 

Step3.最要紧的一步,建立VPD
Function,系统再次回到以下字段,作为VPD策略,其实个人感觉正是基于差别的标准化和条件去分别所要加的规格构成,来兑现多少的访问控制

 

ca88亚洲城网站 33

(4).
相比注册Policy前后,View的出口结果,由10条变为了7条,直接过滤了USD的记录

Step4. 通过API
dbms_rls.add_policy付给VPD策略

    a. 注册前

ca88亚洲城网站 34

       
ca88亚洲城网站 35 

Step5. 检查VPD策略是还是不是赋值成功

    a. 注册后

ca88亚洲城网站 36

        ca88亚洲城网站 37

Test1.
平素询问已投入客制化VPD策略的表,已按VPD策略,只呈现金额小于一千的笔录

 

ca88亚洲城网站 38

Thanks and Regards

 

ca88亚洲城网站 39

Thanks and Regards

ca88亚洲城网站 40

相关文章