Miladin Modrakovic's Blog: Oraclue

Quick test to prove that  Virtual Private Database ( VPD ) works on virtual column.

Here is demo.First create test table and load it ( so lazy that I found this on google too )

/** Create table and insert some rows **/
DROP TABLE employees;
CREATE TABLE employees (
  id          NUMBER,
  first_name  VARCHAR2(10),
  last_name   VARCHAR2(10),
  bonus      NUMBER(9,2),
  extra1       NUMBER(3),
  extra2       NUMBER(3),
  bonus1     AS (ROUND(bonus*(1+extra1/100),2)),
  bonus2     NUMBER GENERATED ALWAYS AS (ROUND(bonus*(1+extra2/100),2)) VIRTUAL,
  CONSTRAINT employees_pk PRIMARY KEY (id)
);

INSERT INTO employees (id, first_name, last_name, bonus, extra1, extra2)
VALUES (1, ‘MARKO’, ‘JANKO’, 100, 5, 10);

INSERT INTO employees (id, first_name, last_name, bonus, extra1, extra2)
VALUES (2, ‘JOE’, ‘JANKO’, 200, 10, 20);

COMMIT;

 /** VPD role **/

drop role view_virtual;
create role view_virtual not identified;

Here is my function that will enable users witt VIEW_VIRTUAL role to view data on column BONUS2.

 /** Create function **/

CREATE OR REPLACE FUNCTION secure_virtual (oowner IN VARCHAR2, ojname IN VARCHAR2)
RETURN VARCHAR2 AS
 
BEGIN
  IF dbms_session.is_role_enabled(‘VIEW_VIRTUAL’)  then
   return ”;
  
  ELSE 
    return ’1=0′; — don’t show column
  END IF;
END secure_virtual;
/

and finally create policy 

/** Create policy **/
BEGIN
  DBMS_RLS.ADD_POLICY (object_schema         => ‘mmodrakovic’,
                       object_name           => ‘employees’,
                       policy_name           => ‘secure_virtual’,
                       function_schema       => ‘mmodrakovic’,
                       policy_function       => ‘secure_virtual’,
                       sec_relevant_cols     => ‘bonus2′,
                       sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);
END;
/

Now when I select from table I cannot see value for the column.So function and policy  works..

SQL> select * from employees;

        ID FIRST_NAME LAST_NAME       BONUS     EXTRA1     EXTRA2     BONUS1     BONUS2
———- ———- ———- ———- ———- ———- ———- ———-
         1 MARKO      JANKO             100          5         10        105
         2 JOE        JANKO             200         10         20        220
Now let’s grant role :
SQL> grant view_virtual to mmodrakovic;

Grant succeeded.

SQL> set role all;

Role set.

SQL> select * from employees;

        ID FIRST_NAME LAST_NAME       BONUS     EXTRA1     EXTRA2     BONUS1     BONUS2
              ———- ———- ———- ———- ———- ———- ———- ———-
         1  MARKO                JANKO                           100            5           10          105              110
         2   JOE                      JANKO                             200         10         20        220          240

and virtual works on virtual…