680 lines
25 KiB
Plaintext
680 lines
25 KiB
Plaintext
#
|
|
# WL#12261 Control (enforce and disable) table encryption
|
|
#
|
|
# Pre-define user u1, which is used in different tests below.
|
|
CREATE USER u1@localhost;
|
|
GRANT ALL ON db1.* TO u1@localhost;
|
|
GRANT CREATE TABLESPACE, PROCESS, SYSTEM_VARIABLES_ADMIN ON *.* TO u1@localhost;
|
|
SET GLOBAL debug= '+d,skip_table_encryption_admin_check_for_set';
|
|
# This test run CREATE/ALTER TABLESPACE in different configurations,
|
|
#
|
|
# - Setting table_encryption_privilege_check to true/false.
|
|
# - Setting default_table_encryption to true/false.
|
|
# - With and without ENCRYPTION clause.
|
|
# - With and without user holding TABLE_ENCRYPTION_ADMIN privilege.
|
|
# - Test INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
# - Check for warnings generated.
|
|
#
|
|
`````````````````````````````````````````````````````````
|
|
# CREATE TABLESPACE with explicit ENCRYPTION clause
|
|
# table_encryption_privilege_check=false
|
|
# [CREATE TABLESPACE] Case 1 )
|
|
`````````````````````````````````````````````````````````
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 N
|
|
DROP TABLESPACE ts1;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
# [CREATE TABLESPACE] Case 2 )
|
|
`````````````````````````````````````````````````````````
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=true;
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 Y
|
|
DROP TABLESPACE ts1;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
`````````````````````````````````````````````````````````
|
|
# table_encryption_privilege_check=true
|
|
# [CREATE TABLESPACE] Case 3 )
|
|
`````````````````````````````````````````````````````````
|
|
SET GLOBAL table_encryption_privilege_check=true;
|
|
SET SESSION default_table_encryption=false;
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 N
|
|
DROP TABLESPACE ts1;
|
|
# Check that we never fail with TABLE_ENCRYPTION_ADMIN
|
|
# Grant user with TABLE_ENCRYPTION_ADMIN
|
|
GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 N
|
|
DROP TABLESPACE ts1;
|
|
# Revoke TABLE_ENCRYPTION_ADMIN from user.
|
|
REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
# [CREATE TABLESPACE] Case 4 )
|
|
`````````````````````````````````````````````````````````
|
|
SET GLOBAL table_encryption_privilege_check=true;
|
|
SET SESSION default_table_encryption=true;
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 Y
|
|
DROP TABLESPACE ts1;
|
|
# Check that we never fail with TABLE_ENCRYPTION_ADMIN
|
|
# Grant user with TABLE_ENCRYPTION_ADMIN
|
|
GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 Y
|
|
DROP TABLESPACE ts1;
|
|
# Revoke TABLE_ENCRYPTION_ADMIN from user.
|
|
REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
`````````````````````````````````````````````````````````
|
|
# CREATE TABLESPACE with explicit ENCRYPTION clause
|
|
# table_encryption_privilege_check=false
|
|
# [CREATE TABLESPACE] Case 5 )
|
|
`````````````````````````````````````````````````````````
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 Y
|
|
DROP TABLESPACE ts1;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
# [CREATE TABLESPACE] Case 6 )
|
|
`````````````````````````````````````````````````````````
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 N
|
|
DROP TABLESPACE ts1;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
# [CREATE TABLESPACE] Case 7 )
|
|
`````````````````````````````````````````````````````````
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=true;
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 Y
|
|
DROP TABLESPACE ts1;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
# [CREATE TABLESPACE] Case 8 )
|
|
`````````````````````````````````````````````````````````
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=true;
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 N
|
|
DROP TABLESPACE ts1;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
`````````````````````````````````````````````````````````
|
|
# table_encryption_privilege_check=true
|
|
# [CREATE TABLESPACE] Case 9 )
|
|
`````````````````````````````````````````````````````````
|
|
SET GLOBAL table_encryption_privilege_check=true;
|
|
SET SESSION default_table_encryption=false;
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
|
|
SHOW WARNINGS;
|
|
ERROR HY000: Tablespace encryption differ from 'default_table_encryption' setting, and user doesn't have enough privilege.
|
|
# Check that we never fail with TABLE_ENCRYPTION_ADMIN
|
|
# Grant user with TABLE_ENCRYPTION_ADMIN
|
|
GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 Y
|
|
DROP TABLESPACE ts1;
|
|
# Revoke TABLE_ENCRYPTION_ADMIN from user.
|
|
REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
# [CREATE TABLESPACE] Case 10 )
|
|
`````````````````````````````````````````````````````````
|
|
SET GLOBAL table_encryption_privilege_check=true;
|
|
SET SESSION default_table_encryption=false;
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 N
|
|
DROP TABLESPACE ts1;
|
|
# Check that we never fail with TABLE_ENCRYPTION_ADMIN
|
|
# Grant user with TABLE_ENCRYPTION_ADMIN
|
|
GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 N
|
|
DROP TABLESPACE ts1;
|
|
# Revoke TABLE_ENCRYPTION_ADMIN from user.
|
|
REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
# [CREATE TABLESPACE] Case 11 )
|
|
`````````````````````````````````````````````````````````
|
|
SET GLOBAL table_encryption_privilege_check=true;
|
|
SET SESSION default_table_encryption=true;
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 Y
|
|
DROP TABLESPACE ts1;
|
|
# Check that we never fail with TABLE_ENCRYPTION_ADMIN
|
|
# Grant user with TABLE_ENCRYPTION_ADMIN
|
|
GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 Y
|
|
DROP TABLESPACE ts1;
|
|
# Revoke TABLE_ENCRYPTION_ADMIN from user.
|
|
REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
# [CREATE TABLESPACE] Case 12 )
|
|
`````````````````````````````````````````````````````````
|
|
SET GLOBAL table_encryption_privilege_check=true;
|
|
SET SESSION default_table_encryption=true;
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
|
|
SHOW WARNINGS;
|
|
ERROR HY000: Tablespace encryption differ from 'default_table_encryption' setting, and user doesn't have enough privilege.
|
|
# Check that we never fail with TABLE_ENCRYPTION_ADMIN
|
|
# Grant user with TABLE_ENCRYPTION_ADMIN
|
|
GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 N
|
|
DROP TABLESPACE ts1;
|
|
# Revoke TABLE_ENCRYPTION_ADMIN from user.
|
|
REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
`````````````````````````````````````````````````````````
|
|
# Check for invalid ENCRYPTION clause value.
|
|
# [CREATE TABLESPACE] Case 13 )
|
|
`````````````````````````````````````````````````````````
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='k';
|
|
SHOW WARNINGS;
|
|
ERROR HY000: Invalid encryption option.
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
`````````````````````````````````````````````````````````
|
|
# The test cases run ALTER TABLESPACE (with no tables)
|
|
# See test tablespace_with_tables.test.
|
|
`````````````````````````````````````````````````````````
|
|
# Unencrypted TABLESPACE to Unencrypted TABLESPACE (Nop)
|
|
#
|
|
# [ALTER TABLESPACE] Case 1 )
|
|
`````````````````````````````````````````````````````````
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 N
|
|
SET SESSION default_table_encryption=false;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
ALTER TABLESPACE ts1 ENCRYPTION='n';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 N
|
|
DROP TABLESPACE ts1;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
# [ALTER TABLESPACE] Case 2 )
|
|
`````````````````````````````````````````````````````````
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 N
|
|
SET SESSION default_table_encryption=false;
|
|
SET GLOBAL table_encryption_privilege_check=true;
|
|
ALTER TABLESPACE ts1 ENCRYPTION='n';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 N
|
|
DROP TABLESPACE ts1;
|
|
# Check that we never fail with TABLE_ENCRYPTION_ADMIN
|
|
# Grant user with TABLE_ENCRYPTION_ADMIN
|
|
GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 N
|
|
SET GLOBAL table_encryption_privilege_check=true;
|
|
ALTER TABLESPACE ts1 ENCRYPTION='n';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 N
|
|
DROP TABLESPACE ts1;
|
|
# Revoke TABLE_ENCRYPTION_ADMIN from user.
|
|
REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
`````````````````````````````````````````````````````````
|
|
# Encrypted TABLESPACE to Encrypted TABLESPACE (Nop)
|
|
#
|
|
# [ALTER TABLESPACE] Case 3 )
|
|
`````````````````````````````````````````````````````````
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 Y
|
|
SET SESSION default_table_encryption=true;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
ALTER TABLESPACE ts1 ENCRYPTION='y';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 Y
|
|
DROP TABLESPACE ts1;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
# [ALTER TABLESPACE] Case 4 )
|
|
`````````````````````````````````````````````````````````
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 Y
|
|
SET SESSION default_table_encryption=true;
|
|
SET GLOBAL table_encryption_privilege_check=true;
|
|
ALTER TABLESPACE ts1 ENCRYPTION='y';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 Y
|
|
DROP TABLESPACE ts1;
|
|
# Check that we never fail with TABLE_ENCRYPTION_ADMIN
|
|
# Grant user with TABLE_ENCRYPTION_ADMIN
|
|
GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 Y
|
|
SET GLOBAL table_encryption_privilege_check=true;
|
|
ALTER TABLESPACE ts1 ENCRYPTION='y';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 Y
|
|
DROP TABLESPACE ts1;
|
|
# Revoke TABLE_ENCRYPTION_ADMIN from user.
|
|
REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
`````````````````````````````````````````````````````````
|
|
# Unencrypted TABLESPACE to encrypted TABLESPACE
|
|
# with database encryption default 'n'
|
|
#
|
|
# [ALTER TABLESPACE] Case 5 )
|
|
`````````````````````````````````````````````````````````
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 N
|
|
SET SESSION default_table_encryption=false;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
ALTER TABLESPACE ts1 ENCRYPTION='y';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 Y
|
|
DROP TABLESPACE ts1;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
# [ALTER TABLESPACE] Case 6 )
|
|
`````````````````````````````````````````````````````````
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 N
|
|
SET SESSION default_table_encryption=false;
|
|
SET GLOBAL table_encryption_privilege_check=true;
|
|
ALTER TABLESPACE ts1 ENCRYPTION='y';
|
|
ERROR HY000: Tablespace encryption differ from 'default_table_encryption' setting, and user doesn't have enough privilege.
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 3828 Tablespace encryption differ from 'default_table_encryption' setting, and user doesn't have enough privilege.
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 N
|
|
DROP TABLESPACE ts1;
|
|
# Check that we never fail with TABLE_ENCRYPTION_ADMIN
|
|
# Grant user with TABLE_ENCRYPTION_ADMIN
|
|
GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 N
|
|
SET GLOBAL table_encryption_privilege_check=true;
|
|
ALTER TABLESPACE ts1 ENCRYPTION='y';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 Y
|
|
DROP TABLESPACE ts1;
|
|
# Revoke TABLE_ENCRYPTION_ADMIN from user.
|
|
REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
`````````````````````````````````````````````````````````
|
|
# Unencrypted TABLESPACE to encrypted TABLESPACE
|
|
# with database encryption default 'y'
|
|
#
|
|
# [ALTER TABLESPACE] Case 7 )
|
|
`````````````````````````````````````````````````````````
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 N
|
|
SET SESSION default_table_encryption=true;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
ALTER TABLESPACE ts1 ENCRYPTION='y';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 Y
|
|
DROP TABLESPACE ts1;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
# [ALTER TABLESPACE] Case 8 )
|
|
`````````````````````````````````````````````````````````
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 N
|
|
SET SESSION default_table_encryption=true;
|
|
SET GLOBAL table_encryption_privilege_check=true;
|
|
ALTER TABLESPACE ts1 ENCRYPTION='y';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 Y
|
|
DROP TABLESPACE ts1;
|
|
# Check that we never fail with TABLE_ENCRYPTION_ADMIN
|
|
# Grant user with TABLE_ENCRYPTION_ADMIN
|
|
GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 N
|
|
SET GLOBAL table_encryption_privilege_check=true;
|
|
ALTER TABLESPACE ts1 ENCRYPTION='y';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 Y
|
|
DROP TABLESPACE ts1;
|
|
# Revoke TABLE_ENCRYPTION_ADMIN from user.
|
|
REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
`````````````````````````````````````````````````````````
|
|
# Encrypted TABLESPACE to unencrypted TABLESPACE
|
|
# with database encryption default 'n'
|
|
#
|
|
# [ALTER TABLESPACE] Case 9 )
|
|
`````````````````````````````````````````````````````````
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 Y
|
|
SET SESSION default_table_encryption=false;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
ALTER TABLESPACE ts1 ENCRYPTION='n';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 N
|
|
DROP TABLESPACE ts1;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
# [ALTER TABLESPACE] Case 10 )
|
|
`````````````````````````````````````````````````````````
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 Y
|
|
SET SESSION default_table_encryption=false;
|
|
SET GLOBAL table_encryption_privilege_check=true;
|
|
ALTER TABLESPACE ts1 ENCRYPTION='n';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 N
|
|
DROP TABLESPACE ts1;
|
|
# Check that we never fail with TABLE_ENCRYPTION_ADMIN
|
|
# Grant user with TABLE_ENCRYPTION_ADMIN
|
|
GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 Y
|
|
SET GLOBAL table_encryption_privilege_check=true;
|
|
ALTER TABLESPACE ts1 ENCRYPTION='n';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 N
|
|
DROP TABLESPACE ts1;
|
|
# Revoke TABLE_ENCRYPTION_ADMIN from user.
|
|
REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
`````````````````````````````````````````````````````````
|
|
# Encrypted TABLESPACE to unencrypted TABLESPACE
|
|
# with database encryption default 'y'
|
|
#
|
|
# [ALTER TABLESPACE] Case 11 )
|
|
`````````````````````````````````````````````````````````
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 Y
|
|
SET SESSION default_table_encryption=true;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
ALTER TABLESPACE ts1 ENCRYPTION='n';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 N
|
|
DROP TABLESPACE ts1;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
# [ALTER TABLESPACE] Case 12 )
|
|
`````````````````````````````````````````````````````````
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 Y
|
|
SET SESSION default_table_encryption=true;
|
|
SET GLOBAL table_encryption_privilege_check=true;
|
|
ALTER TABLESPACE ts1 ENCRYPTION='n';
|
|
ERROR HY000: Tablespace encryption differ from 'default_table_encryption' setting, and user doesn't have enough privilege.
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 3828 Tablespace encryption differ from 'default_table_encryption' setting, and user doesn't have enough privilege.
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 Y
|
|
DROP TABLESPACE ts1;
|
|
# Check that we never fail with TABLE_ENCRYPTION_ADMIN
|
|
# Grant user with TABLE_ENCRYPTION_ADMIN
|
|
GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 Y
|
|
SET GLOBAL table_encryption_privilege_check=true;
|
|
ALTER TABLESPACE ts1 ENCRYPTION='n';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 N
|
|
DROP TABLESPACE ts1;
|
|
# Revoke TABLE_ENCRYPTION_ADMIN from user.
|
|
REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
`````````````````````````````````````````````````````````
|
|
# Check for Invalid encryption option.
|
|
# [ALTER TABLESPACE] Case 13 )
|
|
`````````````````````````````````````````````````````````
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 Y
|
|
SET SESSION default_table_encryption=false;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
ALTER TABLESPACE ts1 ENCRYPTION='k';
|
|
ERROR HY000: Invalid encryption option.
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 3184 Invalid encryption option.
|
|
Error 1533 Failed to alter: TABLESPACE ts1
|
|
Error 1030 Got error 168 - 'Unknown (generic) error from engine' from storage engine
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE 'ts1';
|
|
NAME ENCRYPTION
|
|
ts1 Y
|
|
DROP TABLESPACE ts1;
|
|
SET GLOBAL table_encryption_privilege_check=false;
|
|
SET SESSION default_table_encryption=false;
|
|
# Cleanup
|
|
DROP USER u1@localhost;
|
|
SET GLOBAL debug= '-d,skip_table_encryption_admin_check_for_set';
|