Monday, June 2, 2008

VPD Implementation Process for Oracle10gAS R2 for Discoverer Reports and Portal

Prerequisite

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

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

Tuesday, May 13, 2008

Function to return Nested table

Function to return Nested table

CREATE OR REPLACE FUNCTION func_get_emp

RETURN emp_nest_tab

AS

p_sal emp.sal%TYPE;

old_dept emp.deptno%TYPE := 0;

new_dept emp.deptno%TYPE;

iteration NUMBER (2) := 0;

flag NUMBER := 0;

emp_nest emp_nest_tab;

new_sal NUMBER := 0;

CURSOR cur_emp

IS

SELECT empno

FROM emp

ORDER BY deptno;

empn emp.empno%TYPE;

--emp_ret emp_nest_tab;

BEGIN

OPEN cur_emp;

LOOP

FETCH cur_emp INTO empn;

EXIT WHEN cur_emp%NOTFOUND;

--------------procedure----------------

SELECT sal

INTO p_sal

FROM emp

WHERE empno = empn;

SELECT deptno

INTO new_dept

FROM emp

WHERE empno = empn;

----------- Code to initialize emp_nest-----------

IF flag = 0

THEN

emp_nest := emp_nest_tab ();

flag := 1;

END IF;

----------- Code to initialize emp_nest-----------

emp_nest.EXTEND;

IF old_dept = new_dept

THEN

new_sal := new_sal + p_sal;

emp_nest (emp_nest.COUNT) :=

emp_tab_type (new_dept, p_sal, new_sal, iteration);

iteration := iteration + 1;

ELSE

new_sal := p_sal;

emp_nest (emp_nest.COUNT) :=

emp_tab_type (new_dept, p_sal, new_sal, iteration);

old_dept := new_dept;

iteration := iteration + 1;

END IF;

--------------procedure----------------

END LOOP;

CLOSE cur_emp;

/*FOR cur IN (SELECT *

FROM TABLE (CAST (emp_nest AS emp_nest_tab)))

LOOP

DBMS_OUTPUT.put_line ( CHR (9)

|| cur.ini

|| CHR (9)

|| cur.deptno

|| CHR (9)

|| cur.sal

|| CHR (10)

);

END LOOP;*/

RETURN emp_nest;

END func_get_emp;

/

SELECT *

FROM TABLE (CAST (func_get_emp AS emp_nest_tab))

DEPTNO

SAL

TOT_SAL

INI

10

2,450.00

2,450.00

0

10

5,000.00

7,450.00

1

10

1,300.00

8,750.00

2

20

800.00

800.00

3

20

1,100.00

1,900.00

4

20

3,000.00

4,900.00

5

20

3,000.00

7,900.00

6

20

2,975.00

10,875.00

7

30

1,600.00

1,600.00

8

30

2,850.00

4,450.00

9

30

1,250.00

5,700.00

10

30

950.00

6,650.00

11

30

1,500.00

8,150.00

12

30

1,250.00

9,400.00

13