Submitted by: Geoff Worboys (gworboys)

Votes: 1

This feature requested comes in two parts; first is the ability to use/grant roles with procedures to simplify security management and the second is to discover what roles are activated by the first part. It would be possible (and useful) to implement the first part without implementing the second part... although obviously I would like the second part too.

- - - Part 1 - - -

Currently it is possible (and very useful) to assign privileges to stored procedures, allowing a procedure to perform an action that the the user executing the procedure does not have privileges to perform. The biggest problem with this feature is that in long/complex procedures it can be time-consuming to identify all the specific privileges that need to be assigned.

It could simplify matters significantly if we could tell a procedure to execute using the privileges available to a particular role. This would also ensure that all procedures (that used the role) would reflect changes made to privileges of the role - for example making it much faster/easier to revoke access that may effect many procedures.

This feature request is suggesting that a procedure would automatically execute with (all) the privileges granted - it would not require some special execution syntax (although obviously that would be a possible variation).

I suggest that this syntax (to assign a role) may be appropriate:
GRANT <rolename> TO PROCEDURE <procedurename>;

It seems neat, familiar and consistent with existing grant syntax... but presents (at least) two areas of potential confusion and inconsistency:

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

B\. This syntax has always allowed mulitple roles to be granted to any target \- whereas this feature requested was originally that a procedure would execute under a specific \(single\) role\.\.\. but 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, allowing multiple roles to be granted is not inconsistent with that intention\.  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\.

It may be possible to come up with alternative syntax (I doubt if it is necessary), I would be happy to have this feature under almost any syntax :-)

- - - Part 2 - - -

Assuming the above can be implemented I would like to be able to test a context variable, or call some sort of built-in function, that tells me whether a given role is active. eg:
IsRoleActive( "Administrators" );

When the engine starts execution of a procedure with granted role(s) it would add that role (those roles) to the list of active roles. When procedure execution ends it's role(s) would be removed. Some sort of reference counting or stack system would have to be used to allow for procedures (with grants) calling other procedures (with grants), to ensure the active roles were properly maintained.

The idea behind this feature request is to allow trigger code similar to this:

IF (UPDATING AND IsRoleActive( "Administrators") = 0) THEN
BEGIN
IF (New."Locked" = 0 AND Old."Locked" = 1) THEN
EXCEPTION NOT_AUTH 'Only Administrators can unlock this record.';
END

I have code similar to the above that checks the CURRENT_ROLE context variable, but that is not able to tell me whether the update is coming from a procedure that uses raised privileges. The only other ways to implement such access-level specific trigger code is complicated, messy and error prone (see recent architect list discussion).