Security in stored procedures
-
Hi,
I've been trying to educate myself about the benefits and drawbacks of using stored procedures. So far I've read about ownership chaining and how user A will own a proc and have access to some tables. User B gets exec and now has whatever access user a had to those tables, through the proc. If my dbo is sa and owns all tables and procs, is this a security risk? Can a sql injection attack be thrown into a poorly defended stored procedure and wipe out the db? From what I've read, it seems like it can.
Thanks for any help!
Sam
-
That would take a combinaisonn of having all users be dbo and also using some sort of dynamic sql. 2 of the worst practices combined with a malicious user. You don't see that often but it CAN happen and you must protect yourself from that.
That's why we always push to use static sql in procs.
-
Thanks for your reply Ninja,
All users are not dbo, they just have exec on certain procs. Am I misunderstanding this from BOL: Ownership chains only apply to SELECT, INSERT, UPDATE, and DELETE statements.
This makes me think that even if a stored proc is doing a select, someone could feed a delete statement in since they are inheriting full permissions from the dbo. Is this correct?
What are best practices when creating a search feature. Our developers are using .net. I've come across sp_executesql, which parameterizes any variables and ensures they are the correct type.
I greatly appreciate you comments
Sam
-
That would be a problem only using dynamic sql. And the users would need that have explicit permissions on the base tables to be able to do any damage. Message back if you have any questions after reading this :
The Curse and Blessings of Dynamic SQL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply