Logo
CAR REVIEW
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
PREGNANCY
 
 
Windows Server

SQL Server 2005 : Privilege and Authorization - Basic Impersonation Using EXECUTE AS

7/24/2011 4:52:14 PM
Switching to a different user's execution context has long been possible in SQL Server, using the SETUSER command. This command was only available to members of the sysadmin or db_owner roles (at the server and database levels, respectively), and was therefore not useful for setting up least-privilege scenarios.

SQL Server 2005 introduces a new command for impersonation, EXECUTE AS. This command can be used by any user, and access is controlled by a permissions setting rather than a fixed role. The other benefit over SETUSER is that EXECUTE AS automatically reverts to the original context at the end of a module. SETUSER, on the other hand, leaves the impersonated context active when control is returned to the caller. This means that it is impossible to encapsulate impersonation within a stored procedure using SETUSER and guarantee that the caller will not be able to take control of the impersonated credentials.

To show the effects of EXECUTE AS, start by creating a new user and a table owned by the user:

CREATE USER Tom
WITHOUT LOGIN
GO

CREATE TABLE TomsData
(
AColumn INT
)
GO

ALTER AUTHORIZATION ON TomsData TO Tom
GO

Once the user is created, it can be impersonated using EXECUTE AS, and the impersonation context can be verified using the USER_NAME function:

EXECUTE AS USER='Tom'
GO

SELECT USER_NAME()
GO

The SELECT statement returns the value Tom, indicating that that is the impersonated user. Any action done after EXECUTE AS is run will use Tom's credentials. For example, the user can alter the table, since it owns it. However, an attempt to create a new table will fail, since the user does not have permission to do so:

--This statement will succeed
ALTER TABLE TomsData
ADD AnotherColumn DATETIME
GO

--This statement will fail
CREATE TABLE MoreData
(
YetAnotherColumn INT
)
GO

Once you have completed working with the database in the context of Tom's permissions, you can return to the outer context by using the REVERT command. If you have impersonated another user inside of another context (i.e., called EXECUTE AS more than once), REVERT will have to be called multiple times in order to return context to your login. The USER_NAME function can be checked at any time to find out whose context you are executing under.

To see the effects of nested impersonation, create a second user. The user can be given the right to impersonate Tom, using GRANT IMPERSONATE:

CREATE USER Paul
WITHOUT LOGIN
GO

GRANT IMPERSONATE ON USER::Tom TO PAUL
GO

If Paul is impersonated, the session will have no privileges to select rows from the TomsData table. In order to get those permissions, Tom must be impersonated from within Paul's context:

EXECUTE AS USER='Paul'
GO

--Fails
SELECT *
FROM TomsData
GO

EXECUTE AS USER='Tom'
GO

--Succeeds
SELECT *
FROM TomsData
GO

REVERT
GO

--Returns 'Paul' -- REVERT must be called again to fully revert
SELECT USER_NAME()
GO

The most important thing to understand is that when EXECUTE AS is called, all operations will run as if you are logged in as the impersonated user. You will lose any permissions that the outer user has that the impersonated user does not have, in addition to gaining any permissions that the impersonated user has that the outer user lacks.

For logging purposes, it is sometimes important to record the actual logged in principal. Since both the USER_NAME function and the SUSER_NAME function will return the names associated with the impersonated user, the ORIGINAL_LOGIN function has been added to SQL Server to return the name of the outermost server login. Use of ORIGINAL_LOGIN will allow you to get the name of the logged-in server principal, no matter how nested your impersonation scope is.

WHAT IS A MODULE?

