Using views & triggers for security instead of stored procedures

Question

Conventional wisdom says that a secure application should create stored procedures for insert, update, and delete operations. You would also use stored procedures so you don't have to use triggers, thereby avoiding some common pitfalls of triggers.

Another thought is to cover the whole database with views - so hardly anyone has access to tables themselves, they just do CRUD operations against views. That way, if you want to give someone access to certain columns, you can create a view for them that contains those columns, or just a computation. If you need to impose logic on update & delete operations (i.e. preventing someone from affecting more than 2% of the total rows in a table) you can do this via an instead of trigger.

In order to not fall into common pitfalls of triggers, (1) triggers should only update tables, never other views. (2) Triggers are never put on tables. (3) Views can't access other views. (4) If for some reason you can't do what you want by following the first three rules, create a stored procedure.

The benefit I see from implementing security this way is that you only have to create a view and maybe some triggers (average case - 2 additional objects) whereas if you go the stored procedure route you will always be creating at least 3 or 4 additional objects (depending if you create procedures for select). Also, our NHibernate mappings would be simpler because we wouldn't have to map three procedures for every object.

The question is if there are significant security holes or practical problems with using mostly views and triggers rather than stored procedures.

Answer 1

Personally I tend to be in favor of using stored procedures for application access to a database. I generally use a generic set of procedures which manage operations against each table and ensure data integrity. All decisions regarding who can see or edit a given piece of information are handled in the application itself. Only the app credentials have access to the database, any users that happen to find their way there are locked out, or have read only access at most.

I tend to dislike triggers that implement business logic because they are easy to miss when you are trying to debug something. If all of my insert logic is wrapped in a SP I can pull up that SP and trace through it to diagnose why an insert is failing. If there is also a trigger on the table which I have forgotten about or was unaware of, it may be a while before I realize it and remember to look there. (though this is likely just a factor of my environment, if more triggers were used here I am sure they would be more prominent in my thought processes)

But I am not entirely sure we are looking at this from the same perspective. If you are going to be creating different views each set of users then it sounds like they have direct access to the data store and are not working through an application interface? If there was an application interface for this structure wouldn't it have to be updated and recompiled for each group of users in order to make use of the correct views?

I guess I am saying that stored procs work well for supporting an application interface or pre built reports, but the views and triggers may be a better option when users have read/write access to a common data store without an application interface in the middle.

I would also point out that there is the consideration of what is the standard for your shop. If everyone else there uses stored procs exclusively, and you take another approach, then anyone else trying to come around later and maintain your solution is going to have a hard time.

In the end if it accomplishes the job without being a pain to maintain or causing other problems then it was a good solution.

Answer 2

Ther is nothing inherently wrong with using triggers and views. As with other code they have to be correctly written and often suffer a poor reputation because so many poor developers have written bad code for them. For instance you don't want views to call other views because performance can suffer. You don't want triggers that assume only one row will be affected in the update/delete/insert.