Preventing in database record tampering?

Question

My question regards the best practice / industry standards to prevent rogue users/admins from directly logging into the database console and updating sensitive information such as financial info / balances, etc as opposed to using the software which would otherwise validate the user's actions.

I ask this because the software I maintain has a rudimentary anti-tamper mechanism. A set of fields from each table are check-summed prior to insertion or updating and the resulting signature is stored in each record. When the record is next loaded from the database, the signature is recalculated and compared to the signature previously persisted in the database. If the signatures differ, then an alarm is raised.

There are some challenges with this approach, for instance in the event that new fields need to be added to the signature, this renders all existing signatures outdated. Thus, they either need to be migrated or somehow versioned. Migrating millions of records is not an option. I guess for me, its a case that the mechanism needs to be reworked to encode the version identifier in the signature. Additionally this approach won't catch deleted records. The biggest flaw with this approach is that a user with an initial high balance (ideal record state) could transact and replace his/her record back to its ideal initial state after each transaction. No alarm would be raised as the signature does not take into account previous record states.

I have found so many holes with the concept and implementation of this mechanism that its got me wondering, how does the rest of the world handle this? Banks? Insurance Companies? Financial Institutions?
I imagine that keeping ssh and database access on lock-down is entirely sufficient.

Essentially my question is: Am I correct in saying that this solution smacks of over engineering, or is the basis of the idea sound?

Answer 1

The first thing I would note is that most production databases have a journal which records every change made to the DB. I suppose that someone could potentially modify the journal. Typically, I would expect the journal to be stored in offline using snapshots or by backing it up on a regular basis. So pulling off a change in such scenarios would require a lot of technical sophistication. This is probably the most common way for 'protecting' against such things aside from managing access. Of course, if no one is looking at the journal, such changes could slip by. If you really wanted to strengthen this you could stream the journal and try to detect suspicious changes. For example, a good way to manage a ledger is to never update any row. You simply insert new rows that represent changes to the account. Then if you ever see an update or delete on a row in this table in the journal, you know something is off. If the bad actor inserted a new row, the change wouldn't be hidden.

I know another piece of this is that everything needs to add up in a double-book accounting sense. If money enters or exists an account, an equal and opposite change needs to be accounted for somewhere else. If the numbers don't balance lots of people will go ape-shit.

I guess the one big potential problem with the methodology you describe is: if I can modify the row, what prevents me from calculating a signature for my fraudulent row and changing that field too? As a side note, one of the things that has people excited about blockchain is that it does kind of solve the problem you are asking about. You might want to learn more about how it works.

Answer 2

OK, let's assume that the threat model is a rogue admin with access to a critically important database, like bank transactions. What would I do, given a large amount of time and money?

• Admin access to the DB and access to the app working with that DB are completely decoupled. People who have one don't have the other. If they need to troubleshoot something, they must do it together.
• The app, when it updates a record, cryptographically signs the entire record's content. This excludes the use of any autoincrement keys, values generated by after insert / after update triggers, etc. The entire set of important fields in the record should come from the app, and be signed. If insert / update time is important, then both the app and the DB should provide separate fields, the app-generated field must be included in the signature.
• Every transaction done on the important tables is promptly replicated by the DB's means to a standby database. You'll need a standby database anyway.
• The signature of the data of every transaction done on the important tables is also independently posted to a remote box by the app. This is something the DB operator cannot control.
• An independent watcher process keeps looking at both the fresh records in the standby database, and the stream of signatures coming from the app. If both components of the pair (transaction from DB and signature from app) do not arrive within reasonable time form one another, it raises an alert. If they arrive with the same transaction ID but different signature values, it also raises an alert.
• Another watching process scans through the table in the DB (it can use the standby), and checks app's transaction timestamp and the DB-provided update time; if they differ significantly, it raises an alert.
• As an extra security measure, the app uses a hardware dongle (something like a yubikey, but likely a more performant device) to generate the signatures of data. The device never reveals the private key it uses to generate the signatures; its public key is well-known. This prevents a malicious DB operator from stealing a key and then creating / updating a transaction in the DB and forging a matching signature for it.
• Storing the critical transaction info in an append-only DB that technically does not support updates also helps; it may be a separate DB if you need an updatable DB for other operations. Such a split, of course, increases complexity, but allows to additionally separate access rights, or even physical access to the databases.

