CREATE ROLE statement

Use the CREATE ROLE statement to declare and register a new role.

This statement is an extension to the ANSI/ISO standard for SQL.

Syntax

CREATE ROLE [IF NOT EXISTS] { role | 'role' }


Element	Description	Restrictions	Syntax
role	Name declared here for a role that the DBA creates	Must be unique among role and user names in the database. Maximum number of bytes is 32.	Owner name

Usage

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.

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

If you include the optional IF NOT EXISTS keywords, the database server takes no action (rather than sending an exception to the application) if a role of the specified name is already registered in the current database.

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 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 Informix® 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.

Important: The scope of a user-defined role (and of discretionary access privileges that the GRANT statement assigns to the role) is the current database. When the GRANT DEFAULT ROLE or SET ROLE statement activates a role, the role and its privileges take effect in the current database only. As a security precaution, discretionary access privileges that a user receives only from a role cannot provide access to tables outside the current database through a view or through a trigger action.