Virtual Private database concept

Virtual private database concept is used to support many users with access to the same base tables with restricted row viewing. This is also known as row level security. A table may have 5000 records, but even, if user select all the rows from the table but he can only see those he’s approved without using any filter. This isn’t just applying to SELECT statement but all INSERT, UPDATE and DELETE statement against the table.

Virtual Private database Example

          Create table employee

CREATE TABLE EMPLOYEE

(

  EMPNO    NUMBER(4),

  ENAME    VARCHAR2(10),

  JOB      VARCHAR2(9),

  HIREDATE DATE,

  SAL      NUMBER(7,2),

  COMM     NUMBER(7,2),

  DEPTNO   NUMBER(2)

       ) 

           Populate table with valid set of data

INSERT INTO employee (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)

VALUES (7369, 'SMITH', 'CLERK',  to_date('17-12-1980', 'dd-mm-yyyy'), 800.00, null, 20);

INSERT INTO employee (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)

VALUES (7499, 'ALLEN', 'SALESMAN', to_date('20-02-1981', 'dd-mm-yyyy'), 1600.00, 300.00, 30);

INSERT INTO employee (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)

VALUES (7521, 'WARD', 'SALESMAN', to_date('22-02-1981', 'dd-mm-yyyy'), 1250.00, 500.00, 30);

INSERT INTO employee (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)

VALUES (7566, 'JONES', 'MANAGER', to_date('02-04-1981', 'dd-mm-yyyy'), 2975.00, null, 20);

INSERT INTO employee (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)

VALUES (7654, 'MARTIN', 'SALESMAN', to_date('28-09-1981', 'dd-mm-yyyy'), 1250.00, 1400.00, 30);

INSERT INTO employee (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)

VALUES (7698, 'BLAKE', 'MANAGER', to_date('01-05-1981', 'dd-mm-yyyy'), 2850.00, null, 30);

INSERT INTO employee (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)

VALUES (7782, 'CLARK', 'MANAGER', to_date('09-06-1981', 'dd-mm-yyyy'), 2450.00, null, 10);

INSERT INTO employee (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)

VALUES (7788, 'SCOTT', 'ANALYST', to_date('19-04-1987', 'dd-mm-yyyy'), 3000.00, null, 20);

INSERT INTO employee (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)

VALUES (7839, 'KING', 'PRESIDENT', to_date('17-11-1981', 'dd-mm-yyyy'), 5000.00, null, 10);

INSERT INTO employee (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)

VALUES (7844, 'TURNER', 'SALESMAN', to_date('08-09-1981', 'dd-mm-yyyy'), 1500.00, 0.00, 30);

INSERT INTO employee (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)

VALUES (7876, 'ADAMS', 'CLERK', to_date('23-05-1987', 'dd-mm-yyyy'), 1100.00, null, 20);

INSERT INTO employee (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)

VALUES (7900, 'JAMES', 'CLERK', to_date('03-12-1981', 'dd-mm-yyyy'), 950.00, null, 30);

INSERT INTO employee (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)

VALUES (7902, 'FORD', 'ANALYST', to_date('03-12-1981', 'dd-mm-yyyy'), 3000.00, null, 20);

INSERT INTO employee (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)

VALUES (7934, 'MILLER', 'CLERK', to_date('23-01-1982', 'dd-mm-yyyy'), 1300.00, null, 10);

           Create virtual private database package

CREATE OR REPLACE PACKAGE PKG_VPDC

AS

  L_DEPTNO EMPLOYEE.DEPTNO%TYPE;

  PROCEDURE SET_DEPTNO(V_DEPTNO EMPLOYEE.DEPTNO%TYPE);

  FUNCTION PREDICATE (OBJ_SCHEMA VARCHAR2, OBJ_NAME VARCHAR2)  RETURN VARCHAR2;

END PKG_VPDC;

/

