Oracle Multi-org views are not there in Oracle Apps Release 12. Those views are replaced by Secure Synonyms.
In R12, data is restricted by assigning a virtual private database (VPD) policy to the synonym. This policy allows the system to dynamically generate restricting conditions when queries are run against the synonym. The database rewrites the user’s SQL statement to include conditions set by security policy that are visible to the user when a user directly or indirectly accesses the secure objects
In this article, we will cover secure synonym in Oracle Apps R12 and how to add security policy to synonym using dbms_rls.add_policy package.
Oracle provides dbms_rls package to manage row level security in Oracle Database. We are going to use dbms_rls.add_policy procedure to attach a security policy to a synonym.
dbms_rls.add_policy – add a row level security policy to a table, view or synonym
You can read this Oracle Article on dbms_rls to get syntax and parameter details. Here I am going to cover real-world working example.
Step 1: – Create a custom table and synonym
Use below command to create a dummy table for the demonstration.
CREATE TABLE secure_synonym_demo(org_id NUMBER(5),description VARCHAR2(50));
Insert below records in the table. ORG_ID should be actual org_id in your database.
CREATE SYNONYM secure_synonym FOR secure_synonym_demo;
Data from the synonym before attaching security policy.
SQL> select * From secure_synonym; ORG_ID DESCRIPTION ---------- ------------------------------ 82 India OU 83 China OU 84 USA OU
Step 2:- attach Org dependent security policy to the Synonym
Below procedure attached Org dependent security policy to the Synonym.
BEGIN DBMS_RLS.add_policy ( object_schema => 'APPS', -- schema owning the table/view, current user if NULL object_name => 'SECURE_SYNONYM', --name of table or view policy_name => 'ORG_SEC', --name of policy to be added function_schema => 'APPS', --schema of the policy function, current user if NULL policy_function => 'MO_GLOBAL.ORG_SECURITY', --function to generate predicates for this policy statement_types => 'SELECT, INSERT, UPDATE, DELETE', --statement type that the policy apply, default is any update_check => TRUE, -- policy checked against updated or inserted value? enable => TRUE, -- policy is enabled? static_policy => FALSE, -- policy is static (predicate is always the same)? policy_type => DBMS_RLS.shared_context_sensitive --olicy type - overwrite static_policy if non-null ); END;
Secure synonym created above is now Org dependent synonym. Data from this synonym can be queried only when the context for the corresponding Organization is set.
Oracle provides Mo_global.set_policy_context to set org context. Check below snippets.
SQL> select * From secure_synonym; no rows selected -- No rows returned as security policy is not set SQL> exec mo_global.set_policy_context('S',83); PL/SQL procedure successfully completed. SQL> select * From secure_synonym; ORG_ID DESCRIPTION ---------- ---------------------------- 83 China OU -- only date for OU 83 is shown SQL> exec mo_global.set_policy_context('S',82); PL/SQL procedure successfully completed. SQL> select * From secure_synonym; ORG_ID DESCRIPTION ---------- ----------------------------- 82 India OU -- only date for OU 82 is shown
The effect of this policy is that, whenever you access SECURE_SYNONYM, Oracle RLS will dynamically append WHERE CLAUSE similar to below.
mo_glob_org_access_tmp table is populate with org_id when you run mo_global.set_policy_context API.
SELECT * FROM secure_synonym_demo WHERE EXISTS (SELECT 1 FROM mo_glob_org_access_tmp oa WHERE oa.organization_id = org_id)