7

I have set up table-level InnoDB database encryption on MariaDB.

I'd like to know if there is any way to confirm that the data is truly encrypted. I've tried searching /var/lib/mysql/ibdata1 for sample data in the tables, but I don't know if that's a reliable test or not.

Josh Correia
  • 3,807
  • 3
  • 33
  • 50
MarkRoland
  • 915
  • 2
  • 9
  • 13

4 Answers4

8

I posted this question on mariadb.com, and the suggestion there was to perfom a grep for some known data.

A DBA at Rackspace suggested using the strings command instead, to better handle the binary data, for example:

strings /var/lib/mysql/sample_table/user.ibd | grep "knownuser"

This approach returns no results on an encrypted table and does return results on an unencrypted table (assuming both have "knownuser" loaded into them).

MarkRoland
  • 915
  • 2
  • 9
  • 13
2

You can query information_schema.innodb_tablespaces_encryption. When innodb tablespace is encrypted it is present in the table.

SELECT * FROM information_schema.INNODB_TABLESPACES_ENCRYPTION 
WHERE NAME LIKE 'db_encrypt%';

source

Luis
  • 152
  • 1
  • 4
  • 1
    with 10.5.5 that becomes: `SELECT * FROM information_schema.INNODB_TABLESPACES_ENCRYPTION` and all encrypted tables are listed there. – philw Sep 25 '20 at 14:32
0

My advice for testing is to copy the full dataset to another node without the encryption keys in place and try to start MySQL and query the encrypted tables. I'm making an (big) assumption that they will not be readable since the valid encryption keys are missing.

To parse the files on disk as they lay may prove difficult unless you have a special tool to do this. Maybe something like Jeremy Cole's innodb_ruby would be another litmus test https://github.com/jeremycole/innodb_ruby.

eroomydna
  • 1,261
  • 9
  • 4
  • Thank you. I'll try that out and post back here.I've also re-posted this question on the Mariadb.com Knowledge Base (https://mariadb.com/kb/en/mariadb/verifying-mariadb-101-encryption/#comment_1823) – MarkRoland Nov 23 '15 at 23:01
  • I haven't had a chance to test this, although it seems like a legitimate solution. I have another answer, which I am going to post below. – MarkRoland Nov 26 '15 at 00:05
0

[probably don't works if you change the key which encrypts the log.]

  • Stop the database server.
  • BACKUP the keyfile

  • Change a key in the keyfile. (don't delte - it still has to remain a valid key otherwiese the server can't restart)

  • Start MariaDB again.

  • Try to read the table (e.g. with phpMyAdmin).

If encrypted correctly there is an answer: "The table is encrypted..." when trying to read the encryted table.

  • Stop Maria
  • Restore the backup
  • Restart Maria
Grischan Glaenzel
  • 39
  • 6
  • I did that severall times and mariadb works perfectly when providing a wrong key and when changing the key back to the orginal and i tried that on three different servers. There are only problems when you don't provide a valid key. So i can't belive that this alone caused the problem. – Grischan Glaenzel Feb 06 '17 at 13:54
  • Ah, not providing a valid key may be the issue I had then! Thanks! – Kaspar Lee Feb 07 '17 at 09:28