![SET SQLCA statement [ESQL]](gif/backward.gif)
ASA SQL Reference
SQL Statements
Use this statement to allow a database administrator to impersonate another user, and to enable connection pooling.
{ SET SESSION AUTHORIZATION | SETUSER }
[ [ WITH OPTIONS ] userid ]
WITH OPTIONS By default, only permissions (including group membership) are altered. If WITH OPTIONS is specified, the database options in effect are changed to the current database options of userid.
The SETUSER statement is provided to make database administration easier. It enables a user with DBA authority to impersonate another user of the database.
SETUSER can also be used from an application server to take advantage of connection pooling. Connection pooling cuts down the number of distinct connections that need to be made, which can improve performance.
SETUSER with no user ID undoes all earlier SETUSER statements.
The SETUSER statement cannot be used inside a procedure, trigger, event handler or batch.
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.
Must have DBA authority.
EXECUTE IMMEDIATE statement [SP]
SQL/92 SET SESSION AUTHORIZATION is SQL 92 compliant. SETUSER is a vendor extension.
SQL/99 SET SESSION AUTHORIZATION is a core feature. SETUSER is a vendor extension.
Sybase Adaptive Server Enterprise supports SETUSER, but not the WITH OPTIONS keywords.
The following statements, executed by a user named DBA, change the user ID to be Joe, then Jane, and then back to DBA.
SETUSER 'Joe' // ... operations... SETUSER WITH OPTIONS 'Jane' // ... operations... SETUSER