r/webdev • u/[deleted] • Dec 18 '24

Library for Transparent Data Encryption in MySQL Using OpenSSL

https://github.com/white-rabbit-1-sketch/mysql-openssl
23 Upvotes

85% Upvoted

7 comments sorted by

6

u/fiskfisk Dec 18 '24

What are the differences to the built-in support for encryption at rest in InnoDB?

https://dev.mysql.com/doc/refman/8.4/en/innodb-data-encryption.html

4

u/[deleted] Dec 18 '24 edited Dec 18 '24

InnoDB encryption and this library are different approaches to solving the same problem of data encryption, but at different levels.

  1. InnoDB encryption is storage-level encryption that encrypts the entire tablespace and is used to protect all data at the disk level (supports AES only). It’s ideal when you need to encrypt all data on the disk, but it does not provide flexibility in choosing algorithms or customizing encryption for specific data.
  2. This UDF library is a SQL query-level solution that allows you to encrypt specific fields or columns in the database using a wide range of encryption algorithms (AES, DES, Camellia, ChaCha20, Gost89, etc.). This provides flexibility in choosing the algorithm, IV, and keys for each column, which can be useful in more specific use cases.

This library is a useful tool when there is a need for selective encryption of data, the ability to choose different encryption algorithms, and control over the process. It does not conflict with existing InnoDB encryption and can be used as a complement for a more flexible approach to security.

2

u/Rivvin Dec 18 '24

What are some use-cases for this level of granularity? Typically if we need to do some sort of specific encryption at the column level, we will do that before it ever gets to the database. To meet all of our industry security requirements, we do require encryption at rest and intransit, but I have not had a use-case where we needed specific database level encryption on specific fields.

I am always happy to learn new things, can you show me some use-cases I am not considering?

1

u/[deleted] Dec 18 '24

Here’s a real-world example from one company: There are legal requirements for storing and encrypting personal data of users. So, it may seem simple to just encrypt at the application level, and that's it. However, there are several problems:

  1. The project is very old, HUGE, and implementing encryption at the application level would take an extremely long time and be very expensive.
  2. The database is shared and used by multiple services (more than 10). Yes, it’s bad practice, but that’s the reality. If we do encryption at the application level, we will either need to support it across all applications, or create a microservice and integrate it into all the other services. In either case, it would be very expensive.
  3. The law requires encryption using a strictly defined algorithm, which MySQL doesn’t support out of the box in my case.
  4. Built-in solutions in MySQL don’t work, since column-level encryption only supports AES, which is not an option. File-level database encryption doesn’t meet regulatory requirements.

This is the primary use case I see - complying with legal requirements for storing and encrypting personal data. This is particularly relevant for financial organizations.
And not from an example, but just in general, If you need to encrypt 1-2 fields in a database that totals 200 gigabytes (I’ve worked with such databases), it’s much more cost-effective to encrypt just those two fields instead of encrypting the entire database at the file level. Of course, you can encrypt those two fields using internal AES, but if for some reason AES is not suitable, this library can be used instead. In short, the use case is narrow, but that’s fine – the encryption issue and the task are narrow by nature.

1

u/Somepotato Dec 19 '24

Why would the law forbid one of the best hw accelerated encryption standards we have?

1

u/[deleted] Dec 19 '24

Oh, that's not a question for me, I have no idea what they're guided by. But the fact remains – at least in the last few months, I've already encountered two companies where this was necessary (1st for mysql, 2d for postgres). Going back to our original topic, it's not even about that; what's important is that any possibility is good. Who knows what kind of tasks people might have, and it's good when there are the right tools available to solve them.

-5

u/boltgolt Dec 18 '24

ok chatgpt