Re: Fine Grain Security and select... for update

Annoying defect.

I believe the correct solution is that the dbms_lob ought to respect the policy - however a workaround might be to wrap the dbms_lob package in a "tableX_lob" package which gets called with the primary key and the lob locator. "tableX_lob" package should be declared AUTHID CURRENT_USER (ugh!) so that it executes the fgac policy. The package then needs to update a column in the table to itself based on the primary key, and if this succeeds call dbms_lob - if no row is updated, then the call to dbms_lob should not be made.

Clunky - but it might work. (And, of course, dbms_lob should not be available for direct calls).

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______September 24/26, November 12/14

____USA__________November 7/9 (MI), 19/21 (TX)

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html








Richard Foote wrote in message ...


>Hi Thomas, Vladimir and all,

>

>Interesting !!

>

>I suspect the reasoning for this is that you are updating the LOB via a

>procedure. The procedure is run under the privileges of the owner (in this

>case SYS) and SYS is not affected by FGC.  Hence you can update anything

you


>can select.

>

>So it does kinda make sense but I do agree it's a hole. The only way I can

>see it being plugged is, hummmm, tricky, not sure !! Maybe having the

policy


>set for select as well although this may not always be appropriate. Having

>the dbms.lob procedures with current_user authid might make sense but then

>that opens up other issues.

>

>However, having an implicit update capability just because you can perform

a


>select sounds decidedly suz.

>

>Let's keep it really quiet ...

>

>Richard