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

That much pleases me. :-)

> 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?

As I understand Firebird's implementation of roles at this time
it effectively only ever has one role active at a time. (I for
one would have liked Firebird to implement roles in a more
windows-group-like arrangement, but that does not seem to be
on the agenda anywhere.)

If the current implementation is to continue then there can be
only one EXECUTION_ROLE active at a time. Internally I imagine
FB would maintain a stack, pushing on the role of the current
procedure (or most recent with an explicit role) and popping
them off as the procedure exits. However the trigger-code need
only ever look for EXECUTION_ROLE - the one that is active to
the executing environment right now.

> 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:-(

If multiple roles are considered active (by which I presume
you mean that access levels are merged so that the current
environment would have the combination of all active roles)
then I think that IsRoleActive() would be useful. Certainly
it could achieve my desired result.

(Not IsRoleGranted, as that would imply just knowing that
the grant exists, not whether it has been actived)

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

I had looked at something like this before, but it gets messy
for some of the same reasons you describe above. It is a large
modular system in which procedures call procedures so we end
up needing the table of flags to deal with things in a stack
or reference counter arrangement. When you consider the flag
table maintenance has to go around BEGIN ... WHEN ANY ... END
blocks - it gets quite horrible to maintain and starts to make
even the most simple procedures quite ugly.

But you are right, it would be more secure than what I do now.
I probably should revisit this idea to see if some specific
(simpler) situations could benefit from your simpler example,
and avoid the more complex situations for now. Thanks.


As you said at the top, a role for procedures would be a useful
feature in it's own right. If that is implemented I suggest
that it would also be useful for SQL code to know what role is
(or roles are) active at a given point in the code.

--
Geoff Worboys
Telesis Computing