none
Execution Context Question RRS feed

  • Question

  • I have always had problem with execution context. It seems very tricky to me no matter I am preparing for Cert. exams or at work.

    I have a scenario that seems very strange to me.

    UserC is the owner of Table1 and SP1 (Simple Select statement on Table1, EXEC AS CALLER)

    UserA has Exec permission on SP1 and SELECT permission on Table1

    UserB has EXEC permission on SP1 and Denied Select Permission on Table1

    My Assumption: when UserA runs the SP, no permission error should be raised ; When UserB runs SP1, an error message must be raised , is that right?

    but when I run the following script UserB can query Table1 too. How is that possible?

    --===================================================
    -- Create necessary database
    USE [master]
    GO
    Create database DB_Security
    Go
    --===================================================
    -- Table and records
    USE DB_Security
    GO
    Create table MyTable (ID int identity(1,1) Primary KEy
                        ,Name varchar(100))
                       
    INSERT INTO MyTable values('Test1')
                        ,('Test2')
                        ,('Test3')
                        ,('Test4')
                        ,('Test5')
    --===================================================
    -- Create users
    CREATE USER UserA WITHOUT LOGIN
    CREATE USER UserB WITHOUT LOGIN
    --==================================================
    GRANT SELECT ON MyTable to UserA
    --================================================

    EXECUTE AS USER='USERA'
    SELECT * FROM MyTable
    REVERT

    EXECUTE AS User  = 'USERB'
    SELECT * FROM MyTable
    REVERT

    --======================================================
    ALTER PROCEDURE GetTable
    WITH EXECUTE AS CALLER
    AS
        SELECT USER_NAME()
        SELECT * FROM MyTable


    GRANT EXEC ON GetTable To UserB
    GRANT EXEC ON GetTable to UserA
    DENY SELECT ON MyTable To UserB

    EXECUTE AS User='UserB'
    GO
    EXEC GetTable
    REVERT

    -- The script can not be executed as a whole, it should be ran block by block

    Thanks

    Monday, November 28, 2011 6:08 PM

Answers

All replies

  • The only think I can think of is that the owner of table and SP gave EXEC permission to  UserB, in that case, I guess it is assumed since the owner of Table and SP gave permission, UserB can access the table even though the select on the table is denied.
    Monday, November 28, 2011 7:43 PM
  • UserB can execute the stored procedure as they have execute permissions on the procedure.  The SELECT permission for a TABLE relates to executing adhoc queries against the table, not stored procedures i.e.

    UserB would not be able to execute SELECT * FROM Table using SSMS or from a client application.


    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Tuesday, November 29, 2011 1:09 AM
    Moderator
  • Thanks for helpful answer. but let me ask you this question

    UserA does not have Select permission on Table1, he creates a SP with a select on table1 and give UserB exec permission on the SP, so userB can SELECT table1?

    That seems a little bit strange to me.
    Tuesday, November 29, 2011 5:01 PM
  • Have a read of the following http://www.sommarskog.se/grantperm.html#ownershipchaining
    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Wednesday, November 30, 2011 3:54 AM
    Moderator