Skip Headers

Oracle® Database SQL Reference
10g Release 1 (10.1)

Part Number B10759-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

SET ROLE

Purpose

Use the SET ROLE statement to enable and disable roles for your current session.

When a user logs on, Oracle Database 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.

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


See Also:


Prerequisites

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

Syntax


set_role::=
Description of set_role.gif follows
Description of the illustration set_role.gif

Semantics


role

Specify a role to be enabled for the current session. Any roles not listed and not already enabled are disabled for the current session.

In the IDENTIFIED BY password clause, specify the password for a role. If the role has a password, then you must specify the password to enable the role.


Restriction on Setting Roles

You cannot specify a role unless it was granted to you either directly or through other roles.


ALL Clause

Specify ALL to enable all roles granted to you for the current session except those optionally listed in the EXCEPT clause.

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, then the role remains enabled by virtue of the role to which it has been granted.


Restriction on the ALL Clause

You cannot use this clause to enable roles with passwords that have been granted directly to you.


NONE

Specify NONE to disable all roles for the current session, including the DEFAULT role.

Examples


Setting Roles: Examples

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

SET ROLE dw_manager IDENTIFIED BY warehouse; 

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 dw_manager, issue the following statement:

SET ROLE ALL EXCEPT dw_manager;

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

SET ROLE NONE;