How to add security policy using dbms_rls.add_policy

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.

ORG ID Description
82 India OU
83 China OU
CREATE SYNONYM secure_synonym FOR secure_synonym_demo;

Data from the synonym before attaching security policy.

SQL> select * From secure_synonym;
---------- ------------------------------
82 India OU
83 China OU


Step 2:- attach Org dependent security policy to the Synonym

Below procedure attached Org dependent security policy to the Synonym.

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

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;
---------- ----------------------------
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;
---------- -----------------------------
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.

FROM secure_synonym_demo
FROM mo_glob_org_access_tmp oa   WHERE oa.organization_id = org_id)




Related Post

Leave a Reply

Your email address will not be published. Required fields are marked *