1

I am using MySql 8.0.11 and using keyring_file plugin, I have encrypted a particular table, say t1, in my database.

When I check the contents of t1.ibd file I can see that the contents are encrypted after successful encryption. But, I continue to see the table contents using query (select * from t1) even if the contents in the ibd file are encrypted.

So, does it mean that the encryption works on ibd files only (which contains the data and the indexes) but I will continue to see the table contents without any issue if I have the database credentials?

UPDATE

I read a couple of comments and would like to add the below questions to clarify my original query: After encrypting the ibd files, if a hacker hacks into my system on which the database is hosted, the hacker would be able to see the actual data. So, how has encrypting the ibd files helped me secure the data?

Ravi Agrawal
  • 56
  • 4
  • i geuss you want AES (Advanced Encryption Standard) algorithm https://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html#function_aes-decrypt.. also Read the Caution above the function (which is off screen) – Raymond Nijland Sep 07 '18 at 13:34
  • I know nothing about MySQL encryption, I stumbled across this question by complete accident, but what you are describing makes perfect sense to me. The purpose of a database is to be able to query and retrieve the data back that you put in. If you couldn't query the encrypted data, then what's the point in storing it in the first place? You would never be able to read it again. It's basically as if you had deleted it, but it's still wasting space on disk. – Jörg W Mittag Sep 07 '18 at 13:40
  • @RaymondNijland AES would be applied on per field, but what I want is to encrypt the entire table. – Ravi Agrawal Sep 08 '18 at 04:36
  • @JörgWMittag I understand that we should be able to query the encrypted data to get the actual result (which is not encrypted), but my question is how are we achieving data security with Keyring plugin encryption? – Ravi Agrawal Sep 08 '18 at 04:43
  • I don't understand your question. The keyring plugin doesn't provide encryption. The keyring plugin stores / manages passwords and other secrets. – Jörg W Mittag Sep 08 '18 at 07:56
  • "AES would be applied on per field, but what I want is to encrypt the entire table." Indeed on per column use it on every column on the table then you have encrypted the complete table. But atleast `SELECT * FROM table` does not give "visible"data when you use AES in MySQL that is your question. – Raymond Nijland Sep 08 '18 at 08:57
  • @RaymondNijland - I want to encrypt the entire database. But to check if it is working, I was encrypting one single table. I took reference from https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html which suggests how to use keyring_file plugin to enable encryption. After enabling the keyring_file plugin, we just need to alter the table and set Encryption = Y, which should encrypt the table data. Now, I followed the steps mentioned in the link and was able to encrypt the ibd files but I don't understand how I am protecting the data using the ibd files? – Ravi Agrawal Sep 09 '18 at 04:18
  • @JörgWMittag Yes, keyring file is used to store the key but using this plugin you can have tablespace encryption in MySql. I am able to encrypt the ibd files in Mysql but want to understand how is it helping me to avoid any hacker attack. – Ravi Agrawal Sep 09 '18 at 04:21
  • ... avoid **any** hacker attack ... -- Don't be lulled into a false sense of security as with the [_Gordian Knot_](https://en.wikipedia.org/wiki/Gordian_Knot) – Rick James Oct 03 '18 at 01:45
  • Even, i'm facing the same issue. How DB table contents be encrypted? – Pooja Jan 15 '21 at 07:10

1 Answers1

1

An .ibd file contains all the data and all the indexes for a table. ("Tablespaces" can contain multiple tables; the principles are the same.)

With the plugin (etc), SELECT automagically goes through the decryption, making the encryption 'transparent'. Nevertheless it is real. You did have to do something to start the program, correct? That was when the 'key' was loaded into RAM for use.

Encrypting .ibd files protects you from one threat: someone grabs (or copies) your disk drive.

But beware. There are temp tables, binlogs, other logs, etc, that may or may not be encrypted. They temporarily hold some of the data. Early versions of MySQL encryption failed to include some of these.

The AES functions let you en/decrypt individual strings (such as one column in one row at a time). But this leaves it up to you to protect the en/decryption key. Or at least never have it on disk as plaintext.

Read about "encryption at rest" versus "encryption in flight". Encrypting the files is "at rest". A smart hacker will attack your code so that he can run a SELECT after the credentials have been loaded.

With or without encryption, "SQL injection" (qv) is a well established way to hack into the data, and even into the filesystem. The protection for this comes, for example, comes by validating/escaping/etc data that comes from an HTML <form>. Encrypting files is no protection against this.

I have given you a short list of threats against your data. The real list is much longer. You need to find such a list and decide which ones you are willing to invest in protecting against. Security is not simple.

Rick James
  • 135,179
  • 13
  • 127
  • 222