>
> The problem can also be described in this way: I can give a
> procedure/trigger more access than the user that executes it,
> but I cannot (as far as I know) write a trigger that chooses to
> do something different if it is the result of an update from a
> procedure that is using raised privileges.
>
> At the moment I fudge this via a hidden flags on the table;
> when the flag is turned on the trigger/procedure assumes it is
> in response to a system event. The problem with this is that
> it relies on security by obscurity. It works because the
> installations try very hard to prevent users from gaining
> access to the database via any but this authorised application,
> but it is not a good solution.

Geoff, the ability for a procedure to execute with predefned role without need
to assign to it a lot of rights to a lot of objects is worth doing on my
mind. But what about your particular problem (let triggers know that they are
called as a result of update from special procedure) using that role seems to
be not really related with a role assigned to procedure. If we need to have

>1 roles active, we should think about the fact that one such procedure may

call another one - and what should be done with EXECUTION_ROLE in that case?
If we start such changes we should have many roles, not 2. But this breaks an
idea EXECUTION_ROLE variable. Certainly, builtin function like
isRoleGranted() can be added instead, but this is not very beautiful
solution:-(

Your problem can be solved much easier.

create table specFlags(x int);
grant all on specFlags to <list of your procedures>;
grant select on specFlags to public;

In procedure:

insert into specFlags values(current_transaction);
<do-what-you-need>;
delete from specFlags where x = current_transaction;

In trigger:
if (exists (select * from specFlags where x = current_transaction)) then ....