Search code examples
c#databaseoracle-databasedata-integrity

Data integrity for a large database table

I have to provide data integrity for a large database table. So, if a crafty admin manually changes the table (not via UI) I want to be able to detect it.

My idea is to have HMAC for each record and calculate incremental HMAC for the table when a user change it via UI:

  1. Calculate HMAC for first record - HMAC_Current.
  2. Calculate HMAC for a new record - HMAC_i
  3. Calculate new HMAC for the table as HMAC_Current = HMAC(HMAC_Current + HMAC_i).

Pros:

  • there is no need to calculate HMAC for entire table each time when a user adds a record via UI.

Cons:

  1. When a user deletes or changes a record I have to recalculate HMAC for the table from this record to the end of the table.
  2. When I want to check data integrity I have to check HMAC for each record. Then calculate HMAC for entire table from top to bottom and compare it with HMAC_Current.

Is there a better way to do it?

Solution

  • I see a number of problems with this approach:

    1. If your sysdba has access to all the data, what's stopping them from messing with the HMACs as well? eg: They revert all changes to the table made in the last month. Then they put back the HMAC from last month. Is data integrity "preserved" in this case?

    2. What stops them from subverting the application to mess with the HMACs? eg: If they don't have access to the application, they change the password for a user, and accesses the application as that user to mess with records.

    3. Even if you can get this to work, what's it good for? Say you find a HMAC mismatch. Now who do you hold responsible? An admin? A user? Data corruption?

    The better solution is to use auditing. You can set up all kinds of auditing on Oracle, and have the audits saved somewhere even the dba can't touch. Additionally, there's a huge advantage in using auditing: you can know who changed what. With your scheme, you can't possibly know that.

    You can even set up FGA (fine-grained auditing) so that it'll only audit specific columns and also know what the values were before and after a change, which isn't possible with standard auditing.

    Reference: Configuring and Administering Auditing