0

I have a table in my database where I store records to be processed later (basically orders that need to be invoiced or something similar, but this is not the important part).

Since this software runs on-premises, admins control the database and they're able to insert records directly into it. They usually do this to make my system process records in an unsupported way. Obviously, this leads me to problems that I often need to deal with: inconsistency, invalid domain, missing fields, etc.

To avoid this problem, I'd like to know what are my options to "sign records", that is, identify the records generated by my system in a way that the admins can not reproduce.

Several approaches came to my mind when I think in this problem:

  • Create some undocumented record hash (that can be reverse engineered);
  • Use a digital certificate to sign records (where to store the digital certificate? the system runs offline on-premises);
  • Use some kind of blockchain approach: linking a record with the previous + some proof of work (maybe too hard to implement and error prone).

Are there other approaches I am not considering? If not, between the ones I listed, is there an approach I should stick/avoid?

Diego Queiroz
  • 103
  • 5
  • Really? It is Oracle, but since the pro-users has admin access to the database, I question myself if any database resource will rescue me. So I was searching for something in the application level. Can you point some database feature that would help in this case? (any DBMS) – Diego Queiroz Jun 26 '20 at 14:08
  • 1
    "Pro-users" does not normally mean "full admin privs". You might want to include that in your question. And if that's what you mean, then you are missing some very important context. Why are "users" full admins? Why are they inserting records manually? Why are untrusted users admins? I think you have a much more complex and different problem than the one you presented. – schroeder Jun 26 '20 at 14:36
  • 2
    A signature is a signature. If there's a chance it can be reverse engineered by the same users you are trying to protect the data from, then it's weak. Otherwise you can have validation of integrity. Well, some validation, what happens if an entire record is deleted? Think very carefully before going anywhere near blockchain. There's very few and very specific situations where it is helpful. – Pedro Jun 26 '20 at 15:04
  • Good point @Pedro, indeed I didn't think of records being deleted when I listed blockchain as an option. – Diego Queiroz Jun 26 '20 at 22:41
  • playing the "resistance" in this scenario won't solve you problem, you want to fight for your data in premises where you have no power. – elsadek Jun 27 '20 at 14:24
  • Your title misrepresents what you want to ask. You are not asking about signing records. Signing is just one option you are considering. You are asking for different approaches to accomplish your goal. – schroeder Jun 27 '20 at 14:52
  • 1
    Here's the deal: if you want to know how to sign records in Oracle, then this is not a security question, but a database/Oracle question and should be migrated to the appropriate site. If you want a list of all the ways one could lock records in a generic database from modification from the admins, then it is off-topic as being too broad. If you want to discuss and debate the relative merrits of one of your suggestions against another, it's off-topic as we are not a discussion site. Since you've asked *here* we are answering with security: your database design is working against you. – schroeder Jun 27 '20 at 14:58
  • @schroeder. Did you realize that it was you, and only you, that are talking about Oracle? I don't ever cite the DBMS in the question. After you asked me what DBMS I was using, I asked you to point me things in other DBMS, because the DBMS is not relevant for me. I have a *conceptual* problem but you insist in materialize it to point how the app design is terrible. Why are you insisting in this question? It's clearly not for you. – Diego Queiroz Jun 27 '20 at 19:36
  • And the title perfectly represents what I want: I want to add some information in the record to allow me determine who created it. This is a signature. – Diego Queiroz Jun 27 '20 at 19:39
  • @DiegoQueiroz admin can also alter that _signature_ in the DB's table. – elsadek Jun 27 '20 at 20:40
  • @elsadek Surely they can do. The same way you can change the signature of a signed PDF file, but if you do, the signature vanishes, right? This is the idea behind a signature. :) – Diego Queiroz Jun 27 '20 at 20:51
  • "if a message is digitally signed, any change in the message after signature invalidates the signature" https://en.wikipedia.org/wiki/Digital_signature – Diego Queiroz Jun 27 '20 at 21:01
  • Then you have clearly explained how it is off-topic and not actually about security. You have a programming or database-specific question. If you want to discuss the relevant merits of the "algorithms", then as I also said, that becomes too much of an open-ended discussion topic. – schroeder Jun 28 '20 at 07:27
  • "Are there other approaches I am not considering?" yes- redesign your permissions structure or hold a master database where you control the integrity of the data. Although I understand you are frustrated with this suggestion. – schroeder Jun 28 '20 at 07:35

2 Answers2

2

I'm continuing the theme of @schoeder's questions and answering the questions that you didn't ask.

First though let's answer your questions. You don't want to sign records. You want to figure out who is responsible for the manual edits to your database. The most direct answer to your question is that you want an audit trail. Many databases have solutions for that so google audit trail options for you database and use one of those. Note however that this will be doomed from the start if people share username/passwords for database access.

However, that's not really what you want. Whether you realize it or not, this is a disaster waiting to happen. Direct admin access to the database is the sort of thing that should happen only rarely. Direct access, especially regular direct access, carries some high risks:

  1. The risk that someone becomes a disgruntled employee and uses their access to intentionally break things
  2. The risk that someone does the wrong thing and accidentally deletes everything.
  3. The risk that someone enters data wrong and causes inconsistencies or missing data.

And much more! Having accidentally deleted things myself before (thank goodness for backups!), I take direct database access seriously and cautiously. Not everyone does. Things will eventually go very wrong. Therefore there is only one answer:

