Oracle Apps Custom Table with ORG Access Control policy (RLS Policy)

Step-1: Create Table & Insert data

create table xxcus.xx_tmp_all(id number, name varchar2(100), org_id number);

Replace XXCUS is with your custom schema name. Also make sure to have ORG_ID column and table name suffix as _ALL.

1	insert into xxcus.xx_tmp_all values (1,'a',201);

insert into xxcus.xx_tmp_all values (2,’b’,204);

COMMIT;

Step-2: Create synonyms

1	CREATE OR REPLACE SYNONYM apps.xx_tmp_all FOR xxcus.xx_tmp_all;

CREATE OR REPLACE SYNONYM apps.xx_tmp FOR xxcus.xx_tmp_all;

Step-3: Create Policy

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

begin dbms_rls.add_policy ( object_schema => 'APPS', object_name => 'XX_TMP', policy_name => 'ORG_SEC', function_schema => 'APPS', policy_function => 'MO_GLOBAL.ORG_SECURITY', statement_types => 'INSERT, UPDATE, DELETE, SELECT', update_check => TRUE, enable => TRUE, static_policy => FALSE, policy_type => NULL, long_predicate => FALSE, sec_relevant_cols => NULL, sec_relevant_cols_opt => NULL ); end;

Step-4: Query Data

1	select * from apps.xx_tmp; -- No data retrieved

EXEC MO_GLOBAL.SET_POLICY_CONTEXT(‘S’,201);

select * from apps.xx_tmp; — 201 record retrieved

EXEC MO_GLOBAL.SET_POLICY_CONTEXT(‘S’,204);

select * from apps.xx_tmp; — 204 record retrieved

We can drop by policy with the below command.

1 2 3

begin dbms_rls.drop_policy ('APPS','XX_TEMP','ORG_SEC'); end;

Additional Information

1	select * from dba_policies; -- should give all existing policies

Check session org access

1 2 3 4 5 6 7 8 9

DECLARE RET VARCHAR2(100); org NUMBER; BEGIN RET := MO_GLOBAL.CHECK_ACCESS (201); -- change org for which you need to check access org := mo_global.get_current_org_id; DBMS_OUTPUT.PUT_LINE (RET); dbms_output.put_line (org); END;