WL#12261: Control (enforce and disable) table encryption

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

Currently the option that controls if a table is encrypted or not is:
* Specified per table.
* Everybody can specify it (no extra privileges required) and with any value.
* Has a single compiled in default (off).

This is not very convenient in environments where DBA want to
grant CREATE TABLE to users while still retaining some control
over encryption usage, e.g.:
* They want to enforce that all tables in a certain database are encrypted
* They want to make sure people don't do double encryption since the underlying
  storage engine is encrypted. 

This WL aim to provide following functionality:

  1) Enforce table encryption globally:

  Enable all tables to be encrypted by default. Add global system
  variable 'default_table_encryption'. This variable value would be used
  as default encryption type for the commands CREATE DATABASE and
  CREATE TABLESPACE. This would enable a way to keep all tables
  as encrypted in a MySQL server instance.

  2) Force encryption for certain database:

  Enable all tables in the specific database be encrypted by
  default. Add new DEFAULT ENCRYPTION clause for database to
  support the same. Databases would inherit the global
  'default_table_encryption' as DEFAULT ENCRYPTION if user does not
  supply one.

  3) Disable encryption globally:

  Disable tables encryption in MySQL by default. The system
  variable 'default_table_encryption' enables the same.  This could be
  helpful to avoid double encryption since underlying storage is
  encrypted already in cloud setup.

  4) Backward compatible setup:

    Keep CREATE TABLE ... ENCRYPTION=Y|N and
    CREATE TABLESPACE ENCRYPTION=Y|N work as before.

  5) Introduce a privilege TABLE_ENCRYPTION_ADMIN. This would enable
  user to override the checks mentioned in case 6) below.

  6) Introduce a switch which indicates whether we do privilege
  check if user tries to use non-default value for CREATE DATABASE
  or CREATE TABLESPACE  or when he tries to do CREATE TABLE with
  ENCRYPTION option which deviates from per-database default.
  Following could be the system variable name. It be defined as
  GLOBAL variable.

     --table-encryption-privilege-check=Y|N
FR1) Add new boolean system variable 'default_table_encryption',
     with GLOBAL+SESSION scope. Allow PERSIST/PERSIST_ONLY on this
     variable.

FR2) Allow system variable 'default_table_encryption' to be
     changed during runtime as described in section I.C).1.

FR3) Add new boolean system variable
     'table_encryption_privilege_check', with GLOBAL scope and allow
     behavior as defined in I.C).2.

FR4) Add new privilege TABLE_ENCRYPTION_ADMIN.

The following four FR's point to items in HLS, because each item
in these sections pretty much define a FR.

FR5) Implement HLS II) A) 1., 2. & 3.

FR6) Implement HLS II) B) items 1. to 3.

FR7) Implement HLS II) C) items 1. to 3.

FR8) Implement HLS II) D) items 1. to 11.

FR9) Implement HLS II) E) items 1. to 7.

FR10) Restoring the database and tables into same or higher
     version of mysql would retain the original encryption mode,
     unless we have following condition during restore,

     a) User misses TABLE_ENCRYPTION_ADMIN privilege
     b) AND 'table-encryption-privilege-check' is 'Y'


Upgrade related:

U1) Upgrade from older 8.0 version to 8.0 with WL12261 would
      succeed and may not be affected by both
      'default-table-encrpytion' and 'table-encryption-privilege-check'
      settings.

U2) Upgrade from 5.7 version to 8.0 with WL12261 will force
      --table-encryption-privilege-check='N' during upgrade.


Replication related:

R1. Replication should preserve encryptedness of tables, when the
    encryptedness is specified as Y/N/unspecified in the
    CREATE DATABASE statement.

R2. Replication should preserve encryptedness of tablespaces, when
    the encryptedness is specified as Y/N/unspecified in the
    CREATE TABLESPACE statement.
I. INTRODUCTION:
````````````````

The WL introduces following functionality:

