Oracle Virtual Private Database Tutorial

SET VERIFY OFF

def db_name = ORCL

def dba_user = system
def dba_pass = manager

def ts_name = users

-- (1) create admin user

-- 　login with dba
@@connect &dba_user/&dba_pass@&db_name

-- 　create vpd user
CREATE USER vpd
IDENTIFIED BY vpd
DEFAULT TABLESPACE &ts_name
TEMPORARY TABLESPACE temp;

-- 　grant privileges
GRANT CONNECT, RESOURCE, DBA TO vpd;


-- (2) create table synonym sequence trigger

-- 　connect with vpd user
@@connect vpd/vpd@&db_name

-- 　create table
CREATE TABLE vpd.addrbook
(id      NUMBER(8) PRIMARY KEY,
 owner   VARCHAR2(8) NOT NULL,
 name    VARCHAR2(32) NOT NULL,
 mail    VARCHAR2(32),
 phone   VARCHAR2(32),
 company VARCHAR2(32),
 title   VARCHAR2(32),
 address VARCHAR2(128),
 zip     VARCHAR2(16));

-- 　create public synonym for the table
CREATE PUBLIC SYNONYM addrbook FOR vpd.addrbook;

-- 　grant access privileges of the table to public
GRANT DELETE, INSERT, SELECT, UPDATE ON vpd.addrbook TO public;

-- 　create sequence for ID column
CREATE SEQUENCE vpd.addrbook_id_seq;

-- 　create trigger fill id and owner column
CREATE OR REPLACE TRIGGER vpd.addrbook_insert
BEFORE INSERT ON vpd.addrbook
FOR EACH ROW
BEGIN
  SELECT vpd.addrbook_id_seq.NEXTVAL, SYS_CONTEXT('USERENV', 'session_user')
  INTO   :NEW.id, :NEW.owner
  FROM   dual;
END;
/


-- (3) configure Virtual Private Database

-- 　setup policy
-- 　　VPD user has full control, otherwise user can only access the records he added.
CREATE OR REPLACE PACKAGE vpd.addrbook_security IS
  FUNCTION owner_sec(d1 VARCHAR2, d2 VARCHAR2) RETURN VARCHAR2;
END addrbook_security;
/
CREATE OR REPLACE PACKAGE BODY vpd.addrbook_security IS
  FUNCTION owner_sec(d1 VARCHAR2, d2 VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    IF SYS_CONTEXT('USERENV', 'session_user') = 'VPD' THEN
      RETURN NULL;
    ELSE
      RETURN 'owner = ''' || sys_context('USERENV', 'session_user') || '''';
    END IF;
  END owner_sec;
END addrbook_security;
/

-- 　bind the security policy to table
EXECUTE DBMS_RLS.ADD_POLICY('vpd', 'addrbook', 'addrbook_policy', 'vpd', 'addrbook_security.owner_sec', 'SELECT, INSERT, UPDATE, DELETE')

SET VERIFY OFF

-- database name
def db_name = ORCL

def dba_user = system
def dba_pass = manager

-- ###########################################################################
@@connect &dba_user/&dba_pass@&db_name

-- (1) create test user and grant privilege
GRANT CREATE SESSION TO adams IDENTIFIED BY wood;
GRANT CREATE SESSION TO clark IDENTIFIED BY cloth;


-- (2) add data for adams
@@connect adams/wood@&db_name
INSERT INTO addrbook (name, company, title, phone, mail, zip, address)
VALUES('KING', 'JOCKSPORTS', 'PRESIDENT', '598-6609', 'king@jocksports.com', '96711', '345 VIEWRIDGE BELMONT CA');
INSERT INTO addrbook (name, company, title, phone, mail, zip, address)
VALUES('CLARK', 'EVERY MOUNTAIN', 'MANAGER', '996-2323','clark@everymountain.com', '93301', '574 SURRY RD. CUREPTINO CA');
INSERT INTO addrbook (name, company, title, phone, mail, zip, address)
VALUES('BLAKE', 'TKB SPORT SHOP', 'MANAGER', '368-1223', 'blake@tkb.com', '94061', '490 BOLI RD. REDWOOD CITY CA');
INSERT INTO addrbook (name, company, title, phone, mail, zip, address)
VALUES('MARTIN', 'VOLLYRITE', 'SALESMAN', '644-3341', 'martin@vollyrite.com', '95133', '9722 HAMILTON BURLINGAME CA');
commit;


-- (3) add data for clark
@@connect clark/cloth@&db_name
INSERT INTO addrbook (name, company, title, phone, mail, zip, address)
VALUES('ALLEN', 'K + T SPORTS', 'SALESMAN', '376-9966','allen@kt-sports.com', '91003', '3476 EL PASEO SANTA CLARA CA');
INSERT INTO addrbook (name, company, title, phone, mail, zip, address)
VALUES('JAMES', 'SHAPE UP', 'CLERK', '364-9777','james@shape-up.com', '94301', '908 SEQUOIA PALO ALTO CA');
INSERT INTO addrbook (name, company, title, phone, mail, zip, address)
VALUES('FORD', 'WOMANS SPORTS', 'ANALYST', '967-4398','ford@womans-sports.com', '93301', 'VALCO VILLAGE SUNNYVALE CA');
INSERT INTO addrbook (name, company, title, phone, mail, zip, address)
VALUES('MILLER', 'JUST TENNIS', 'CLERK', '677-3341', 'miller@justtennis.com', '97544', 'HILLVIEW MALL BURLINGAME CA');
commit;

-- test it
-- there are 8 rows in the table, but clark only can see 4 rows which he added.

select count(*) from addrbook;

COUNT(*)
-----
4

-- vpd can see all the 8 rows

@@connect vpd/vpd

select count(*) from addrbook;

COUNT(*)
-----
8