Re: FGAC where clause question

On Fri, 22 Sep 2006, hans_at_nospam.com wrote:
> 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 is bad. You are putting literals in all of your rewritten queries.

Get the user from context: (Psuedo-code. Been working on Sybase for a bit, so don't have Oracle handy)

Result := 'idcus in (select idcus from vmo_cust_user where iduser=sys_context(userid,...);

> 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).

Get a trace and see what sql is executed and make sure it is what you expect and that you can run it in SQLPLUS.

> 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

I'd solve the first problem before I try to tackle the next. The first is much more problematic.

-- 
Galen Boyer