CREATE OR REPLACE PACKAGE BODY PKG_VPDC AS

 

  PROCEDURE SET_DEPTNO(V_DEPTNO EMPLOYEE.DEPTNO%TYPE) IS

  BEGIN

    L_DEPTNO := V_DEPTNO;

  END SET_DEPTNO;

  FUNCTION PREDICATE  (OBJ_SCHEMA VARCHAR2, OBJ_NAME VARCHAR2)

  RETURN VARCHAR2 IS

  BEGIN

    RETURN 'DEPTNO = ' || L_DEPTNO;

  END PREDICATE;

 

END PKG_VPDC;

    /

           Create DEPTUSER table and populate it with valid set of data   

CREATE TABLE DEPTUSER

(

  DEPTNO         NUMBER,

  USERNAME              VARCHAR2 (10)

);

INSERT INTO DEPTUSER VALUES ( 10, 'USER10');

INSERT INTO DEPTUSER VALUES ( 20, 'USER20');

INSERT INTO DEPTUSER VALUES ( 30, 'USER30');

          Create database logon trigger

CREATE OR REPLACE TRIGGER TRG_VPDC

  AFTER LOGON ON DATABASE

DECLARE

  L_DEPTNO DEPTUSER.DEPTNO%TYPE;

BEGIN

  SELECT DEPTNO INTO L_DEPTNO

  FROM DEPTUSER WHERE UPPER(USERNAME) = USER;

  PKG_VPDC.SET_DEPTNO(L_DEPTNO);

END;

      /

          Add policy

BEGIN

  DBMS_RLS.ADD_POLICY(USER,'EMPLOYEE', 'DEPARTMENT SECRET', USER,

                       'PKG_VPDC.PREDICATE','SELECT, UPDATE, DELETE');

END;

           Add database Users

CREATE USER USER10 IDENTIFIED BY USER10;

CREATE USER USER20 IDENTIFIED BY USER20;

CREATE USER USER30 IDENTIFIED BY USER30;

GRANT CREATE SESSION TO USER10;

GRANT CREATE SESSION TO USER20;

GRANT CREATE SESSION TO USER30;

GRANT ALL ON EMPLOYEE TO USER10;

GRANT ALL ON EMPLOYEE TO USER20;

GRANT ALL ON EMPLOYEE TO USER30;

GRANT EXECUTE ON PKG_VPDC TO PUBLIC

CREATE PUBLIC SYNONYM EMPLOYEE FOR EMPLOYEE;

          Output

SQL> CONN USER10/USER10

CONNECTED TO ORACLE DATABASE 10G ENTERPRISE EDITION RELEASE 10.2.0.1.0

CONNECTED AS USER10

SQL> SELECT * FROM EMPLOYEE;

EMPNO ENAME      JOB       HIREDATE          SAL      COMM DEPTNO

----- ---------- --------- ----------- --------- --------- ------

 7782 CLARK      MANAGER   6/9/1981      2450.00               10

 7839 KING       PRESIDENT 11/17/1981    5000.00               10

 7934 MILLER     CLERK     1/23/1982     1300.00               10

SQL> CONN USRE20/USER20

NOT LOGGED ON

SQL> CONN USER20/USER20

CONNECTED TO ORACLE DATABASE 10G ENTERPRISE EDITION RELEASE 10.2.0.1.0

CONNECTED AS USER20

SQL> SELECT * FROM EMPLOYEE;

EMPNO ENAME      JOB       HIREDATE          SAL      COMM DEPTNO

----- ---------- --------- ----------- --------- --------- ------

 7369 SMITH      CLERK     12/17/1980     800.00               20

 7566 JONES      MANAGER   4/2/1981      2975.00               20

 7788 SCOTT      ANALYST   4/19/1987     3000.00               20

 7876 ADAMS      CLERK     5/23/1987     1100.00               20

 7902 FORD       ANALYST   12/3/1981     3000.00               20

SQL> CONN USER30/USER30

CONNECTED TO ORACLE DATABASE 10G ENTERPRISE EDITION RELEASE 10.2.0.1.0

