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

...

> What you have described in not so much a "Role" but rather a
> "Mode".

Is the fact that I can currently give a procedure access to a
table, that the user may not have access to, a special "mode"?

This is really all I am saying about a procedure having a role;
it is giving a procedure additional access without having to
write out all the details. A short-hand not unlike the
existing short-hand of granting a role to users.

I do not seem to be alone in thinking that, at least this part,
this would be useful.


The first hurdle is to work out whether it is feasible. Are
there major technical problems to it's implementation, anyone?

The second hurdle is to work out some suitable syntax...

This: GRANT <rolename> TO PROCEDURE <procedurename>;
would seem neat and familiar... but presents (at least) two
areas of potential confusion and inconsistency:

1. The syntax: GRANT <rolename> TO USER <username>;
syntax creates access only in potentia, unrealised until the
user explicitly logs on with that role, whereas I am suggesting
that a procedure would automatically get the access available
from the assigned role(s). Since procedures do not logon I do
not see this inconsistency as particularly confusing.

2. As Doug pointed out, this syntax has always allowed mulitple
roles to be granted to any target - whereas my original
suggestion was that a procedure would execute under a specific
role. Having given it some thought I see no reason to reject
the idea of a procedure being granted multiple roles...

. The main intention (of this part of the proposal) is to
make it easier to maintain custom security on a procedure.
Assuming it is technically feasible for a procedure to be
given the access of all granted roles at once I see no
reason not to let this happen.

. There is some minor inconsistency - that the procedure can
have multiple active roles while a user cannot. But given
the difference in object nature I do not see this as a
very significant area of confusion either.

. Even the second part of my suggestion (detect active roles
in SQL code) is easily adaptable via an "IsRoleActive"
function or a SYSTEM context variable listing active roles.


It may be possible to come up with alternative syntax but I
doubt if it is really necessary. (OTOH I would be content to
have this feature under almost any syntax :-)


The second part of the suggestion (making the name(s) of active
roles visible to other SQL code) is effectively a separate
feature, although I would suspect that it would make sense to
implement at the same time... that seems like the easy part of
the problem.

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

> This type of functionality is already supported via the
> RDB$Context (Transaction and Session) variable namespaces.

Yes these would be very useful... except, as I said earlier,
UDFs have no security (except the read-only nature of the
SYSTEM namespace).

--
Geoff Worboys
Telesis Computing