Using SQL roles

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

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:

create role reader; create role updater; create role taskLeaderA; create role taskLeaderB; create role projectLeader; grant reader to updater; grant updater to taskLeaderA; grant updater to taskLeaderB; grant taskLeaderA to projectLeader; grant taskLeaderB to projectLeader;

The roles would then have the following containment relationships:

reader | v updater / \ taskLeaderA taskLeaderB \ / projectLeader

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

CREATE ROLE statement
SET ROLE statement
DROP ROLE statement
GRANT statement
REVOKE statement
CURRENT_ROLE function
SYSCS_DIAG.CONTAINED_ROLES table function
SYSROLES system table