Virtual Private Database

From: Donna <dhoffman0917_at_yahoo.com>
Date: 21 Sep 2002 20:34:04 -0700
Message-ID: <79808a91.0209211934.13b48c4a@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 Sat Sep 21 2002 - 22:34:04 CDT