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>
