Mysql by default AES for all tables in the database

Question

I need to hide data from user access in mysql. Even if the user knows the password, they may need a key to decrypt the data. is it possible?

In mysql there is AES_ENCRYPT option for encrypting data on insertion, is there an option to give standard encryption for all columns of the table in the database?

Answer 1

you probably need to do this in your application and not directly in mysql. store data encrypted but decrypt / encrypt in application level code

Answer 2

What is the point of allowing the user to connect if they do not have a decryption key and all tables are encrypted? You need to edit your question to describe your use case in more detail.

Access control for each user is more flexible. Suppose you use encryption, and one day you want to disable access for one specific user. You will need to change the key, which means that you must re-encrypt all the data with the new key, and then notify all other users of the updated key. It is very uncomfortable.

If you just use your login or your GRANT privileges to control access, you can disable any user account and / or use REVOKE to change your privileges. All other users will still have the access that they did before. It is much simpler.

In addition, MySQL does not have the global option "encrypt all tables". It does not even have the ability to encrypt all the data inserted in this table.

MySQL has some encryption functions , such as AES_ENCRYPT() , but it is processed at the level of individual SQL expressions:

 INSERT INTO MyTable SET someColumn = AES_ENCRYPT('Something sensitive', 'thePassword'); 

You will need to do this every time you insert or update a row.

Then decrypt the same every time you choose:

 SELECT AES_DECRYPT(someColumn, 'thePassword') FROM MyTable... 

Someone above MariaDB encryption mentioned. It does not do what you want. This means that the table space file on the disk is encrypted, but still the MariaDB server automatically decrypts it for everyone who connects to the server. Thus, it is no better than SQL access privileges. It also cannot encrypt query logs or error logs or binary logs.