Granting Privileges to Roles and the WITH GRANT OPTION Keywords

A role is a named set of table access privileges that the CREATE ROLE statement can register in the database, and the GRANT statement can populate with access privileges on local database objects. The GRANT statement can also assign a role to a group of users, to PUBLIC, or to other roles. For a non-default role, the access privileges of the role are enabled when a user who holds the role specifies that role in the SET ROLE statement to activate that role for the current session. The REVOKE statement can deprive a role of specific privileges, and can exclude specific users, the PUBLIC group, or other roles from a previously granted role.

The owner of database objects that a user creates while a role is set is the user, or some other user specified in the DDL statement; a role cannot be the owner of an object.

In version 8.50 of Extended Parallel Server and in earlier releases, the WITH GRANT OPTION keywords of the GRANT statement are valid only when access privileges are granted to individual users or to PUBLIC. These keywords enable the grantee to grant the same privileges to other users or to roles. XPS 8.50 issues an error if WITH GRANT OPTION is specified when a grantee specified after the TO keyword is a role, rather than a user or PUBLIC.

This restriction is inconvenient in some contexts, such as large databases where a large number of users require access privileges on hundreds of tables to access those tables, or to create views on those tables. The management of access privileges on tables and views can be simplified if users who receive access privileges through roles can grant the necessary privileges to other users.

With the introduction of this feature, however, the GRANT statement also supports the WITH GRANT OPTION keywords when access privileges are granted to roles. An access privilege that has been granted to a role with this option can subsequently be granted to another user (or to a list of users) by users for whom the specified role is currently the active role. A role that a user has previously been granted to a user becomes the active role when the user executes a valid SET ROLE statement that specifies that role, or when the user establishes a connection to a database where that role is the default role.

This feature does not change the following current restrictions on granting access privileges to roles:

• You cannot (directly or indirectly) grant a role to yourself.
• This feature only applies to Table-level and Routine-level access privileges. It does not change the current prohibition of any role as the grantee of Database-level privileges (Connect, DBA, and Resource).

In XPS 8.51, the following syntax of the GRANT statement supports this feature:

          .-,---------.                 .-,----.
          V           |                 V      |
>>-GRANT----privilege-+--ON--table--TO----role-+---------------->
 
>--WITH GRANT OPTION--+-------------+--------------------------><
                      '-AS--grantor-'
 

Here the input variables have the following definitions:

• privilege specifies a table-level or column-level access privilege that is granted to one or more existing roles.
• table specifies the table, view, or subset of columns within a table or view in the local database on which the privilege is valid.
• role specifies a role that is registered in the local database, to which the privileges are granted with the right to grant the privileges to other users, to other roles, or to PUBLIC.
• table specifies the user who can revoke the privilege. If the AS grantor clause is omitted, the user who grants the privileges can revoke them.

The AS grantor clause is valid only for users who hold the DBA privilege on the database.

See the IBM Informix Guide to SQL: Syntax for the complete syntax of the GRANT statement.

The syntax of the REVOKE statement is not changed by this feature, but the extension of the WITH GRANT OPTION feature to the privileges of roles affects how the privileges of roles are granted and revoked:

• When a user grants privileges WITH GRANT OPTION to a role, the user is the grantor and the role is the grantee,
• A user who sets the role as the active role can use those privileges to access database objects, but only the role (not the user) can grant the privileges to other users.
• A user who holds both the DBA privilege and the role can use the AS grantor clause of the GRANT statement to grant the privileges to other users on behalf of the role as grantor.
• Users who are granted the role, but do not hold the DBA privilege, cannot use the AS clause to grant to others any privileges of the role that they do not also hold individually or as members of the PUBLIC group.
• Views created from tables on which access privileges were granted to roles WITH GRANT OPTION inherit the access privileges of their base tables.
• A DBA can use the AS clause of the REVOKE statement to specify the role as the revoker of privileges that were received WITH GRANT OPTION from the role.
• A user who granted privileges to a role WITH GRANT OPTION can subsequently revoke those privileges.