Restricting IMPERSONATE permissions only to certain stored procedures

Question

Restricting IMPERSONATE permissions only to certain stored procedures

Banana-823045

SSCommitted

Points: 1689
More actions
October 26, 2011 at 10:22 am

#250722

I'm wondering if there is a way to limit IMPERSONATE permissions so that the users cannot use "EXECUTE AS LOGIN 'proxylogin'". It seems that same question was asked a while ago with no answers. I've also read Erland's treatise on EXECUTE AS and am not certain if I really want to use TRUSTWORTHY to also grant server-level permissions without explicitly granting the users the IMPERSONATE.
I've already limited the proxylogin to only what it need to be able to do the job; it's just the arbitrary EXECUTE AS I don't want to deal with.
Suggestions?

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Can you explai what you are looking for in a little more detail? I'm not sure what you are asking about with regard to EXECUTE AS. Are we talking about users executing created with EXECUTE AS or are you talking about users actually changing their session context using EXECUTE AS?

Banana-823045 SSCommitted Points: 1689 More actions · Answer 2

Sure.

I do not want to grant permissions on server directly to any users but there are some operations that I would like to allow users to perform without depending on me. So, I have a set of stored procedures that uses EXECUTE AS LOGIN = 'proxylogin' that has the necessary server - level permissions.

My concern is that there is nothing preventing the users who were granted IMPERSONATE permissions to just write ad-hoc queries that uses EXECUTE AS LOGIN = 'proxyuser' and not use my stored procedures at all. I'd really like to know if I can limit the scope of IMPERSONATE permission to be available only if they use my stored procedure and not in any other context.

I hope that clarify.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Banana-823045 (10/26/2011)
Sure.
I do not want to grant permissions on server directly to any users but there are some operations that I would like to allow users to perform without depending on me. So, I have a set of stored procedures that uses EXECUTE AS LOGIN = 'proxylogin' that has the necessary server - level permissions.
My concern is that there is nothing preventing the users who were granted IMPERSONATE permissions to just write ad-hoc queries that uses EXECUTE AS LOGIN = 'proxyuser' and not use my stored procedures at all. I'd really like to know if I can limit the scope of IMPERSONATE permission to be available only if they use my stored procedure and not in any other context.
I hope that clarify.

If you have the permission to impersonate the proxyuser used by your stored procedures when you create them, your users don't need the impersonate permission to run your stored procedures.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

From BOL regarding the EXECUTE AS Clause:

By specifying the context in which the module is executed, you can control which user account the Database Engine uses to validate permissions on objects that are referenced by the module. This provides additional flexibility and control in managing permissions across the object chain that exists between user-defined modules and the objects referenced by those modules. Permissions must be granted to users only on the module itself, without having to grant them explicit permissions on the referenced objects. Only the user that the module is running as must have permissions on the objects accessed by the module.

Banana-823045 SSCommitted Points: 1689 More actions · Answer 5

I really hope I'm wrong but this article seems to suggest that this does not work this way when we're talking about impersonating a server principal.

Article

In this, it says you have to grant IMPERSONATE permission to enable one login to execute as other login within a sproc.

I originally started using EXECUTE AS OWNER clause in my sproc, thinking that would enable me to use my credentials but apparently that doesn't work because clause works only as a database principal and not server principal.

I just tried again and used EXECUTE AS OWNER with "EXECUTE AS LOGIN = 'proxylogin' " within my sproc to see if it'd enable the user to execute my sproc without needing the IMPERSONATE permission - I got a permission error presumably because even though they executed as owner, the login is still unchanged.

Banana-823045 SSCommitted Points: 1689 More actions · Answer 6

Just a quick addendum to hopefully demonstrate the crux of problem.

This will fail in an error:

EXECUTE AS USER = 'dbo';

EXECUTE AS LOGIN = 'proxylogin';

REVERT;

Even though the user 'dbo' is actually me and my login is mapped to a sysadmin role, executing as different database user even if it's still me, myself and I, appears to impede any opportunity to change logins in order to gain server level permissions.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

I'll have to play with this at home. I know where I am working now, they have a procedure dbo.TruncateTable used to truncate a table without giving the developers access to TRUNCATE TABLE statement due to the elevated permissions needed to execute the TRUNCATE TABLE.

Here is the start of their CREATE PROCEDURE:

CREATE PROCEDURE [dbo].[TruncateTable]

(

@tname sysname,

@schema sysname

)

WITH EXECUTE AS SELF

AS

...

This is created with a user that has necessary permissions to execute the TRUNCATE TABLE statement.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Banana-823045 (10/26/2011)
Just a quick addendum to hopefully demonstrate the crux of problem.
This will fail in an error:
EXECUTE AS USER = 'dbo';
EXECUTE AS LOGIN = 'proxylogin';
REVERT;
REVERT;
Even though the user 'dbo' is actually me and my login is mapped to a sysadmin role, executing as different database user even if it's still me, myself and I, appears to impede any opportunity to change logins in order to gain server level permissions.

This is changing the context of the session itself. This would require that the original user have the impersonate privledge.

Banana-823045 SSCommitted Points: 1689 More actions · Answer 9

Lynn Pettis (10/26/2011)
I'll have to play with this at home. I know where I am working now, they have a procedure dbo.TruncateTable used to truncate a table without giving the developers access to TRUNCATE TABLE statement due to the elevated permissions needed to execute the TRUNCATE TABLE.
Here is the start of their CREATE PROCEDURE:
CREATE PROCEDURE [dbo].[TruncateTable]
(
@tname sysname,
@schema sysname
)
WITH EXECUTE AS SELF
AS
...
This is created with a user that has necessary permissions to execute the TRUNCATE TABLE statement.

Okay, so I gave this a try. Removed the EXECUTE AS LOGIN from the sproc and put in EXECUTE AS SELF. I'm running as a sysadmin LOGIN + dbo USER so this should have had given executor all permissions they need from my sproc.

However, when i ran it as a lesser-privileged user with only EXECUTE permission on my sproc, I get permission error.

In your example, TRUNCATE TABLE is a database level permission. I'm talking about server level permissions. As mentioned in my previous example, EXECUTE AS USER will fail to grant additional server level permissions and as you said, we need IMPERSONATE permission to make this statement. I want to emphasize that when I executed my simple example, I was already logged in as a sysadmin and this still fails with the permission error.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

What server level permissions are you trying to give your users?

Banana-823045 SSCommitted Points: 1689 More actions · Answer 11

In this case, ability to create new logins -- using SQL Server auth ( 🙁 ), and we wanted to have a way to allow "admins" of the database to self-manage their users, including creating the login that should only map to their own database.

Banana-823045 SSCommitted Points: 1689 More actions · Answer 12

Hopefully not too soon to bump the thread and see if anyone has any suggestions or maybe a better approach to achieve the goal of enabling a database user to perform a server level action without having the direct permissions.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 13

If the database is owned by SA, then "dbo" is actually "SA". Use EXECUTE AS OWNER within the proc and grant EXECUTE privs only to those users or roles that you want to execute the proc. The proc will have the privs to do everything in it.

Do make sure that there's no possibility of SQL Injection before you do so.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 14

P.S. ... and no IMPERSONATE privs are required...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)