Re: Virtual Private Database

Hi Donna,

You've missed a key step and there's a key misunderstanding in how you're trying to accomplish this.

Creating a context area and creating a policy function have nothing in common per se. You are attempting to use the procedure that populates your lot_sec_ctx as the policy procedure. This makes no sense and is the reason for your error.

You need to write a function that returns a varchar2 that is then used to generate the predicates for your policy. Note that this function could call your lot_sec_ctx to more efficiently determine the correct predicate.

For example, your policy function could look something like thus:

create or replace package app_security as

    function customer_check (p1 varchar2, p2 varchar2)     return varchar2;
end;

create or replace package body app_security as

    function customer_check (p1 varchar2, p2 varchar2)     return varchar2
is

   v_where_condition varchar2(2000);
begin

   v_where_condition:= 'hostname= sys_context(''lot_sec_ctx'', ''customer_id'')';

   return (v_where_condition);
end;
end;

and instead use app_security.customer_check when defining the policy:

EXECUTE DBMS_RLS.add_policy -
('TEST','LOT','LOT_POLICY','TEST','app_security.customer_check',- 'SELECT',TRUE,TRUE); Now this code has never had the pleasure of being executed so there could very well be the odd typo in there but hopefully you get the idea.

Good Luck

Richard

"Donna" <dhoffman0917_at_yahoo.com> wrote in message news:79808a91.0209211934.13b48c4a_at_posting.google.com...
> Hi,
> I am new to Virtual Private Database and could use some help. Can
> anyone experienced in setting up VPD kindly review this code? I
> simply want to select on a table that has a security policy to filter
> data by hostname=customer_id (meaning the user can only view the data
> in the table reflecting their 'hostname'). All procedures have been
> compiled successfully with no warnings and when tested the context is
> being set and the value of customer_id is being set with the
> customer_id_var.
> The problem is when I 'select * from lot', I get the errors below...
> Can any one help me? Thanks in advance.
>
> DH
>
> ORA-28112: failed to execute policy function
>
> And the trace file displays:
>
> Policy function execution error:
> Logon user : TEST
> Table or View : TEST.LOT
> Policy name : LOT_POLICY
> Policy function: TEST.LOT_SEC.SET_CUSTOMER_ID
> ORA-06550: line 1, column 59:
> PLS-00306: wrong number or types of arguments in call to
> 'SET_CUSTOMER_ID'
> ORA-06550: line 1, column 54:
>
>
> Table: Lot
>
> lot_id number,
> lot_create_date date,
> customer_id varchar2(12) (value equals user's hostname)
>
>
> Context: lot_sec_ctx (created successfully)
> create or replace context lot_sec_ctx using test.lot_sec;
>
> Procedures: (created successfully)
>
> create or replace package lot_sec is procedure
> set_customer_id;
> end lot_sec;
> /
>
> create or replace package body lot_sec as
> procedure set_customer_id
> is
> customer_id_var varchar2(12);
> begin
> customer_id_var := SYS_CONTEXT('USERENV','HOST');
>

dbms_session.set_context('LOT_SEC_CTX','customer_id',customer_id_var);
> end set_customer_id;
> end lot_sec;
> /
>
> EXECUTE DBMS_RLS.add_policy -
> ('TEST','LOT','LOT_POLICY','TEST','LOT_SEC.set_customer_id',-
> 'SELECT',TRUE,TRUE);
Received on Sun Sep 22 2002 - 07:58:15 CDT