Reference Manual
   CHAPTER 9. SQL Statements     

SETUSER statement

Function 

To allow a database administrator to impersonate another user, and to enable connection pooling.

Syntax 

{ SET SESSION AUTHORIZATION | SETUSER }
[ [ WITH OPTIONS ] userid ]

Parameters 

userid: The user ID to be impersonated

Permissions 

Must have DBA authority.

See also 

EXECUTE IMMEDIATE statement

GRANT statement

REVOKE statement

SET OPTION statement

Description 

The SETUSER statement is provided to make database administration easier. It enables a user with DBA authority to impersonate another user of the database.

Also, you can use SETUSER from an application server to take advantage of connection pooling. This cuts down the number of distinct connections that need to be made, and so helps performance.

If WITH OPTIONS is specified, the database options in effect are changed to the current database options of userid. By default, only permissions (including group membership) are altered.

SETUSER with no user ID undoes all earlier SETUSER statements. It returns the active user ID to that of the original connection.

There are several uses for the SETUSER statement, including the following:

• Creating objects     You can use SETUSER to create a database object that is to be owned by another user.

• Permissions checking     By acting as another user, with their permissions and group memberships, a DBA can test the permissions and name resolution of queries, procedures, views, and so on.

• Providing a safer environment for administrators     The DBA has permission to carry out any action in the database. If you wish to ensure that you do not accidentally carry out an unintended action, you can use SETUSER to switch to a different user ID with fewer permissions.

Standards and compatibility 

• SQL/92     SET SESSION AUTHORIZATION is SQL 92 compliant. SETUSER is a vendor extension.

• Sybase     Adaptive Server Enterprise supports SETUSER, but not the WITH OPTIONS keywords.

Examples 

• The following statements, executed by a user named DBA, change the user ID to be Joe, then Jane, and then DBA

  SETUSER 'Joe'
  // ... operations...
  SETUSER 'Jane'
  // ... operations...
  SETUSER