SQL in a Nutshell, 3rd Edition

Name

SET ROLE Statement

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

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

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. The following sections detail the analogous commands supported by the platforms discussed in this book; check your platform’s documentation for more information.

MySQL

SET ROLE is not supported. An analogous method to control connection settings in MySQL is controlled in the [client] section of the .my.cnf configuration file in the home directory. For example:

[client] host=server_name user=user_name password=client_password ...