SQL in a Nutshell

Name

SET ROLE

Synopsis

The SET ROLE command enables and disables specific security roles for the current session. Sessions are created using the CONNECT statement, while roles are created using the CREATE ROLE statement.

Vendor	Command
SQL Server	Not supported
MySQL	Not supported
Oracle	Supported, with variations
PostgreSQL	Not supported

SQL99 Syntax and Description

SET ROLE {NONE | role_name}

The session is opened using the CONNECT statement. Once a user session is initiated, issuing the SET ROLE statement grants that session a set of privileges associated with a role. The SET ROLE command can be issued only outside of a transaction.

SET ROLE NONE assigns the current session to a NULL role.

When a role is assigned to the currently active user session, a character string, database variable, or even a system function such as CURRENT_ROLE or SYSTEM_ROLE may be used. In any case, the value specified must be a valid role name.

Oracle Syntax and Variations

SET ROLE {role_name [IDENTIFIED BY password] [,...n] 
| [ALL [EXCEPT role_name [,...]]
|  NONE;

When a user initiates a connection, Oracle explicitly assigns the privileges that are roles to the user. The role(s) under which the session is operating can be changed with the SET ROLE command. Oracle uses the MAX_ENABLED_ROLES initialization parameter to control the maximum number of roles that can be opened concurrently.

The role_name specified must be a valid role name already created within Oracle. Any roles not specified are unavailable ...