Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17266

Document how to determine which Aria tables are encrypted

    XMLWordPrintable

Details

    Description

      You can query information_schema.INNODB_TABLESPACES_ENCRYPTION to determine which InnoDB tables are encrypted:

      https://mariadb.com/kb/en/library/encrypting-data-for-innodb-xtradb/#seeing-which-tables-are-encrypted

      https://mariadb.com/kb/en/library/information-schema-innodb_tablespaces_encryption-table/

      Aria tables can also be encrypted if aria_encrypt_tables is enabled:

      https://mariadb.com/kb/en/library/aria-system-variables/#aria_encrypt_tables

      However, I can't tell if there is actually any way to determine which Aria tables are encrypted.

      The only way I've been able to come up with is by finding out which Aria tables use the row_format PAGE:

      SELECT TABLE_SCHEMA, TABLE_NAME 
      FROM information_schema.TABLES 
      WHERE ENGINE='Aria' 
      AND ROW_FORMAT='PAGE'
      AND TABLE_SCHEMA != 'information_schema';
      

      And then finding some data from a particular table:

      MariaDB [(none)]> SELECT * FROM db1.aria_tab LIMIT 1;
      +----+------+
      | id | str  |
      +----+------+
      |  1 | str1 |
      +----+------+
      1 row in set (0.00 sec)
      

      And then checking whether you can find some plain text from that data in the data file:

      $ sudo strings /var/lib/mysql/db1/aria_tab.MAD | grep "str1"
      str1
      

      However, it would probably make sense to have an information_schema table that says which Aria tables are encrypted.

      Also, we should document whether Aria tables are also encrypted/decrypted by InnoDB's background encryption threads (configured by innodb_encryption_threads and innodb_encryption_rotate_key_age) or if they are encrypted/decrypted by some other means. My tests seem to indicate that Aria tables are not encrypted/decrypted by InnoDB's background threads. In fact, it appears that the encryption status of an Aria table depends solely on the value of aria_encrypt_tables at the time that it was created. See MDEV-17267.

      Attachments

        Issue Links

          Activity

            People

              GeoffMontee Geoff Montee (Inactive)
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.