Entire impersonation stack should be retrievable. - by Erland Sommarskog

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


7
0
Sign in
to vote
ID 238908 Comments
Status Resolved Workarounds
Type Suggestion Repros 0
Opened 11/17/2006 12:55:36 PM
Access Restriction Public

Description

If there are stacked impersonations with EXECUTE AS (either as a statement or through the module header), you can only retrieve the current with SYSTEM_USER & co and the original with original_login(). But none of these may be relevant. Consider the case that the middle-tier authenticates the real user, then connects to SQL Server with a proxy login to execute an EXECUTE AS on behalf of the real user (who is a database-only user). Then for some reason there is a module which has EXECUTE AS in the header. Now, we cannot retrieve the original user. 
Sign in to post a comment.
Posted by Microsoft on 4/1/2009 at 9:23 AM
We will consider this request for a future version of SQL Server.

Il-Sung.
Posted by Erland Sommarskog on 8/8/2008 at 11:35 AM
Obviously, SQL Server cannot sort out what the application layer performs on its side. But consider this scenario.

1) User Fred connects to application.
2) Application connects to SQL Server with the proxy user Appl_admin.
3) Application performs as EXECUTE AS USER = 'Fred'. Fred is a user WITHOUT
    LOGIN in the application database. The person Fred does not have any login in
    SQL Server.
4) The user performs an action that causes the application to call a stored procedure
    which for some reason has an EXECUTE AS OWNER clause.
5) The stored procedure performs an update, and the table has a trigger that logs
    auditing informtation.

The trigger can find that dbo (SYSTEM_USER) and Appl_admin (original_login()) are
involved in the plot, but Fred is nowhere to be found. Obviously, SQL Server can
never tell which user on the impersonation stack is the real user. But what SQL
SQL Server can do and should do, is to expose the full impersonation stack
one way or another.

I've reopened the item.
Posted by Microsoft on 8/8/2008 at 10:29 AM
Unfortunately, this is not a problem that SQL Server can solve as we cannot know what the original context was at the application server. All SQL Server knows is the security context that was used to connect to the server.

Il-Sung.
Posted by Microsoft on 11/27/2006 at 1:44 PM
Thanks for your feedback, we are actively looking into the issue of call stacks and impersonation. I do not think we will have an answer for SQL 2005 but may in a future release as this is important from an auditing perspective.