SET ROLE statement

Use the SET ROLE statement to enable the privileges of a user-defined role. This statement is an extension to the ANSI/ISO standard for SQL.

Syntax

SET ROLE { { role | 'role' } | { NULL | NONE | DEFAULT } }


Element	Description	Restrictions	Syntax
`role`	Name of a role to be enabled	Must already exist in the database and must already have been granted to the user, but cannot be a built-in role. If enclosed between quotation marks, `role` is case sensitive.	Owner name;

Usage

Any user who is granted a role can enable the role by using the SET ROLE statement. You can only enable one role at a time. If you execute the SET ROLE statement after a role is already set, the new role replaces the old role as the current role.

The SET ROLE statement returns an error if the user does not currently hold the role, or if the role is a built-in role. (The access privileges held by a built-in role, such as the EXTEND role or the DBSECADM role, are always in effect, and do not require activation by the SET ROLE statement if the user holds that role.)

Users can be granted a default role for the database instance when the DBA issues the GRANT DEFAULT ROLE statement. If no default role exists for the user in the current database, role NULL or NONE is assigned by default. In this context, NULL and NONE are synonyms. Roles NULL and NONE can have no privileges. To set your role to NULL or NONE disables your current role.

When you use SET ROLE to enable a role, you gain the privileges of the role, in addition to the privileges of PUBLIC and your own privileges. If a role is granted to another role that has been assigned to you, you gain the privileges of both roles, in addition to any privileges of PUBLIC and your own privileges.

After SET ROLE executes successfully, the specified role remains effective until the current database is closed or the user executes another SET ROLE statement. Only the user, however, not the role, retains ownership of any database objects, such as tables, that were created during the session.

A role is in scope only within the current database. You cannot use privileges that you acquire from a role to access data in another database. For example, if you have privileges from a role in the database named acctg, and you execute a distributed query over the databases named acctg and inventory, your query cannot access the data in the inventory database unless you were also granted appropriate privileges in the inventory database. As a security precaution, discretionary access privileges that the user holds only from a role cannot provide access to tables outside the current database through a view or through the action of a trigger.

If your database supports explicit transactions, you must issue the SET ROLE statement outside a transaction. If your database is ANSI-compliant, SET ROLE must be the first statement of a new transaction. If the SET ROLE statement is executed while a transaction is active, an error occurs. For more information about SQL statements that initiate an implicit transaction, see SET SESSION AUTHORIZATION and Transactions.

If the SET ROLE statement is executed as a part of a trigger or SPL routine, and the owner of the trigger or SPL routine was granted the role with the WITH GRANT OPTION, the role is enabled even if you are not granted the role. For example, this code fragment sets a role and then relinquishes it after a query:

EXEC SQL set role engineer;
EXEC SQL select fname, lname, project
      INTO :efname, :elname, :eproject FROM projects
      WHERE project_num > 100 AND lname = 'Larkin';
printf ("%s is working on %s\n", efname, eproject);
EXEC SQL set role NULL;