vpd implementation question

From: Kumar Madduri <ksmadduri_at_gmail.com>
Date: Mon, 6 Apr 2009 17:56:27 -0700
Message-ID: <a2b1e7610904061756id73bfb3vc07ceaad275e65e6_at_mail.gmail.com>

Hi
I am trying to implement vpd using a slightly modified version of the example given here
http://oraclue.com/2009/02/26/virtual-on-virtual-vpd-on-virtual-column/

My example is like this.

CREATE TABLE employees_km (
  id NUMBER,
  first_name VARCHAR2(10),
  last_name VARCHAR2(10),

  bonus      NUMBER(9,2),
  extra1       NUMBER(3),
  extra2       NUMBER(3),
  bonus1     number(10),
     CONSTRAINT employees_pk PRIMARY KEY (id)

) ;

INSERT INTO employees_KM (id, first_name, last_name, bonus, extra1, extra2) VALUES (1, 'MARKO', 'JANKO', 100, 5, 10); INSERT INTO employees_KM (id, first_name, last_name, bonus, extra1, extra2) VALUES (2, 'JOE', 'JANKO', 200, 10, 20); COMMIT; create role view_virtual not identified;

  CREATE ROLE VIEW_VIRTUAL1 NOT IDENTIFIED; CREATE OR REPLACE FUNCTION secure_virtual_km (oowner IN VARCHAR2, ojname IN VARCHAR2)
RETURN VARCHAR2 AS
 BEGIN
  IF dbms_session.is_role_enabled('VIEW_VIRTUAL') then    return '';

       ELSE
    return '1=0';
  END IF;
END secure_virtual_km;
/

CREATE OR REPLACE FUNCTION secure_virtual_km1 (oowner IN VARCHAR2, ojname IN VARCHAR2)
RETURN VARCHAR2 AS
 BEGIN
  IF dbms_session.is_role_enabled('VIEW_VIRTUAL1') then    return '';

        ELSE
    return '1=0';
  END IF;
END secure_virtual_km1;
/

BEGIN

  DBMS_RLS.ADD_POLICY (object_schema         => 'apps',
                       object_name           => 'employees_km',
                       policy_name           => 'policy1',
                       function_schema       => 'apps',
                       policy_function       => 'secure_virtual_km',
                       sec_relevant_cols     => 'first_name',
                       sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);

END;
/

BEGIN

  DBMS_RLS.ADD_POLICY (object_schema         => 'apps',
                       object_name           => 'employees_km',
                       policy_name           => 'policy2',
                       function_schema       => 'apps',
                       policy_function       => 'secure_virtual_km1',
                       sec_relevant_cols     => 'last_name',
                       sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);

END;
/

From sqlprompt, I enable one role at a time and try to view the output and I get unexpected results. When I enable the view_virtual role, I am supposed to mask first_name but last_name gets masked and when I enable view_virtual1 role then I am supposed to see first_name but last_name should be masked but it happens the other way.

The idea behind doing this is the following. The data is divided in to sensitive and prohibited. All users requesting sql access will be assigned to a role which has least privileges (as determined by the vpd policy above) and they would not have access to sensitive and prohibitive data.
Some small set of users can have access to sensitive data (and a role is defined that will be assigned to these users). Even smaller set of uses have access to prohibitive data (again a role is defined for this users).
I want to check what role is enabled for a user and accordingly display the data (by appyling the vpd policy). I think when multiple polices are defined on the same table but different columns the where clause is getting 'AND'ed but even if I keep it simple and drop the second policy, I still did not get texpected results.
Any ideas why this is happening?

Thank you
kumar

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Apr 06 2009 - 19:56:27 CDT