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

Well, I guess you really captured the core of the problem yourself: if you don't use stored procedures for all CRUD operations, you have to grant at least a app-specific db user account at least SELECT rights on all tables.

If you want to allow the db account to do even more work, that account might also need other permission, like being able to UPDATE and possibly DELETE on certain tables.

I don't see how a non-stored proc approach would have any security benefits - it does open up the gate just a bit more, the question really is: can you afford to? Can you secure that app-specific DB account enough so it won't compromise your system's overall security?

One possible compromise might be to use views or table access to allow SELECT, but handle everything else (UPDATEs, DELETEs, INSERTs) using stored procs - half secure, half convenient...

As it often is - this is a classic trade-off between convenience (non-sp approach; using an ORM possibly) and security (all SProc approach; probably more cumbersome, but a bit safer).

Marc

Answer 2

This is one of those areas where conventional wisdom is correct: exposing just the stored procedures gives you more control over security. Giving direct access to tables and views is easier, and there are times you need to do it, but it's going to be less secure.

Answer 3

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 4

The biggest security advantage to not using stored procedures is clarity. You know exactly what an account can do, by seeing what access to tables it has. With stored procedures, this isn't necessarily the case. If an account has the ability to execute procedure X, that does limit the account to executing that and not hitting an underlying table, but X can do anything. It could drop tables, alter data, delete data etc.

To know what an account can do with stored procedures you have to look at the stored procedure. Each time a sproc is updated, someone will have to look at what it does to make sure that something didn't get "accidentally" placed in it. The real problem with security in sprocs comes from inside the organization, not from rogue attackers.

Here's an example:

Let's say you are trying to restrict access to the employee table. Without stored procedures, you just deny access to the table. To get access someone pretty much has to blatantly ask you to grant permissions. Sure they could get you to run a script to grant access, but most people at least try to review a script which alters the database schema (assuming the script doesn't update a sproc, which I will talk about below).

There are potentially hundreds of stored procedures for an application. In my experience, they get updated quite frequently, add a field here, delete one there. For someone to review the number of update procedure scripts all the time becomes daunting, and in most organizations the database team starts to only quickly look at the procedure (or not look at it all), and move it along. This is where the real problem comes in. Now, in this example, if someone on the IT staff wants to allow access to a table, that person just needs to slip in a line of code granting access or doing something else. In a perfect world this would get caught. Most of us don't work in a perfect world.

The real problem with stored procedures is that they add a level of obfuscation to the system. With obfuscation comes complexity, and with complexity comes ultimately more work to understand an administrate the underlying system. Most people in IT are overworked and things slip through. In this instance you don't try and attack the system to gain access, you use the person in charge of the system to get what you want. Mitnick was right, in security people are the problem.

The majority attacks against an organization come from the inside. Any time you introduce complexity into any system, holes appear, things can get overlooked. Don't believe it, think about where you work. Go through the steps about who you would ask to get access to a system. Pretty soon you realize that you can get people to overlook things at the right moment. The key to successfully penetrating a system with people involved is to do something which seems innocuous, but is really subversive.

Remember, if I am trying to attack a system: I am not your friend; I have no interest in your kids or hobbies; I will use you in any way necessary to get what I want; I don't care if I betray you. The idea of "but he was my friend and that's why I trusted him to believe what he was doing was correct," is no comfort after the fact.

Answer 5

In addition to the traditional security separation with stored procedures (EXEC permission on procedures, rely on ownership chaining for data access) stored procedures can be code signed, resulting in very granular and specific access control to any server functionality like linked servers, server scoped management views, controlled access to stored procedures and even data in other databases outside of user ordinary access.

Ordinary requests made in T-SQL batches, no matter how fancy and how many layer upon layers of code generation and ORM are behind it, simply cannot be signed and thus cannot use one of the most specific and powerful access control mechanisms available.

Answer 6

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.

Answer 7

It's an imperfect analogy, but I like to compare the tables in the DB's "dbo" schema to "private" data in OO terminology, and Views and Stored Procs to "public." One can even make a "public" schema separate from the dbo schema to make the distinction explicit. If you follow that idea, you get a security advantage as well as an extensibility advantage.

One account (not the web app's account) has dbo access and owns the database, and the web app connects using another account restricted to the public-facing structures.