I asked this question at stack overflow but I haven't been getting as much response as I was hoping, maybe this community has more experience.
From what I understand of conventional wisdom you should create stored procedures for insert, update, and delete operations. This creates a layer of abstraction and security between the database and application developers. Stored procedures are also useful to eliminate or reduce the need for 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.
asked Mar 18 '11 at 09:13 AM in Default
You have one direct question, but I think your overall statement implies many more topics worth looking at.
The first thing to note when considering security is that it should generally be a "defense in depth." I happen to have written about this recently in the context of SQL Injection attacks in particular at http://www.simple-talk.com/sql/learn-sql-server/sql-injection-defense-in-depth/.
Now, just "using views" will not increase your security at all. If your view is something like
And you give someone unrestricted access to this view then they can update to it, insert to it, and delete from it and it will pass through to affect the underlying table. You only need the instead of trigger you mentioned to make a view updatable if there is something about the view that would make it not updatable directly. See http://msdn.microsoft.com/en-us/library/ms187956.aspx. Views certainly can play a role in security, but they do so by limiting columns or rows that can be accessed through them. They are only useful as part of a security plan in conjunction with properly administered permissions/triggers that limit what can be done through them.
Similarly just "using stored procedures" will not improve your security at all. For a SQL Injection perspective on this, take a look at http://blogs.msdn.com/b/brian_swan/archive/2011/02/16/do-stored-procedures-protect-against-sql-injection.aspx. Again, stored procedures can be a useful part of an overall security plan, but they only increase security if combined with properly set permissions and other techniques that compliment them.
Now, as for triggers, they represent "hidden code". In other words, it may not be readily obvious to other developpers when the trigger will fire and affect the work they are doing. This alone means you should be wary of them. They certainly can have a place in a well designed database (I have occassionally used them to permit updates through a view, and very frequently use them for logging purposes), but the fact they are "hidden code" by itself means you should use them with caution.
I know that none of this directly answers your question, but hopefully it will help.
answered Mar 18 '11 at 01:40 PM
+1 to Timothy, but, to answer you directly, I don't think it would make much difference. Yes, you could use instead of triggers to perform the logic that you would usually do in a stored procedure for the particular DML operation - but because the trigger has to be multi-row based, it's likely to be harder to write. Debugging the sort of procedural code you might find in a CRUD procedure is way easier than doing the same in a trigger, IMHO.
So, yes, you're saving yourself having to map 1 out of 3 objects. But, the consequential work is probably much greater than the saving you made.
answered Mar 18 '11 at 01:55 PM
Matt Whitfield ♦♦
I second what @TimothyAWiseman has written. This is generally a smart idea. :-)
In the Oracle world (at least back when I was in the Oracle world), a very common method of security was to have a large number of different views, which provided subsets of columns and filtered access to tables. End users (even those who connected to the server using Toad or SQL*Plus) usually did not get base table access unless they needed it. This sounds very similar to your proposal (with all of the triggers being optional), and it can certainly work--it did for the folks who implemented this strategy, and SQL Server has similar tools.
But part of the reason they did this was because stored procedures didn't work quite the same way (at least from what I remember), and Oracle never pushed stored procedures--they preferred view/table access and PL/SQL functions. Microsoft has pushed stored procedures hard. Honestly, though, they're just two means to the same end. Stored procedures versus view-plus-trigger means nothing if you give everybody db_owner rights on the database, and if you give them the correct rights, either is absolutely workable. In fact, there's no reason you couldn't do both: stored procedures which update the views, which in turn call instead-of triggers to update the base tables. It's paranoia and probably overkill, though.
In the end, though, I believe that your architecture proposal would probably be more a little more limiting and a bit more confusing than you think. You may end up using views for more than just limitation--you perhaps would also use them for read-only expansions (e.g., joining several tables, including lookup tables, together to help form objects). In that case, you would need a way of clarifying which views have triggers and which don't. Also, the trouble with triggers, even without getting into multi-table trigger-triggering, is that they are easy to write for the single-row case, but people often forget to think of multi-record scenarios, or cases where most of the records work but some fail. That's an additional set of test cases to write for each object, whereas for a stored procedure, you don't need to worry about it so much, especially for basic one-record CUD procs.
If you have a smallish project, it might be good to experiment, however. There's nothing saying that you couldn't possibly get that setup to work, or that it wouldn't scale, etc. etc. I've seen this in action and it does work. But it's not really inherently more or less secure than using stored procedures, though, so that wouldn't be much of a reason to do this.
answered Mar 18 '11 at 02:23 PM