Granting Privileges to Roles Using the WITH GRANT OPTION Keywords

For Version 8.50 of Extended Parallel Server and earlier releases, the WITH GRANT OPTION keywords of the GRANT statement are valid only when access privileges are granted to individual users. With this feature of XPS 8.51, 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, providing greater flexibility in granting role-based access privileges on views.

This feature is implemented by the following changes to the user interface:

• The GRANT statement of SQL now supports the following new syntax:

  GRANT privileges ON table TO role WITH GRANT OPTION [AS grantor ] 

• This new GRANT syntax is supported only if IFMX_ROLES_WITH_GRANT, a new server environment variable, was set in the database server environment before the server started up.
• In the systabauth and syscolauth system catalog tables, systabauth.grantor and syscolauth.grantor can be the identifier of a role. Earlier releases support only the authorization identifier of an individual user in this column.
• In the same tables, systabauth.tabauth and syscolauth.colauth can use uppercase characters, such as 'S' (instead of lowercase 's') for the Select privilege, when systabauth.grantee or syscolauth.grantee (respectively) specifies the name of a role that has been granted an access privilege WITH GRANT OPTION.

For more information about using the WITH GRANT OPTION keywords when granting privileges to roles, see Propagating Access Privileges Through Roles.