Update your application to match your actual use-case, remove database access from everyone who doesn't need it, and make sure it is only used for necessary maintenance tasks!

I'm afraid that in the long term that's really the only correct answer.

Conor Mancone
  • 29,899
  • 13
  • 91
  • 96
  • Thanks for your answer, I really appreciate your concern about my application but this is not what I am searching for. I am interested in solutions to my scenario, and not to debate why give admin access to the database is a bad idea. I want to talk about algorithms and not about server configuration and permissions. – Diego Queiroz Jun 26 '20 at 22:48
  • @DiegoQueiroz I provided an answer to your question too: audit logs! – Conor Mancone Jun 26 '20 at 23:45
  • Audit logs will just provide me a way to know that someone touched the database. They won't give me any other hint to allow me to distinguish my records from someone else (the so called 'pro-user' may use my credentials to appear to be me). Also, I need to invalidate records while processing them. Audit logs does not appear to fit well this use case. – Diego Queiroz Jun 27 '20 at 00:38
  • Security is an emergent property of a system. You can't "bolt on" security. Security emerges from the elements in the system. So, yes, understanding why malicious actors are admins and debating that is relevant because there might not be an answer for what to do to add to your vulnerable system to make it more secure. The answer, very likely is, that you need to change the system. – schroeder Jun 27 '20 at 07:42
1

You cannot protect the database from the users who have full permissions on the database. Your suggested approaches all point to the same thing: an external system (obfuscated (hashed) data, blockchain, CAs) where the local admins don't have permissions.

And there's your clue.

The only way is to protect the data is to get it or get the integrity controls into another permissions scope. And once your thinking goes there, then it really doesn't matter what you choose. The choices depend on your functional requirements, not on security, because the security problem has been solved simply by pulling some of the data outside of the user's control.

schroeder
  • 123,438
  • 55
  • 284
  • 319
  • You said it much better than me – Conor Mancone Jun 27 '20 at 10:27
  • Thank you for your edit, but your suggestion to the title does not proceed (I'm not trying to protect the database from admins). I'm after a way to sign records, in a way that is hard to reproduce. Indeed, admins can try to break (what should I expect, they're admins). I'm pleased about your concern, and you're mostly right, but you're also trying to change the subject of my question. – Diego Queiroz Jun 27 '20 at 12:40
  • If this is not enough to make you stop trying to give me an answer I did not ask for, think that when an app is distributed, I have no control over it (they're in a wild zone). Think in me as the developer of Google Chrome, trying to make your life hard to avoid you from changing Chrome's system files manually. It is a similar use case. Got it? – Diego Queiroz Jun 27 '20 at 12:44
  • You may say: "but you can't", "after you distributed the software, it is not yours", "the user has the right to do it", "who controls the hardware controls everything", "you are trying to do a thing that will fail". And then I answer, "I know. I am aware of it". But I'd like to discuss my options and the pros/cons of each of them. Based on this, @Pedro comment was more helpful than any other, because he pointed me a thing that I did not consider. – Diego Queiroz Jun 27 '20 at 12:48
  • 1
    @DiegoQueiroz there are built-in ways to sign and encrypt records in Oracle. But they cannot protect the data from the admins who can re-sign, decrypt them. So your requirement/dependency is to lock out the admins. I'm not trying to change the subject, I'm highlighting your critical requirements. – schroeder Jun 27 '20 at 14:47
  • 1
    And no, this is not the similar use case as developing Google Chrome. You are comparing code with data. And you are trying to compare **open source** code to data that is currently open that you want to close. And we're not talking about binaries running on a system, we're talking about data in a database where there is normal and expected access granted to admins. This is not similar in any way. – schroeder Jun 27 '20 at 14:50
  • @DiegoQueiroz I get that you don't like the answers you have been presented so far, but I think you're really missing the bigger picture here. In essence your issue is that people are accessing your database improperly and causing issues as a result. The obvious (and only) solution is to remove the access that is letting people mess up your data. This is a "people" problem, not a technical problem, but you still keep insisting on a technical answer. – Conor Mancone Jun 27 '20 at 16:32
  • Here's a good analogy for your situation. Imagine I asked: "Everytime I invite my friends over to my house they steal something. Is there a good chain or lock I can use to attach my stuff to the wall so they can't steal it?" There is only one answer there: no, that will never work. You need to stop inviting your friends over. You have the same fundamental problem. You can't give people full admin access to your database and also stop them from screwing things up and hiding their tracks afterward, if that's what they want to do. – Conor Mancone Jun 27 '20 at 16:34
  • @schroeder I think you are not aware that Chrome stores all its information in small embedded SQLite databases. – Diego Queiroz Jun 27 '20 at 19:44
  • @Conor Mancone, your analogy is not wrong. I would say that: "yeah, I know, but I still want to invite friends, and I know they'll steal me sometimes, but what can I do to make it harder?". Again: I want to talk about complexity to reverse engineer hash codes, ways to securely store private keys, and maybe blockchain. Did you realize, for example, that blockchain, on its essence, solves my problem? (since it is to complex for an attacker to recalculate all records). So again, if talking about algorithms is not a thing you guys are expecting to talk about, this will be an endless chat. – Diego Queiroz Jun 27 '20 at 19:54
  • Yes, I am aware. – schroeder Jun 28 '20 at 07:36