FGAC where clause question

Hi!

I have set up a security policy so when you query for the company table
(vmo_company) you will only see companies which the current user is allowed
to see. The connection of which companies a user is allowed to see is stored in a table vmo_cust_user which basically it holds companykey (idcus), userid pairs .

Result := 'idcus in (select idcus from vmo_cust_user where iduser=''' || myUser || ''')';

This works as I want but I am a bit afraid of the subselect that may return many records. From SQL+ I can write a query like this where I can also add a criteria in the subselect that that vmo_company.idcus=vmo_cust_user.idcus

Select * from vmo_company where idcus in
(select idcus from vmo_cust_user where iduser='some_user' and
vmo_copmany.idcus=vmo_cust_user.idcus)

I tried to do this in my policy restriction as well Result := 'idcus in (select idcus from vmo_cust_user where iduser=''' || myUser || ''' and vmo_company.idcus=vmo_cust_user.idcus )';

This however generates an error ORA-28112 Could not run policy function
(message translated from swedish).

Questions:
1 Is it possible to set criterias in the subselect that uses columns in the outer query? Any alias that must be used? 2 I guess it is not possible to join the table directly without having to do an IN clause? I have not found any chance to add tables in the original from clause. What I mean is that if a query like "Select some_fields from vmo_company" that an application sends in can be transformed to Select some_fields from vmo_company, vmo_cust_user where vmo_company.idcus=vmo_cust_user.idcus and vmo_cust_user.iduse='some_user' which actually means adding vmo_cust_user to the from clause as well. 3. If you have used VPD what is your experience (both good and bad)? Anything particular to have in mind?

Windows platform
Oracle 10.2 Enterprise Edition

Regards
/Hans Received on Fri Sep 22 2006 - 01:13:19 CDT