Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Fabienne23
Starting Member
1 Post |
 Posted - 2008-07-28 : 12:54:42
|
| I posted this same question in another forum but it seems that it has been encoutering some technical problems and I am not sure if my post went through at all. Hoping for better luck here! I’ve heard several times that using stored procedures is better from a security standpoint than giving direct access to tables. One thing is not quite clear to me though. For example if I need to add a record to a table I can create an SP and give the client ‘execute’ permission or I can give the client ‘insert’ permission to the table istead. Either way the client has the same type of permission (insert) whether via a SP or via a direct SQL insert statement. Assuming that the client application deals with all data input verification (only valid input is accepted and sent to the database) So why is using a SP is better in this case? I understand that SP also enhances performance (execution plans) but I am just wondering about the security aspect of it. Is there any other security reason why using SP is better?Thanks! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-28 : 13:03:47
|
| http://codebetter.com/blogs/jeremy.miller/archive/2005/07/05/130093.aspx |
 |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-07-28 : 13:21:11
|
quote:
Originally posted by Fabienne23
Assuming that the client application deals with all data input verification (only valid input is accepted and sent to the database) So why is using a SP is better in this case?
Assuming that you only have one client application....and that in that application you have only one piece of code that does the inserts....and that you can easily modify this code if the database table structure changes....then doing direct inserts would not be so bad.BUT...If you have multiple client applications....or multiple INSERT code blocks....or you need to be able to change the table structure without breaking the application....then it is better to keep this functionality in ONE place rather than having the logic duplicated across the Enterprise, and the means Stored Procedures are the way to go.e4 d5 xd5 Nf6 |
|
|
|
|
|
|
Security: SP vs direct access to a table