example of using oracle virtual private database, including column masking

vpd.sql

-- CREATE TABLES

DROP TABLE reviews;
DROP TABLE salary;
DROP TABLE employees;

CREATE TABLE employees (
  emp_name VARCHAR(30),
  phone VARCHAR(30),
  position VARCHAR(30),
  department VARCHAR(30),
  PRIMARY KEY (emp_name)
);

CREATE TABLE salary (
  emp_name VARCHAR(30),
  salary INT,
  PRIMARY KEY (emp_name),
  CONSTRAINT fk_emp_name FOREIGN KEY (emp_name) REFERENCES employees(emp_name)
);

CREATE TABLE reviews (
  reviewer VARCHAR(30),
  reviewee VARCHAR(30),
  rating INT,
  PRIMARY KEY (reviewer, reviewee),
  CONSTRAINT fk_reviewer FOREIGN KEY (reviewer) REFERENCES employees(emp_name),
  CONSTRAINT fk_reviewee FOREIGN KEY (reviewee) REFERENCES employees(emp_name)
);

INSERT INTO employees (emp_name, phone, position, department) VALUES
('alexk', 81298374, 'manager', 'finance');
INSERT INTO employees (emp_name, phone, position, department) VALUES
('chelseac', 95532753, 'employee', 'finance');
INSERT INTO employees (emp_name, phone, position, department) VALUES
('dongy', 64440942, 'manager', 'hr');
INSERT INTO employees (emp_name, phone, position, department) VALUES
('joycey', 93875312, 'employee', 'hr');
INSERT INTO employees (emp_name, phone, position, department) VALUES
('alicec', 80923513, 'manager', 'engineering');
INSERT INTO employees (emp_name, phone, position, department) VALUES
('bobn', 94189732, 'employee', 'engineering');
INSERT INTO employees (emp_name, phone, position, department) VALUES
('carolt', 64440219, 'employee', 'finance');
INSERT INTO employees (emp_name, phone, position, department) VALUES
('evew', 64448291, 'employee', 'hr');
INSERT INTO employees (emp_name, phone, position, department) VALUES
('felixp', 85982173, 'employee', 'hr');
INSERT INTO employees (emp_name, phone, position, department) VALUES
('gregd', 91736459, 'employee', 'engineering');

INSERT INTO salary (emp_name, salary) VALUES ('alexk', 50000);
INSERT INTO salary (emp_name, salary) VALUES ('chelseac', 50000);
INSERT INTO salary (emp_name, salary) VALUES ('dongy', 60000);
INSERT INTO salary (emp_name, salary) VALUES ('joycey', 30000);
INSERT INTO salary (emp_name, salary) VALUES ('alicec', 55000);
INSERT INTO salary (emp_name, salary) VALUES ('bobn', 60000);
INSERT INTO salary (emp_name, salary) VALUES ('carolt', 45000);
INSERT INTO salary (emp_name, salary) VALUES ('evew', 45000);
INSERT INTO salary (emp_name, salary) VALUES ('felixp', 40000);
INSERT INTO salary (emp_name, salary) VALUES ('gregd', 80000);

INSERT INTO reviews (reviewer, reviewee, rating) VALUES ('alexk', 'chelseac', 10);
INSERT INTO reviews (reviewer, reviewee, rating) VALUES ('alexk', 'dongy', 8);
INSERT INTO reviews (reviewer, reviewee, rating) VALUES ('alexk', 'carolt', 2);
INSERT INTO reviews (reviewer, reviewee, rating) VALUES ('alexk', 'felixp', 5);
INSERT INTO reviews (reviewer, reviewee, rating) VALUES ('chelseac', 'alexk', 5);
INSERT INTO reviews (reviewer, reviewee, rating) VALUES ('chelseac', 'carolt', 1);
INSERT INTO reviews (reviewer, reviewee, rating) VALUES ('chelseac', 'dongy', 6);
INSERT INTO reviews (reviewer, reviewee, rating) VALUES ('dongy', 'joycey', 3);
INSERT INTO reviews (reviewer, reviewee, rating) VALUES ('dongy', 'evew', 9);
INSERT INTO reviews (reviewer, reviewee, rating) VALUES ('dongy', 'felixp', 4);
INSERT INTO reviews (reviewer, reviewee, rating) VALUES ('dongy', 'gregd', 2);
INSERT INTO reviews (reviewer, reviewee, rating) VALUES ('joycey', 'dongy', 6);
INSERT INTO reviews (reviewer, reviewee, rating) VALUES ('joycey', 'evew', 10);
INSERT INTO reviews (reviewer, reviewee, rating) VALUES ('joycey', 'felixp', 1);
INSERT INTO reviews (reviewer, reviewee, rating) VALUES ('alicec', 'bobn', 4);
INSERT INTO reviews (reviewer, reviewee, rating) VALUES ('alicec', 'gregd', 9);
INSERT INTO reviews (reviewer, reviewee, rating) VALUES ('bobn', 'alicec', 7);
INSERT INTO reviews (reviewer, reviewee, rating) VALUES ('bobn', 'gregd', 3);
INSERT INTO reviews (reviewer, reviewee, rating) VALUES ('carolt', 'alexk', '8');
INSERT INTO reviews (reviewer, reviewee, rating) VALUES ('carolt', 'chelseac', 9);
INSERT INTO reviews (reviewer, reviewee, rating) VALUES ('evew', 'dongy', 2);
INSERT INTO reviews (reviewer, reviewee, rating) VALUES ('evew', 'joycey', 6);
INSERT INTO reviews (reviewer, reviewee, rating) VALUES ('evew', 'felixp', 10);
INSERT INTO reviews (reviewer, reviewee, rating) VALUES ('felixp', 'dongy', 5);
INSERT INTO reviews (reviewer, reviewee, rating) VALUES ('felixp', 'joycey', 8);
INSERT INTO reviews (reviewer, reviewee, rating) VALUES ('felixp', 'evew', 3);
INSERT INTO reviews (reviewer, reviewee, rating) VALUES ('gregd', 'alicec', 7);
INSERT INTO reviews (reviewer, reviewee, rating) VALUES ('gregd', 'bobn', 2);

DROP USER alexk;
DROP USER chelseac;
DROP USER dongy;
DROP USER joycey;

CREATE USER alexk IDENTIFIED BY alexk0;
CREATE USER chelseac IDENTIFIED BY chelseac0;
CREATE USER dongy IDENTIFIED BY dongy0;
CREATE USER joycey IDENTIFIED BY joycey0;

GRANT CREATE SESSION TO alexk;
GRANT CREATE SESSION TO chelseac;
GRANT CREATE SESSION TO dongy;
GRANT CREATE SESSION TO joycey;

-- HELPERS

CREATE OR REPLACE FUNCTION get_dept (
  user_name IN VARCHAR2
) RETURN VARCHAR2 AS
user_dept VARCHAR(30);
BEGIN
  SELECT department INTO user_dept FROM employees WHERE emp_name = user_name;
  RETURN (user_dept);
END get_dept;
/

CREATE OR REPLACE FUNCTION get_position (
  user_name IN VARCHAR2
) RETURN VARCHAR2 AS
user_position VARCHAR(30);
BEGIN
  SELECT position INTO user_position FROM employees WHERE emp_name = user_name;
  RETURN (user_position);
END get_position;
/

CREATE OR REPLACE FUNCTION get_username RETURN VARCHAR2 AS
BEGIN
  RETURN LOWER(SYS_CONTEXT('USERENV', 'SESSION_USER'));
END get_username;
/

GRANT EXECUTE ON get_dept TO alexk;
GRANT EXECUTE ON get_dept TO chelseac;
GRANT EXECUTE ON get_dept TO dongy;
GRANT EXECUTE ON get_dept TO joycey;

GRANT EXECUTE ON get_position TO alexk;
GRANT EXECUTE ON get_position TO chelseac;
GRANT EXECUTE ON get_position TO dongy;
GRANT EXECUTE ON get_position TO joycey;

GRANT EXECUTE ON get_username TO alexk;
GRANT EXECUTE ON get_username TO chelseac;
GRANT EXECUTE ON get_username TO dongy;
GRANT EXECUTE ON get_username TO joycey;

-- POLICY 1

GRANT SELECT, UPDATE ON employees TO alexk;
GRANT SELECT, UPDATE ON employees TO chelseac;
GRANT SELECT, UPDATE ON employees TO dongy;
GRANT SELECT, UPDATE ON employees TO joycey;

CREATE OR REPLACE FUNCTION get_phone (
  v_schema IN VARCHAR2, v_obj IN VARCHAR2
) RETURN VARCHAR2 AS
BEGIN
  RETURN 'emp_name = ''' || get_username() || ''' OR phone LIKE ''6444%''';
END get_phone;
/

BEGIN DBMS_RLS.ADD_POLICY (
  object_schema =>   'system',
  object_name   =>   'employees',
  policy_name   =>   'get_info_policy',
  policy_function   => 'get_phone',
  policy_type => DBMS_RLS.CONTEXT_SENSITIVE,
  statement_types   => 'SELECT',
  sec_relevant_cols => 'phone',
  sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS
  );
END;
/

CREATE OR REPLACE FUNCTION upd_phone (
  v_schema IN VARCHAR2, v_obj IN VARCHAR2
) RETURN VARCHAR2 AS
BEGIN
  RETURN 'emp_name = ''' || get_username() || '''';
END upd_phone;
/

BEGIN DBMS_RLS.ADD_POLICY (
  object_schema =>   'system',
  object_name   =>   'employees',
  policy_name   =>   'upd_info_policy',
  policy_function   => 'upd_phone',
  policy_type => DBMS_RLS.CONTEXT_SENSITIVE,
  statement_types   => 'UPDATE',
  update_check => TRUE,
  sec_relevant_cols => 'phone'
  );
END;
/

GRANT EXECUTE ON get_phone TO alexk;
GRANT EXECUTE ON get_phone TO chelseac;
GRANT EXECUTE ON get_phone TO dongy;
GRANT EXECUTE ON get_phone TO joycey;

GRANT EXECUTE ON upd_phone TO alexk;
GRANT EXECUTE ON upd_phone TO chelseac;
GRANT EXECUTE ON upd_phone TO dongy;
GRANT EXECUTE ON upd_phone TO joycey;

-- POLICY 2

GRANT SELECT ON salary TO alexk;
GRANT SELECT ON salary TO chelseac;
GRANT SELECT, UPDATE on salary TO dongy;
GRANT SELECT, UPDATE on salary TO joycey;

CREATE OR REPLACE FUNCTION get_salary (
  v_schema IN VARCHAR2, v_obj IN VARCHAR2
) RETURN VARCHAR2 AS
user_name VARCHAR(30);
BEGIN
  user_name := get_username();
  IF(get_dept(user_name) = 'hr') THEN RETURN NULL;
  ELSE RETURN 'emp_name = ''' || user_name || '''';
  END IF;
END get_salary;
/

BEGIN DBMS_RLS.ADD_POLICY (
  object_schema =>   'system',
  object_name   =>   'salary',
  policy_name   =>   'get_sal_policy',
  policy_function   => 'get_salary',
  policy_type => DBMS_RLS.CONTEXT_SENSITIVE,
  statement_types   => 'SELECT'
  );
END;
/

CREATE OR REPLACE FUNCTION upd_salary (
  v_schema IN VARCHAR2, v_obj IN VARCHAR2
) RETURN VARCHAR2 AS
BEGIN
  RETURN 'emp_name != ''' || get_username() || '''';
END upd_salary;
/

BEGIN DBMS_RLS.ADD_POLICY (
  object_schema =>   'system',
  object_name   =>   'salary',
  policy_name   =>   'upd_sal_policy',
  policy_function   => 'upd_salary',
  policy_type => DBMS_RLS.CONTEXT_SENSITIVE,
  statement_types   => 'UPDATE'
  );
END;
/

CREATE OR REPLACE FUNCTION protect_managers (
  v_schema IN VARCHAR2, v_obj IN VARCHAR2
) RETURN VARCHAR2 AS
user_name VARCHAR(30);
BEGIN
  user_name := get_username();
  IF(get_position(user_name) = 'manager') THEN RETURN NULL;
  ELSE RETURN 'system.get_position(emp_name) = ''employee''';
  END IF;
END protect_managers;
/
BEGIN DBMS_RLS.ADD_POLICY (
  object_schema =>   'system',
  object_name   =>   'salary',
  policy_name   =>   'managers_sal_policy',
  policy_function   => 'protect_managers',
  policy_type => DBMS_RLS.CONTEXT_SENSITIVE,
  statement_types   => 'SELECT, UPDATE'
  );
END;
/

GRANT EXECUTE ON get_salary TO alexk;
GRANT EXECUTE ON get_salary TO chelseac;
GRANT EXECUTE ON get_salary TO dongy;
GRANT EXECUTE ON get_salary TO joycey;

GRANT EXECUTE ON upd_salary TO alexk;
GRANT EXECUTE ON upd_salary TO chelseac;
GRANT EXECUTE ON upd_salary TO dongy;
GRANT EXECUTE ON upd_salary TO joycey;

GRANT EXECUTE ON protect_managers TO alexk;
GRANT EXECUTE ON protect_managers TO chelseac;
GRANT EXECUTE ON protect_managers TO dongy;
GRANT EXECUTE ON protect_managers TO joycey;

-- POLICY 3

GRANT SELECT, INSERT, UPDATE, DELETE ON reviews TO alexk;
GRANT SELECT, INSERT, UPDATE, DELETE ON reviews TO chelseac;
GRANT SELECT, INSERT, UPDATE, DELETE ON reviews TO dongy;
GRANT SELECT, INSERT, UPDATE, DELETE ON reviews TO joycey;

CREATE OR REPLACE FUNCTION get_rating (
  v_schema IN VARCHAR2, v_obj IN VARCHAR2
) RETURN VARCHAR2 AS 
user_name VARCHAR(30);
condition VARCHAR(400);
BEGIN
  user_name := get_username();
  condition :='reviewee = ''' || user_name || ''' OR reviewer = ''' || user_name || '''';
  IF (get_position(user_name) = 'manager') THEN condition := condition || 'OR get_dept(reviewee) = ''' || get_dept(user_name) || ''' AND reviewee != ''' || user_name || '''';
  END IF;
  RETURN (condition);
END get_rating; 
/

CREATE OR REPLACE FUNCTION get_rating (
  v_schema IN VARCHAR2, v_obj IN VARCHAR2
) RETURN VARCHAR2 AS 
user_name VARCHAR(30);
condition VARCHAR(400);
BEGIN
  user_name := get_username();
  condition :='reviewee = ''' || user_name || ''' OR reviewer = ''' || user_name || '''';
  IF (get_position(user_name) = 'manager') THEN condition := condition || 'OR get_dept(reviewee) = ''' || get_dept(user_name) || ''' AND reviewee != ''' || user_name || '''';
  END IF;
  RETURN (condition);
END get_rating; 
/

BEGIN DBMS_RLS.ADD_POLICY (
    object_schema =>   'system',
    object_name   =>   'reviews',
    policy_name   =>   'get_rating_policy',
    policy_function   => 'get_rating',
    policy_type => DBMS_RLS.CONTEXT_SENSITIVE,
    statement_types   => 'SELECT',
    sec_relevant_cols => 'rating',
    sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS
  );
END;
/

CREATE OR REPLACE FUNCTION upd_rating (
  v_schema IN VARCHAR2, v_obj IN VARCHAR2
) RETURN VARCHAR2 AS 
BEGIN
  RETURN 'reviewer = ''' || get_username() || '''';
END upd_rating; 
/

BEGIN DBMS_RLS.ADD_POLICY (
  object_schema =>   'system',
  object_name   =>   'reviews',
  policy_name   =>   'upd_rating_policy',
  policy_function   => 'upd_rating',
  policy_type => DBMS_RLS.CONTEXT_SENSITIVE,
  statement_types   => 'INSERT, UPDATE, DELETE',
  update_check => TRUE
);
END;
/

CREATE OR REPLACE FUNCTION get_reviewer (
  v_schema IN VARCHAR2, v_obj IN VARCHAR2
) RETURN VARCHAR2 AS
user_name VARCHAR(30);
condition VARCHAR(400);
BEGIN
  user_name := get_username();
  condition := 'reviewer = ''' || user_name || '''';
  RETURN (condition);
END get_reviewer;
/

BEGIN DBMS_RLS.ADD_POLICY (
    object_schema =>   'system',
    object_name   =>   'reviews',
    policy_name   =>   'get_reviewer_policy',
    policy_function   => 'get_reviewer',
    policy_type => DBMS_RLS.CONTEXT_SENSITIVE,
    statement_types   => 'SELECT',
    sec_relevant_cols => 'reviewer',
  sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS
  );
END;
/

GRANT EXECUTE ON get_rating TO alexk;
GRANT EXECUTE ON get_rating TO chelseac;
GRANT EXECUTE ON get_rating TO dongy;
GRANT EXECUTE ON get_rating TO joycey;

GRANT EXECUTE ON upd_rating TO alexk;
GRANT EXECUTE ON upd_rating TO chelseac;
GRANT EXECUTE ON upd_rating TO dongy;
GRANT EXECUTE ON upd_rating TO joycey;

GRANT EXECUTE ON get_reviewer TO alexk;
GRANT EXECUTE ON get_reviewer TO chelseac;
GRANT EXECUTE ON get_reviewer TO dongy;
GRANT EXECUTE ON get_reviewer TO joycey;