[Db-derby Wiki] Update of "SqlRoles" by DagWanvik

[Apache Wiki <wi...@apache.org>]

Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification.

The following page has been changed by DagWanvik:
http://wiki.apache.org/db-derby/SqlRoles

New page:
= SQL Roles for Derby =

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

[[TableOfContents]]

== 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:
http://www.wiscorp.com/sql_2003_standard.zip) 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 also 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''
http://csrc.nist.gov/rbac/sandhu-ferraiolo-kuhn-00.pdf

[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.
http://csrc.nist.gov/rbac/RBAC_DBMS_Comparison.pdf

== Language elements ==
Section and page references arfe 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?