O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required