CONNECTED AS USER30

SQL> SELECT * FROM EMPLOYEE;

EMPNO ENAME      JOB       HIREDATE          SAL      COMM DEPTNO

----- ---------- --------- ----------- --------- --------- ------

 7499 ALLEN      SALESMAN  2/20/1981     1600.00    300.00     30

 7521 WARD       SALESMAN  2/22/1981     1250.00    500.00     30

 7654 MARTIN     SALESMAN  9/28/1981     1250.00   1400.00     30

 7698 BLAKE      MANAGER   5/1/1981      2850.00               30

 7844 TURNER     SALESMAN  9/8/1981      1500.00      0.00     30

 7900 JAMES      CLERK     12/3/1981      950.00               30

6 ROWS SELECTED

        SQL>

Bulk Binding

How Bulk Binding improve performance ?

Bulk binding was introduced in oracle 8i. it enables PL/SQL program to fetch many rows from a cursor in a single call. it also allows DML operation. Whenever PL/SQL block calls SQL statement , control transfers to the SQL engine to fetch the data from the database, and transfer fetched data and control to PL/SQL block for further processing.

Bulk binding decresses context switch between PL/SQL engine and SQL Engine that makes code execution faster.Context switch occurs every time when PL/SQL calls SQL engine to parse, execute or fetch from cursor. Since context switching uses CPU time, by reducing the number of context switching between engines incresses performance of the the program.

How does Bulk Binding Work with SELECT or FETCH Statement?

Lets take an example of cursor which is used to process individual rows returned by SQL query. This will require lot of context switching when we use cursor for loop. When we use bulk binding in our program it will fetch all the data from the database and store it in a collection data type (VARRAY, Nested table, %ROWTYPE, RECORD TYPE, CURSOR TYPE). Once data stored in the collection variable can be manuplate at OS level. This eleminates lot of context switching beween the engine when large data is concerned.

DECLARE

   TYPE typ_all_objects IS TABLE OF all_objects%ROWTYPE;

   tab_all_objects typ_all_objects := typ_all_objects();

   l_start number;

BEGIN

   l_start := dbms_utility.get_time;

   FOR cur_rec IN (SELECT * FROM all_objects) LOOP

     tab_all_objects.extend;

     tab_all_objects(tab_all_objects.last) := cur_rec;

   END LOOP;

   DBMS_OUTPUT.PUT_LINE('Simple Cursor took '|| (dbms_utility.get_time - l_start) ||

                                                     ' to process '||   tab_all_objects.count||' rows; ');

   l_start := dbms_utility.get_time;

   SELECT *

     BULK COLLECT INTO

     tab_all_objects

     FROM all_objects;

   DBMS_OUTPUT.PUT_LINE('Bulk Collect took '||   (dbms_utility.get_time - l_start) ||

                                                          ' to process '||    tab_all_objects.count||' rows; ');

END;

/

Simple Cursor took 112 to process 40786 rows;

Bulk Collect took 97 to process 40786 rows;

Why sometime it is necessary to use LIMIT clause in Bulk Binding?

Collection uses system memeory, so doing bulk collection from a large query cause a performance problem. Sometime it is necessary to use smaller chucks without grabbing all server memory. LIMIT clause helps to process data in smaller chucks.

DECLARE

    TYPE typ_all_objects IS TABLE OF all_objects%ROWTYPE;

    tab_all_objects typ_all_objects;

   CURSOR bulk_collect_limit is

          SELECT * FROM all_objects;

BEGIN

   OPEN bulk_collect_limit;

   LOOP

       FETCH bulk_collect_limit BULK COLLECT INTO tab_all_objects LIMIT 20000 ;

       EXIT WHEN tab_all_objects.COUNT = 0;

        -- processing codes

       dbms_output.put_line(lpad(tab_all_objects.COUNT,5,' ') || ' Rows');

  END LOOP;

 CLOSE bulk_collect_limit;

END;

/

20000 Rows

20000 Rows

786 Rows