Some thoughts towards fashioning SQL roles for Derby. Feel free to pick up on this, I won't be able to address it more for a while. -Dag

SQL model

The SQL 2003 standard (IEC/ISO 9075-2-2003; if you don't have access to the standard, this link contains an almost identical draft: describes the SQL role concept in section 4.34.3, p 113. A SQL role can be thought of as a named set of privileges. A role may be granted to a user or to other roles via the <grant role statement>. Thus, roles can be said to contain other roles, recursively. The user-role and role-privilege relations are many-many, so a user can have many roles and a role many have many users and similarly for role-privilege. The effective privileges at an instant is the union of privileges granted the the current user, to PUBLIC and the set of privileges granted to the current role and its contained roles.

Like privileges, granting a role can take an option to allow it to be passed on. For roles it is called WITH ADMIN OPTION.

At any one time, a SQL session may have exactly one current user and/or¹ a current role, which are stacked when e.g. invoking a procedure which executes with definer's rights.

¹As far as I can tell, since SQL allows creation of schema objects owned by a role (cf AUTHORIZATION <schema authorization identifier>), the current user may end up empty when executing with definer rights a procedure owned by a role.. We may want to subset this functionality for Derby for now.

Note that when comparing SQL roles with the proposed RBAC standard [1], SQL roles falls short in this respect: SQL allows only one role to be active at any one time.

Some vendors (e.g. Sybase, Oracle) have extended the standard SQL role concept to allow several concurrently active roles whereas, for example, Informix does not allow this [2]. Requiring max one active role yields less flexible role management.

Sybase and Oracle also allow users to connect with a default role active, whereas the standard says that when a SQL session is initiated, only a <SQL session user identifier> is active.

Sybase also supports another RBAC feature not found in the standard SQL model, so-called Separation of Duty. This includes static and dynamic exclusion: two roles are in static exclusion if a user cannot be granted both roles, and two roles are in dynamic exclusion if a user cannot activate or enable both roles at the same time. Since SQL only allows only one active role, it could be said to support dynamic exclusion.

SQL layers roles in two features: T331 Basic roles and T332 Extended roles. Basic roles has no CURRENT_ROLE built-in.

A priori, we probably want to stick with the SQL model for Derby in spite of the weaknesses indicated.

[1] Sandhu, Ravi, Ferraiolo, David and Kuhn, Richard: The NIST Model for Role-Based Access Control: Towards a Unified Standard

[2] R. Chandramouli, R. Sandhu, Role Based Access Control Features in Commercial Database Management Systems, 21st National Information Systems Security Conference, October 6-9, 1998, Crystal City, Virginia.

Language elements

Section and page references are to IEC/ISO 9075-2-2003.

<role definition> ("CREATE ROLE")

12.4, page 741

<drop role statement> ("DROP ROLE")

12.6, page 744

<grant role statement> (extends current <grant statement>)

12.5, page 742

<revoke role statement> (extends current <revoke statement>)

12.7, page 745

<set role statement>

18.3, page 909

CURRENT_ROLE (extends current <general value specification>)

6.4, page 176

Roles name space

In SQL, role names live in the same name space as user names. Since Derby has pluggable user authentication (there is no user catalog in db), how can we know at role definition (CREATE ROLE ..) time that a proposed role name is allowed (unique)?

We have to come up with a workable solution for this, I can see two possibilities, are there others? In both cases, if a user attempt to connect with a user id already in use as a role, the connection should be rejected.

1) disallowing role creation for an identifier if a schema is created for that user or privileges are granted to that identifier. A slight problem here is that a user schema is only created on demand, so a user may experience suddenly being locked out by a role creation..

2) check against a user mapping stored in Derby catalog cf. discussion in DERBY-866 Built-in Derby User Management. At least, if when, such a mapping is introduced, the role creation logic could take advantage of it. But can roles be implemented without addressing DERBY-866 first?

Some other issues

* Who should be able to create roles? Only database owner? All? SQL says the privilege required to create roles is implementation defined (section 12.4, Access rule 1). A database level system privilege?

* A priori, database level system privileges should be assignable to roles as well as schema object privileges. But it seems these have collapsed into database owner powers for now (DERBY-2264). System level system privileges are not covered here, any any role concept at this level would be unconnected to SQL roles (?).

* Implementation: does the current privileges catalogs and checking model impact how we should go about building roles into Derby?