Where to do Data Validation?

#21

Comments embedded.
On Nov 20, 12:55 pm, Brian Tkatch <N/A> wrote:

Quote:

On Tue, 20 Nov 2007 09:51:49 -0800, DA Morgan <damor... (AT) psoug (DOT) org
wrote:

Brian Tkatch wrote:
On Mon, 19 Nov 2007 09:48:12 -0800 (PST), Vijai Kalyan
vijai.kal... (AT) gmail (DOT) 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.

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'.

Quote:

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.

Quote:

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.

Quote:

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.

Quote:

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?

Quote:

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.

Quote:

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.

Quote:

B.

Reconsider.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

David Fitzjarrell

#22

On Tue, 20 Nov 2007 13:12:54 -0800, DA Morgan <damorgan (AT) psoug (DOT) org>
wrote:

Quote:

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.

Quote:

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.

Quote:

You get a 68 for your effort.

And here i thought i would get a 68 overall, but *at least* a 90 on effort.

Quote:

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?

Quote:

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.

Quote:

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.

Quote:

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.

Quote:

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.

Quote:

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.

#23

On Tue, 20 Nov 2007 13:33:52 -0800 (PST), "fitzjarrell (AT) cox (DOT) net"
<fitzjarrell (AT) cox (DOT) net> wrote:

<SNIP>

Quote:

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.

Quote:

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.

Quote:

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.

Quote:

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.

Quote:

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.

Quote:

And, possibly, the DBA group is tired of you and your antics.

Nope.

Quote:

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.

Quote:

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.

Quote:

B.

Reconsider.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

David Fitzjarrell

#24

On Nov 20, 3:59 pm, Brian Tkatch <N/A> wrote:

Quote:

On Tue, 20 Nov 2007 13:33:52 -0800 (PST), "fitzjarr... (AT) cox (DOT) net"

fitzjarr... (AT) cox (DOT) 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?

Quote:

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.

Quote:

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.

Quote:

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.

Quote:

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.

Quote:

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.

Quote:

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.

Quote:

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?

Quote:

B.

B.

Reconsider.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

David Fitzjarrell- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

David Fitzjarrell

#25

fitzjarrell (AT) cox (DOT) net wrote:

Quote:

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.

Quote:

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.

Quote:

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.

Quote:

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.

Quote:

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.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#26

Brian Tkatch wrote:

Quote:

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?

Quote:

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!

Quote:

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?

Quote:

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.

Quote:

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.

Quote:

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.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#27

Brian Tkatch wrote:

Quote:

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.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#28

On Tue, 20 Nov 2007 19:03:38 -0800, DA Morgan <damorgan (AT) psoug (DOT) org>
wrote:

Quote:

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.

Quote:

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.

Quote:

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.

#29

On Tue, 20 Nov 2007 14:14:24 -0800 (PST), "fitzjarrell (AT) cox (DOT) net"
<fitzjarrell (AT) cox (DOT) net> wrote:

<SNIP>

Does knowledge of Oracle come with a neccesary dose of pompousness?

B.

#30

On Tue, 20 Nov 2007 19:14:14 -0800, DA Morgan <damorgan (AT) psoug (DOT) org>
wrote:

Quote:

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.

Quote:

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.

Quote:

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.