Prerequisite
- Required privileges:
GRANT SELECT_CATALOG_ROLE TO PORTALFDW;
GRANT EXECUTE ON DBMS_RLS TO PORTALFDW;
GRANT CREATE FUNCTION TO PORTALFDW;
GRANT CREATE PACKAGE TO PORTALFDW;
Note: To grant the above privileges you have to connect with system or sys user.
Assumption
- All the actions are being performed in same schema where the base objects of all reports are stored.
Step 1: Create a table named USER_SECURITY with two columns SOL_ID and LOGIN_ID as
CREATE TABLE USER_SECURITY (SOL_ID VARCHAR2 (20), LOGIN_ID VARCHAR2 (30));
Note: SOL_ID will be mapped to all base tables of all the reports while LOGIN_ID will store portal login ID (Portal User or SSO User as it is created on portal). Select this SOL_ID column from the view created in step 3 to map SOL_ID column on each base table of each report. Here SOL_ID is the lowest level of the hierarchy (i.e. ZONE -> REGION -> BRANCH here branch represents SOL_ID). For any project we must take a column of the lowest level of the hierarchy into the table USER_SECURITY and into all base tables of all reports.
The table USER_SECURITY must be populated in a manner that the user is mapped to each Branch he has access to. (e.g. if a SSO user belongs to 10 branches, then 10 rows must be populated for this SSO user. I.e. each row will have same SSO user name with different branch name or code for these 10 rows). Refer following example:
Table USER_SECURITY will populate like this. This table contains test date not live data.
| SOL_ID | LOGIN_ID |
| 0001 | ZONE1_ REGION1_ BRANCH1_USER111 |
| 0002 | ZONE1_ REGION1_ BRANCH2_USER111 |
| 0003 | ZONE1_ REGION2_ BRANCH3_USER111 |
| 0004 | ZONE1_ REGION2_ BRANCH4_USER111 |
| 0001 | ZONE1_ REGION1_ BRANCH1_USER222 |
| 0002 | ZONE1_ REGION1_ BRANCH2_USER222 |
| 0003 | ZONE1_ REGION2_ BRANCH3_USER222 |
| 0004 | ZONE1_ REGION2_ BRANCH4_USER222 |
| 0001 | REGION1_ BRANCH1_USER11 |
| 0002 | REGION1_ BRANCH2_USER11 |
| 0001 | REGION1_ BRANCH1_USER22 |
| 0002 | REGION1_ BRANCH2_USER22 |
| 0003 | REGION2_ BRANCH3_USER11 |
| 0004 | REGION2_ BRANCH4_USER11 |
| 0003 | REGION2_ BRANCH3_USER22 |
| 0004 | REGION2_ BRANCH4_USER22 |
| 0001 | BRANCH1_USER1 |
| 0001 | BRANCH1_USER2 |
| 0002 | BRANCH2_USER1 |
| 0002 | BRANCH2_USER2 |
| 0003 | BRANCH3_USER1 |
| 0003 | BRANCH3_USER2 |
| 0004 | BRANCH4_USER1 |
| 0004 | BRANCH4_USER2 |
Step 2: Populate some sample data or actual data into the table USER_SECURITY, either manually or automatically (using query) from source tables if possible.
Step 3: Create a view on the table USER_SECURITY as
CREATE OR REPLACE VIEW VW_USER_SECURITY AS SELECT *FROM USER_SECURITY;
Step 4: Create a package as
/* Package Specification */
CREATE OR REPLACE PACKAGE VPD_SECURITY AS
FUNCTION COMPANY_SECURITY (D1 VARCHAR2, D2 VARCHAR2)
RETURN VARCHAR2;
END;
/
/* Package Body */
CREATE OR REPLACE PACKAGE BODY VPD_SECURITY AS
/* Limits select statements based on company_security */
FUNCTION COMPANY_SECURITY (D1 VARCHAR2, D2 VARCHAR2)
RETURN VARCHAR2
IS
D_PREDICATE VARCHAR2 (2000);
BEGIN
D_PREDICATE:= 'SOL_ID IN (SELECT SOL_ID FROM USER_SECURITY WHERE LOGIN_ID = SYS_CONTEXT ('||'''USERENV'||''','''||'CLIENT_IDENTIFIER'''||'))';
RETURN D_PREDICATE;
END COMPANY_SECURITY;
END VPD_SECURITY;
/
Step 5: Create a policy as
BEGIN
DBMS_RLS.ADD_POLICY ('PORTALFDW', 'VW_USER_SECURITY',
'SECURITY_POLICY', ' PORTALFDW ', 'VPD_SECURITY.COMPANY_SECURITY', 'SELECT');
END;
Note: The ADD_POLICY procedure has parameters name in sequence like (Object Schema, Object Name, Policy Name, Function Schema, Policy Function, Statement Type)
Step 6: Create a function as
CREATE OR REPLACE FUNCTION SET_CLIENT_ID (USERNAME VARCHAR2) RETURN NUMBER IS
BEGIN
DBMS_SESSION.SET_IDENTIFIER (USERNAME);
RETURN 1;
EXCEPTION WHEN OTHERS THEN
RETURN 0;
END;
/
Step 7: Register the function SET_CLIENT_ID into the Oracle Discoverer Administrator and then put a blank sheet in each workbook in Oracle Discoverer Desktop, which must call this registered function.
· Create a new sheet in the workbook
· And click finish
· In new sheet go to ToolsàCalculations
· Click New:
o Name: - User
o Calculation: - SET_CLIENT_ID (USER)
o Click OK
· OK
-------------------------------------------------------------------------------------------------------------
Guidelines
-------------------------------------------------------------------------------------------------------------
If you face any problem to load/insert/update/delete/truncate date from the table on which policy is implemented then you have to drop the policy and after load/insert/update/delete/truncate
Operation you have to recreate the policy.
To drop the policy execute following script:
BEGIN
DBMS_RLS.DROP_POLICY ('PORTALFDW', 'VW_USER_SECURITY',
'SECURITY_POLICY');
END;
/
Note: The DROP_POLICY procedure has parameters name in sequence like (Object Schema, Object Name, Policy Name).
To test VPD through back end (i.e. TOAD, SQL*PLUS) follow the steps:
SELECT SET_CLIENT_ID ('BRANCH1_USER1') FROM DUAL;
SELECT *FROM ANY_BASE_TABLE
WHERE SOL_ID IN (SELECT SOL_ID FROM VW_USER_SECURITY);
ANY_BASE_TABLE is a test table that you have to create for testing. This table must contain SOL_ID column and at least few values of the SOL_ID column must be common in both a table ANY_BASE_TABLE and a view VW_USER_SECURITY. You can refer any other existing table in place of ANY_BASE_TABLE.
