January 8, 2014 at 9:56 am
Okay so the scenario we have is this:
We have two schemas, let's call them data and access. And we have a user, Bill.
Bill does not have any rights to operations on tables or other objects in the data schema, only to objects in the access schema.
In a stored procedure inside access, if we accidentally perform an operation on the data schema, whilst running as Bill, it runs just fine, allowing Bill to do things he could not otherwise do. This is often what we would want to happen of course.
Execute As Caller, which you'd assume would solve this does not, as it implicitly grants all rights that are referenced within a stored procedure to whoever has execute rights. None of the Execute As options seem to have any effect here.
Sometimes this is what I want, sometimes it's exactly what I don't want. Is there any way I can force this behaviour? Such that calling a stored procedure as Bill which performs operations Bill is not entitled to do will actually error?
I'm open to any suggestions, as my efforts so far have been an epic fail.
January 8, 2014 at 10:10 am
When a user has rights to execute a stored procedure it will be assumed that they can do whatever the procedure code does. It does not check privileges for each and every statement inside the procedure.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 8, 2014 at 11:10 am
Sean Lange (1/8/2014)
When a user has rights to execute a stored procedure it will be assumed that they can do whatever the procedure code does. It does not check privileges for each and every statement inside the procedure.
Yes and no. "It Depends".
To cover what you wrote above, which is correct in the absence of "AS"... from BOL...
EXECUTE permissions for a module default to the owner of the module, who can transfer them to other users. When a module is run that executes a string, permissions are checked in the context of the user who executes the module, not in the context of the user who created the module. However, if the same user owns the calling module and the module being called, EXECUTE permission checking is not performed for the second module. For more information, see Ownership Chains.
To cover the specific instance of EXECUTE AS CALLER or SELF, again, from BOL (Hilighting is mine)...
EXECUTE permissions for a module default to the owner of the module, who can transfer them to other users. When a module is run that executes a string, permissions are checked in the context of the user who executes the module, not in the context of the user who created the module. However, if the same user owns the calling module and the module being called, EXECUTE permission checking is not performed for the second module. For more information, see Ownership Chains.
If the module accesses other database objects, execution succeeds when you have EXECUTE permission on the module and one of the following is true:
The module is marked EXECUTE AS USER or SELF, and the module owner has the corresponding permissions on the referenced object.
The module is marked EXECUTE AS CALLER,[font="Arial Black"] and you have the corresponding permissions on the object.[/font]
The module is marked EXECUTE AS user_name, and user_name has the corresponding permissions on the object.
In reference to what mark.hough asked about, EXECUTE AS CALLER should have prevented the user from using the proc to make a change to the schema through the proc. It may be, however, that the user has privs to the underlying objects through a "Group" or DB role or System role or has even been granted specific privs on the underlying objects themselves.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply