Create Role

Valid in: SQL, ESQL

The Create Role statement defines one or more role identifiers and their associated password. Role identifiers are used to associate privileges with applications. After the role identifiers are created and privileges have been granted to them, use them with the connect statement to associate those privileges with the session. For a discussion of role identifiers, see the Database Administrator Guide. For information about the privileges granted to role identifiers, see Grant (role) .

Only users who have been granted access to a role can use a role. The creator of a role is automatically granted access to that role.

Syntax

The Create Role statement has the following format:

[EXEC SQL] CREATE ROLE role_id {, role_id}
[WITH with_option {,with_option}]

with_option = NOPASSWORD | PASSWORD = 'role_password'

| PASSWORD = X'encrypted_role_password'

               | EXTERNAL_PASSWORD

               | NO PRIVILEGES | PRIVILEGES = ( priv {,priv} )

               | NOSECURITY_AUDIT | SECURITY_AUDIT

• role_id

  Specifies the user name to be created. Must be a valid object name that is unique among all role, group, and user identifier names in the installation.

  If an invalid role identifier is specified, the DBMS Server returns an error but processes all valid role identifiers.

  Role identifiers are stored in the iirole catalog of the iidbdb. For details about system catalogs, see the Database Administrator Guide.

• role_password

  Allows a user to change his or her own password. In addition, users with the MAINTAIN_USERS privilege can change or remove any password. If role_password contains uppercase or special characters, enclose it in single quotes. Any blanks in the password are removed when the password is stored.

  Limits: Role_password can be no longer than 24 characters

  Default: NOPASSWORD if the password clause is omitted.

  To remove the password associated with role_id, specify NOPASSWORD.

  To allow a user's password to be passed to an external authentication server for authentication, specify EXTERNAL_PASSWORD.

• priv

  Specifies one of the following subject privileges, which applies to the user regardless of the database to which the user is connected:

  • CREATEDB

    Allows the user to create databases.

  • TRACE

    Allows the user to use tracing and debugging features.

  • SECURITY

    Allows the user to perform security-related functions (such as creating and dropping users).

  • OPERATOR

    Allows the user to perform database backups and other database maintenance operations.

  • MAINTAIN_LOCATIONS

    Allows the user to create and change the characteristics of database and file locations.

  • AUDITOR

    Allows the user to register or remove audit logs and to query audit logs.

  • MAINTAIN_AUDIT

    Allows the user to change the alter user security audit and alter profile security audit privileges. Also allows the user to enable, disable, or alter security audit.

  • MAINTAIN_USERS

    Allows the user to perform various user-related functions, such as creating, altering or dropping users, profiles and group and roles, and to grant or revoke database and installation resource controls.

    Default: NOPRIVILEGES if the privileges clause is omitted.

• NOSECURITY_AUDIT | SECURITY_AUDIT

  Specifies audit options, as follows:

  • NOSECURITY_AUDIT

    Uses the security_audit level for the user using the role.

  • SECURITY_AUDIT

    Audits all activity for anyone who uses the role, regardless of any SECURITY_AUDIT level set for an individual user.

    Default: NOSECURITY_AUDIT if the security_audit clause is omitted.

Embedded Usage

The with clause in an embedded Create Role statement can be specified using a host string variable (with :hostvar).

Permissions

You must have the maintain_users privilege and be connected to the iidbdb database. Additional privileges are required to perform certain operations, as summarized in the table below:

Locking

The Create Role statement locks pages in the iirole catalog of the iidbdb. This can cause sessions attempting to connect to the server to suspend until the statement is completed.

Related Statements

Alter Role

Drop Role

Grant (role)

Examples: Create Role

The following are Create Role statement examples:

1. Create a role identifier and password for the inventory application of a bookstore.

   create role bks_onhand with password = 'hgwells';

2. Create a role identifier with no password for the daily sales application of the bookstore.

   create role dly_sales with nopassword;

3. Create a role identifier and its password for the new employee application of the bookstore.

   create role new_emp with password = 'good luck';

4. In an application, create a role identifier and its password for an accounts payable application.

   exec sql create role acct_pay with
   password = piper;

5. Create a role with a password and additional privileges.

   create role sysop
   with password = 'sysoppwd',
   privileges = (operator, createdb, maintain_locations);

6. Create a role with external password verification.

   create role sysop
   with external_password;