Each of the privilege escalation examples that follow use stored procedures to show the functionality. However, please be aware that these methods work for any kind of module that SQL Server supports. A module is defined as any kind of code container that can be created inside of SQL Server: a stored procedure, view, user-defined function, trigger, or CLR assembly.
Other -----------------
- Configuring Standard Permissions for Exchange Server 2010 (part 2) - Understanding & Assigning Advanced Exchange Server Permissions
- Configuring Standard Permissions for Exchange Server 2010 (part 1)
- Feature Overview of Microsoft Lync Server 2010 : Dial-In Conferencing & Enterprise Voice
- Feature Overview of Microsoft Lync Server 2010 : Instant Messaging & Web Conferencing
- Feature Overview of Microsoft Lync Server 2010 : Presence
- Installing Windows Small Business Server 2011
- Business Server 2011 : Planning Fault Tolerance and Avoidance - Disk Arrays
- Microsoft Dynamics GP 2010 : Improving financial reporting clarity by splitting purchasing accounts & Speeding up lookups with Advanced Lookups
- Microsoft Dynamics GP 2010 : Remembering processes with an Ad hoc workflow
- Microsoft Dynamics GP 2010 : Gaining additional reporting control with Account Rollups
- SharePoint 2010 Search : Replacing the SharePoint Search Engine (part 2) - FAST Search Server 2010 for SharePoint
- SharePoint 2010 Search : Replacing the SharePoint Search Engine (part 1) - Google Search Appliance
- Microsoft Dynamics NAV : Backing up and restoring with SQL Server
- Microsoft Dynamics NAV : Using HotCopy backup & Testing the database
- Microsoft Dynamics NAV : Creating and restoring backups using a Dynamics NAV client
- Microsoft SQL Server 2008 Analysis Services : Building Basic Dimensions and Cubes - Setting up a new Analysis Services project
- Windows Server 2008 Server Core : Managing IIS - Working with the ApplicationHost.CONFIG File
- Microsoft Dynamics CRM 2011 : Creating a Dynamic Marketing List
- Microsoft Dynamics CRM 2011 : Evaluating Members Included in a List by Using Advanced Find & Removing Selected Members from a List
- Microsoft Dynamics AX 2009 : The MorphX Tools - Label Editor
 
 
Most view of day
- Microsoft Dynamic AX 2009 : .NET Business Connector - Usage Scenarios for .NET Business Connector
- Windows Server 2003 on HP ProLiant Servers : Defining the Windows 2003 Infrastructure
- Maintaining Security : Authorizing Administrative Actions, Restricting Access to Web Content
- Microsoft Project 2010 : Fine-Tuning Task Details (part 5) - Changing Task Types
- Integrating BizTalk Server 2010 and Microsoft Dynamics CRM : Communicating from BizTalk Server to Dynamics CRM (part 6)
- Microsoft Project 2010 : Fine-Tuning Task Details (part 2) - Setting Task Constraints
- Managing Client Protection : User Account Control (part 4) - How to Configure User Account Control
- BizTalk 2010 : WCF LOB SQL Adapter - Consuming ASDK SQL Adapter in Visual Studio (part 2)
- BizTalk 2006 : Creating More Complex Pipeline Components (part 4) - Custom Disassemblers
- Adobe After Effects CS5 : Building a 3D object - Working with 3D text
Top 10
- Microsoft Lync Server 2013 : Director Troubleshooting (part 3) - Synthetic Transactions,Telnet
- Microsoft Lync Server 2013 : Director Troubleshooting (part 2) - DNS Records, Logs
- Microsoft Lync Server 2013 : Director Troubleshooting (part 1) - Redirects, Certificates
- Microsoft Lync Server 2013 : Administration of the Director Role (part 4) - Services Management, Client Version Filter
- Microsoft Lync Server 2013 : Administration of the Director Role (part 3) - Topology Status
- Microsoft Lync Server 2013 : Administration of the Director Role (part 2) - Ports,Firewall Rules
- Microsoft Lync Server 2013 : Administration of the Director Role (part 1) - Services
- Microsoft Lync Server 2013 : Configuring the Director (part 2) - Web Services Ports,Reverse Proxy
- Microsoft Lync Server 2013 : Configuring the Director (part 1) - SRV Records, Web Services FQDN Overrides
- Sharepoint 2013 : SharePoint Designer 2013 (part 2) - Locking Down SharePoint Designer
 
 
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
2015 Camaro