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