>>-SET ROLE--+-+-role---+--+-----------------------------------><
             | '-'role'-'  |
             '-+-NULL----+-'
               +-NONE----+
               '-DEFAULT-'
 

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.

All users are assigned their DEFAULT role as granted by the DBA using GRANT DEFAULT ROLE statement for the database instance. 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.

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;