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
Roles are available only when SQL authorization mode is enabled (that is,
when the property
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:
The roles would then have the following containment relationships:
In this case, the
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:
When a user first connects to
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:
Assuming that the database owner had granted the
To retrieve the current role identifier in SQL, call the CURRENT_ROLE function.
Within stored procedures and functions that contain SQL, the
current role depends on whether the routine executes with invoker's
rights or with definer's rights, as specified by the EXTERNAL SECURITY clause in
the CREATE FUNCTION or CREATE PROCEDURE statements in
the
Within routines that execute with invoker's rights, the following applies: initially, inside a nested connection, the current role is set to that of the calling context. So is the current user. Such routines may set any role granted to the invoker or to PUBLIC.
Within routines that execute with definer's rights, the following applies: initially, inside a nested connection, the current role is NULL, and the current user is that of the definer. Such routines may set any role granted to the definer or to PUBLIC.
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 (and user) state. Any dynamic result set passed out of a stored procedure sees the current role (or user) of the nested context.
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
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 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
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.
For details on the following statements, functions, and system table related
to roles, see the