470,636 Members | 1,524 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,636 developers. It's quick & easy.

Need good idea

Hi guys

We have a following problem. For security reasons in each table in our
DB we have addition field which is calculated as hash value of all
columns in particular row.

Every time when some field in particular row is changed we create and
call select query from our application to obtain all fields for this
row and then re-calculate and update the hash value again.

Obviously such approach is very ineffective, the alternative is to
create trigger on update event and then execute stored procedure which
will re-calculate and update the hash value. The problem with this
approach is that end user could then change the date in the tables and
then run this store procedure to adjust hash value.

We are looking for some solution that could speed up the hash value
updating without allowing authorized user to do it

Thanks in advance,
Leon
Jul 23 '05 #1
6 1068
Vlad Olevsky wrote:
Hi guys

We have a following problem. For security reasons in each table in our
DB we have addition field which is calculated as hash value of all
columns in particular row.

Every time when some field in particular row is changed we create and
call select query from our application to obtain all fields for this
row and then re-calculate and update the hash value again.

Obviously such approach is very ineffective, the alternative is to
create trigger on update event and then execute stored procedure which
will re-calculate and update the hash value. The problem with this
approach is that end user could then change the date in the tables and
then run this store procedure to adjust hash value.

We are looking for some solution that could speed up the hash value
updating without allowing authorized user to do it

Thanks in advance,
Leon

In DB2 for LUW you can define the column as a generated column.
I presume you have some sort of UDF already that does the actually
hashing. Last I heard SS 2005 will have persistent generated columns as
well.
In general (x-product) you can use a combination of a check constraint
and (before) triggers.

One must but wonder WHY this column is required. Are you affraid of
corruption or sabotage?

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Jul 23 '05 #2

"Vlad Olevsky" <le********@yahoo.com> schrieb im Newsbeitrag news:50**************************@posting.google.c om...
Obviously such approach is very ineffective, the alternative is to
create trigger on update event and then execute stored procedure which
will re-calculate and update the hash value. The problem with this
approach is that end user could then change the date in the tables and
then run this store procedure to adjust hash value.

We are looking for some solution that could speed up the hash value
updating without allowing authorized user to do it

As Frank pointed out, try to create a trigger which calls a function.
Let the function run with the grants of the caller and give only
authorized callers the exec grant of the function.

Greetings!
Volker
Jul 23 '05 #3
Vlad Olevsky wrote:
Hi guys

We have a following problem. For security reasons in each table in our
DB we have addition field which is calculated as hash value of all
columns in particular row.

Every time when some field in particular row is changed we create and
call select query from our application to obtain all fields for this
row and then re-calculate and update the hash value again.

Obviously such approach is very ineffective, the alternative is to
create trigger on update event and then execute stored procedure which
will re-calculate and update the hash value. The problem with this
approach is that end user could then change the date in the tables and
then run this store procedure to adjust hash value.

We are looking for some solution that could speed up the hash value
updating without allowing authorized user to do it

Thanks in advance,
Leon


This may come as a shock to you Leon but the solution in each of the
products whose usenet group you copied on this uses a completely
different solution.

I'd suggest you start by apologizing, to all, for your lack of
identifying the product and version and for posting to every usenet
group you can spell.

And then repost in the one, and only, group where your query is
appropriate.

Thank you.
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu
(replace 'x' with 'u' to respond)
Jul 23 '05 #4
Serge Rielau (sr*****@ca.ibm.com) writes:
In DB2 for LUW you can define the column as a generated column.
I presume you have some sort of UDF already that does the actually
hashing. Last I heard SS 2005 will have persistent generated columns as
well.


Actually, SQL 2000 has it as well. The difference is that PERSISTED is
a keyword in SQL 2005, and, I assume, that in SQL 2005 you can persist
a computed colum, without indexing it.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5
Erland Sommarskog wrote:
Serge Rielau (sr*****@ca.ibm.com) writes:
In DB2 for LUW you can define the column as a generated column.
I presume you have some sort of UDF already that does the actually
hashing. Last I heard SS 2005 will have persistent generated columns as
well.

Actually, SQL 2000 has it as well. The difference is that PERSISTED is
a keyword in SQL 2005, and, I assume, that in SQL 2005 you can persist
a computed colum, without indexing it.

Yes, in SS2000 the generated column is virtual (i.e. not persisted). The
planned syntax in the standard is "GENERATED BY REFERENCE", being the
default for compatibility with SS2000.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Jul 23 '05 #6
Serge Rielau (sr*****@ca.ibm.com) writes:
Yes, in SS2000 the generated column is virtual (i.e. not persisted).


Unless, as I said, it is indexed, in which case it is implicitly persisted.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Martijn van Oosterhout | last post: by
48 posts views Thread by Chad Z. Hower aka Kudzu | last post: by
4 posts views Thread by robinsand | last post: by
46 posts views Thread by Bruce W. Darby | last post: by
7 posts views Thread by elgiei | last post: by
1 post views Thread by Korara | last post: by
???
reply views Thread by Stoney L | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.