SET ROLE

Syntax

Purpose

To enable and disable roles for your current session. For information on creating roles, see "CREATE ROLE".

When a user logs on, Oracle enables all privileges granted explicitly to the user and all privileges in the user's default roles. During the session, the user or an application can use the SET ROLE statement any number of times to change the roles currently enabled for the session. The number of roles that can be concurrently enabled is limited by the initialization parameter MAX_ENABLED_ROLES. For information on changing a user's default roles, see "ALTER USER"

You can see which roles are currently enabled by examining the SESSION_ROLES data dictionary view.

Prerequisites

You must already have been granted the roles that you name in the SET ROLE statement.

Keywords and Parameters

role	is a role to be enabled for the current session. Any roles not listed are disabled for the current session. Restriction: You cannot specify a role unless it was granted to you either directly or through other roles.
	IDENTIFIED BY password	is the password for a role. If the role has a password, you must specify the password to enable the role.
ALL	enables all roles granted to you for the current session except those optionally listed in the EXCEPT clause. Restriction: You cannot use this clause to enable roles with passwords that have been granted directly to you.
	EXCEPT	Roles listed in the EXCEPT clause must be roles granted directly to you. They cannot be roles granted to you through other roles.
	If you list a role in the EXCEPT clause that has been granted to you both directly and through another role, the role remains enabled by virtue of the role to which it has been granted.
NONE	disables all roles for the current session, including the DEFAULT role.

Examples

To enable the role GARDENER identified by the password MARIGOLDS for your current session, issue the following statement:

SET ROLE gardener IDENTIFIED BY marigolds; 

To enable all roles granted to you for the current session, issue the following statement:

SET ROLE ALL; 

To enable all roles granted to you except BANKER, issue the following statement:

SET ROLE ALL EXCEPT banker;

To disable all roles granted to you for the current session, issue the following statement:

SET ROLE NONE;