Informix Guide to SQL: Syntax SQL Statements

Use the SET ROLE statement to enable the privileges of a role.

Any user who is granted a role can enable the role 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, by default, assigned the role NULL or NONE (NULL and NONE are synonymous). The roles NULL and NONE have no privileges. When you set the role to NULL or NONE, you disable the current role.

When you set 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, you gain the privileges of both roles, in addition to those of PUBLIC and your own privileges. After a SET ROLE statement executes successfully, the role remains effective until the current database is closed or the user executes another SET ROLE statement. Additionally, the user, not the role, retains ownership of all the database objects, such as tables, that were created during a session.

The scope of a role is within the current database only. You cannot use the privileges 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 your were granted privileges in the inventory database.

Your cannot execute the SET ROLE statement while in a transaction. If the SET ROLE statement is executed while a transaction is active, an error occurs.

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.

The following example sets the role engineer:

SET ROLE engineer

The following example sets a role and then relinquishes the role after it performs a SELECT operation:

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;

Related statements: CREATE ROLE, DROP ROLE, GRANT, and REVOKE

For a discussion of how to use roles, see the Informix Guide to SQL: Tutorial.