x

Using views & triggers for security instead of stored procedures

I asked this question at [stack overflow][1] 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.

[1]: http://stackoverflow.com/questions/5353277/using-views-triggers-for-security-instead-of-stored-procedures
more ▼

asked Mar 18, 2011 at 09:13 AM in Default

kelloti gravatar image

kelloti
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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

Creat view vwOnTable
as
select [list all columns]
from table

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][3]. 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.

[3]: http://blogs.msdn.com/b/brian_swan/archive/2011/02/16/do-stored-procedures-protect-against-sql-injection.aspx
more ▼

answered Mar 18, 2011 at 01:40 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.6k 21 23 32

@TimothyAWiseman - thanks for the answer and reading material. Do you think triggers would be less "hidden" if we used a naming convention to differentiate updatable views from reporting/aggregate views?
Mar 18, 2011 at 03:04 PM kelloti

If you use a consistent naming structure? Partially. The problem is that, with a name, you can tell that there is a trigger, but not what the trigger does. That trigger could do a number of things, and thus could have undesirable side effects.

Risk mitigation strategies, such as limiting what is allowable inside a trigger (sort of like what you described above in your question) can help, but a trigger will always be relatively more opaque than a stored procedure or a SQL statement generated inside a data layer.
Mar 18, 2011 at 03:23 PM Kevin Feasel

Yes, I think that would help. With that said, it is very easy to forget that a trigger is there and I have personally spent many hours trying to explain wierd occurrences before I finally tracked down the trigger that was causing it.

In short, triggers can be extremely helpful for certain things (logging for instance), but I would generally be fairly careful about using them.
Mar 18, 2011 at 03:25 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

+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.
more ▼

answered Mar 18, 2011 at 01:55 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

I also second what @Matt Whitfield has written. Again, this is a smart idea, especially when it takes me 5 times as much space to say almost exactly what he says...
Mar 18, 2011 at 02:24 PM Kevin Feasel
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Mar 18, 2011 at 02:23 PM

Kevin Feasel gravatar image

Kevin Feasel
6.1k 3 5 11

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1850
x415
x163
x121
x33

asked: Mar 18, 2011 at 09:13 AM

Seen: 3162 times

Last Updated: Mar 18, 2011 at 09:13 AM