WL#12261: Control (enforce and disable) table encryption
Affects: Server-8.0
—
Status: Complete
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);
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.