Need good idea

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Vlad Olevsky
    #1

    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
    Tags: None
    • Serge Rielau
      #2
      Re: Need good idea

      Vlad Olevsky wrote:[color=blue]
      > 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[/color]
      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

        Comment

        • Volker Hetzer
          #3
          Re: Need good idea


          "Vlad Olevsky" <leonid4142@yah oo.com> schrieb im Newsbeitrag news:50540181.0 505030658.64f68 390@posting.goo gle.com...[color=blue]
          > 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[/color]
          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

            Comment

            • DA Morgan
              #4
              Re: Need good idea

              Vlad Olevsky wrote:
              [color=blue]
              > 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[/color]

              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
              damorgan@x.wash ington.edu
              (replace 'x' with 'u' to respond)

                Comment

                Previous template Next
                Working...