Using SQL roles

When the SQL standard authorization mode is enabled, object owners
can use the SQL roles facility to administer privileges.

SQL roles are useful for administering privileges when a database has many
users. Roles provide a more powerful way to grant privileges to users' sessions
than to grant privileges to each user of the database, which easily becomes
tedious and error-prone when many users are involved. Roles do not in and of
themselves give better database security, but used correctly, they facilitate
better security. Only the
database owner
can create, grant, revoke, and drop roles. However, object owners can grant and
revoke privileges for those objects to and from roles, as well as to and from
individual users and PUBLIC (all users).

Note: Derby implements a
subset of SQL roles. The fact that only the database owner can create, grant,
revoke, and drop roles is an implementation restriction.

Creating and granting roles

Roles are available only when SQL authorization mode is enabled (that is,
when the property derby.database.sqlAuthorization is set to
TRUE).

Old databases must be (hard) upgraded to at least Release 10.5 before roles
can be used.

If SQL authorization mode is enabled, the database owner can use the
CREATE ROLE statement to create roles. The database owner can then use the GRANT
statement to grant a role to one or more users, to PUBLIC, or to another role.

A role A contains another role B if role B is granted to role A, or is
contained in a role C granted to role A. Privileges granted to a contained role
are inherited by the containing roles. So the set of privileges identified by
role A is the union of the privileges granted to role A and the privileges
granted to any contained roles of role A.

For example, suppose the database owner issued the following statements:

In this case, the projectLeader role contains all the other
roles and has all their privileges. If the database owner then revokes
updater from taskLeaderA,
projectLeader still contains that role through
taskLeaderB.

The SYSCS_DIAG.CONTAINED_ROLES diagnostic table function can be used to
determine the set of contained roles for a role.

Cycles are not permitted in role grants. That is, if a role contains another
role, you cannot grant the container role to the contained role. For example,
the following statement would not be permitted:

grant projectLeader to updater;

Setting roles

When a user first connects to
Derby, no role is set, and
the CURRENT_ROLE function returns null. During a session, the user can call the
SET ROLE statement to set the current role for that session. The role can be
any role that has been granted to the session's current user or to PUBLIC. To
unset the current role, call SET ROLE with an argument of NONE. At any time
during a session, there is always a current user, but there is a current role
only if SET ROLE has been called with an argument other than NONE. If a current
role is not set, the session has only the privileges granted to the user
directly or to PUBLIC.

For example, if the database owner created and granted the roles shown in the
previous session, a user would have to issue a SET ROLE statement to have them
take effect. Suppose a user issued the following statement:

SET ROLE taskLeaderA;

Assuming that the database owner had granted the taskLeaderA
role to the user, the user would be allowed to set the role as shown and would
have all the privileges granted to the taskLeaderA,
updater, and reader roles.

To retrieve the current role identifier in SQL, call the CURRENT_ROLE
function.

Within stored procedures and functions that contain SQL, the current role is
on the authorization stack. Initially, inside a nested connection, the current
role is set to that of the calling context. Upon return from the stored
procedure or function, the authorization stack is popped, so the current role
of the calling context is not affected by any setting of the role inside the
called procedure or function. If the stored procedure opens more than one
nested connection, these all share the same (stacked) current role state. Any
dynamic result set passed out of a stored procedure sees the current role of
the nested context.

Granting privileges to roles

Once a role has been created, both the database owner and the object owner
can grant privileges on tables and routines to that role. You can grant the same
privileges to roles that you can grant to users. Granting a privilege to a role
implicitly grants privileges to all roles that contain that role. For example,
if you grant delete privileges on a table to updater, every
user in the updater, taskLeaderA,
taskLeaderB, and projectLeader role will also
have delete privileges on that table, but users in the reader
role will not.

Revoking privileges from a role

Either the database owner or the object owner can revoke privileges from a
role.

When a privilege is revoked from a role A, that privilege is no longer held
by role A, unless A otherwise inherits that privilege from a contained role.

If a privilege to an object is revoked from role A, a session will lose that
privilege if it has a current role set to A or a role that contains A, unless
one or more of the following is true:

The privilege is granted directly to the current user

The privilege is granted to PUBLIC

The privilege is also granted to another role B in the current role's set of
contained roles

The session's current user is the database owner or the object owner

Revoking roles

The database owner can use the REVOKE statement to revoke a role from a user,
from PUBLIC, or from another role.

When a role is revoked from a user, that session can no longer keep that
role, nor can it take on that role in a SET ROLE statement, unless the role is
also granted to PUBLIC. If that role is the current role of an existing session,
the current privileges of the session lose any extra privileges obtained through
setting that role.

The default drop behavior is CASCADE. Therefore, all persistent objects
(constraints, views and triggers) that rely on that role are dropped. Although
there may be other ways of fulfilling that privilege at the time of the revoke,
any dependent objects are still dropped. This is an implementation limitation.
Any prepared statement that is potentially affected will be checked again on the
next execute. A result set that depends on a role will remain open even if that
role is revoked from a user.

When a role is revoked from a role, the default drop behavior is also
CASCADE. Suppose you revoke role A from role B. Revoking the role will have the
effect of revoking all additional applicable privileges obtained through A from
B. Roles that contain B will also lose those privileges, unless A is still
contained in some other role C granted to B, or the privileges come through some
other role. See
Creating and granting roles
for an example.

Dropping roles

Only the database owner can drop a role. To drop a role, use the DROP ROLE
statement.

Dropping a role effectively revokes all grants of this role to users and
other roles.

Further information

For details on the following statements, functions, and system table related
to roles, see the Derby Reference Manual.