Where to do Data Validation?
Vijai Kalyan
I have a question about data validation. I have been reading some
articles that indicate that data validation should be done at the
application level (while also having integrity constraints in the
database) whereas other articles mention that data validation should
be done only at the database level because that's what integrity
constraints are for.
Any for or against on these two viewpoints?
Thanks,
Vijai.
Mark D Powell
Where data should be validated depends on the data and the
application. In many cases data should be validated by both the
application and the database.
The application should usually provide all necessary edits for data
being of the right type and value ranges. It should pretty well be
impossible to enter orders for non-existent customers or suppliers or
to enter 8 digits for a column that supports only 6 digits via online
screens (web pages).
At the same time referential integrity constraints should definitely
be defined in the database. This way any checks the application
misses or does not include will be made by the database. Plus the
database level constraints will protect the data integrity from batch
and non-application data entry points. (FTP files loaded via sqlldr
or batch programs) The database level integrity checks will also
protect the data from many code design errors especially once the
original team moves on and new people less familiar with the
application take over support and enhancements.
So my answer is that both the application and the database contain
integrity checking.
HTH -- Mark D Powell --
Frank van Bortel
As no application outlives data, you should always
perform business rule checking, referential constraints
and any other constraint checking there, where your
data will reside: in the database.
Now, probably a lot of your customers will find it
very annoying that -after filling in dozens of
fields- you present them with a blanked screen, and
the error line: "Invalid date; please input valid date".
They will ask 'what date, where'? Hence it may be a
good idea to insert some user-friendliness and check
inputs *the moment they are entered*, too.
Of course, some environments (HTML) cannot do that,
so you'll have to revert to tricks - JavaScript is
such a trick.
So the answer is: both. Unless you want to create
a non-sellable application :)
--
Regards,
Frank van Bortel
Top-posting is one way to shut me up...
Walt
As Mark and Frank suggest, do both. If you have to pick one or the
other, definitely do it at the database. Fortunately, you don't have to
choose one or the other.
My take is that you do validation at the database to enforce data
integrity, and you do validation at the application level to provide a
more pleasant experience for the user.
//Walt
DA Morgan
Always both. Though the validation checks may be different depending
on the location where the check takes place.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Galen Boyer
A very solid way to think is that the database defines your data
validations and then, you author a meta reader that reads the metadata
from the database and produces code for the front-end that then is used
at runtime to check the same things. Defined in one place and then
replicated by code into the place directly next to the user.
Not all in the database will make it into the GUI tier and not all
checks needed in the GUI will have a place in the database, but it sure
does solve most of what you are looking for.
--
Galen Boyer
Brian Tkatch
If the database is just a convenient oplace to store data, and the
program is what matters, put the validation in the program.
If the program is just a convenient way to work with the data, and the
database is what matters, put the validation in the database.
B.
fitzj...@cox.net
On Nov 20, 8:09 am, Brian Tkatch <N/A> wrote:
> On Mon, 19 Nov 2007 09:48:12 -0800 (PST), Vijai Kalyan
>
> >Hi All,
>
> >I have a question about data validation. I have been reading some
> >articles that indicate that data validation should be done at the
> >application level (while also having integrity constraints in the
> >database) whereas other articles mention that data validation should
> >be done only at the database level because that's what integrity
> >constraints are for.
>
> >Any for or against on these two viewpoints?
>
> >Thanks,
>
> >Vijai.
>
> If the database is just a convenient oplace to store data, and the
> program is what matters, put the validation in the program.
>
And if this conveniently stored data is 'hosed' how will the
application function? By not validating in BOTH places regardless one
runs the risk of application failure and lost work.
> If the program is just a convenient way to work with the data, and the
> database is what matters, put the validation in the database.
And, yet, why should the users be subjected to what can be 'terse' and
'cryptic' error messages from Oracle when the application can, and
should, validate the input data for proper format? Validation should
occur here, too, regardless.
The answer to the question, then, is validate in both, and there is no
conditional response to that. Failure to do so is carelessness and
poor design, in my opinion.
>
> B.
David Fitzjarrell
Brian Tkatch
>Comments embedded.
>On Nov 20, 8:09 am, Brian Tkatch <N/A> wrote:
>> On Mon, 19 Nov 2007 09:48:12 -0800 (PST), Vijai Kalyan
>>
>> <vijai.kal...@gmail.com> wrote:
>> >Hi All,
>>
>> >I have a question about data validation. I have been reading some
>> >articles that indicate that data validation should be done at the
>> >application level (while also having integrity constraints in the
>> >database) whereas other articles mention that data validation should
>> >be done only at the database level because that's what integrity
>> >constraints are for.
>>
>> >Any for or against on these two viewpoints?
>>
>> >Thanks,
>>
>> >Vijai.
>>
>> If the database is just a convenient oplace to store data, and the
>> program is what matters, put the validation in the program.
>>
>
>And if this conveniently stored data is 'hosed' how will the
>application function? By not validating in BOTH places regardless one
>runs the risk of application failure and lost work.
If the database is just a convenient place to store data, it is the
equivalent of having a flat file with speedy querying. What protection
is there against a flat file? Probably backups. Same here.
>> If the program is just a convenient way to work with the data, and the
>> database is what matters, put the validation in the database.
>
>And, yet, why should the users be subjected to what can be 'terse' and
>'cryptic' error messages from Oracle when the application can, and
>should, validate the input data for proper format? Validation should
>occur here, too, regardless.
Methinks you have never seen error messages before. Most of the time
the application is even more cryptic!
>The answer to the question, then, is validate in both, and there is no
>conditional response to that. Failure to do so is carelessness and
>poor design, in my opinion.
I do not disagree. But i have yet to see it happen.
B.
>
>>
>> B.
>
>
>David Fitzjarrell
fitzj...@cox.net
On Nov 20, 10:12 am, Brian Tkatch <N/A> wrote:
> On Tue, 20 Nov 2007 07:49:28 -0800 (PST), "fitzjarr...@cox.net"
>
>
>
>
>
> <fitzjarr...@cox.net> wrote:
> >Comments embedded.
> >On Nov 20, 8:09 am, Brian Tkatch <N/A> wrote:
> >> On Mon, 19 Nov 2007 09:48:12 -0800 (PST), Vijai Kalyan
>
> >> <vijai.kal...@gmail.com> wrote:
> >> >Hi All,
>
> >> >I have a question about data validation. I have been reading some
> >> >articles that indicate that data validation should be done at the
> >> >application level (while also having integrity constraints in the
> >> >database) whereas other articles mention that data validation should
> >> >be done only at the database level because that's what integrity
> >> >constraints are for.
>
> >> >Any for or against on these two viewpoints?
>
> >> >Thanks,
>
> >> >Vijai.
>
> >> If the database is just a convenient oplace to store data, and the
> >> program is what matters, put the validation in the program.
>
> >And if this conveniently stored data is 'hosed' how will the
> >application function? By not validating in BOTH places regardless one
> >runs the risk of application failure and lost work.
>
> If the database is just a convenient place to store data, it is the
> equivalent of having a flat file with speedy querying. What protection
> is there against a flat file? Probably backups. Same here.
>
No, it is not, it is a (relatively) relational repository for said
data,. and as such should implement data validation policies.
> >> If the program is just a convenient way to work with the data, and the
> >> database is what matters, put the validation in the database.
>
> >And, yet, why should the users be subjected to what can be 'terse' and
> >'cryptic' error messages from Oracle when the application can, and
> >should, validate the input data for proper format? Validation should
> >occur here, too, regardless.
>
> Methinks you have never seen error messages before.
Think again, as your last attempt missed the mark by a considerable
distance.
> Most of the time
> the application is even more cryptic!
A testament to poor design and implementation, which, in my opinion,
you suggested in your original response.
>
> >The answer to the question, then, is validate in both, and there is no
> >conditional response to that. Failure to do so is carelessness and
> >poor design, in my opinion.
>
> I do not disagree. But i have yet to see it happen.
>
You don't work for a living? I do and, in general, error messages
from a user interface are less likely to be as confusing as those
sometimes produced by Oracle. Simply because you can't author a user-
friendly block of error text doesn't mean someone else can't.
> B.
>
>
>
>
>
> >> B.
>
> >David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
David Fitzjarrell
gazzag
> A very solid way to think is that the database defines your data
> validations and then, you author a meta reader that reads the metadata
> from the database and produces code for the front-end that then is used
> at runtime to check the same things. Defined in one place and then
> replicated by code into the place directly next to the user.
>
> Not all in the database will make it into the GUI tier and not all
> checks needed in the GUI will have a place in the database, but it sure
> does solve most of what you are looking for.
>
> --
> Galen Boyer
I agree. I also think that one should be mindful of the difference
between data "validation" and data "verification". That is to say,
"validation" is to validate the data on input. e.g. yes, the
underlying datatype in the database may well be a date datatype but
the application is expecting, for example, three pairs of two numbers
to signify DDMMYY. If the format is valid it will be passed to the
database for "verification". That is to say: yes, that's a valid date
but does it make sense, in this context, with our business rules and
logic as enforced by database design and the use of constraints.
As a _general_ rule, validation is done at application level;
verification at database level.
HTH
-g
Brian Tkatch
>> Most of the time
>> the application is even more cryptic!
>
>A testament to poor design and implementation, which, in my opinion,
>you suggested in your original response.
True, and intended. If the DB is "just a convenient place to store
data", there isn't a good design anyway. Hence, my comment.
>
>>
>> >The answer to the question, then, is validate in both, and there is no
>> >conditional response to that. Failure to do so is carelessness and
>> >poor design, in my opinion.
>>
>> I do not disagree. But i have yet to see it happen.
>>
>
>You don't work for a living? I do and, in general, error messages
>from a user interface are less likely to be as confusing as those
>sometimes produced by Oracle.
As those *sometimes* produced by Oracle. In most cases, well, in most
of my cases, the error is a CONSTRAINT error, and if it was
intellegently named, it is very informative.
A programmatical error usually hides the real error and displays what
the programmer assumed caused it. In which case only an strace, code
review, or Googling seems to be the answer to that.
> Simply because you can't author a user-
>friendly block of error text doesn't mean someone else can't.
Of course they can. It's just rarely found in the real world.
B.
DA Morgan
Your first paragraph is frighteningly bad advice.
First what is the purpose of validation? There are two possible answers:
1. To protect the integrity of the data
2. To improve the efficiency of other error handling mechanisms by
putting the validation closer to the source of the error ... for
example into the front-end user interface.
In no case can a validation in a front-end or tool protect data in a
database from corruption ... EVER ... unless the quality of the data
is guaranteed by the database itself.
Putting validation into the application will never, EVER, keep someone
with SQL*Plus from destroying it.
Reconsider.
DA Morgan
>
> If the database is just a convenient place to store data, it is the
> equivalent of having a flat file with speedy querying. What protection
> is there against a flat file? Probably backups. Same here.
Nonsense. Total and complete nonsense. What you've written is roughly
the equivalent of "if the car is just a way to move from place to place
it doesn't need an emergency brake."
Your statement is totally irresponsible. If someone can use 3x5 cards
then by all means they should use 3x5 cards. If they have purchased
Oracle licenses and hired trained Oracle professionals their data, by
definition, is more important than your mother's cookie recipes.
>>> If the program is just a convenient way to work with the data, and the
>>> database is what matters, put the validation in the database.
>> And, yet, why should the users be subjected to what can be 'terse' and
>> 'cryptic' error messages from Oracle when the application can, and
>> should, validate the input data for proper format? Validation should
>> occur here, too, regardless.
>
> Methinks you have never seen error messages before. Most of the time
> the application is even more cryptic!
Irrelevant and offensive nonsense.
>> The answer to the question, then, is validate in both, and there is no
>> conditional response to that. Failure to do so is carelessness and
>> poor design, in my opinion.
>
> I do not disagree. But i have yet to see it happen.
Then you live in a cave.
DA Morgan
> True, and intended. If the DB is "just a convenient place to store
> data", there isn't a good design anyway. Hence, my comment.
All databases are intended to be a convenient way to store data. What
other possible purpose can they serve?
Were you in my program at UW I'd call you into my office to discuss
leaving the program. You are giving serious indications that you just
don't understand the role of IT in the current millennium.
Brian Tkatch
wrote:
>Brian Tkatch wrote:
>
>> True, and intended. If the DB is "just a convenient place to store
>> data", there isn't a good design anyway. Hence, my comment.
>
>All databases are intended to be a convenient way to store data. What
>other possible purpose can they serve?
To define the data model.
>Were you in my program at UW I'd call you into my office to discuss
>leaving the program. You are giving serious indications that you just
>don't understand the role of IT in the current millennium.
You wouldn't have time to. I would have left much earlier due to your
sour attitude and willingness to belittle others.
Fortunately, in the NG, i can ignore most of that (hard as it is) and
get to your more infromative posts, where you can really shine.
B.
Brian Tkatch
wrote:
>Brian Tkatch wrote:
>>
>> If the database is just a convenient place to store data, it is the
>> equivalent of having a flat file with speedy querying. What protection
>> is there against a flat file? Probably backups. Same here.
>
>Nonsense. Total and complete nonsense. What you've written is roughly
>the equivalent of "if the car is just a way to move from place to place
>it doesn't need an emergency brake."
No, it is not.
To use your analogy, it would be like saying "if a person bought the
car to move from place to place, but won't learn how to drive, it
would be better to ride a bicylce."
<SNIP>
B.
Brian Tkatch
wrote:
>Brian Tkatch wrote:
>> On Mon, 19 Nov 2007 09:48:12 -0800 (PST), Vijai Kalyan
>> <vijai....@gmail.com> wrote:
>>
>>> Hi All,
>>>
>>> I have a question about data validation. I have been reading some
>>> articles that indicate that data validation should be done at the
>>> application level (while also having integrity constraints in the
>>> database) whereas other articles mention that data validation should
>>> be done only at the database level because that's what integrity
>>> constraints are for.
>>>
>>> Any for or against on these two viewpoints?
>>>
>>> Thanks,
>>>
>>> Vijai.
>>
>> If the database is just a convenient oplace to store data, and the
>> program is what matters, put the validation in the program.
>>
>> If the program is just a convenient way to work with the data, and the
>> database is what matters, put the validation in the database.
>>
>> B.
>
>Your first paragraph is frighteningly bad advice.
>
>First what is the purpose of validation? There are two possible answers:
>1. To protect the integrity of the data
>2. To improve the efficiency of other error handling mechanisms by
> putting the validation closer to the source of the error ... for
> example into the front-end user interface.
Had i been in your class, i would have added a third option to this
test:
3. To define the data model, to make the db the authority on the data.
>In no case can a validation in a front-end or tool protect data in a
>database from corruption ... EVER ... unless the quality of the data
>is guaranteed by the database itself.
Although, i would promote that statement as encouragement, it is not
correct. In a controlled environment, the UI can be made to be the
only path of data entry, as such, it can do the data integrity
checking.
In many cases, and sadly most cases i have seen, the DB is merely a
convenient place to store data, and basically all checking was done in
the UI code. And that was what the programmers wanted. Mostly because
it was easy to understand, and could be changed without a change
request to the DBAs which is usually more pain than it is worth. To
that end, COLUMNs are added and reused, and rarely DROPped, just to
not have to bother with the DBA group.
>Putting validation into the application will never, EVER, keep someone
>with SQL*Plus from destroying it.
Unless it is a controlled environment.
While i understand your sentiment--indeed, they are my feelings as
well--it just is not that way in the "real world" when the programmer
considers the DB just a convenient place to store data.
B.
>
>Reconsider.
DA Morgan
> On Tue, 20 Nov 2007 10:00:37 -0800, DA Morgan <damo...@psoug.org>
> wrote:
>
>> Brian Tkatch wrote:
>>
>>> True, and intended. If the DB is "just a convenient place to store
>>> data", there isn't a good design anyway. Hence, my comment.
>> All databases are intended to be a convenient way to store data. What
>> other possible purpose can they serve?
>
> To define the data model.
Seek professional help.
DA Morgan
>> Your first paragraph is frighteningly bad advice.
>>
>> First what is the purpose of validation? There are two possible answers:
>> 1. To protect the integrity of the data
>> 2. To improve the efficiency of other error handling mechanisms by
>> putting the validation closer to the source of the error ... for
>> example into the front-end user interface.
>
> Had i been in your class, i would have added a third option to this
> test:
>
> 3. To define the data model, to make the db the authority on the data.
This is fluff and you know it. Data is data is data. The fact that some
may be metadata for another system is irrelevant and an attempt to
change the subject.
Databases hold data in logical structures called tables.
The data they hold can either have meaning or not have meaning.
If the intent is for it to have meaning that meaning must be defined
and constrained within the structure of the database.
An external application can never guarantee the integrity of data
whether or not it may be metadata to another system.
You get a 68 for your effort.
>> In no case can a validation in a front-end or tool protect data in a
>> database from corruption ... EVER ... unless the quality of the data
>> is guaranteed by the database itself.
>
> Although, i would promote that statement as encouragement, it is not
> correct. In a controlled environment, the UI can be made to be the
> only path of data entry, as such, it can do the data integrity
> checking.
Nonsense: Total and complete nonsense. All Oracle databases have
SQL*Plus and thus a means of accessing the data without the UI.
> In many cases, and sadly most cases i have seen, the DB is merely a
> convenient place to store data, and basically all checking was done in
> the UI code.
Sadly is not the word I would use. What you are saying is that because
bad practices exist in some organizations they should be accepted. Some
of us aim a bit higher.
> And that was what the programmers wanted.
And programmers make these decisions? Programmers? You've got to be
kidding.
> Mostly because
> it was easy to understand, and could be changed without a change
> request to the DBAs which is usually more pain than it is worth. To
> that end, COLUMNs are added and reused, and rarely DROPped, just to
> not have to bother with the DBA group.
Which is fascinating but irrelevant to anything else in this thread.
>> Putting validation into the application will never, EVER, keep someone
>> with SQL*Plus from destroying it.
>
> Unless it is a controlled environment.
There is no such thing and, in fact, is impossible to build. If you can
find a single computer system, running Oracle, that does not have
administrative access I'd like to hear about it.
> While i understand your sentiment--indeed, they are my feelings as
> well--it just is not that way in the "real world" when the programmer
> considers the DB just a convenient place to store data.
You have put up the impossible ... an Oracle database on a system with
no operating system admin, no storage admin, no network admin, no DBA,
and no passwords as a straw horse. As the system does not exist neither
does your argument.
Brian Tkatch
wrote:
>Brian Tkatch wrote:
>> On Tue, 20 Nov 2007 10:00:37 -0800, DA Morgan <damo...@psoug.org>
>> wrote:
>>
>>> Brian Tkatch wrote:
>>>
>>>> True, and intended. If the DB is "just a convenient place to store
>>>> data", there isn't a good design anyway. Hence, my comment.
>>> All databases are intended to be a convenient way to store data. What
>>> other possible purpose can they serve?
>>
>> To define the data model.
>
>Seek professional help.
I did, unfortunately, you responded. :P
B.
fitzj...@cox.net
Comments embedded.
On Nov 20, 12:55 pm, Brian Tkatch <N/A> wrote:
> On Tue, 20 Nov 2007 09:51:49 -0800, DA Morgan <damor...@psoug.org>
Define 'controlled environment'. Now, implement that and still give
users access. Then try to restrict what they use to access the
database. It's surprisingly easy to defeat such tactics as logon
triggers can easily be fooled by simply renaming the undesirable
application. sqlplus.exe can be renamed to anything one would want to
call it and, thus, can be used to directly access the data
circumventing exclusive use of your UI. And, as a result your data is
vulnerable to attack. So much for your 'controlled environment'.
> In many cases, and sadly most cases i have seen, the DB is merely a
> convenient place to store data, and basically all checking was done in
> the UI code.
And that's just peachy until you have simultaneous inserts into a
table and attempt to enforce a pseudo primary key or pseudo foreign
key constraint through the UI. And end up with duplicate key values
or dependent inserts failing because the newly inserted parent record
hasn't yet been committed.
> And that was what the programmers wanted.
And programmers run the show where you are? I can now understand why
data validation is so low in priority on your list.
> Mostly because
> it was easy to understand, and could be changed without a change
> request to the DBAs which is usually more pain than it is worth.
Ensuring data integrity is more of a pain than having a continuingly
running application? This sounds more like a paint and body shop than
an IT shop.
> To
> that end, COLUMNs are added and reused, and rarely DROPped, just to
> not have to bother with the DBA group.
Interesting that in the quest for 'efficiency' you place data in
misnamed columns simply to circumvent proper change control and a
logical table design/implementation. And, possibly, the DBA group is
tired of you and your antics. Is there no development or test
environment configured to facilitate such changes?
>
> >Putting validation into the application will never, EVER, keep someone
> >with SQL*Plus from destroying it.
>
> Unless it is a controlled environment.
>
Define that and implement it in a foolproof (and user-proof) manner.
Then see how much work you actually can get done.
> While i understand your sentiment--indeed, they are my feelings as
> well--it just is not that way in the "real world" when the programmer
> considers the DB just a convenient place to store data.
>
If the inmates are running the asylum you have more to worry about
than data integrity.
> B.
>
>
>
>
>
> >Reconsider.- Hide quoted text -
Brian Tkatch
wrote:
>Brian Tkatch wrote:
>
>>> Your first paragraph is frighteningly bad advice.
>>>
>>> First what is the purpose of validation? There are two possible answers:
>>> 1. To protect the integrity of the data
>>> 2. To improve the efficiency of other error handling mechanisms by
>>> putting the validation closer to the source of the error ... for
>>> example into the front-end user interface.
>>
>> Had i been in your class, i would have added a third option to this
>> test:
>>
>> 3. To define the data model, to make the db the authority on the data.
>
>This is fluff and you know it. Data is data is data. The fact that some
>may be metadata for another system is irrelevant and an attempt to
>change the subject.
Other then the redundant "Data is data is data.", i disagree with
everything you just wrote.
>Databases hold data in logical structures called tables.
>
>The data they hold can either have meaning or not have meaning.
>
>If the intent is for it to have meaning that meaning must be defined
>and constrained within the structure of the database.
>
>An external application can never guarantee the integrity of data
>whether or not it may be metadata to another system.
As a component of a controlled environment, however, it can.
>You get a 68 for your effort.
:( And here i thought i would get a 68 overall, but *at least* a 90 on effort.
>
>>> In no case can a validation in a front-end or tool protect data in a
>>> database from corruption ... EVER ... unless the quality of the data
>>> is guaranteed by the database itself.
>>
>> Although, i would promote that statement as encouragement, it is not
>> correct. In a controlled environment, the UI can be made to be the
>> only path of data entry, as such, it can do the data integrity
>> checking.
>
>Nonsense: Total and complete nonsense. All Oracle databases have
>SQL*Plus and thus a means of accessing the data without the UI.
And without a username this does what?
>> In many cases, and sadly most cases i have seen, the DB is merely a
>> convenient place to store data, and basically all checking was done in
>> the UI code.
>
>Sadly is not the word I would use. What you are saying is that because
>bad practices exist in some organizations they should be accepted. Some
>of us aim a bit higher.
And what you are saying is that because bad practices exist in some
organizations they should pretend they do not exist. Some of us aim a
bit more realistically.
>
>> And that was what the programmers wanted.
>
>And programmers make these decisions? Programmers? You've got to be
>kidding.
Umm, perhaps you need to leave you ivory tower and see the real world.
I'm not saying it should be like this, i'm saying it is like this.
>
> > Mostly because
>> it was easy to understand, and could be changed without a change
>> request to the DBAs which is usually more pain than it is worth. To
>> that end, COLUMNs are added and reused, and rarely DROPped, just to
>> not have to bother with the DBA group.
>
>Which is fascinating but irrelevant to anything else in this thread.
I was giving a reason as to why a large organization has programmers
as the DB designers. I thought a bit of reasoning was called for.
>
>>> Putting validation into the application will never, EVER, keep someone
>>> with SQL*Plus from destroying it.
>>
>> Unless it is a controlled environment.
>
>There is no such thing and, in fact, is impossible to build. If you can
>find a single computer system, running Oracle, that does not have
>administrative access I'd like to hear about it.
And i would just as well keep you ignorant of it. Who knows what you
would do!
B.
Brian Tkatch
<fitzj...@cox.net> wrote:
<SNIP>
>> >In no case can a validation in a front-end or tool protect data in a
>> >database from corruption ... EVER ... unless the quality of the data
>> >is guaranteed by the database itself.
>>
>> Although, i would promote that statement as encouragement, it is not
>> correct. In a controlled environment, the UI can be made to be the
>> only path of data entry, as such, it can do the data integrity
>> checking.
>>
>
>Define 'controlled environment'.
One implementation i have seen (in many groups in the same
organization) is one username which the program uses to connect, but
is not given to the users.
>Now, implement that and still give users access. Then try to restrict what they use to access the
>database. It's surprisingly easy to defeat such tactics as logon
>triggers can easily be fooled by simply renaming the undesirable
>application. sqlplus.exe can be renamed to anything one would want to
>call it and, thus, can be used to directly access the data
>circumventing exclusive use of your UI. And, as a result your data is
>vulnerable to attack. So much for your 'controlled environment'.
I'd like to see you do that.
>
>> In many cases, and sadly most cases i have seen, the DB is merely a
>> convenient place to store data, and basically all checking was done in
>> the UI code.
>
>And that's just peachy until you have simultaneous inserts into a
>table and attempt to enforce a pseudo primary key or pseudo foreign
>key constraint through the UI. And end up with duplicate key values
>or dependent inserts failing because the newly inserted parent record
>hasn't yet been committed.
>
>> And that was what the programmers wanted.
>
>And programmers run the show where you are? I can now understand why
>data validation is so low in priority on your list.
They do in most cases. The data modelers rarely understand what the
program is doing, and the DBAs usually don't care.
>
>> Mostly because
>> it was easy to understand, and could be changed without a change
>> request to the DBAs which is usually more pain than it is worth.
>
>Ensuring data integrity is more of a pain than having a continuingly
>running application? This sounds more like a paint and body shop than
>an IT shop.
Actually, it was a large IT operation. When there are tens of
thousands of workers, groups separate, and people stop caring.
>
>> To
>> that end, COLUMNs are added and reused, and rarely DROPped, just to
>> not have to bother with the DBA group.
>
>Interesting that in the quest for 'efficiency' you place data in
>misnamed columns simply to circumvent proper change control and a
>logical table design/implementation.
I never did that. Indeed, i was called in for cleanup.
> And, possibly, the DBA group is tired of you and your antics.
Nope.
> Is there no development or test
>environment configured to facilitate such changes?
Even test is a restricted environment. Someting i complained about on
many an occasion.
>>
>> >Putting validation into the application will never, EVER, keep someone
>> >with SQL*Plus from destroying it.
>>
>> Unless it is a controlled environment.
>>
>
>Define that and implement it in a foolproof (and user-proof) manner.
>Then see how much work you actually can get done.
>
>> While i understand your sentiment--indeed, they are my feelings as
>> well--it just is not that way in the "real world" when the programmer
>> considers the DB just a convenient place to store data.
>>
>
>If the inmates are running the asylum you have more to worry about
>than data integrity.
Be veeeery afraid. They run more than you are willing to admit.
B.
fitzj...@cox.net
> On Tue, 20 Nov 2007 13:33:52 -0800 (PST), "fitzjarr...@cox.net"
>
> <fitzjarr...@cox.net> wrote:
>
> <SNIP>
>
> >> >In no case can a validation in a front-end or tool protect data in a
> >> >database from corruption ... EVER ... unless the quality of the data
> >> >is guaranteed by the database itself.
>
> >> Although, i would promote that statement as encouragement, it is not
> >> correct. In a controlled environment, the UI can be made to be the
> >> only path of data entry, as such, it can do the data integrity
> >> checking.
>
> >Define 'controlled environment'.
>
> One implementation i have seen (in many groups in the same
> organization) is one username which the program uses to connect, but
> is not given to the users.
Yet, through sqlplus and a local server connection one doesn't need
the 'application only' account to access and possibly disrupt
application data. Sort of circumvents your 'control', doesn't it?
>
> >Now, implement that and still give users access. Then try to restrict what they use to access the
> >database. It's surprisingly easy to defeat such tactics as logon
> >triggers can easily be fooled by simply renaming the undesirable
> >application. sqlplus.exe can be renamed to anything one would want to
> >call it and, thus, can be used to directly access the data
> >circumventing exclusive use of your UI. And, as a result your data is
> >vulnerable to attack. So much for your 'controlled environment'.
>
> I'd like to see you do that.
>
>
Search google or this newsgroup for examples. They are widely
available.
>
> >> In many cases, and sadly most cases i have seen, the DB is merely a
> >> convenient place to store data, and basically all checking was done in
> >> the UI code.
>
> >And that's just peachy until you have simultaneous inserts into a
> >table and attempt to enforce a pseudo primary key or pseudo foreign
> >key constraint through the UI. And end up with duplicate key values
> >or dependent inserts failing because the newly inserted parent record
> >hasn't yet been committed.
>
> >> And that was what the programmers wanted.
>
> >And programmers run the show where you are? I can now understand why
> >data validation is so low in priority on your list.
>
> They do in most cases. The data modelers rarely understand what the
> program is doing, and the DBAs usually don't care.
>
Which explains much in why your 'world' is so ... off-kilter.
>
>
> >> Mostly because
> >> it was easy to understand, and could be changed without a change
> >> request to the DBAs which is usually more pain than it is worth.
>
> >Ensuring data integrity is more of a pain than having a continuingly
> >running application? This sounds more like a paint and body shop than
> >an IT shop.
>
> Actually, it was a large IT operation. When there are tens of
> thousands of workers, groups separate, and people stop caring.
>
Prove that unequivocably. Where I work people care. And it isn't a
local 7-Eleven, either.
>
>
> >> To
> >> that end, COLUMNs are added and reused, and rarely DROPped, just to
> >> not have to bother with the DBA group.
>
> >Interesting that in the quest for 'efficiency' you place data in
> >misnamed columns simply to circumvent proper change control and a
> >logical table design/implementation.
>
> I never did that. Indeed, i was called in for cleanup.
>
Were you really? Given the mess you've presented I'd have never
guessed.
> > And, possibly, the DBA group is tired of you and your antics.
>
> Nope.
>
> > Is there no development or test
> >environment configured to facilitate such changes?
>
> Even test is a restricted environment. Someting i complained about on
> many an occasion.
>
'Restricted'? Meaning the DBAs actually care about what goes into and
comes out of that database? Wow, what concepts -- data integrity and
proper change control.
>
>
>
>
>
>
> >> >Putting validation into the application will never, EVER, keep someone
> >> >with SQL*Plus from destroying it.
>
> >> Unless it is a controlled environment.
>
> >Define that and implement it in a foolproof (and user-proof) manner.
> >Then see how much work you actually can get done.
>
I notice you failed to answer that challenge.
> >> While i understand your sentiment--indeed, they are my feelings as
> >> well--it just is not that way in the "real world" when the programmer
> >> considers the DB just a convenient place to store data.
>
> >If the inmates are running the asylum you have more to worry about
> >than data integrity.
>
> Be veeeery afraid. They run more than you are willing to admit.
>
And yet again you 'speak' without thought. Is cognitive action so
foreign to you?
> B.
>
>
>
>
>
> >> B.
>
> >> >Reconsider.- Hide quoted text -
>
> >> - Show quoted text -- Hide quoted text -
>
> >> - Show quoted text -
>
> >David Fitzjarrell- Hide quoted text -
DA Morgan
> And, as a result your data is
> vulnerable to attack. So much for your 'controlled environment'.
Controlled environments exist in Fantasy Land and laboratories. Not
on real-world production servers.
>> And that was what the programmers wanted.
>
> And programmers run the show where you are? I can now understand why
> data validation is so low in priority on your list.
Makes me wonder what hotdog stand uses Oracle software.
>> Mostly because
>> it was easy to understand, and could be changed without a change
>> request to the DBAs which is usually more pain than it is worth.
>
> Ensuring data integrity is more of a pain than having a continuingly
> running application? This sounds more like a paint and body shop than
> an IT shop.
Body shop ... hot dog stand ... he's definitely making an impression.
>>> Putting validation into the application will never, EVER, keep someone
>>> with SQL*Plus from destroying it.
>> Unless it is a controlled environment.
>
> Define that and implement it in a foolproof (and user-proof) manner.
> Then see how much work you actually can get done.
Then add Pete Finnigan, 10 minutes, and stir.
>> While i understand your sentiment--indeed, they are my feelings as
>> well--it just is not that way in the "real world" when the programmer
>> considers the DB just a convenient place to store data.
>
> If the inmates are running the asylum you have more to worry about
> than data integrity.
We are talking to one of the inmates. I can't think of a single one
of Oracle's customers I know that would tolerate this nonsense for
even ten minutes.
DA Morgan
> Other then the redundant "Data is data is data.", i disagree with
> everything you just wrote.
And your background in IT upon which you base this disagreement is?
>> Databases hold data in logical structures called tables.
>>
>> The data they hold can either have meaning or not have meaning.
>>
>> If the intent is for it to have meaning that meaning must be defined
>> and constrained within the structure of the database.
>>
>> An external application can never guarantee the integrity of data
>> whether or not it may be metadata to another system.
>
> As a component of a controlled environment, however, it can.
There is no such thing as a controlled environment: Wake up!
>>>> In no case can a validation in a front-end or tool protect data in a
>>>> database from corruption ... EVER ... unless the quality of the data
>>>> is guaranteed by the database itself.
>>> Although, i would promote that statement as encouragement, it is not
>>> correct. In a controlled environment, the UI can be made to be the
>>> only path of data entry, as such, it can do the data integrity
>>> checking.
>> Nonsense: Total and complete nonsense. All Oracle databases have
>> SQL*Plus and thus a means of accessing the data without the UI.
>
> And without a username this does what?
One does not need a username to break in. Do you think locks on car
doors prevent auto theft too?
>>> In many cases, and sadly most cases i have seen, the DB is merely a
>>> convenient place to store data, and basically all checking was done in
>>> the UI code.
>> Sadly is not the word I would use. What you are saying is that because
>> bad practices exist in some organizations they should be accepted. Some
>> of us aim a bit higher.
>
> And what you are saying is that because bad practices exist in some
> organizations they should pretend they do not exist. Some of us aim a
> bit more realistically.
You are the poster child for bad practices at the moment. What I am
saying is that your employer needs to be protected from you. You are
telling us that because the money is stored in a vault we don't need
to worry about a bank robbery. You are promoting a fantasy.
>>> And that was what the programmers wanted.
>> And programmers make these decisions? Programmers? You've got to be
>> kidding.
>
> Umm, perhaps you need to leave you ivory tower and see the real world.
> I'm not saying it should be like this, i'm saying it is like this.
Nonsense. Absolute nonsense. If that is what it is like where you work
I would guess that is probably true given that they hired you and no
doubt have hired others like you.
But if you think that is what it is like at Amazon.com, AT&T, Boeing,
T-Mobile, Washington Mutual Bank, Matsushita, etc. etc. etc. you are
in need of a 12 step program.
>>>> Putting validation into the application will never, EVER, keep someone
>>>> with SQL*Plus from destroying it.
>>> Unless it is a controlled environment.
>> There is no such thing and, in fact, is impossible to build. If you can
>> find a single computer system, running Oracle, that does not have
>> administrative access I'd like to hear about it.
>
> And i would just as well keep you ignorant of it. Who knows what you
> would do!
ALTER USER tkatch ACCOUNT LOCK;
would be a good first step.
DA Morgan
>> Define 'controlled environment'.
>
> One implementation i have seen (in many groups in the same
> organization) is one username which the program uses to connect, but
> is not given to the users.
One does not need a username to connect to Oracle.
You might want to sit back and ask yourself what is going on when two
very senior Oracle professionals are both saying the same thing to you
and not one person is saying you are correct. The message should be
extraordinarily clear.
You are wrong and, apparently, too ignorant about Oracle to know what
you don't know.
Consider using this opportunity to learn something rather than just
digging a deeper and deeper hole.
David ... I think we've made our point to any lurkers. Brian has
distinguished himself from most serious professional by his logon to
this group (Brian Tkatch <N/A>). Obviously this just a troll and his
only point in posting is to create havoc. Kill File! He's all yours
if you wish to respond. I'm through feeding the troll.
Brian Tkatch
wrote:
>Brian Tkatch wrote:
>
<SNIP>
>Nonsense. Absolute nonsense. If that is what it is like where you work
>I would guess that is probably true given that they hired you and no
>doubt have hired others like you.
Perhaps you have not read what i have posted. I do not do the things i
am talking about. Indeed, i have been asked to fix these issues.
However, many people are like this.
>But if you think that is what it is like at Amazon.com, AT&T, Boeing,
>T-Mobile, Washington Mutual Bank, Matsushita, etc. etc. etc. you are
>in need of a 12 step program.
And perhaps you need a dose of reality.
B.
Brian Tkatch
<fitzj...@cox.net> wrote:
<SNIP>
Does knowledge of Oracle come with a neccesary dose of pompousness?
B.
Brian Tkatch
wrote:
>Brian Tkatch wrote:
>
>>> Define 'controlled environment'.
>>
>> One implementation i have seen (in many groups in the same
>> organization) is one username which the program uses to connect, but
>> is not given to the users.
>
>One does not need a username to connect to Oracle.
>
>You might want to sit back and ask yourself what is going on when two
>very senior Oracle professionals are both saying the same thing to you
>and not one person is saying you are correct. The message should be
>extraordinarily clear.
And perhaps you need to re-read the point i was making.
>You are wrong and, apparently, too ignorant about Oracle to know what
>you don't know.
>
>Consider using this opportunity to learn something rather than just
>digging a deeper and deeper hole.
I would. But you clothe your speech with so much negativity it's hard
to find the diamonds in all the mud.
B.