Re: Proposal for 2.0: SODA 2.0

["Adam Thompson" <..hidden..>]

Many of the data integrity constraints can be embedded as CHECK or referential constraints in the database schema; this alone can minimize the *possibility* of someone changing data in the wrong way.  For those transactions that have to be multi-step, e.g. GL transactions, it makes perfect sense to wrap the INSERTs in a stored procedure.  Also, DEFER constraints can mitigate this problem (rules are only checked at COMMIT time, not at INSERT/UPDATE/DELETE time).

I think there are already too many stored procedures; using SPs as a means of enforcing security doesn't have mainstream traction outside traditional Sybase (and MS SQL) shops.  I feel that practice encourages developers to make the DB into a black box: the "you're not good enough to see MY tables" mindset.  Which (IMHO) leads quickly to a bad case of Not-Invented-Here syndrome.

Leave the DB structure as open as possible, enforcing security with the most direct tools the DB gives us for that task, *removing* an extra access layer where needed.  VIEWs are the mechanism for projecting complex relationships in an understandable way.
The other upside is that declarative security can be auto-discovered and auto-documented.  Procedural security cannot.  Maintenance down the road is somewhat easier declaratively, too, for the same reasons of discoverability.

I've been programming, managing, using and teaching RDBMSes for nigh 20 years now: using stored procedures as a security mechanism is often (but not always, by any means!) associated with programmers or DBAs looking for job security, not about transparency or doing things the right way.  You might say I've grown a strong bias against the technique as I've observed it and managed it over the years.
That's not to say SPs have no place - they are an *excellent* hammer... when you're dealing with nails, not screws.

-Adam