Re: Data Constraints Vs Application Constraints

From: FrankHamersley <FrankHamersleyZat_at_hotmail.com>
Date: Mon, 14 Mar 2005 11:31:28 GMT
Message-ID: <kCeZd.197522$K7.80454_at_news-server.bigpond.net.au>


DA Morgan wrote:
> lennart_at_kommunicera.umea.se wrote:
>

>> If possible, setup a copy of the db somewhere (might not be possible
>> due to size and platform), and try to add as many f.k and check
>> constraints that time admits. If the database have been around for a
>> while, you are very likely to find violations in the data. That should
>> indicate why dataintegrity rules should be enforced in the database.
>>
>> Main argument for your position is of course that data is better
>> protected that way (as indicated above). Other arguments that come to
>> mind are, it is much easier to maintain and understand.
>>
>> Just my 2 skr thoughts

>
> I'll add one more that is highly significant.
>
> For every front-end developer that thinks they have coded application
> constraints that are air-tight ... there is some guy with a tool, that
> can connect to the database, and make ad hoc changes. Think every DBA,
> every developer, every hacker, and lots of angry disgruntled employees.

<heretic> Seems to be happening a bit lately - I claim Devil's Advocate status to enjoy your tolerance!

Not quite sure what you are suggesting (on second reading) but my first instinct was that you infer embedded inviolable constraints prevent illicit activity to tables! I'll respond to that but feel free to qualify more accurately if I was wrong to make that leap.

Constraints do not provide any data security at all - sure it stops the miscreant from inserting "unreferenced" or "unreferencable" rows, but in the absence of object ACL's it still allows the unauthorised or unsanctioned manipulation of data by a non app based connection. Might be a bit harder to pull off, but its not that difficult to reverse engineer the hoops that have to be lept through.

Whilst I accept the view that the app level integrity is only as good as the coder (so are the triggers and stored procs as it happens) care use of the ACL mechanisms provided by modern DBMS's provide security.

Personally, my current bent is to favour no access at all to tables (well perhaps allow read to selected reporters) and control all through stored procs/triggers and perhaps at a pinch some constraints.

</heretic> Cheers, Frank. Received on Mon Mar 14 2005 - 12:31:28 CET

Original text of this message