Database permissions and ORMs

Question

I've been using .NET's Entity Framework a lot lately and have absolutely no wish to go back to using Stored Procedures. Been shocked though that the company I'm building this project for had a policy where applications were only given accounts that only had permissions to access stored procedures!

Apparently, they believe that there's a security risk involved in allowing applications to access the tables/views directly. I don't get this.

1. My first question is, can someone enlighten me as to what kind of security risk applications having direct access to the database may pose? AND
2. If that's the case, are there any other ORM solutions that can provide a workaround to this (I can't think of any logical possibility atm) that would allow me to circumvent the restrictions on the user account to be assigned to me? OR is my understanding that I'd need direct permissions for the tables and views wrong?

Answer 1

Idiotic limited thinking - unless they put the complete access logic into the daabase.,

http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

has a good explanation on why security is NOT a reason. As I said - unless the complete business logic validating who sees what is in the database.... which is then not a multi tier application anymore.

Answer 2

When you think about it, in a certain context, limiting access to stored procedures makes a lot of sense. The procedures expose an API and handle processing (e.g. check complex constraints) which is in theory fine. There's no easy way to declare cross-column constraints like "if column A is null, column B should be one of {X, Y, Z}". Multiple applications might be using the procedure API and all could benefit from the procedures making sure the data is treated the right way.

However, anyone who has tried to write a lot of logic in the database and a lot of logic in an general purpose OOP language knows that the former tends lead to an unmaintainable, DB-locked mountain of unintelligable code, while the later is generally recognized as "the way to write complex applications/systems".

While the stored procedure API approach is far from extinct, I would be genuinely surprised to see a new project started using this pattern. ORMs are far from perfect, but they do provide huge benefits which are more and more taken for granted: the whole application can be written in one language (Python, Java, Groovy, Ruby...), you can usually switch the DBMS in a few minutes (which works wonders e.g. when you're running tests on hsqldb, but use postgresql in production), data packaging from and to the database is a lot simpler (the ORM usually returns domain objects, rather than primitives), there are caching advantages etc.

In light of this, it is perfectly acceptable that an application have full CRUD access to everything in it's database. Also, if you have an account which only allows you to invoke stored procedures, I wouldn't recommend spending time looking into how you can circumvent the access rights: a better use of your time would be to argue for CRUD table access privileges.