Need a 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 updating 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 stored procedure to adjust hash value.
We are looking for some solution that could speed up the hash value
updating without allowing unauthorized user to do itHave you checked out CHECKSUM() in BOL?
"Vlad Olevsky" <leonid4142@.yahoo.com> wrote in message
news:50540181.0505030708.b3397b6@.posting.google.com...
> 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 updating 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 stored procedure to adjust hash value.
> We are looking for some solution that could speed up the hash value
> updating without allowing unauthorized user to do it|||If you want to enforce that values can only be changed through your
application, the best way to do that is make sure that only your application
has permissions to use certain stored procedures and tables. For this you
can use application roles.
Having a trigger on the table to calculate the hash value won't do anything
useful, because anyone who updates the table, will fire that trigger and the
hash value will updated correctly.
Jacco Schalkwijk
SQL Server MVP
"Vlad Olevsky" <leonid4142@.yahoo.com> wrote in message
news:50540181.0505030708.b3397b6@.posting.google.com...
> 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 updating 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 stored procedure to adjust hash value.
> We are looking for some solution that could speed up the hash value
> updating without allowing unauthorized user to do it|||How are you using this hash value? Is it supposed to provide some
user-authentication? Without understanding the application of this it's
difficult to recommend an alternative.
David Portas
SQL Server MVP
--|||If the Checksum() function is not sufficient, you might try a computed colum
n.
Thomas
"Vlad Olevsky" <leonid4142@.yahoo.com> wrote in message
news:50540181.0505030708.b3397b6@.posting.google.com...
> 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 updating 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 stored procedure to adjust hash value.
> We are looking for some solution that could speed up the hash value
> updating without allowing unauthorized user to do it|||Jacco raised a very good point. If your intent is to store a hash value of
some sort to indicate that a row has not been tampered with by any means
outside of your application, you should probably generate the hash code and
insert it from the application side, not via a trigger or other mechanism
internal to the database that users would have access to via QA.
"Vlad Olevsky" <leonid4142@.yahoo.com> wrote in message
news:50540181.0505030708.b3397b6@.posting.google.com...
> 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 updating 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 stored procedure to adjust hash value.
> We are looking for some solution that could speed up the hash value
> updating without allowing unauthorized user to do it|||you could continue to use a trigger, but the trigger only does something if
called by your application, so if someone changed a row via query analyser
the trigger will not fire.. see below
create trigger mytrigger on mytable after update
as
begin
if app_name() = 'myapp'
begin
' do stuff here
end
end
go
"Vlad Olevsky" <leonid4142@.yahoo.com> wrote in message
news:50540181.0505030708.b3397b6@.posting.google.com...
> 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 updating 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 stored procedure to adjust hash value.
> We are looking for some solution that could speed up the hash value
> updating without allowing unauthorized user to do it|||Thanks Mark!
The only one remark. To prevent end-user from looking and modifying the
trigger code we could create trigger using 'WITH ENCRYPTION' flag.
This flag encrypts the syscomments entries that contain the text of
CREATE TRIGGER. Using WITH ENCRYPTION prevents the trigger from being
published as part of SQL Server replication. So tamper will never know
what we check within trigger.
Mark wrote:
> you could continue to use a trigger, but the trigger only does
something if
> called by your application, so if someone changed a row via query
analyser
> the trigger will not fire.. see below
> create trigger mytrigger on mytable after update
> as
> begin
> if app_name() = 'myapp'
> begin
> ' do stuff here
> end
> end
> go
> "Vlad Olevsky" <leonid4142@.yahoo.com> wrote in message
> news:50540181.0505030708.b3397b6@.posting.google.com...
our
and
this