A) Support DEFAULT ENCRYPTION property for DATABASE.

 1. The purpose of this default is to defines if tables under the
    database should be encrypted or unencrypted by default.
  
 2. This default is always set for a database. Either from server
    global default is inherited or it is explicitly specified by user.
    We do not differentiate between implicit and explicit values
    here.

 3. Add new DD column mysql.schemata.default_encryption with
    type ENUM('No', 'Yes') to store the new DEFAULT ENCRYPTION
    database property.


B) Support new privilege TABLE_ENCRYPTION_ADMIN. 

   The primary purpose of this new privilege is to enable users
   to override global or per-database ENCRYPTION properties,
   introduced in this WL. Detailed explanation on behavior
   changes are in HLS.


C) Add system variables to control following two behaviors,

  1. The first system variable to control global encryption default, which
     it is used when someone does CREATE  DATABASE and CREATE
     TABLESPACE without ENCRYPTION clause, it doesn't affect CREATE
     TABLE directly. Following is the system variable name:

     --default-table-encryption=Y|N
     
     This variable,
     * Would be GLOBAL+SESSION variable. A SESSION variable because of alteast
       following two primary reason,

       - Allow replication slave applier thread to use the
         --default-table-encryption value stored in the log event
         and set it per session during DDL execution.

       - Allow applying binary logs using mysqlbinlog tool.

     * Allow --default-table-encryption to be changed during runtime, by a
       user who owns both TABLE_ENCRYPTION_ADMIN and SYSTEM_VARIABLES_ADMIN
       privileges.

     * Allow SET PERSIST/PERSIST_ONLY on this variable, which is useful
       in cloud environment.

     * Allow users with SUPER and TABLE_ENCRYPTION_ADMIN privilege to set
       the variable.


  2. The second option is a switch which indicates whether we do
     privilege check if user tries to use non-default value for
     CREATE DATABASE or CREATE TABLESPACE  or when he tries to do
     CREATE TABLE with ENCRYPTION option which deviates from
     per-database default. Following is the system variable name. It
     be defined as GLOBAL variable.

       --table-encryption-privilege-check=Y|N

     * Allow SET PERSIST/PERSIST_ONLY on this variable, which is useful
       in cloud environment.

     * Allow users with SUPER privilege to set the variable.

  
  Note:
  We can think of a design to achieve the same using single variable as
  following, but it may be complex for end-users to understand. So, we
  are not going this way. This is JFYI.

  # Use single server variable '--table-encryption'.

    The variable --table-encryption would support three values
    'default|enabled|disabled'.

    'default' would mean,
       - we disable 'default-table-encryption' = N.
       - we disable 'table-encryption-privilege-check' = N.

    'enabled' would mean,
       - we enable 'default-table-encryption' = Y.
       - we enable 'table-encryption-privilege-check' = Y.

    'disabled' would mean,
       - we disable 'default-table-encryption' = N.
       - we enable 'table-encryption-privilege-check' = Y.

D) Allow ENCRYPTION clause for tables using general tablespace.

  This allows more uniform way of treating ENCRYPTION clause in
  CREATE/ALTER TABLE command and simplifies the design of this WL.
  Behavior change with specific commands are noted in below
  section II. E).


