[MDEV-9571] Encrypted table does not show encrypted="yes" in definition Created: 2016-02-17  Updated: 2016-02-17  Resolved: 2016-02-17

Status: Closed
Project: MariaDB Server
Component/s: Encryption
Affects Version/s: 10.1.11
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Valerie Parham-Thompson Assignee: Jan Lindström (Inactive)
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Centos7


 Description   

Using the data encryption at rest feature, when I create a table explicitly via an alter statement, the show create table statement will show "`encrypted`='yes' `encryption_key_id`=1" in the definition. For example: 

CREATE TABLE `t1` (
 
  `intcol1` int(32) DEFAULT NULL,
 
  `intcol2` int(32) DEFAULT NULL,
 
  `charcol1` varchar(128) DEFAULT NULL,
 
  `charcol2` varchar(128) DEFAULT NULL,
 
  `charcol3` varchar(128) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 `encrypted`='yes' `encryption_key_id`=1

However, if I implicitly create an encrypted table using the default config, "`encrypted`='yes' `encryption_key_id`=1" does not show in this output.

my.cnf contents:

plugin-load-add=file_key_management.so
 
file-key-management-filename = /var/lib/mysql/keys.txt
 
innodb-encrypt-tables

Create a table with this global/default encryption in place:

create table t6 (
 
    -> `intcol1` int(32) DEFAULT NULL,
 
    ->   `intcol2` int(32) DEFAULT NULL,
 
    ->   `charcol1` varchar(128) DEFAULT NULL,
 
    ->   `charcol2` varchar(128) DEFAULT NULL,
 
    ->   `charcol3` varchar(128) DEFAULT NULL
 
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
 
 
insert into mysqlslap.t6 values (1,2,'show','the','definition');

Table appears to be encrypted when I view it via xxd:

snippet...
 
0006340: affb 892a 9f38 5f50 c3f0 ecda e4a0 4d5b  ...*.8_P......M[
 
0006350: d781 91af 5b64 20ef cac6 8300 3a0b 9673  ....[d .....:..s
 
0006360: fdd6 84ab d666 4a08 e34d 34a5 a7ed 5e8c  .....fJ..M4...^.
 
0006370: eece 66fe f435 e967 5264 6ab0 d1c7 9fa7  ..f..5.gRdj.....
 
0006380: e3a1 6570 aca2 21bc ac8a f1c7 42ee f8b7  ..ep..!.....B...
 
0006390: 2d8f fa4d a555 ba5c 5e52 774e 3e75 739d  -..M.U.\^RwN>us.
 
00063a0: 551c 3cb9 2ef3 bf9e f72b c2a7 3083 3ab6  U.<......+..0.:.
 
...snippet

But I can't tell that from within mysql:

show create table mysqlslap.t6\G
 
*************************** 1. row ***************************
 
       Table: t6
 
Create Table: CREATE TABLE `t6` (
 
  `intcol1` int(32) DEFAULT NULL,
 
  `intcol2` int(32) DEFAULT NULL,
 
  `charcol1` varchar(128) DEFAULT NULL,
 
  `charcol2` varchar(128) DEFAULT NULL,
 
  `charcol3` varchar(128) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 
1 row in set (0.00 sec)

or

MariaDB [mysqlslap]> show table status like 't6'\G
 
*************************** 1. row ***************************
 
           Name: t6
 
         Engine: InnoDB
 
        Version: 10
 
     Row_format: Compact
 
           Rows: 1
 
 Avg_row_length: 16384
 
    Data_length: 16384
 
Max_data_length: 0
 
   Index_length: 0
 
      Data_free: 0
 
 Auto_increment: NULL
 
    Create_time: 2016-02-16 17:02:33
 
    Update_time: NULL
 
     Check_time: NULL
 
      Collation: latin1_swedish_ci
 
       Checksum: NULL
 
 Create_options:
 
        Comment:



 Comments   
Comment by Elena Stepanova [ 2016-02-17 ]

Somehow, there is no unified behavior regarding showing implicit options in SHOW CREATE TABLE. More often than not engine-specific implicit options are not shown, but there are exceptions, e.g. PAGE_CHECKSUM for Aria).

Regarding encryption specifically, I think the whole point of implicit encryption is that it's not ingrained upon table creation, but can change depending on the server variables, and the table can be decrypted/encrypted on the fly. If the option appeared in SHOW CREATE TABLE, it would have been even more confusing, and generally unreliable.

My guesses might be wrong though, I'll assign this to jplindst to confirm it works as expected.

Comment by Valerie Parham-Thompson [ 2016-02-17 ]

Thanks, Elena. I look forward to learning more about how it works.

I did find a way to tell if a table is encrypted, I believe, with this query:

select name from information_schema.innodb_tablespaces_encryption where encryption_scheme=1;

Comment by Sergei Golubchik [ 2016-02-17 ]

If SHOW CREATE TABLE does not show the option, it means the option has the default value. In this case it means that if encryption is globally enabled — the table will be encrypted, if encryption is globally disabled — the table will be not encrypted.

Think of it that way — SHOW CREATE TABLE does not shows what properties the table has now, it shows what CREATE TABLE statement one should use to create a new table with exactly the same behavior as the original table. And to create a table that is encrypted when the encryption is globally enabled, and not encrypted when the encryption is globally disabled — such a table should not have ENCRYPTION=YES, that's why SHOW CREATE TABLE does not show it.

Comment by Valerie Parham-Thompson [ 2016-02-17 ]

OK, thank you for the explanation, Sergei.

Generated at Thu Feb 08 07:35:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.