The above does not include any considerations of a malicious app operator who could feed fake data to the app. For that, another layer (or several) is generally used, depending on the nature of the app; this is why e.g. a credit card has a crypto chip that sings its transaction info.

Answer 3

Best industry practice for this exact purpose: blockchain.

However, that is not suitable for a lot of companies or existent systems.

I recommend then that you better prevent tampering and also prevent getting blamed for tampered records. Here are best practices for this:

• If not already done, grant yourself high privilege access to your database using your own domain account. Not database account, not service account, not group account, your own personal domain account

• Remove all access to your database (except for your own personal domain account). Literally, each and all accounts.

• Add only those accounts that should have access to the DB, always using personal domain accounts (such as your peers). This must logged into an external logbook, such an spreadsheet in a shared drive.

• This logbook must contain the accounts that have access, when was access granted, when should it be revoked, the justification why this person should have access, the granted access-level, the name of the persons and teams they work for, which manager approved the access (and takes responsibility for misuse) and, of course, which dba granted the access and entered the row into the logbook.

• This logbook must be accessed only by those which enough access like to grant/revoke access to other users

• Use the bare minimum privileges you can grant on a case by case scenario.

• Never add domain groups. So, never add "myappadmins" group or "domain admins" groups.

• If you MUST have some service accounts, then you must control the password. This means you should be able to change the password and give it only to those individuals you "trust". This must be logged into the external logbook, so you know who knows the password, as well as who should you notify when the password changes.

  • Each consumer team should have its own set of services accounts BY APPLICATION, so they can't share it with other teams without being noticed. Also, create them by environment (myapp_svc_dev, myapp_svc_prod, etc.), so only leaders know the password of prod accounts.

With all these measurements, many things will happen:

1. You will reduce the possibility that people change the data. Since you will grant access only to those you trust technically speaking, it's less likely to happen (also, not everyone has write access).
2. Since only personal accounts are tied to the system, you should be able to spot "who broke the data".
3. Because of the logbook, you can easily make accountable to those that broke the data, if they are misusing their granted access for things outside of the original justification they provided. This also applies to the managers who approved the requests.
4. Since there are no groups-access, its not like someone can add persons to groups and get away with gaining access.
5. If you run an audit and detect there are accounts with access that are not in the logbook, you can immediately revoke them all with zero notice (since they were never justified to be there), without being blamed (since someone violated the security process). Also, this should immediately start an investigation to check who is violating the granting-access process.
6. Since service accounts are controlled by you, you can change the password any time of the year, and only those within your logbook should be notified. If you know there's a leak, you can tell them there's a leak and you will no longer provide such account access (since service accounts are by team, it would only affect one team).
7. People immediately realize they can be pointed if there are records altered (or misusage of accounts, leaks on passwords for service accounts, etc.) and that their manager will be notified, etc., so a lot of people just simply refrain from requesting access at all (as opposed of the "it doesn't hurt I have access to this system"), and even managers think it twice before approving. Red tape works on this case.

Although this doesn't completely prevent tampering, it does reduce it a lot, and doesn't require any code changes nor has performance penalties. It also helps spotting fishy situations once I'm a while (I've been on meetings where I have seeing someone explaining something then logging with a service account that belongs to another team, for example)

This has worked very good on big companies, so I hope it works for you.

Answer 4

There are specific append only databases that have commit graphs and data lineage included out of the box. You can see who has made changes at any point, you can also roll the database back to that point, or perhaps remove the specific commit from the bad actor (it is a git-like data management system). If they are hash-chained, you can prove that they fit in a sequence at a particular point. The two examples that come to top of mind are TerminusDB, Fluree and ImmuDB. They combine the best ideas of immutable databases and in the case of Terminus hash chains and Fluree a blockchain.