Ensuring record value integrity in a open-source database

Question

How can I ensure the records in a database can not be altered by other than the middle tier software (e.g. discourage the DBA of changing values)?

I want to implement a simple multi-tier accounting program using open-source stack. The primary function of the application is to track money paid for one product. The main part of the data model is basically this:

CREATE TABLE ACCOUNT_LOG(
    USER_ID NVARCHAR2(128), /* user identifier of some sort */
    TIMEST  TIMESTAMP,      /* the UTC timestamp of the payment. */
    PREV_AM NUMBER(13,3),   /* the previous money level. */
    DIFF_AM NUMBER(13,3),   /* the the money delta (+/- possible) */
    NEXT_AM NUMBER(13,3),   /* the new money amount. */
    UOM     NVARCHAR(20)    /* the money type (Euro, Dollar, etc.) */
CONSTRAINT pk PRIMARY KEY (USER_ID, TIMEST));

However, this structure is vulnerable to a DBA, as he/she can go in and change amounts for various persons or put in unauthorized money increases.

How can I ensure, that the data in this table can 'only' be altered by the middle tier software (e.g. detect alterations of other means)? Note that I'd like to use an open source DB engine, as my program should be as cheap as possible.

I have my own ideas (dirty ways), but I'd like to hear your opinion/best practice. Also, please feel free to ask for further details if necessary.

Thank you for your time.

Answer 1

First: Since you'll hand out all the code to the customer, there is no way to make it really secure.

Second: A way with (in my opinion) good balance between effort and effect would be to add an extra column, then when ever you change the values, concatenate all the values, add a secreat password to it (better term would be 'salt'), run it through a cryptographic hash algorithm and put the result in the extra field. When you read the data, you repeat the whole thing and compare the values. If they don't match somebody fiddled with the values.

If detecting changes is not sufficient, you can use an encryption algorithm instead of a hash, thereby enabling recreation of the original data.

---

Actually if you have the option to keep the implementation of the concatenation, salting and hashing away from the customer site this could become pretty save. The obvious way to do that to have a little tool for calculating the hash on your site. When the values need changing, the user/admin need to contact you to get the new correct hash value.

Of course this only works, when the number of changes is not to high and the lengthy time needed for mailing you, and getting the reply is ok.

Answer 2

You can try some tricks to prevent the DBA from changing your data, but they will be just that: tricks. Another approach, is to implement some sort of audit trail, where you log all modifications to your data.

But since the DBA has access to all objects in your database, he can figure out which methods you are using and disable them, if he really wants. That's what DBA means: This person has full access to the database.

Answer 3

You can implement a design that runs all database activity through a transaction table that serves as an audit trail for the database. It should include a timestamp column, a userid, and some kind of context indicator (e.g. what form was used to generate the transaction.) And include an identity column unique key to expose out-of-sequence timestamps.

Sure a DBA could run transactions through the audit trail, but at least you could verify (by interview) that the change sources really were what the audit indicates.

You can also have permissions on the transaction table that are different from the rest of the database, but that obviously involves policy issues.

It's generally easier (and the consequences are more obvious) if you make sure that changes are exposed and reviewable than to prevent them. And people seem to be adept at discovering legitimate reasons to do what you are trying to prevent.