473,324 Members | 2,511 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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
Nov 12 '05 #1
6 1207
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
Nov 12 '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
Nov 12 '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)
Nov 12 '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
Nov 12 '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
Nov 12 '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
Nov 12 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: mr.iali | last post by:
Hi Everyone I would like to get into software developent using a programming language like c++, java or pl/sql for oracle. I have no idea where to start from. Which language is there more...
4
by: nsr93 | last post by:
I am not sure if this was the proper group to post this, but here is my question: I am a Java consultant. I have new client I am working for to make a web based application similar to an...
6
by: Martijn van Oosterhout | last post by:
I've had some fun in the past where I've had to grant a lot of tables and other similar system commands. Unfortunatly, you can't use queries to fill in fields for you. Anyway, I've implemented a...
19
by: James Fortune | last post by:
I have a lot of respect for David Fenton and Allen Browne, but I don't understand why people who know how to write code to completely replace a front end do not write something that will automate...
48
by: Chad Z. Hower aka Kudzu | last post by:
A few of you may recognize me from the recent posts I have made about Indy <http://www.indyproject.org/indy.html> Those of you coming to .net from the Delphi world know truly how unique and...
15
by: Cheryl Langdon | last post by:
Hello everyone, This is my first attempt at getting help in this manner. Please forgive me if this is an inappropriate request. I suddenly find myself in urgent need of instruction on how to...
4
by: robinsand | last post by:
My apologies to those of you who are more advanced Visual C++ .NET programmers, but I am working on a project for an MBA course that is condensed into an eight-week schedule, and I need help...
46
by: Bruce W. Darby | last post by:
This will be my very first VB.Net application and it's pretty simple. But I've got a snag in my syntax somewhere. Was hoping that someone could point me in the right direction. The history: My...
20
by: mike | last post by:
I help manage a large web site, one that has over 600 html pages... It's a reference site for ham radio folks and as an example, one page indexes over 1.8 gb of on-line PDF documents. The site...
7
by: elgiei | last post by:
Good morning at all, i have to implement a server,that every n-seconds (eg. 10sec) sends to other clients,which files and directory has been deleted or modified. i build a n-tree, for each...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.