May 9, 2011 at 11:52 pm
Even though we work extensively on security and permissions, at times we will be in a situation to confuse ourself with a simple but tricky question. here is one of that kind(not to all but atleast to me). Particular User has been given db_datareader access to a specific database. The same user has been given an Explicit GRANT EXECUTE permission that is assigned to pubilc. Now my doubt is that, if in any stored procedure there is insert, update and delete statements and when this particular user try to execute.. wat will happen internally. Whether the user can execute that stored procedure without any issues or he will not be able to execute as he has got just db_datareader permission. Will be grateful if any our members can explian in detail.
Thanks
Sethu
May 10, 2011 at 6:46 am
it's actually a very common security setup to have a group with ONLY execute permissions; some applications are designed so that all data is accessed via stored procs, so the users never even get db_datareader; they simply cannot access the base tables directly.
the security assumption is, if you give EXECUTE on a proc, then the proc will be execute in the context of the owner (dbo most likely) of the proc, and not the caller of the proc.
permissions for underlying objects are not checked; so if the user can call the proc, whatever the proc does in the database, whether select/insert/update/delete, or even DDL commands, will be executed.
only if the proc tries to do something outside of the database, like xp_cmdshell, will things start failing due to permissions of the caller.
Lowell
May 10, 2011 at 7:02 am
Lowell (5/10/2011)
it's actually a very common security setup to have a group with ONLY execute permissions; some applications are designed so that all data is accessed via stored procs, so the users never even get db_datareader; they simply cannot access the base tables directly.the security assumption is, if you give EXECUTE on a proc, then the proc will be execute in the context of the owner (dbo most likely) of the proc, and not the caller of the proc.
permissions for underlying objects are not checked; so if the user can call the proc, whatever the proc does in the database, whether select/insert/update/delete, or even DDL commands, will be executed.
only if the proc tries to do something outside of the database, like xp_cmdshell, will things start failing due to permissions of the caller.
It executes in the context of the caller (not the owner) unless you specify EXECUTE AS in the stored procedure creation (which is new in 2005 but isn't present in 2000 and below). With that said, if two objects have the same owner, and one refers to another, like a stored procedure referring to a table, an ownership chain forms. In this case SQL Server recognizes that the objects have the same owner and will assume that the reference is intentional and desired and so will not check permissions on the second object. That's how stored procedures can perform operations against tables that the user does not have access to normally.
In the given scenario here, since the user has db_datareader membership and has EXECUTE against all stored procedures, any SELECT from within a stored procedure in that database will work, regardless of owner. However, INSERT, UPDATE, and DELETE will only work if an ownership chain is formed, as described above.
K. Brian Kelley
@kbriankelley
May 10, 2011 at 7:08 am
thank you for the more detailed explanation Brian; I'm better informed now. I had assumed something else, and I'm glad you posted this!
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply