Use this statement to allow a database administrator to impersonate another user, and to enable connection pooling.
{ SET SESSION AUTHORIZATION | SETUSER }
[ [ WITH OPTION ] userid ]
WITH OPTION clause By default, only permissions (including group membership) are altered. If WITH OPTION is specified, the database options in effect are changed to the current database options of userid.
userid The user ID is an identifier (SETUSER syntax) or a string (SET SESSION AUTHORIZATION syntax). See Identifiers and Strings.
The SETUSER statement is provided to make database administration easier. It enables a user with DBA authority to impersonate another user of the database. After running a SETUSER statement, you can check which user you are impersonating by running one of the following commands:
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 database administrator can test the permissions and name resolution of queries, procedures, views, and so on.
Providing a safer environment for administrators The database administrator has permission to carry out any action in the database. If you want 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.
The SETUSER statement cannot be used within procedures, triggers, events, or batches.
Must have DBA authority.
SQL/2003 SET SESSION AUTHORIZATION is a core feature. SETUSER is a vendor extension.
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 OPTION "Jane" // ... operations... SETUSER |
The following statement sets the user to Jane. The user ID is supplied as a string rather than as an identifier.
SET SESSION AUTHORIZATION 'Jane'; |
| Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |