Database with table encryption needed lazarus / 16-Databases ---------------------------------------------------------------------- thehidden · 2019-05-10 22:18 Hi, I have a customer request to use a database with table encryption. Meaning that even the database administrator can not read/add/alter the database content. Password for the tables will be send by my software to the database server. Database administrator can do everything else on the DB Server, exept accessing the content of the table. Does anybody knows a database which works together with Free Pascal/Lazarus and has this feature? SQLite has it, but this is a single user Database. Thank you for help in advance. TheHidden ---------------------------------------------------------------------- lucamar · 2019-05-10 22:56 Most database servers offer that possibility: PostgreSQL, Firebird, etc. In fact, SQLite is one of the few which doesn't have it as a core feature but through a plugin. Google for: " encryption" (p.e. "PostgreSQL encryption") to learn more ---------------------------------------------------------------------- thehidden · 2019-05-11 13:25 QuoteMost database servers offer that possibility: PostgreSQL, Firebird, etc. In fact, SQLite is one of the few which doesn't have it as a core feature but through a plugin. Yes, and the administrator is not EXCLUDED from accessing. In other words: The DB Administrator has still full access, even the Database is encrypted. Exeption is SQLite for Single User, where the Password can be stored in the application. But I need it for multiuser environements. It is a must be that the DB Administrator is excluded from accessing/altering the table content. MsSQL 2016 could to it in theroy. In reality you would lost the usage of joins, where and between in SQL Statements. Its is not that a DB Administrator is not trustworth. It is a legal requirement from my customer in germany for this kind of application. ---------------------------------------------------------------------- valdir.marcos · 2019-05-11 14:51 Quote from: thehidden on May 11, 2019, 12:18:55 amHi, I have a customer request to use a database with table encryption. Meaning that even the database administrator can not read/add/alter the database content. Password for the tables will be send by my software to the database server. Database administrator can do everything else on the DB Server, exept accessing the content of the table. Does anybody knows a database which works together with Free Pascal/Lazarus and has this feature? SQLite has it, but this is a single user Database. Thank you for help in advance. TheHidden Quote from: lucamar on May 11, 2019, 12:56:44 amMost database servers offer that possibility: PostgreSQL, Firebird, etc. In fact, SQLite is one of the few which doesn't have it as a core feature but through a plugin. Google for: " encryption" (p.e. "PostgreSQL encryption") to learn more Quote from: thehidden on May 11, 2019, 03:25:05 pmQuoteMost database servers offer that possibility: PostgreSQL, Firebird, etc. In fact, SQLite is one of the few which doesn't have it as a core feature but through a plugin. Yes, and the administrator is not EXCLUDED from accessing. In other words: The DB Administrator has still full access, even the Database is encrypted. Exeption is SQLite for Single User, where the Password can be stored in the application. But I need it for multiuser environements. It is a must be that the DB Administrator is excluded from accessing/altering the table content. MsSQL 2016 could to it in theroy. In reality you would lost the usage of joins, where and between in SQL Statements. Its is not that a DB Administrator is not trustworth. It is a legal requirement from my customer in germany for this kind of application.Unless you customer is highly proficient  in encryption techniques, you have some classic alternatives: 1. encrypt hard disk; 2. encrypt database; 3. encrypt via software only some fields from some tables. For instance: id, name, password etc. If you want to go beyond that, you can customize the last solution by asking for a "pass phrase" each time your software or specific form is opened. You can use that "pass phrase", which is unknown to everybody else besides your customer himself, to encrypt/decrypt stored information on database. ---------------------------------------------------------------------- lucamar · 2019-05-11 15:09 Quote from: thehidden on May 11, 2019, 03:25:05 pmYes, and the administrator is not EXCLUDED from accessing. In other words: The DB Administrator has still full access, even the Database is encrypted. Exeption is SQLite for Single User, where the Password can be stored in the application. I think you're conflating two different issues: access control and data control. Data from an encrypted database shouldn't be decodable by anyone without the key, not even the administrator. Indeed, probably one of the more frequent reasons to encrypt a database is to prevent having the data read in clear even if the server machine and/or the DB admin account themselves are compromised. Access control is quite another thing: it just prevents someone else from sending/receiving data to the server: it's more a question of who can communicate with the DB server than of what those able to do it can do after that. You can see that having the DB admin unable to communicate with it is ... not a good idea ---------------------------------------------------------------------- thehidden · 2019-05-11 15:41 Lucamar QuoteI think you're conflating two different issues: access control and data control.Not realy me, but tax law in germany. The CPA from my customer is auditing the company and told my customer that the tax law requires that all actions in the database must be logged. No alternation, adding or deleting without a log entry. Even when it has been done by the DB Administrator. If last is not managed or possible by the Database Server, it must be ensured that the DB Administrator can not access the table content. My customer has shown me the relevant parts of the tax law and the guidlines "GoB". Sorry, but it is realy written inside that an auditor can count on this "feature". Vladir.marcos QuoteUnless you customer is highly proficient  in encryption techniques, you have some classic alternatives: 1. encrypt hard disk; 2. encrypt database; 3. encrypt via software only some fields from some tables. For instance: id, name, password etc. As written above, it looks like that I can use only 3. to get the order from my customer. Because the others does not fit their tax law. ---------------------------------------------------------------------- lucamar · 2019-05-11 15:49 Quote from: thehidden on May 11, 2019, 05:41:54 pmNot realy me, but tax law in germany. The CPA from my customer is auditing the company and told my customer that the tax law requires that all actions in the database must be logged. No alternation, adding or deleting without a log entry. Even when it has been done by the DB Administrator. If last is not managed or possible by the Database Server, it must be ensured that the DB Administrator can not access the table content. My customer has shown me the relevant parts of the tax law and the guidlines "GoB". Sorry, but it is realy written inside that an auditor can count on this "feature". Yes, I see. Hmmm ... there must be a way, or all German businesses would be outlaws Let me chaeck a couple things: there may be a simpler way of preventing the admin from accessing some tables. In the meantime you could check stackoverflow and similar; this is not really a FPC question but a general database one. ---------------------------------------------------------------------- john horst · 2019-05-11 16:00 This post is rather cringe... The accountant should hire a security professional. By doing what you are doing... lets me know you are not one. If you don't trust the Admin then get another Admin. CouchDB is what you are looking for... You can tail, disk encrypt, has roles baked in, fault tolerent and it will keep revisions. Master Master replication. I hope you at least have the keys to the kingdom use a revocable cert.... I'm rather confused how you would keep a malicious admin from getting the top secret password being sent to the machine he is logged into.... ---------------------------------------------------------------------- lucamar · 2019-05-11 16:15 Been thinking about this and I think you should attack the problem from the other side. The requirement is two-prong: either preventing the admin from accessing or log all accesses. Preventing the admin from accessing is nigh impossible, so instead, look for a way to log accesses regardless of who makes them. That should be easier ... maybe. We are already outside my experience-field, sorry. ---------------------------------------------------------------------- thehidden · 2019-05-11 16:41 Quote from: john horst on May 11, 2019, 06:00:42 pmThis post is rather cringe... The accountant should hire a security professional. By doing what you are doing... lets me know you are not one. If you don't trust the Admin then get another Admin. CouchDB is what you are looking for... You can tail, disk encrypt, has roles baked in, fault tolerent and it will keep revisions. Master Master replication. I hope you at least have the keys to the kingdom use a revocable cert.... I'm rather confused how you would keep a malicious admin from getting the top secret password being sent to the machine he is logged into.... Sorry, but maybe wrong understood. I am writing software and have got a request from a possible german customer. My customer has an internal audit to simulate an official tax audit. They are testing 100 % Law Compliance. Problem: As written above; DB Administrators, their possible access and the german tax law. There is no reasen, question, concerns, etc. about a "trustworth" Administrator. It is simple Law compliance. Nothing more, nothing less. If it is to complicated, I will tell my customer "Change the law, or change your location (Spain is nice, Ireland is nice) or have luck finding somebody else. As long as you are in germany, I don't sell you any software.". ---------------------------------------------------------------------- john horst · 2019-05-11 16:52 My point is, by having the password at two locations, the server and your app, you now have two places to retrieve the password. If you want the data encrypted inside, encrypt it with gpg or something and insert, don't give the admin the key to decrypt. That won't stop him from modifying though, only modifying the original doc. Now, if this data is always being decrypted to plain text and displayed... then once again the key is useless. The key has to be stored somewhere even if you send it, memory perhaps? I think  he is misinterpreting German law. You are asking the impossible.  The strategy is no different than a traditional password authentication. Take password -> bcrypt -> insert. It won't stop the admin from just removing the record or replacing. ---------------------------------------------------------------------- thehidden · 2019-05-11 17:08 Quote from: john horst on May 11, 2019, 06:52:36 pmMy point is, by having the password at two locations, the server and your app, you now have two places to retrieve the password. If you want the data encrypted inside, encrypt it with gpg or something and insert, don't give the admin the key to decrypt. Now, if this data is always being decrypted to plain text and displayed... then once again the key is useless. The key has to be stored somewhere even if you send it, memory perhaps? I think  he is misinterpreting German law. You are asking the impossible. Yes, I think you are right. It sounds like a Mission Impossible. Thanks to all for your help. I was unsure if this can be handled or not. Now I will give the customer a kick and let him know he need somebody else for writing a software keeping all the rules. ---------------------------------------------------------------------- john horst · 2019-05-11 17:32 Well don't give up that easy. I told you how to do it. I modified the post above. Use CouchDb, set up roles, limit what the admin can and can't do. It keeps a revision. I keep all my source code in it, and you can replicate it as well. See real time changes by tailing / LOG. Store the password in a file on the same server as the db. Make sure it has proper permissions. This is the best you can do really, the proper thing. It's acid complaint and fault tolerant. If Germany has a problem they need to get over it lol. Bonus it has Authentication baked in too. You don't have to worry about messing it up. ---------------------------------------------------------------------- thehidden · 2019-05-11 17:42 I must see if I can configure CouchDB by script from my application. CouchDB isn't SQL. Must see how I can work with it. My current application I was planning to upgrade for this customer works with an SQL Database. Maybe I will think about in the next few days. ---------------------------------------------------------------------- john horst · 2019-05-11 18:05 Well it sounds like a chore. The concept is still the same though. You can't guarantee without having a couple of copies of the same document in the database. A revision. You have to limit what the admin can modify, delete, which would be the record that was reinserted encrypted. The Admin can't see / change / delete that. ---------------------------------------------------------------------- john horst · 2019-05-11 18:14 You could always just have data location 1 (Admin DB) & Data location 2 (Unmodified, encrypted data). It cuts both ways, you can prove the admin tampered with location 1 but had no access to location 2. Encrypt what you don't want the admin to see. If he deletes the record restore from data location 2. ---------------------------------------------------------------------- lucamar · 2019-05-11 20:09 In case it serves you, this is from the PostgreSQL manual: Quote from: ‪"PostgreSQL 11 Doc.: 18.8. Encryption Options"Client-Side Encryption If the system administrator for the server's machine cannot be trusted, it is necessary for the client to encrypt the data; this way, unencrypted data never appears on the database server. Data is encrypted on the client before being sent to the server, and database results have to be decrypted on the client before being used. The only problem with that approach re. the German law is if the admin (or other) delete data; any other operation would make unencrypted data appear in the database, which is a dead give-away of tampering. I don't know the German law, but most laws of that kind don't really care about whether data is tampered with or not but that, if tampered with, the fact (and, if possible, the culprit) be made obvious. ---------------------------------------------------------------------- ahiggins · 2019-06-04 18:25 @thehidden  ***I might be well off target with this***  Have you looked at Firebird 3 it seems you can write your own plug-in, (pascal example with FB3PATH\examples\dbcrypt.pas) and looks like it encrypts users data at a page level. Oh I'm struggling porting the CryptKeyHolder.cpp to pascal if anybody can help (my C++ is extremely limited)