Custom table contains data partitioned by ORG_ID.
All you need to do in such case is to assign package function MO_GLOBAL.ORG_SECURITY to that
table/synonym/view.
Will the
Multi Org Row Level security be applied against the table or the synonym or the view?
In theory, RLS can be applied against any of the above objects. However in
practice, you will apply RLS against
Objects in APPS Schema. This means, you will most probably apply RLS
on Synonyms. Basically, the Multi Org Views are now replaced by RLS Secured
Synonyms. Hence no code change is required where the
pre-R12 Multi-Org secured view was being accessed. The responsibility of
securing data as per ORG_ID now lies with RLS [also known as VPD - Virtual
Private Database].
I have made changes to my Multi Org
Security Profile, by attaching a new Org Hierarchy. Do i need to run any
process?
Just like we do in HRMS, it is advised that any changes to Security
Profiles must be followed by running "Security List Maintenance"
How can we achieve
multi-org functionality in R12 for custom tables in 11i?
R12 Multi Org Design For
Custom Tables.
Steps:
1. Create a Table in Custom Schema for multi org data.
Ex: CREATE TABLE XX_TABLE_ALL
(
num NUMBER,
name VARCHAR2 (100),
org_id NUMBER
);
Insert some data with different org_id’s
2. Give the Grants to Apps Schema for above created table
GRANT ALL ON XX.XX_TABLE_ALL TO APPS
3. Connect to APPS Schema
Create a synonym named XX_TABLE_ALL in APPS schema, referring to
XX.XX_TABLE_ALL.
CREATE OR REPLACE SYNONYM APPS.XX_TABLE_ALL FOR PO.XX_TABLE_ALL;
Create another synonym named XX_TABLE in APPS, referring to XX.XX_TABLE_ALL.
CREATE OR REPLACE SYNONYM APPS.XX_TABLE FOR PO.XX_TABLE_ALL;
4. Now we can apply Row Level security to XX_TABLE, using function
MO_GLOBAL.ORG_SECURITY.
Oracle RLS will dynamically append WHERE CLAUSE similar to below
SELECT * FROM XX_TABLE WHERE EXISTS (SELECT 1 FROM mo_glob_org_access_tmp oa
WHERE oa.organization_id = org_id)
For Applying RLS we can add policy for XX_TABLE synonym by using
DBMS_RLS.ADD_POLICY API.
Example for add Policy with ADD_POLICY API
BEGIN
dbms_rls.add_policy ('apps', -- Schema Name
'TAB1', -- synonym name
'ORG_SEC',-- use policy_name 'ORG_SEC' –standard policy
'apps', --function_schem
'MO_GLOBAL.ORG_SECURITY',--Standard MO VPD policy fun
'SELECT, INSERT, UPDATE, DELETE', -- statement_type
TRUE, -- update_check
TRUE -- Policy enable
);
END;
After creating policy for the synonym we can get data only for which org_id
initialized in the back end by using MO_GLOBAL.SET_POLICY_CONTEXT('S',’Org_id’);
Example for Testing:
Run the query
SELECT * FROM XX_TABLE
(The query returns no data)
Initialize the org_id
BEGIN
MO_GLOBAL.SET_POLICY_CONTEXT('S',12);
END;
Rerun the query
You will get the data for org_id 12 only.
Setup the Security Profile:
1.Create a Security Profile in HRMS Responsibility
HRMS Manager>Security>Profile
In this setup specify different Operating Units that you
want to Access.
2.Run the Security List Maintenance
This is a required step when you create a new Profile or
modify existing one.
3.Setup the MO: Security Profile value to above created security profile.
This "MO: Security Profile" Profile option
has to be setup at the responsibility level.This will ensure that
Responsibility will have access to the data of all the
Operating units under the Security Profile.
Important profile
option
MO: Security Profile:
MO: Operating Unit:
This comes from 11i and is still valid only its evaluated, if there is no value
setup at MO:Security
Profile.
MO: Default Operating Unit
If the security profile is setup to access multiple operating units, this
requires users to select
the Operating Unit every time user access the Sub ledger pages. To avoid this
the Default Operating Unit profile can be setup.
How MOAC impacts
the way we work in TOAD
11i
To set the Org id in TOAD
1. Get the Org_id from HR_ORGANIZATION_UNITS
2. In toad execute the below code
begin
fnd_client_info.set_org_context(&org_id);
end;
To set the responsibility context in TOAD
1. Get the User id, Responsibility Id and Application id from Front end
Help>Diagnostic>Examine and select BLOCK as
"$PROFILES$" and then get the respective IDS
2. Using the IDs execute the below code in TOAD
begin
FND_GLOBAL.APPS_INITIALIZE(user_id in
number,resp_id in number,resp_appl_id in
number);
end;
R12:
To set the Org id in TOAD
1. Get the Org_id from HR_ORGANIZATION_UNITS
2. In toad execute the below code
--Sets the 201 as single Org id
exec MO_GLOBAL.SET_POLICY_CONTEXT('S',201);
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
To set the responsibility context and initiate MOAC in TOAD
1. Get the User id, Responsibility Id and Application id from Front end
Help>Diagnostic>Examine and select BLOCK as
"$PROFILES$" and then get the respective IDS
2. Using the IDs execute the below code in TOAD
a. exec FND_GLOBAL.INITIALIZE
This will set your
responsibility id, user_id etc
b. call MO_GLOBAL.INIT('AR')
This will read the
MO profile option values for your responsibility/user, and will initialize the
Multi Org Access.
MOAC for table
access
In 11i _ALL Tables where non Org specific and Org specific views were created
on these tables.
But in R12 its different concept
a. For the table AP_INVOICES_ALL a synonym AP_INVOICES_ALL is
created in APPS.
b. Also another synonym AP_INVOICES is created which refers to
AP_INOICES_ALL.
c. A Row Level security is applied to AP_INVOICES, using package
function
MO_GLOBAL.ORG_SECURITY.
This can be double-checked by running SQL select *
from all_policies where
object_name='AP_INVOICES'
e. The effect of this policy is that,whenever you access AP_INVOICES, Oracle
RLS will
dynamically append WHERE CLAUSE similar to below
SELECT * FROM AP_INVOICES
WHERE EXISTS (SELECT 1 FROM mo_glob_org_access_tmp oa WHERE
oa.organization_id =
org_id)
No comments:
Post a Comment