SQL in a Nutshell, 2nd Edition

Name

SET ROLE Statement

Synopsis

The SET ROLE statement enables and disables specific roles for the current session.

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

SQL2003 Syntax

SET ROLE {NONE | role_name}

Keywords

NONE

Assigns the CURRENT ROLE role to the current session.

role_name

Associates the set of privileges associated with the named role with the current session.

Rules at a Glance

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

The value for role_name must reference a valid user role existing on the server. You may specify the role name either as a literal or through a variable.

Programming Tips and Gotchas

Sessions are created using the CONNECT statement, while roles are created using the CREATE ROLE statement.

Most database platforms offer some method of setting or changing the role used during a user session. SET ROLE is the ANSI standard approach to setting the role used during a user session, but it is not widely supported by the different database platforms. Check your platform to find an analogous command supported by your specific vendor.

DB2

Not supported. DB2 does not yet support roles.

MySQL

SET ROLE is not supported. An analogous method to control connection settings in MySQL is controlled in the [client] ...