locked
Impersonation Security Effectiveness? RRS feed

  • Question

  • Hello,

    I was learning about the T-SQL Execute AS impersonation ability and something seems to be going over my head. I have a SQL Account say SQLWeb that I give access to DbaseA. SQLWeb runs DbaseA.DBO.sp_GetData. This sp calls DbaseB.dbo.usp_GetData. Since SQLWeb does not have access to DbaseB this fails. SQLOverlord does however have acces so I grant Impersonate SQLOverlord to SQLWeb. Well in effect what have I accomplished? SQLWeb now can exec sp's on Dbase2 so why go through the hassle. So I know there has to be a reason it's just not clicking in my pea brain.

    Thank You

    JB

    Friday, October 29, 2010 9:29 PM

Answers

  • The idea is to allow the SP to EXECUTE AS an empowered user (usually secured from regular user access) not to provide EXECUTE AS ability to the actual application user. Else, it defeats the purpose as you have concluded yourself.

    Take this very commonly cited example; TRUNCATE TABLE. This is a DDL, not a DML though it appears to do the same thing as DELETE on a table (yeah, quite different under the hood). You may want to provide a couple of users who do maintenance work the ability to truncate a table but this requires ALTER TABLE level permissions which you really don't want to grant unless absolutely necessary. So you go ahead and write a sproc that does the TRUNCATE TABLE task which EXECUTES AS a user that has ALTER TABLE permissions. You then grant EXECUTE permissions to your maintenance users and nothing else. Now your maintenance users can truncate the table by calling that sproc but if they tried to issue a TRUNCATE TABLE command directly against the table, it will fail.

    This is a very simple example but it does provide a good example of a good use case.

    In your case, your usp_GetData can be set to EXECUTE AS SQLOverlord and SQLWeb still has no direct permissions on the underlying table. You then grant SQLWeb EXECUTE permissions on usp_GetData. When SQLWeb calls the sproc, it will check for EXECUTE permissions which will succeed, then executes under SQLOverlord's context and return the results to SQLWeb.

    BOL has more details on the different ways to use EXECUTE AS and the impact of the different approaches. Happy tinkering.

     


    No great genius has ever existed without some touch of madness. - Aristotle
    • Proposed as answer by Tom Li - MSFT Monday, November 1, 2010 6:35 AM
    • Marked as answer by zzpluralza Monday, November 1, 2010 4:36 PM
    Saturday, October 30, 2010 12:30 AM

All replies

  • The idea is to allow the SP to EXECUTE AS an empowered user (usually secured from regular user access) not to provide EXECUTE AS ability to the actual application user. Else, it defeats the purpose as you have concluded yourself.

    Take this very commonly cited example; TRUNCATE TABLE. This is a DDL, not a DML though it appears to do the same thing as DELETE on a table (yeah, quite different under the hood). You may want to provide a couple of users who do maintenance work the ability to truncate a table but this requires ALTER TABLE level permissions which you really don't want to grant unless absolutely necessary. So you go ahead and write a sproc that does the TRUNCATE TABLE task which EXECUTES AS a user that has ALTER TABLE permissions. You then grant EXECUTE permissions to your maintenance users and nothing else. Now your maintenance users can truncate the table by calling that sproc but if they tried to issue a TRUNCATE TABLE command directly against the table, it will fail.

    This is a very simple example but it does provide a good example of a good use case.

    In your case, your usp_GetData can be set to EXECUTE AS SQLOverlord and SQLWeb still has no direct permissions on the underlying table. You then grant SQLWeb EXECUTE permissions on usp_GetData. When SQLWeb calls the sproc, it will check for EXECUTE permissions which will succeed, then executes under SQLOverlord's context and return the results to SQLWeb.

    BOL has more details on the different ways to use EXECUTE AS and the impact of the different approaches. Happy tinkering.

     


    No great genius has ever existed without some touch of madness. - Aristotle
    • Proposed as answer by Tom Li - MSFT Monday, November 1, 2010 6:35 AM
    • Marked as answer by zzpluralza Monday, November 1, 2010 4:36 PM
    Saturday, October 30, 2010 12:30 AM
  • So in saying this back to you. Rather than have an TSQL....execute as 'User'; Truncate Table.....TSQL    inline create the SP with execute as perms and thus the WebUser does not need the Grant Impersonate command. The Sp has the impersonation ability and the webusr is limited as desired.

    Sunday, October 31, 2010 4:02 AM
  • Yup, you got the idea. There's obviously a bit more detail to it in the implementation but you are on the right track. WebUser does not have ALTER TABLE or impersonation capabilities but can still perform specific advanced tasks.

     


    No great genius has ever existed without some touch of madness. - Aristotle
    Monday, November 1, 2010 12:11 AM