0

For encryption purposes of some columns I wrote UDFs. I then realized that passing the keys through SQL is nonsense as the SYSDBA can trace SQL and look into session environment. And he exactly is the one, who should not access the data.

Full database encryption is no option for me due to the fact that most of the data does not need to be encrypted and speed is an important thing.

My approach is to transfer the key from client to server with ECC public/private key technique. This is easy to accomplish but where can I store the key for a session within my UDF? Can I get hold of some kind of information about the session of the caller within my UDF?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
MichaSchumann
  • 1,361
  • 1
  • 17
  • 36
  • 1
    Stop using UDFs (they will be unsupported in the future because of inherent security risks) and switch to UDRs. But this only moves the security problem, because both UDFs and UDRs are running in-process, so by definition an administrator with sufficient access will be able to dump memory and obtain sufficient information (or otherwise subvert data or code to do this). The only real way is to never needing the key inside the database server, but do encryption and decryption in the client (which then makes it an application problem, with similar security issues with regard to admins). – Mark Rotteveel Feb 06 '19 at 12:56
  • you can use USB encryption tokens, like HASP/Sentinel/Gemalto – Arioch 'The Feb 06 '19 at 13:26
  • @Mark: your are right. I have to find a balance between difficulty to access data and usability especial selecting data. I currently do complete encryption on the client and use additional hashes to allow searching for some information but that is a pain and introduces new attack points with data that is predictable like sex or so. The typical admin can look into tables and trace sql but he cannot dump and analyze memory or write programs to do so I would say. – MichaSchumann Feb 06 '19 at 13:45
  • @Arioch: Yes, thanks, but that is not applicable in the environment I am targeting. – MichaSchumann Feb 06 '19 at 13:46
  • @Mark: Is there any good tutorial on writing UDRs with Delphi (10.3)? – MichaSchumann Feb 06 '19 at 13:47
  • I don't program in Delphi, so I'm not aware of any. – Mark Rotteveel Feb 06 '19 at 14:12
  • I think I will go with UDF for now as information un UDRs for Delphi is absolutely rare and I already have my UDF library for Linux, Windows and Mac mainly up and running. Also, Firebird 4 seems only to set the default to disallow UDFs but it can be enabled... https://www.firebirdnews.org/udfs-are-deprecated-in-firebird-4/ – MichaSchumann Feb 06 '19 at 14:16
  • why not just have encrypting/decrypting in the client itself ? The client is the entity that has the "secret key", then let it just be using it, instead of passing it around into uncontrolled network land – Arioch 'The Feb 06 '19 at 14:37
  • Well, if you are really that heavily into this endeavor, which looks pretty pointless to me, then you can make the Firebird's `CURRENT_CONNECTION` built-in variable a required parameter to all the UDF calls. Inside the Delphi-written UDF then you would have a global variable of `TDictionary` type. You would envelope it into multithreading locks though. – Arioch 'The Feb 06 '19 at 14:42
  • `The typical admin can look into tables and trace sql but he cannot dump and analyze memory or write programs` - why would he? UDFs and UDRs are designed to be called by SQL requests, actually they are only called by means of SQL. So "trace SQL" is exactly the tool to copy the decrypted data on every UDF call. – Arioch 'The Feb 06 '19 at 14:45
  • Hopefully in the observable future a connection to Firebird Server would not be able to migrate across several Firebird Server processes. – Arioch 'The Feb 06 '19 at 14:48
  • @arioch: For data I transfer to the client I would decrypt on the client. But for queries and comparison I need decryption on the server. And as the key is transferred via public key to the UDF library and stored inside (that's the plan) only the session can use the key. Or am I wrong and the Admin can also take over a complete session? – MichaSchumann Feb 07 '19 at 08:57
  • @MichaSchumann to make efficient queries you would have to have indexes and some hashing. Decrypting on server would at best decrease network traffic but would still make server enumerate all potential rows, like a "natural scan" (if there would be no other limiting conditions by public columns). Not quite efficient, it seems. It looks the best option would be to move from 2-tier client/server model to the 3-tier client/application server/SQL server model. And then appserver would do decryption and maybe filtering. See DataSnap, mORMot and similar approaches. – Arioch 'The Feb 07 '19 at 09:02
  • W.R.T. admin - I think that SQL tracing exposes all the query parameters, so as soon as you would pass your secret key to the UDF - the key would be stored in the log. Also as the admin would see UDF declarations - he can easily do a proxy(MITM) DLL with same functions, logging data and calling your DLL. Granted, you may try some challenge-response schemes, like UDF generates temporary key, sends it to client, and client sends its own key encrypted. But the more complex is the scheme, the less reliable it becomes and it can make user support and remote problems investigation a nightmare. – Arioch 'The Feb 07 '19 at 09:05
  • Basically, as soon as you somehow seeded your UDF with decryption keys and started using it - nothing would stop SYSDBA from making one more connection to the database and starting calling the same UDF functions with the same secret key parameter value, decrypting all the data he might want. – Arioch 'The Feb 07 '19 at 09:09
  • The idea is that the UDF creates a ECC keypair for every session and the client can query the public key, encrypt the "real" key with it and send it back. So as long as the attacker does not look into the memory of the udf or takes over his session he will not be able to read the decryption key. But anyways, is it possible to disable the trace? – MichaSchumann Feb 07 '19 at 09:19
  • The main goal is to avoid that data is inadvertently exposed to service personell or admins checking something in the database. The criminal energy in the environment I am targeting is very very low, and the admins/service people have many other ways to access personal data. So It is more a kind of obfuscation using encryption. It should be "hard" for normal skilled admins to access the data. – MichaSchumann Feb 07 '19 at 09:27
  • I guess SYSDBA can disable the trace, but then he can enable the trace too :-D // `creates a ECC keypair for every session` - then the client disconnects, the DLL is unloaded - and the secret key is lost for good. The once encrypted data would never get decrypted. // Think also about backup-restore, imagine your users would erroneously delete some data, in 4-5 months they would notice it, and then they would try to restore 5 months ago FBK to export the missed data form it into the up-to-date database. Will they be able to decrypt that old backup? – Arioch 'The Feb 08 '19 at 10:39
  • The keypair would only serve for secure transfer of the aes key for the data encryption to the udf. – MichaSchumann Feb 08 '19 at 10:40
  • you need obfuscation? use ZIP(or any other packer) inside base64 (or any other baseNN), then cut the tale-telling "=" or "==" on the data end, and transcode from standard base64 alphabet to some your custom one (substitution cypher) – Arioch 'The Feb 08 '19 at 10:41
  • good idea thanks. As I now stumbled across sorting as an n other issue with encrypted columns I have to rethink the whole thing. – MichaSchumann Feb 08 '19 at 10:43
  • `The keypair would only serve for secure transfer of the aes key for the data encryption to the udf.` - you would still have to pass to those UDF calls some "handle" to tell them which of many registered AES keys to pick for the requested function call. In my example above that was `CURRENT_CONNECITON` value. As long as SYSDBA traces SQL requests - he would learn this handle/token value, and then he can call those very same UDF functions passing them that handle to decrypt the data. The attacker does not have to KNOW THE key, he only has to know HOW to make the key invoked. – Arioch 'The Feb 08 '19 at 10:48
  • `stumbled across sorting` of course, the "natural scan" I mentioned above. Typically I would add indexed columns with extracted data and populate them by `before insert or update position 100000` trigger, which would call the UDF to extract the often used data parts from the BLOB. But, when the BLOB gets encrypted it all gets complicated :-) – Arioch 'The Feb 08 '19 at 10:52
  • Apart from all discussion I would like to come back to the core question: How can I get session information within an udf without having to feed it from outside? – MichaSchumann Feb 09 '19 at 08:38

0 Answers0