September 19, 2010 at 12:13 am
I have a stored proc that a user needs to execute. Within the stored proc there are a couple of things that need to be done with somewhat elevated permissions. Rather than have this low end user have those perms, I call another proc inside an EXECUTE AS LOGIN = 'xxx' and have the elevated items done in that fashion.
However, it appears that the user executing the initial proc need IMPERSONATE permissions on the login that is executing the higher level stuff. Does this mean that the low end user can now do everything the higher end user can do as a result of the IMPERSONATE requirement? If so, where is the benefit here?
I want the low end user to be able to call the proc, but not have the permissions necessary to do the elevated work themselves.
Thanks!
September 22, 2010 at 2:38 pm
Impersonate is useful when you want to give another user the same permissions for a period of time. For example, you could allow another user to impersonate your account while you are on vacation if they can be trusted to fill in for you (without divulging your password) and then simply revoke those permissions when you return from vacation.
Depending on what you want to allow this user to do, you might be able to give them more granular permission only to the level needed to perform the task and not all the permissions of the user you want to impersonate.
Can you give us more detail as to what operations you want to allow the user to do?
October 11, 2010 at 1:29 pm
AaronTC05 (9/22/2010)
Impersonate is useful when you want to give another user the same permissions for a period of time. For example, you could allow another user to impersonate your account while you are on vacation if they can be trusted to fill in for you (without divulging your password) and then simply revoke those permissions when you return from vacation.Depending on what you want to allow this user to do, you might be able to give them more granular permission only to the level needed to perform the task and not all the permissions of the user you want to impersonate.
Can you give us more detail as to what operations you want to allow the user to do?
Depending on what you want to allow this user to do, you might be able to give them more granular permission only to the level needed to perform the task and not all the permissions of the user you want to impersonate.
Can you give us more detail as to what operations you want to allow the user to do?[/quote]
Well here is a good example. I need some of my developers to be able to view some connection information (via DMVs) for given databases. I DO NOT want the developers to have VIEW SERVER STATE permissions. So, I create a new account, lets say VSS and grant it VIEW SERVER STATE perms. I then wrap the code that pulls the info from the DMVs into a stored proc, with the first line of the proc being EXECUTE AS LOGIN = 'VSS'. I grant the developers permission to execute the stored procedure and off we go.
Well, kind of. In order to take advantage of the EXECUTE AS LOGIN = 'VSS', the developers must posses permissions to impersonate VSS. So, now while their individual accounts do not have VIEW SERVER STATE permissions, they have "access" to an account that does. All they would need to do is preface any query with EXECUTE AS LOGIN = 'VSS' and they have view server state permissions.
I guess I simply do not see the security "advantage" here. I guess if they never see the definition of the proc, they are not going to know about their ability to impersonate the account but that feels like "security through obscurity".
Thoughts? Comments?
October 11, 2010 at 2:01 pm
Here is an example of a real world use of impersonation:
http://nickgrattan.wordpress.com/2007/07/24/bringing-impersonation-to-sql-server-2005/
For what you want to do it sounds like you'll need to grant 'permanent' (not impersonate) permission to VIEW SERVER STATE to the user/role and then DENY access to the specific DMVs that you don't want your users to have access to (via the proc or otherwise), rather than using impersonate which seems to be useful for granting temporary access to another account for various reasons.
See this article for info about DMVs:
http://www.codeproject.com/KB/database/Dynamic_Management_Views.aspx
Scripting out the DENY should be easy enough, you can see a query for getting a list of all the DMVs here:
http://blog.sqlauthority.com/2010/05/15/sql-server-list-all-the-dmv-and-dmf-on-server/
Hope this helps!
October 11, 2010 at 2:12 pm
AaronTC05 (10/11/2010)
Here is an example of a real world use of impersonation:http://nickgrattan.wordpress.com/2007/07/24/bringing-impersonation-to-sql-server-2005/
For what you want to do it sounds like you'll need to grant 'permanent' (not impersonate) permission to VIEW SERVER STATE to the user/role and then DENY access to the specific DMVs that you don't want your users to have access to (via the proc or otherwise), rather than using impersonate which seems to be useful for granting temporary access to another account for various reasons.
See this article for info about DMVs:
http://www.codeproject.com/KB/database/Dynamic_Management_Views.aspx
Scripting out the DENY should be easy enough, you can see a query for getting a list of all the DMVs here:
http://blog.sqlauthority.com/2010/05/15/sql-server-list-all-the-dmv-and-dmf-on-server/
Hope this helps!
It does..... my other thought though is granting VIEW SERVER STATE to the developers and then explicitly denying access to the not needed DMVs, still allows the them, with their own credentials, to view connections outside of the one database I am tying them too simply by querying the appropriate DMV. Whereas with EXECUTE AS, unless they specifically use it outside of the proc I force them to use (and know about the account they can impersonate), I can more tightly control what they can see, which is one of the goals.
I think this might be a 6 of one, half dozen of another, type of scenario......
I might be talking in circles here....
October 11, 2010 at 2:35 pm
mnDBA (10/11/2010)
AaronTC05 (10/11/2010)
Here is an example of a real world use of impersonation:http://nickgrattan.wordpress.com/2007/07/24/bringing-impersonation-to-sql-server-2005/
For what you want to do it sounds like you'll need to grant 'permanent' (not impersonate) permission to VIEW SERVER STATE to the user/role and then DENY access to the specific DMVs that you don't want your users to have access to (via the proc or otherwise), rather than using impersonate which seems to be useful for granting temporary access to another account for various reasons.
See this article for info about DMVs:
http://www.codeproject.com/KB/database/Dynamic_Management_Views.aspx
Scripting out the DENY should be easy enough, you can see a query for getting a list of all the DMVs here:
http://blog.sqlauthority.com/2010/05/15/sql-server-list-all-the-dmv-and-dmf-on-server/
Hope this helps!
It does..... my other thought though is granting VIEW SERVER STATE to the developers and then explicitly denying access to the not needed DMVs, still allows the them, with their own credentials, to view connections outside of the one database I am tying them too simply by querying the appropriate DMV. Whereas with EXECUTE AS, unless they specifically use it outside of the proc I force them to use (and know about the account they can impersonate), I can more tightly control what they can see, which is one of the goals.
I think this might be a 6 of one, half dozen of another, type of scenario......
I might be talking in circles here....
Done talking in circles here. The overall goal is to get them locked down to see as little information as possible with as little risk as possible.
If I allow them to impersonate a login that has VIEW SERVER STATE, the worst case scenario is they could end up with VIEW SERVER STATE perms by prefacing a query with EXECUTE AS LOGIN = 'VSS'.
If I grant them VIEW SERVER STATE perms and explicitly deny select on the unneeded DMVs, then worst case scenario, they can just select all records (albeit seeing more than just connections to their given DB) from a handful of DMVs.
Regardless, I can still control what they can see via the stored proc they execute.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply