Skip to Main Content
SQL in a Nutshell
book

SQL in a Nutshell

by Kevin Kline
December 2000
Intermediate to advanced content levelIntermediate to advanced
224 pages
9h 52m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

SQL in a Nutshell, 3rd Edition

SQL in a Nutshell, 3rd Edition

Kevin Kline

Publisher Resources

ISBN: 1565927443