Re: Accessing tables in another schema from a trigger

On 2 Apr 2004 00:20:58 -0800, bbcworldtour_at_hotmail.com (Bo Brunsgaard) wrote:

>Hi group,
>
>Purely a matter of curiosity:
>
>To save hassle and administration we generally only grant access
>through roles. However, in some rare cases we need a trigger to be
>able to access tables in another schema, which will only work if
>access to the tables is granted explicitly to the user, and not
>through a role (we're on 9.1 on Windows XP, if that matters).
>
>While it does mess up our setup of privileges a little bit, it is rare
>enough for me to live with (anyway, it's hardly worth dying over :-)
>I had another case of it yesterday and that rekindled my curiosity, so
>I wonder: does anyone here know the reasons and rationale behind this?
>
>Thanks in advance
>
>Bo Brunsgaard
>Vitus Bering, Denmark

The rationale behind this PL/SQL is compiled into p-code, and privilege checking is not done during runtime (which would be expensive), but during compilation time (so-called 'early binding' as opposed to 'late binding').
As roles are volatile, they can have changed since the procedure was compiled. Consequently, without explicitly using the authid current_user pragma, they are ignored.

BTW: in 9i your trigger can CALL a procedure. My guess is this could be a procedure with pragma authid current_user. I never tried it though, as I usually try to avoid a myriad of schema's (and most of our customers are treating their Oracle databases as if they were sqlserver anyway)

--
Sybrand Bakker, Senior Oracle DBA