Stored Procedures vs No Stored Procedures - Security Viewpoint

Question

For a web application database, from a security standpoint only, what are arguments counter to the point for an sp only solution where the app db account has no rights to tables and views and only exec on sps?

If someone intercepts the app db account, the surface area exposed to an attack is much less then when tables and views aren't exposed. What security advantages would a non sp solution offer (or not)? I see many advantages to using a non sp solution, but exposing all the tables leaves me a little worried.

The question is for major database vendor products in general but specifically, sql server 2008.

Answer 1

From a security point of view only, I can't see any advantages a non-SP approach would have over an SP approach because:

• you have to grant permissions directly to the underlying tables etc
• with a sproc, all the real-underlying schema information can be encapsulated/hidden away (SPs can be encrypted too)

Answer 2

Let's take a system that needs to be really secure, say your company's accounting system. If you use procs and grant access only to the procs, then users cannot do anything other than what the proc does, ever. This is an internal control designed to make sure that the business rules for the system cannot be gotten around by any user of the system. This is what prevents people from making a company purchase and then approving the funds themselves opening up the door to fraud. This also prevents many people in the organization from deleting all records in the accounts table because they do not have delete rights except the ones granted from the proc which will allow only one delete at a time.

Now developers have to have more rights in order to develop, but they should not have more rights on a production machine ever if you want to consider security. True a developer could write a malicous sp which does something bad when put to prod. This same developer though could put the same code into the application version and be as likely to be caught or not causght as if they maliciously change a proc. Personally I think the proc might be easier to catch because it might get reveiwed separately from the code by the dbas which might mean the manager or configuration management person and the dbas had a chance to look at it vice just the manager or configuration management person. We all know reality is that no one pushing code to prod has the time to review each piece of it personally, so hiring trustworthy developers is critical. Having code review and source control in place can help find a malicious change or roll it back to a previous version but the use of sps vice application code are both at risk from developers no matter what.

The same is true for system admins. The must have full rights to the system in order to do their jobs. They can potentially do a lot of damage without being caught. The best you can do in this case is limit this access to as few people as possible and do the best you can in hiring trustworthy people. At least if you have few people with this access, it is easier to find the source of the problem if it occurs. You can minimize risk by having off-site backups (so at least what the admin breaks if they turn bad can be fixed to some extent) but you can never completely get rid of this risk. Again this is true no matter what way you allow the applications to access data.

So the real use of sps is not to eliminate all possible risk, but to make it so fewer people can harm the system. The use of application code to affect database information is inherently unsecure and in my opinion should not be allowed in any system storing financial information or personal information.