> For what I understand, you want something like
> execute procedure <name> using <rolename>;

> Did I get the idea?

Well certainly an explicit "using <rolename>" could solve
the problem too (but then I may have to write a new procedure
just so I can call the desired procedure in context).

What I actually had in mind something more along the lines of:
GRANT <rolename> TO PROCEDURE <procedure-name>;
whereupon the procedure would automatically execute within
the context of that <rolename>. The second piece of the puzzle
would be a context variable EXECUTION_ROLE that could be used
by SQL code to tell the actual executing role/context from the
users connection role given.

This would solve two problems at once. It would allow triggers
to detect changes that take place in the presence of raised
privileges AND it would save having to work through a procedure
and work out which pieces I have to write explicit grants for
(grant update to that table because some users may not have
update access, grant select to this other table because not all
users can read that one, grant execute on that sub-procedure
because a few users dont see that directly etc etc).

The idea would be that the engine would take care of the role
scope automatically; opening the new scope at start of execute
and closing and end. If I have to use my own variable (such
as with package private variables) then to achieve the same
result I would have to enclose the desired code in BEGIN...
WHEN ANY ... END block (no big deal but neater without).

--
Geoff Worboys
Telesis Computing