vpd implementation question

  • From: Kumar Madduri <ksmadduri@xxxxxxxxx>
  • To: Oracle-L@xxxxxxxxxxxxx
  • Date: Mon, 6 Apr 2009 17:56:27 -0700

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
--
//www.freelists.org/webpage/oracle-l


Other related posts:

  • » vpd implementation question - Kumar Madduri