II. DESIGN:
```````````

A) DEFAULT ENCRYPTION property for DATABASE:

  The primary purpose of this property is to enable option to
  create all the tables in database to be encrypted OR unencrypted.

 1. Extend the CREATE DATABASE command to accept DEFAULT ENCRYPTION=Y/N

    E.g., 'CREATE DATABASE db1 DEFAULT ENCRYPTION=Y|N'
  
    Where,
    'Y', indicates that tables under this database would be
    encrypted by default, but can be overridden.
  
    'N', indicates that tables under this database would be
    unencrypted by default, but can be overridden.

 2. SHOW CREATE DATABASE will always show the DEFAULT ENCRYPTION
    clause. This clause would be shown in comment /*! */. This
    enables portability.

    CREATE DATABASE db1 /*!80014 DEFAULT ENCRYPTION=Y */;

 3. Add new column INFORMATION_SCHEMA.SCHEMATA.DEFAULT_ENCRYPTION
    which show the current default for the respective database.


B) Behavior of CREATE|ALTER DATABASE:

 1. CREATE DATABASE without explicit DEFAULT ENCRYPTION clause
    always succeeds and uses 'default-table-encryption' as
    database encryption property.

 2. CREATE/ALTER DATABASE with explicit ENCRYPTION clause
    succeeds and uses value specified in this clause as database
    encryption property unless following condition is met,

    a) User misses TABLE_ENCRYPTION_ADMIN privilege
    b) AND 'table-encryption-privilege-check' is 'Y'
    c) AND this value doesn't match 'default-table-encryption' value,

    In the latter case we fail with an appropriate error message.

 3. ALTER DATABASE without explicit DEFAULT ENCRYPTION clause
    will retain the original DEFAULT ENCRYPTION value of DATABASE.


C) Behavior of CREATE|ALTER TABLESPACE:

 1. CREATE TABLESPACE without explicit ENCRYPTION clause
    always succeeds and uses 'default-table-encryption' as
    tablespace encryption property. The ENCRYPTION clause would
    always be stored with tablespace metadata hence forth.


 2. CREATE/ALTER TABLESPACE with explicit ENCRYPTION clause
    succeeds and uses value specified in this clause as
    tablespace encryption property unless following condition is
    met,

    a) User misses TABLE_ENCRYPTION_ADMIN privilege
    b) AND 'table-encryption-privilege-check' is 'Y'
    c) AND this value doesn't match 'default-table-encryption' value,

    In the latter case we fail with an appropriate error message.

 3. ALTER TABLESPACE without explicit ENCRYPTION clause will retain
    the original ENCRYPTION type of tablespace

 4. ALTER TABLESPACE which encrypts tablespace would succeed,
    unless following condition is met,

    a) User misses TABLE_ENCRYPTION_ADMIN privilege
    b) AND 'table-encryption-privilege-check' is 'Y'
    c) AND per-database encryption default is 'N' for any of the
       table in this tablespace being altered .

    In the latter case we fail with an appropriate error message.

 5. ALTER TABLESPACE which decrypts tablespace would succeed,
    unless following condition is met,

    a) User misses TABLE_ENCRYPTION_ADMIN privilege
    b) AND 'table-encryption-privilege-check' is 'Y'
    c) AND per-database encryption default is 'Y' for any of the
       table in this tablespace being altered .

    In the latter case we fail with an appropriate error message.

 6. The ENCRYPTION clause value for the tablespace is stored the
    same way as before this WL. i.e.,

    a The key 'encryption' in  mysql.tablespace.options DD column
      holds the value 'Y/N'

    b This value is stored for SE that does not support tablespace too.
      E.g., tablespace with ENCRYPTION='n' should be acceptable, unless
      the SE rejects it.

    c After the WL, there is always a ENCRYPTION clause for a tablespace
      stored. It is either inherited from 'default-table-encryption' or
      is explicitly specified by the user. We do not differentiate between
      a explicit and implicitly inherited property.

    d Attempt to use ENCRYPTION clause with values other than
      'N' (or equivalent 'n' or '') when creating/altering tablespace in
      SEs which don't support encryption would produce error and not
      silently accept it  (as it is done now).

    e Do not store implicit/explicit value ENCRYPTION='N' in dictionary for
      SE's that does not support encryption. We store it only for tablespace
      using SE's that support encryption.

    f We store the ENCRYPTION property along with mysql.tablespaces.options
      for file-per-table tablespace too, similar to the way we store for
      general tablespace.


D) Behavior of CREATE|ALTER TABLE:

 1. Notes on the way ENCRYPTION property is stored in DD.

   a. ENCRYPTION property is stored the same way as before this WL.
      i.e., the key 'encryption' in mysql.tables.options DD column
      holds the value.

   b. ENCRYPTION clause is stored the same way for both file-per-table
      and general tablespace.

   c. ENCRYPTION clause is always stored with value 'N', if user does
      not supply and if per-database default is 'N'. We do not
      differentiate between implicit and explicit ENCRYPTION clause.
      SHOW CREATE TABLE would work as per D.11).

   d. ENCRYPTION clause is always stored with value 'Y', if user does
      not supply it and the per-database default is 'Y'. In other words
      the inherited value 'Y' from per-database default is treated as
      explicit value and SHOW CREATE TABLE would show it.

   e. Attempt to use ENCRYPTION option with values other than
      'N' (or equivalent 'n' or '') when creating/altering tables in
      SE's which don't support encryption would produce error and not
      silently accept it (as it is done now).

   f. Do not store implicit/explicit value ENCRYPTION='N' in dictionary for
      SE's that does not support encryption. We store it only for tables
      using SE's that support encryption.
 

 2. CREATE TABLE without explicit ENCRYPTION clause using file-per-table
    tablespace always succeeds and uses database default encryption as
    table/tablespace encryption property.

 3. CREATE TABLE using a engine that does not support encryption
    with ENCRYPTION=N clause will succeed if database encryption is
    'N'.

 4. CREATE TABLE using ENCRYPTION=N on a engine that
    do-not-support-encryption will succeed if database encryption is
    'Y', unless following condition is met,

    a) User misses TABLE_ENCRYPTION_ADMIN privilege
    b) AND 'table-encryption-privilege-check' is 'Y'
    c) AND The database encryption is 'Y'

    In the latter case we fail with an appropriate error message.

    4.1) Emit a warning when user attempts to create a unencrypted
    table without ENCRYPTION clause in following situation,

      a) 'table-encryption-privilege-check' is 'N'
      b) AND The database encryption is 'Y'

 5. CREATE TABLE without ENCRYPTION clause on a engine that
    do-not-support-encryption will fail if database
    encryption is 'Y'.


 6. CREATE/ALTER TABLE with explicit ENCRYPTION clause for table using
    file-per-table tablespace succeeds and uses value specified in this
    clause as table/tablespace encryption property, unless following
    condition is met,

    a) User misses TABLE_ENCRYPTION_ADMIN privilege
    b) AND 'table-encryption-privilege-check' is 'Y'
    c) AND this value doesn't match database encryption value.

    In the latter case we fail with an appropriate error message.

 7. Changing table encryption through ALTER TABLE without explicitly
    specifying ENCRYPTION clause in it (e.g. by moving table between
    tablespaces or by changing table storage engine) is not allowed.

 8. (not used)

 9.1 Changing table (through ALTER TABLE, without changing table database)
    using file-per-table or general unencrypted tablespace to use
    file-per-table or general unencrypted tablespace will always succeed
    irrespective of user owning TABLE_ENCRYPTION_ADMIN or the per-database
    default encryption.

 9.2 Moving (through RENAME TABLE or ALTER TABLE RENAME) unencrypted table
    to a new database succeeds unless the following condition is met:

    a) User misses TABLE_ENCRYPTION_ADMIN privilege
    b) AND 'table-encryption-privilege-check' is 'Y'
    c) AND destination database encryption is 'Y'.

    In the latter case we fail with an appropriate error message.

 10.1 Changing (through ALTER TABLE) encrypted table to use a encrypted
     file-per-table or general tablespace without moving it to different
     database will always succeed irrespective of user owning
     TABLE_ENCRYPTION_ADMIN or the per-database default encryption.

 10.2 Moving (through RENAME TABLE or ALTER TABLE RENAME) encrypted table
     to a new database succeeds unless the following condition is met:
    
     a) User misses TABLE_ENCRYPTION_ADMIN privilege
     b) AND 'table-encryption-privilege-check' is 'Y'
     c) AND destination database encryption is 'N'.

     In the latter case we fail with an appropriate error message.

 10.3 Do not allow moving encrypted tables from SE which supports
      encryption to SE which doesn't support encryption unless user explicitly
      specifies ENCRYPTION=N in the same ALTER TABLE.

 11. SHOW CREATE TABLE and INFORMATION_SCHEMA.TABLES.OPTIONS
     will show ENCRYPTION clause if,

    i) the table is encrypted

        OR

    ii) If the table's encryption type is different from database
        encryption type.


E) Behavior of CREATE|ALTER TABLE using general tablespace and
   the ENCRYPTION clause:

 1. Allow ENCRYPTION clause for CREATE TABLE and ALTER TABLE
    commands with tables using general tablespace. This is currently
    not allowed and we throw error.

 2. Persist the value of ENCRYPTION clause in data dictionary
    tables for table using general tablespace too.

 3. CREATE/ALTER TABLE with the explicit ENCRYPTION clause along
    with TABLESPACE clause, shall emit an error if the ENCRYPTION
    clause type differs from the tablespace encryption type.

 4. If the ENCRYPTION clause is not provided in CREATE TABLE or
    ALTER TABLE, then the implicit ENCRYPTION clause would inherit
    value from per-database ENCRYPTION default.

    This means that with per-database ENCRYPTION='n' (which is a
    default configuration with --default-table-encryption='N'),
    and attempt run CREATE TABLE without explicit ENCRYPTION
    option and using a encrypted tablespace will start to fail.
    (by definition in D.6)

 5. ALTER TABLE with ENCRYPTION clause, without TABLESPACE clause
    shall emit an error if ENCRYPTION type differs from
    tablespace encryption type.

 6. Note that the above behavior cannot be overriden by users
    with TABLE_ENCRYPTION_ADMIN privilege.

 7. SHOW CREATE TABLE command shall works same as mentioned in
    point D.11 for tables using general tablespace too.

 Note that the behavior defined in section D) are applied after
 first checking for condition in points 2., 3., and 4. defined
 above.


F) MYSQLDUMP AND RESTORE:

 1. SHOW CREATE DATABASE uses versioned mysql-specific
    commenting style like /*!80014 DEFAULT ENCRYPTION */ for
    portability, so the older server would ignore parsing this
    new clause.

 2. Restoring the database and tables into same or higher version
    of mysql would retain the original encryption mode, unless we
    have following condition during restore,

    a) User misses TABLE_ENCRYPTION_ADMIN privilege
    b) AND 'table-encryption-privilege-check' is 'Y'

    Restore failure case 1)
      CREATE DATABASE|TABLESPACE with explicit ENCRYPTION clause will fail
      if the DATABASE|TABLESPACE encryption type does not match 
      'default-table-encryption' type and if a) and b) are true.

    Restore failure case 2)
      CREATE TABLE with explicit ENCRYPTION clause will fail if
      the encryption type does not match database encryption type
      and if a) and b) are true.

    Q) Is there a way to avoid this create table from failing ?
    user would need atleast one of a) or b).

    Perhaps the restore option should be run by a user with
    higher rights?

   2.1) Single-table dump-restores might not preserve original
   encryption='N' property if per-database default or global
   default on target system differ from source system.

   2.2) In the scope of this WL, we assume that general tablespaces are
   restored with correct encryption property. 

G) UPGRADE AND DOWNGRADE:

 1. Upgrade from older 8.0 version to 8.0 with WL12261 would
    succeed and may not be affected by both
    'default-table-encrpytion' and 'table-encryption-privilege-check'
    settings. Because these defaults will affect only new
    objects that are created.
    
    Any new objects created during internal upgrade process
    would not be affected by user provided settings for
    'default-table-encrpytion' and 'table-encryption-privilege-check'.
    The internal upgrade procedure would consider setting two
    variables as 'false'.

    Details:
    a Schema default encryption would be stored as mentioned in I.A.3).
      As this is a new DD column, it gets the default value as
      'No' for all schema's and this is a expected value to have after
      upgrade.

    b Tablespace encryption values from older 8.0 would be stored
      in mysql.tablespaces.options. The upgrade process would store
      the clause ENCRYPTION='N', for all unencrypted tablespace that
      were originally created in older 8.0 without ENCRYPTION clause.

    c Tables using file-per-table tables would retain the encryption
      flag in mysql.tables.options with key 'encryption'.

    d Tables using general encryption tablespace shall be updated
      with key encryption='y' in mysql.tables.options.

    e The upgrade process shall store ENCRYPTION='N' clause for all
      the following unencrypted objects, 

      - Tables in general tablespace.
      - Tables in file-per-table tablespace.

    f Remove existing 'encryption' clause if stored for following items,

      - Tables in SEs which do not support encryption.
      - Tablespace in SEs which do not support encryption.

 2. Upgrade from 5.7 version to 8.0 with WL12261 will force
    --table-encryption-privilege-check='N' during upgrade.
    Otherwise, it is bit confusing to users.

 3. Any new objects created during internal upgrade from 5.7
    instances would not be affected by user provided settings
    for 'default-table-encrpytion' and 'table-encryption-privilege-check'.

    The upgrade procedure would consider both the variables
    'default-table-encryption' and 'table-encryption-privilege-check'
    as 'false'. There by we get following behavior.

    This also means that, these settings would not affect
    encryption used for 'mysql' system tablespace. That is, it
    remains unencrypted during server startup, regardless of these
    settings. Mainly because 'default-table-encrpytion' is meant
    to govern user objects and not system object. The encryption
    of 'mysql' can be managed as defined in WL#12063.

    Details:
    a Schema default encryption would be stored as mentioned in I.A.3).
      As this is a new DD column, it gets the default value as
      'No' for all schema's and this is a expected value to have after
      upgrade.

    b The upgrade process would store the clause ENCRYPTION='N', for all
      general tablespace, as there are no encrypted tablespaces possible
      in 5.7.

    c Tables using file-per-table tables would retain the encryption
      flag in mysql.tables.options with key 'encryption'.

    d The upgrade process shall store ENCRYPTION='N' clause for all
      the following unencrypted objects, 

      - Tables in file-per-table tablespace.

    e Remove existing 'encryption' clause if stored for following items,

      - Tables in SEs which do not support encryption.
      - Tablespace in SEs which do not support encryption.

 4. DOWNGRADE is not supported as of now.


H) REPLICATION:

 1. The new DEFAULT ENCRYPTION clause for CREATE DATABASE would
    work fine in replication environment. In master-slave
    replication or even in group replication, as we do not
    replicate logs from new server to a old server.


 2. Changes required:

 2.1) To support correct replication of CREATE DATABASE/TABLESPACE DDLs
      without explicit ENCRYPTION clause between servers with different
      values of 'default-table-encryption' options we need to store value
      of this system variable in the binlog event of these DDLs.

 2.2) In Group Replication setups we need to avoid problems which can occurs
      after failover or in multi-primary node if some servers in group are
      using different "--default-table-encryption" values.
      We will use existing GR mechanism to enforce that all group replication
      server nodes have the same value for 'default-table-encryption' system
      variable.


I) PERFORMANCE:

  There shouldn't be any performance issues caused by this WL changes.


J) DOCUMENTATION:

  Yes, changes are required. FR's should help understand the changes.
I) API's introduced:

This API is especially used to ask SE the type of predefined
tablespace names like innodb_file_per_table and innodb_system.

/**                                                                             
  Get the tablespace type given the name, from the SE.                          
                                                                                
  @param[in]  tablespace_name tablespace name                                   
  @param[out] space_type      type of space                                     
                                                                                
  @return Operation status.                                                     
  @retval == 0  Success.                                                        
  @retval != 0  Error (unknown space type, no error code returned)              
*/                                                                              
typedef bool (*get_tablespace_type_by_name_t)(const char *tablespace_name,          
                                              Tablespace_type *space_type);