>>-CREATE ROLE--+-role---+-------------------------------------><
                '-'role'-'
 

CREATE ROLE declares a new role and registers it in the system catalog. A role can associate a set of authorization identifiers with a set of access privileges on database objects. The system catalog maintains information about the roles (and their corresponding privileges) that are granted to users or to other roles.

Only the database administrator (DBA) can use CREATE ROLE to create a new role. The DBA can assign the privileges required for some work task to a role, such as engineer, and then use the GRANT statement to assign that role to specific users, instead of granting that set of privileges to each user individually.

The role name is an authorization identifier. It cannot be a user name that is known to the database server or to the operating system of the database server. The role name cannot already be listed in the username column of the sysusers system catalog table, nor in the grantor or grantee columns of the systabauth, syscolauth, sysfragauth, sysprocauth, or sysroleauth system catalog tables.

In Dynamic Server, the role name also cannot match the name of any user or role that is already listed in the grantor or grantee columns of the sysxtdtypeauth system catalog table, nor any built-in role, such as EXTEND or NONE.

After a role is created, the DBA can use the GRANT statement to assign the role to PUBLIC, to users, or to other roles, and to grant specific privileges to the role. (A role cannot, however, hold database-level privileges.) After a role is granted successfully to a user or to PUBLIC, the user must use the SET ROLE statement to enable the role. Only then can the user exercise the privileges of the role.

To create the role engineer, for example, enter the following statement:

CREATE ROLE engineer

To grant access privileges to the role engineer, the DBA can issue GRANT statements that include engineer in the list of grantees:

GRANT USAGE ON LANGUAGE SPL TO engineer

To assign the role engineer to user kaycee, the DBA could issue this statement:

GRANT ROLE engineer TO kaycee

To activate the role engineer, user kaycee must issue the following statement:

SET ROLE engineer 

If this SET ROLE statement is successful, user kaycee acquires whatever privileges have been granted to the role engineer, in addition to any other privileges that kaycee already holds as an individual or as PUBLIC.

A user can be granted several roles, but no more than one non-default role, as specified by SET ROLE, can be enabled for any user at a given time.

An exception to requiring SET ROLE to explicitly enable a role is any default role that the DBA specifies in the GRANT DEFAULT ROLE role TO user statement. If that statement succeeds, the default role is automatically enabled when user connects to the database. Any role can be a default role. (Similarly, users to whom the Dynamic Server DBSA grants the EXTEND role need not execute SET ROLE before they can create and drop external routines and shared libraries.)

CREATE ROLE, when used with the GRANT and SET ROLE statements, enables a DBA to create one set of privileges for a role and then grant the role to many users, instead of granting the same set of privileges individually to many users.

With the GRANT DEFAULT ROLE and SET ROLE DEFAULT statements, default roles enable a DBA to assign privileges to a role that is activated automatically when any user who holds that default role connects to the database. This feature is useful when an application performs operations that require specific access privileges, but the application does not include SET ROLE statements.

The REVOKE statement can cancel access privileges of a role, remove users from a role, or cancel the default status of a role for one or more users. A role exists until either the DBA or a user to whom the role was granted with the WITH GRANT OPTION keywords uses the DROP ROLE statement to drop the role.

Related statements: DROP ROLE , GRANT, REVOKE, and SET ROLE

For a discussion of how to use roles, see the IBM Informix Database Design and Implementation Guide.