1420 lines
68 KiB
Plaintext
1420 lines
68 KiB
Plaintext
# Create encrypt table before loading keyring plugin
|
|
SET GLOBAL innodb_file_per_table = 1;
|
|
SELECT @@innodb_file_per_table;
|
|
@@innodb_file_per_table
|
|
1
|
|
DROP TABLE IF EXISTS t_encrypt;
|
|
CREATE TABLE t_encrypt(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
|
|
ERROR HY000: Can't find master key from keyring, please check in the server log if a keyring plugin is loaded and initialized successfully.
|
|
UNINSTALL PLUGIN keyring_file;
|
|
ERROR 42000: PLUGIN keyring_file does not exist
|
|
ALTER INSTANCE ROTATE INNODB MASTER KEY;
|
|
ERROR HY000: Can't find master key from keyring, please check in the server log if a keyring plugin is loaded and initialized successfully.
|
|
# Starting server with keyring plugin
|
|
# restart: --early-plugin-load=keyring_file=keyring_file.so --loose-keyring_file_data=MYSQL_TMP_DIR/mysecret_keyring --plugin-dir=KEYRING_PLUGIN_PATH
|
|
DROP DATABASE IF EXISTS tde_db;
|
|
DROP TABLE IF EXISTS tde_db.t_encrypt;
|
|
CREATE DATABASE tde_db;
|
|
SET GLOBAL innodb_file_per_table = 1;
|
|
SELECT @@innodb_file_per_table;
|
|
@@innodb_file_per_table
|
|
1
|
|
CREATE TABLE tde_db.t_encrypt(c4 JSON ,
|
|
c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
|
|
c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
|
|
c7 POINT NOT NULL SRID 0,
|
|
spatial INDEX idx2 (c7)
|
|
) ENCRYPTION="Y" ENGINE = InnoDB;
|
|
SHOW CREATE TABLE tde_db.t_encrypt;
|
|
Table Create Table
|
|
t_encrypt CREATE TABLE `t_encrypt` (
|
|
`c4` json DEFAULT NULL,
|
|
`c5` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
|
|
`c6` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
|
|
`c7` point NOT NULL /*!80003 SRID 0 */,
|
|
SPATIAL KEY `idx2` (`c7`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
|
|
INSERT INTO tde_db.t_encrypt(c4,c7) VALUES('{ "key_a": 1, "key_b": 2, "key_c": 3 }',ST_GeomFromText('POINT(383293632 1754448)'));
|
|
INSERT INTO tde_db.t_encrypt(c4,c7) select c4,c7 from tde_db.t_encrypt;
|
|
SELECT c4,c5,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
|
|
c4 c5 ST_AsText(c7)
|
|
{"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
{"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
SELECT c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
|
|
c4 c5 c6 ST_AsText(c7)
|
|
{"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
{"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
SHOW CREATE TABLE tde_db.t_encrypt;
|
|
Table Create Table
|
|
t_encrypt CREATE TABLE `t_encrypt` (
|
|
`c4` json DEFAULT NULL,
|
|
`c5` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
|
|
`c6` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
|
|
`c7` point NOT NULL /*!80003 SRID 0 */,
|
|
SPATIAL KEY `idx2` (`c7`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
|
|
# In connection 1
|
|
LOCK TABLES tde_db.t_encrypt WRITE;
|
|
# In connection default
|
|
SHOW OPEN TABLES LIKE 't_encrypt';
|
|
Database Table In_use Name_locked
|
|
tde_db t_encrypt 1 0
|
|
"ALTER INSTANCE.." do not conflict with "LOCK TABLE .." COMMAND
|
|
ALTER INSTANCE ROTATE INNODB MASTER KEY;
|
|
# In connection 1
|
|
INSERT INTO tde_db.t_encrypt(c4,c7) VALUES('{ "key_a": 1, "key_b": 2, "key_c": 3 }',ST_GeomFromText('POINT(383293632 1754448)'));
|
|
SELECT c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
|
|
c4 c5 c6 ST_AsText(c7)
|
|
{"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
{"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
{"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
UNLOCK TABLES;
|
|
# In connection default
|
|
SELECT c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
|
|
c4 c5 c6 ST_AsText(c7)
|
|
{"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
{"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
{"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
SELECT c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
|
|
c4 c5 c6 ST_AsText(c7)
|
|
{"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
{"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
{"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
DROP DATABASE tde_db;
|
|
SET GLOBAL innodb_file_per_table=1;
|
|
DROP DATABASE IF EXISTS tde_db;
|
|
CREATE DATABASE tde_db;
|
|
USE tde_db;
|
|
DROP TABLE IF EXISTS tde_db.t_encrypt;
|
|
DROP TABLE IF EXISTS tde_db.t_encrypt_1;
|
|
SET GLOBAL innodb_file_per_table = 1;
|
|
SELECT @@innodb_file_per_table;
|
|
@@innodb_file_per_table
|
|
1
|
|
CREATE TABLE tde_db.t_encrypt(c1 INT, c2 char(20), c3 BLOB) ENCRYPTION="Y" ENGINE = InnoDB;
|
|
CREATE TABLE tde_db.t_encrypt_1(c1 INT, c2 char(20)) ENCRYPTION="Yes" ENGINE = InnoDB;
|
|
ERROR HY000: Invalid encryption option.
|
|
CREATE TABLE tde_db.t_encrypt_1(c1 INT, c2 char(20)) ENCRYPTION="y" ENGINE = InnoDB;
|
|
DROP TABLE tde_db.t_encrypt_1;
|
|
CREATE TABLE tde_db.t_encrypt_1(c1 INT, c2 char(20),c3 BLOB) ENGINE = InnoDB;
|
|
SHOW CREATE TABLE tde_db.t_encrypt;
|
|
Table Create Table
|
|
t_encrypt CREATE TABLE `t_encrypt` (
|
|
`c1` int(11) DEFAULT NULL,
|
|
`c2` char(20) DEFAULT NULL,
|
|
`c3` blob
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
|
|
INSERT INTO tde_db.t_encrypt VALUES(0, "aaaaa",repeat('A', 20000));
|
|
INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
|
|
INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
|
|
INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
|
|
INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
|
|
INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
|
|
INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
|
|
SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
|
|
c1 c2 right(c3, 20)
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
#Insert into non encrypted table
|
|
INSERT INTO tde_db.t_encrypt_1 SELECT * FROM tde_db.t_encrypt;
|
|
SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt_1 LIMIT 10;
|
|
c1 c2 right(c3, 20)
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
SELECT COUNT(*) FROM tde_db.t_encrypt_1;
|
|
COUNT(*)
|
|
64
|
|
# Starting server with keyring plugin restart with keying
|
|
# restart: --early-plugin-load=keyring_file=keyring_file.so --loose-keyring_file_data=MYSQL_TMP_DIR/mysecret_keyring --plugin-dir=KEYRING_PLUGIN_PATH
|
|
SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
|
|
c1 c2 right(c3, 20)
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
SELECT COUNT(*) FROM tde_db.t_encrypt;
|
|
COUNT(*)
|
|
64
|
|
#check non encrypted table
|
|
SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt_1 LIMIT 10;
|
|
c1 c2 right(c3, 20)
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
SELECT COUNT(*) FROM tde_db.t_encrypt_1;
|
|
COUNT(*)
|
|
64
|
|
DROP TABLE tde_db.t_encrypt;
|
|
DROP TABLE tde_db.t_encrypt_1;
|
|
SET GLOBAL innodb_file_per_table=1;
|
|
DROP DATABASE IF EXISTS tde_db;
|
|
DROP TABLE IF EXISTS tde_db.t_encrypt;
|
|
CREATE DATABASE tde_db;
|
|
USE tde_db;
|
|
# File per table is set 0. Encryption not possible.
|
|
SET GLOBAL innodb_file_per_table = 0;
|
|
SELECT @@innodb_file_per_table;
|
|
@@innodb_file_per_table
|
|
0
|
|
CREATE TABLE tde_db.t_encrypt(c4 JSON ,
|
|
c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
|
|
c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
|
|
c7 POINT NOT NULL SRID 0,
|
|
spatial INDEX idx2 (c7)
|
|
) ENCRYPTION="Y" ENGINE = InnoDB;
|
|
ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 3825 Request to create 'encrypted' table while using an 'unencrypted' tablespace.
|
|
SET GLOBAL innodb_file_per_table = 1;
|
|
SELECT @@innodb_file_per_table;
|
|
@@innodb_file_per_table
|
|
1
|
|
CREATE TABLESPACE s_alt1 ADD DATAFILE 's_alt1.ibd';
|
|
CREATE TABLE tde_db.t_encrypt (a int, b text) ENCRYPTION="Y" TABLESPACE=`s_alt1` ENGINE=InnoDB;
|
|
ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 3825 Request to create 'encrypted' table while using an 'unencrypted' tablespace.
|
|
DROP TABLESPACE s_alt1;
|
|
CREATE TEMPORARY TABLE tde_db.t_encrypt (a int, b text) ENCRYPTION="Y" ENGINE=InnoDB;
|
|
ERROR HY000: ENCRYPTION clause is not valid for temporary tablespace.
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 3833 ENCRYPTION clause is not valid for temporary tablespace.
|
|
CREATE TABLE tde_db.t_encrypt_myisam (a int, b text) ENCRYPTION="Y" ENGINE=MyISAM;
|
|
ERROR 42000: The storage engine for the table doesn't support ENCRYPTION
|
|
CREATE PROCEDURE tde_db.row_format_t_encrypt(row_form VARCHAR(1000))
|
|
begin
|
|
declare i int default 1;
|
|
declare has_error int default 0;
|
|
DECLARE CONTINUE HANDLER FOR 1062 SET has_error = 1;
|
|
DROP TABLE IF EXISTS tde_db.t_encrypt;
|
|
SET @sql_text = CONCAT('CREATE TABLE tde_db.t_encrypt ('," c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,c3 VARCHAR(255), c4 JSON ,c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,c7 POINT NOT NULL SRID 0,spatial INDEX idx2 (c7) ) ", ' ENCRYPTION="Y" ', row_form ,' ENGINE=InnoDB');
|
|
PREPARE stmt FROM @sql_text;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
SHOW CREATE TABLE tde_db.t_encrypt;
|
|
INSERT INTO tde_db.t_encrypt(c3,c4,c7) VALUES (REPEAT('a',200),'{ "key_a": 1, "key_b": 2, "key_c": 3 }',ST_GeomFromText('POINT(383293632 1754448)'));
|
|
INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
|
|
INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
|
|
INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
|
|
INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
|
|
INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
|
|
SELECT COUNT(*) FROM tde_db.t_encrypt;
|
|
SELECT c2,c4,c5,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
|
|
SELECT c2,c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
|
|
DELETE FROM tde_db.t_encrypt WHERE c2 > 10;
|
|
UPDATE tde_db.t_encrypt SET c2 = 100 WHERE c2=1;
|
|
SELECT c2,c4,c5,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
|
|
SELECT c2,c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
|
|
SHOW CREATE TABLE tde_db.t_encrypt;
|
|
end|
|
|
call tde_db.row_format_t_encrypt(" ROW_FORMAT=DYNAMIC ");
|
|
Table Create Table
|
|
t_encrypt CREATE TABLE `t_encrypt` (
|
|
`c2` int(11) NOT NULL AUTO_INCREMENT,
|
|
`c3` varchar(255) DEFAULT NULL,
|
|
`c4` json DEFAULT NULL,
|
|
`c5` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
|
|
`c6` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
|
|
`c7` point NOT NULL /*!80003 SRID 0 */,
|
|
PRIMARY KEY (`c2`),
|
|
SPATIAL KEY `idx2` (`c7`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC ENCRYPTION='Y'
|
|
COUNT(*)
|
|
32
|
|
c2 c4 c5 ST_AsText(c7)
|
|
1 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
13 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
14 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
c2 c4 c5 c6 ST_AsText(c7)
|
|
1 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
13 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
14 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
c2 c4 c5 ST_AsText(c7)
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
c2 c4 c5 c6 ST_AsText(c7)
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
Table Create Table
|
|
t_encrypt CREATE TABLE `t_encrypt` (
|
|
`c2` int(11) NOT NULL AUTO_INCREMENT,
|
|
`c3` varchar(255) DEFAULT NULL,
|
|
`c4` json DEFAULT NULL,
|
|
`c5` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
|
|
`c6` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
|
|
`c7` point NOT NULL /*!80003 SRID 0 */,
|
|
PRIMARY KEY (`c2`),
|
|
SPATIAL KEY `idx2` (`c7`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC ENCRYPTION='Y'
|
|
# restart with keying
|
|
# restart: --early-plugin-load=keyring_file=keyring_file.so --loose-keyring_file_data=MYSQL_TMP_DIR/mysecret_keyring --plugin-dir=KEYRING_PLUGIN_PATH
|
|
SELECT c2,c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
|
|
c2 c4 c5 c6 ST_AsText(c7)
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
SELECT COUNT(*) FROM tde_db.t_encrypt;
|
|
COUNT(*)
|
|
8
|
|
INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
|
|
SELECT COUNT(*) FROM tde_db.t_encrypt;
|
|
COUNT(*)
|
|
16
|
|
call tde_db.row_format_t_encrypt(" ROW_FORMAT=COMPACT ");
|
|
Table Create Table
|
|
t_encrypt CREATE TABLE `t_encrypt` (
|
|
`c2` int(11) NOT NULL AUTO_INCREMENT,
|
|
`c3` varchar(255) DEFAULT NULL,
|
|
`c4` json DEFAULT NULL,
|
|
`c5` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
|
|
`c6` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
|
|
`c7` point NOT NULL /*!80003 SRID 0 */,
|
|
PRIMARY KEY (`c2`),
|
|
SPATIAL KEY `idx2` (`c7`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT ENCRYPTION='Y'
|
|
COUNT(*)
|
|
32
|
|
c2 c4 c5 ST_AsText(c7)
|
|
1 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
13 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
14 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
c2 c4 c5 c6 ST_AsText(c7)
|
|
1 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
13 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
14 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
c2 c4 c5 ST_AsText(c7)
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
c2 c4 c5 c6 ST_AsText(c7)
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
Table Create Table
|
|
t_encrypt CREATE TABLE `t_encrypt` (
|
|
`c2` int(11) NOT NULL AUTO_INCREMENT,
|
|
`c3` varchar(255) DEFAULT NULL,
|
|
`c4` json DEFAULT NULL,
|
|
`c5` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
|
|
`c6` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
|
|
`c7` point NOT NULL /*!80003 SRID 0 */,
|
|
PRIMARY KEY (`c2`),
|
|
SPATIAL KEY `idx2` (`c7`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT ENCRYPTION='Y'
|
|
# restart with keying
|
|
# restart: --early-plugin-load=keyring_file=keyring_file.so --loose-keyring_file_data=MYSQL_TMP_DIR/mysecret_keyring --plugin-dir=KEYRING_PLUGIN_PATH
|
|
SELECT c2,c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
|
|
c2 c4 c5 c6 ST_AsText(c7)
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
SELECT COUNT(*) FROM tde_db.t_encrypt;
|
|
COUNT(*)
|
|
8
|
|
INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
|
|
SELECT COUNT(*) FROM tde_db.t_encrypt;
|
|
COUNT(*)
|
|
16
|
|
call tde_db.row_format_t_encrypt(" ROW_FORMAT=REDUNDANT ");
|
|
Table Create Table
|
|
t_encrypt CREATE TABLE `t_encrypt` (
|
|
`c2` int(11) NOT NULL AUTO_INCREMENT,
|
|
`c3` varchar(255) DEFAULT NULL,
|
|
`c4` json DEFAULT NULL,
|
|
`c5` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
|
|
`c6` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
|
|
`c7` point NOT NULL /*!80003 SRID 0 */,
|
|
PRIMARY KEY (`c2`),
|
|
SPATIAL KEY `idx2` (`c7`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT ENCRYPTION='Y'
|
|
COUNT(*)
|
|
32
|
|
c2 c4 c5 ST_AsText(c7)
|
|
1 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
13 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
14 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
c2 c4 c5 c6 ST_AsText(c7)
|
|
1 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
13 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
14 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
c2 c4 c5 ST_AsText(c7)
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
c2 c4 c5 c6 ST_AsText(c7)
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
Table Create Table
|
|
t_encrypt CREATE TABLE `t_encrypt` (
|
|
`c2` int(11) NOT NULL AUTO_INCREMENT,
|
|
`c3` varchar(255) DEFAULT NULL,
|
|
`c4` json DEFAULT NULL,
|
|
`c5` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
|
|
`c6` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
|
|
`c7` point NOT NULL /*!80003 SRID 0 */,
|
|
PRIMARY KEY (`c2`),
|
|
SPATIAL KEY `idx2` (`c7`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT ENCRYPTION='Y'
|
|
# restart with keying
|
|
# restart: --early-plugin-load=keyring_file=keyring_file.so --loose-keyring_file_data=MYSQL_TMP_DIR/mysecret_keyring --plugin-dir=KEYRING_PLUGIN_PATH
|
|
SELECT c2,c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
|
|
c2 c4 c5 c6 ST_AsText(c7)
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
SELECT COUNT(*) FROM tde_db.t_encrypt;
|
|
COUNT(*)
|
|
8
|
|
INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
|
|
SELECT COUNT(*) FROM tde_db.t_encrypt;
|
|
COUNT(*)
|
|
16
|
|
call tde_db.row_format_t_encrypt(" ROW_FORMAT=COMPRESSED " );
|
|
Table Create Table
|
|
t_encrypt CREATE TABLE `t_encrypt` (
|
|
`c2` int(11) NOT NULL AUTO_INCREMENT,
|
|
`c3` varchar(255) DEFAULT NULL,
|
|
`c4` json DEFAULT NULL,
|
|
`c5` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
|
|
`c6` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
|
|
`c7` point NOT NULL /*!80003 SRID 0 */,
|
|
PRIMARY KEY (`c2`),
|
|
SPATIAL KEY `idx2` (`c7`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED ENCRYPTION='Y'
|
|
COUNT(*)
|
|
32
|
|
c2 c4 c5 ST_AsText(c7)
|
|
1 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
13 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
14 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
c2 c4 c5 c6 ST_AsText(c7)
|
|
1 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
13 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
14 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
c2 c4 c5 ST_AsText(c7)
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
c2 c4 c5 c6 ST_AsText(c7)
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
Table Create Table
|
|
t_encrypt CREATE TABLE `t_encrypt` (
|
|
`c2` int(11) NOT NULL AUTO_INCREMENT,
|
|
`c3` varchar(255) DEFAULT NULL,
|
|
`c4` json DEFAULT NULL,
|
|
`c5` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
|
|
`c6` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
|
|
`c7` point NOT NULL /*!80003 SRID 0 */,
|
|
PRIMARY KEY (`c2`),
|
|
SPATIAL KEY `idx2` (`c7`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED ENCRYPTION='Y'
|
|
# restart with keying
|
|
# restart: --early-plugin-load=keyring_file=keyring_file.so --loose-keyring_file_data=MYSQL_TMP_DIR/mysecret_keyring --plugin-dir=KEYRING_PLUGIN_PATH
|
|
SELECT c2,c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
|
|
c2 c4 c5 c6 ST_AsText(c7)
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
SELECT COUNT(*) FROM tde_db.t_encrypt;
|
|
COUNT(*)
|
|
8
|
|
INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
|
|
SELECT COUNT(*) FROM tde_db.t_encrypt;
|
|
COUNT(*)
|
|
16
|
|
call tde_db.row_format_t_encrypt(" ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 ");
|
|
Table Create Table
|
|
t_encrypt CREATE TABLE `t_encrypt` (
|
|
`c2` int(11) NOT NULL AUTO_INCREMENT,
|
|
`c3` varchar(255) DEFAULT NULL,
|
|
`c4` json DEFAULT NULL,
|
|
`c5` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
|
|
`c6` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
|
|
`c7` point NOT NULL /*!80003 SRID 0 */,
|
|
PRIMARY KEY (`c2`),
|
|
SPATIAL KEY `idx2` (`c7`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 ENCRYPTION='Y'
|
|
COUNT(*)
|
|
32
|
|
c2 c4 c5 ST_AsText(c7)
|
|
1 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
13 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
14 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
c2 c4 c5 c6 ST_AsText(c7)
|
|
1 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
13 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
14 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
c2 c4 c5 ST_AsText(c7)
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
c2 c4 c5 c6 ST_AsText(c7)
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
Table Create Table
|
|
t_encrypt CREATE TABLE `t_encrypt` (
|
|
`c2` int(11) NOT NULL AUTO_INCREMENT,
|
|
`c3` varchar(255) DEFAULT NULL,
|
|
`c4` json DEFAULT NULL,
|
|
`c5` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
|
|
`c6` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
|
|
`c7` point NOT NULL /*!80003 SRID 0 */,
|
|
PRIMARY KEY (`c2`),
|
|
SPATIAL KEY `idx2` (`c7`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 ENCRYPTION='Y'
|
|
# restart with keying
|
|
# restart: --early-plugin-load=keyring_file=keyring_file.so --loose-keyring_file_data=MYSQL_TMP_DIR/mysecret_keyring --plugin-dir=KEYRING_PLUGIN_PATH
|
|
SELECT c2,c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
|
|
c2 c4 c5 c6 ST_AsText(c7)
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
SELECT COUNT(*) FROM tde_db.t_encrypt;
|
|
COUNT(*)
|
|
8
|
|
INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
|
|
SELECT COUNT(*) FROM tde_db.t_encrypt;
|
|
COUNT(*)
|
|
16
|
|
# Create partition table
|
|
DROP TABLE tde_db.t_encrypt;
|
|
CREATE TABLE tde_db.t_encrypt (c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,c3 VARCHAR(255), c4 JSON ,c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL ) ENCRYPTION="Y" ENGINE=InnoDB PARTITION BY RANGE (c2) (PARTITION p1 VALUES LESS THAN (4),PARTITION p2 VALUES LESS THAN (8),PARTITION p3 VALUES LESS THAN (1000)) ;
|
|
SHOW CREATE TABLE tde_db.t_encrypt;
|
|
Table Create Table
|
|
t_encrypt CREATE TABLE `t_encrypt` (
|
|
`c2` int(11) NOT NULL AUTO_INCREMENT,
|
|
`c3` varchar(255) DEFAULT NULL,
|
|
`c4` json DEFAULT NULL,
|
|
`c5` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
|
|
`c6` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
|
|
PRIMARY KEY (`c2`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
|
|
/*!50100 PARTITION BY RANGE (`c2`)
|
|
(PARTITION p1 VALUES LESS THAN (4) ENGINE = InnoDB,
|
|
PARTITION p2 VALUES LESS THAN (8) ENGINE = InnoDB,
|
|
PARTITION p3 VALUES LESS THAN (1000) ENGINE = InnoDB) */
|
|
INSERT INTO tde_db.t_encrypt(c3,c4) VALUES (REPEAT('a',200),'{ "key_a": 1, "key_b": 2, "key_c": 3 }');
|
|
INSERT INTO tde_db.t_encrypt(c3,c4) SELECT c3,c4 FROM tde_db.t_encrypt;
|
|
INSERT INTO tde_db.t_encrypt(c3,c4) SELECT c3,c4 FROM tde_db.t_encrypt;
|
|
INSERT INTO tde_db.t_encrypt(c3,c4) SELECT c3,c4 FROM tde_db.t_encrypt;
|
|
INSERT INTO tde_db.t_encrypt(c3,c4) SELECT c3,c4 FROM tde_db.t_encrypt;
|
|
INSERT INTO tde_db.t_encrypt(c3,c4) SELECT c3,c4 FROM tde_db.t_encrypt;
|
|
SELECT c2,c4,c5 FROM tde_db.t_encrypt ORDER BY c2 LIMIT 10;
|
|
c2 c4 c5
|
|
1 {"key_a": 1, "key_b": 2, "key_c": 3} 1
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1
|
|
4 {"key_a": 1, "key_b": 2, "key_c": 3} 1
|
|
5 {"key_a": 1, "key_b": 2, "key_c": 3} 1
|
|
6 {"key_a": 1, "key_b": 2, "key_c": 3} 1
|
|
7 {"key_a": 1, "key_b": 2, "key_c": 3} 1
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1
|
|
10 {"key_a": 1, "key_b": 2, "key_c": 3} 1
|
|
SELECT c2,c4,c5,c6 FROM tde_db.t_encrypt ORDER BY c2 LIMIT 10;
|
|
c2 c4 c5 c6
|
|
1 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
5 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
10 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
DELETE FROM tde_db.t_encrypt WHERE c2 > 10;
|
|
UPDATE tde_db.t_encrypt SET c2 = 100 WHERE c2=1;
|
|
SELECT c2,c4,c5 FROM tde_db.t_encrypt ORDER BY c2 LIMIT 10;
|
|
c2 c4 c5
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1
|
|
4 {"key_a": 1, "key_b": 2, "key_c": 3} 1
|
|
5 {"key_a": 1, "key_b": 2, "key_c": 3} 1
|
|
6 {"key_a": 1, "key_b": 2, "key_c": 3} 1
|
|
7 {"key_a": 1, "key_b": 2, "key_c": 3} 1
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1
|
|
10 {"key_a": 1, "key_b": 2, "key_c": 3} 1
|
|
100 {"key_a": 1, "key_b": 2, "key_c": 3} 1
|
|
SELECT c2,c4,c5,c6 FROM tde_db.t_encrypt ORDER BY c2 LIMIT 10;
|
|
c2 c4 c5 c6
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
5 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
10 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
ALTER TABLE tde_db.t_encrypt TRUNCATE PARTITION p2;
|
|
SELECT c2,c4,c5 FROM tde_db.t_encrypt ORDER BY c2 LIMIT 10;
|
|
c2 c4 c5
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1
|
|
10 {"key_a": 1, "key_b": 2, "key_c": 3} 1
|
|
100 {"key_a": 1, "key_b": 2, "key_c": 3} 1
|
|
SELECT c2,c4,c5,c6 FROM tde_db.t_encrypt ORDER BY c2 LIMIT 10;
|
|
c2 c4 c5 c6
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
10 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
SHOW CREATE TABLE tde_db.t_encrypt;
|
|
Table Create Table
|
|
t_encrypt CREATE TABLE `t_encrypt` (
|
|
`c2` int(11) NOT NULL AUTO_INCREMENT,
|
|
`c3` varchar(255) DEFAULT NULL,
|
|
`c4` json DEFAULT NULL,
|
|
`c5` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
|
|
`c6` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
|
|
PRIMARY KEY (`c2`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
|
|
/*!50100 PARTITION BY RANGE (`c2`)
|
|
(PARTITION p1 VALUES LESS THAN (4) ENGINE = InnoDB,
|
|
PARTITION p2 VALUES LESS THAN (8) ENGINE = InnoDB,
|
|
PARTITION p3 VALUES LESS THAN (1000) ENGINE = InnoDB) */
|
|
# restart with keying
|
|
# restart: --early-plugin-load=keyring_file=keyring_file.so --loose-keyring_file_data=MYSQL_TMP_DIR/mysecret_keyring --plugin-dir=KEYRING_PLUGIN_PATH
|
|
SELECT c2,c4,c5,c6 FROM tde_db.t_encrypt ORDER BY c2 LIMIT 10;
|
|
c2 c4 c5 c6
|
|
2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
10 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
|
|
DROP TABLE tde_db.t_encrypt;
|
|
DROP DATABASE tde_db;
|
|
SET GLOBAL innodb_file_per_table=1;
|
|
DROP DATABASE IF EXISTS tde_db;
|
|
DROP TABLE IF EXISTS tde_db. t_encrypt;
|
|
CREATE DATABASE tde_db;
|
|
USE tde_db;
|
|
SET GLOBAL innodb_file_per_table = 1;
|
|
SELECT @@innodb_file_per_table;
|
|
@@innodb_file_per_table
|
|
1
|
|
CREATE TABLE tde_db.t_encrypt(c2 INT NOT NULL PRIMARY KEY,
|
|
c3 CHAR(255) Default 'No text',
|
|
c4 JSON ,
|
|
c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
|
|
c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
|
|
c7 POINT NOT NULL SRID 0,
|
|
spatial INDEX idx2 (c7)
|
|
) ENCRYPTION="Y" ENGINE = InnoDB;
|
|
CREATE PROCEDURE tde_db.populate_t_encrypt()
|
|
begin
|
|
declare i int default 1;
|
|
declare has_error int default 0;
|
|
DECLARE CONTINUE HANDLER FOR 1062 SET has_error = 1;
|
|
while (i <= 2000) DO
|
|
insert into tde_db.t_encrypt(c2,c3,c4,c7) VALUES(i,CONCAT(REPEAT('a',200),LPAD(CAST(i AS CHAR),4,'0')),'{ "key_a": 1, "key_b": 2, "key_c": 3 }',ST_GeomFromText('POINT(383293632 1754448)'));
|
|
set i = i + 1;
|
|
end while;
|
|
end|
|
|
CREATE PROCEDURE tde_db.populate_t_encrypt_small()
|
|
begin
|
|
declare i int default 1;
|
|
declare has_error int default 0;
|
|
DECLARE CONTINUE HANDLER FOR 1062 SET has_error = 1;
|
|
while (i <= 500) DO
|
|
insert into tde_db.t_encrypt(c2,c3,c4,c7) VALUES(i,CONCAT(REPEAT('a',200),LPAD(CAST(i AS CHAR),4,'0')),'{ "key_a": 1, "key_b": 2, "key_c": 3 }',ST_GeomFromText('POINT(383293632 1754448)'));
|
|
set i = i + 1;
|
|
end while;
|
|
end|
|
|
CREATE PROCEDURE tde_db.read_t_encrypt()
|
|
begin
|
|
declare i int default 1;
|
|
while (i <= 30) DO
|
|
SELECT * FROM (SELECT * FROM tde_db.t_encrypt ORDER BY RAND() LIMIT 1) AS A WHERE A.c2 < 0 ;
|
|
set i = i + 1;
|
|
end while;
|
|
end|
|
|
CREATE PROCEDURE tde_db.rotate_master_key()
|
|
begin
|
|
declare i int default 1;
|
|
declare has_error int default 0;
|
|
while (i <= 500) DO
|
|
ALTER INSTANCE ROTATE INNODB MASTER KEY;
|
|
set i = i + 1;
|
|
end while;
|
|
end|
|
|
CREATE PROCEDURE tde_db.create_encrypt_table(encrypt VARCHAR(5))
|
|
begin
|
|
declare i int default 1;
|
|
declare has_error int default 0;
|
|
while (i <= 50) DO
|
|
SET @sql_text = CONCAT('CREATE TABLE ',CONCAT('tde_db.t_encrypt_',encrypt,'_',i),' (c1 INT) ENCRYPTION="',encrypt,'"' ,' ENGINE=InnoDB');
|
|
PREPARE stmt FROM @sql_text;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
set i = i + 1;
|
|
end while;
|
|
end|
|
|
SHOW CREATE TABLE tde_db.t_encrypt;
|
|
Table Create Table
|
|
t_encrypt CREATE TABLE `t_encrypt` (
|
|
`c2` int(11) NOT NULL,
|
|
`c3` char(255) DEFAULT 'No text',
|
|
`c4` json DEFAULT NULL,
|
|
`c5` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
|
|
`c6` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
|
|
`c7` point NOT NULL /*!80003 SRID 0 */,
|
|
PRIMARY KEY (`c2`),
|
|
SPATIAL KEY `idx2` (`c7`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
|
|
# In connection con1 - Running insert
|
|
call tde_db.populate_t_encrypt();
|
|
# In connection con2 - Running insert
|
|
call tde_db.populate_t_encrypt_small();
|
|
# In connection con3 : Running select
|
|
call tde_db.read_t_encrypt();
|
|
# In connection con4 : Running select
|
|
call tde_db.read_t_encrypt();
|
|
# In connection con5 - Running "alter instance"
|
|
call tde_db.rotate_master_key();
|
|
# In connection con6 - Running "create table"
|
|
call tde_db.create_encrypt_table("Y");
|
|
# In connection con7 - Running "create table"
|
|
call tde_db.create_encrypt_table("N");
|
|
# In connection con1
|
|
# In connection con2
|
|
# In connection con3
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
# In connection con4
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
c2 c3 c4 c5 c6 c7
|
|
# In connection con5
|
|
# In connection con6
|
|
# In connection con7
|
|
USE tde_db;
|
|
SELECT c2,right(c3,20),c4,c5,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
|
|
c2 right(c3,20) c4 c5 ST_AsText(c7)
|
|
1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
2 aaaaaaaaaaaaaaaa0002 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
3 aaaaaaaaaaaaaaaa0003 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
4 aaaaaaaaaaaaaaaa0004 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
5 aaaaaaaaaaaaaaaa0005 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
6 aaaaaaaaaaaaaaaa0006 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
7 aaaaaaaaaaaaaaaa0007 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
8 aaaaaaaaaaaaaaaa0008 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
9 aaaaaaaaaaaaaaaa0009 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
10 aaaaaaaaaaaaaaaa0010 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
|
|
c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
|
|
1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
2 aaaaaaaaaaaaaaaa0002 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
3 aaaaaaaaaaaaaaaa0003 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
4 aaaaaaaaaaaaaaaa0004 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
5 aaaaaaaaaaaaaaaa0005 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
6 aaaaaaaaaaaaaaaa0006 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
7 aaaaaaaaaaaaaaaa0007 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
8 aaaaaaaaaaaaaaaa0008 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
9 aaaaaaaaaaaaaaaa0009 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
10 aaaaaaaaaaaaaaaa0010 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
SELECT COUNT(*) FROM tde_db.t_encrypt;
|
|
COUNT(*)
|
|
2000
|
|
SELECT c2,right(c3,20),c4,c5,ST_AsText(c7) FROM tde_db.t_encrypt WHERE c2%200 = 0;
|
|
c2 right(c3,20) c4 c5 ST_AsText(c7)
|
|
200 aaaaaaaaaaaaaaaa0200 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
400 aaaaaaaaaaaaaaaa0400 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
600 aaaaaaaaaaaaaaaa0600 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
800 aaaaaaaaaaaaaaaa0800 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
1000 aaaaaaaaaaaaaaaa1000 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
1200 aaaaaaaaaaaaaaaa1200 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
1400 aaaaaaaaaaaaaaaa1400 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
1600 aaaaaaaaaaaaaaaa1600 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
1800 aaaaaaaaaaaaaaaa1800 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
2000 aaaaaaaaaaaaaaaa2000 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt WHERE c2%200 = 0;
|
|
c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
|
|
200 aaaaaaaaaaaaaaaa0200 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
400 aaaaaaaaaaaaaaaa0400 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
600 aaaaaaaaaaaaaaaa0600 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
800 aaaaaaaaaaaaaaaa0800 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
1000 aaaaaaaaaaaaaaaa1000 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
1200 aaaaaaaaaaaaaaaa1200 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
1400 aaaaaaaaaaaaaaaa1400 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
1600 aaaaaaaaaaaaaaaa1600 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
1800 aaaaaaaaaaaaaaaa1800 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
2000 aaaaaaaaaaaaaaaa2000 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
# restart with keying
|
|
# restart: --early-plugin-load=keyring_file=keyring_file.so --loose-keyring_file_data=MYSQL_TMP_DIR/mysecret_keyring --plugin-dir=KEYRING_PLUGIN_PATH
|
|
SELECT c2,right(c3,20),c4,c5,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
|
|
c2 right(c3,20) c4 c5 ST_AsText(c7)
|
|
1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
2 aaaaaaaaaaaaaaaa0002 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
3 aaaaaaaaaaaaaaaa0003 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
4 aaaaaaaaaaaaaaaa0004 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
5 aaaaaaaaaaaaaaaa0005 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
6 aaaaaaaaaaaaaaaa0006 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
7 aaaaaaaaaaaaaaaa0007 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
8 aaaaaaaaaaaaaaaa0008 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
9 aaaaaaaaaaaaaaaa0009 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
10 aaaaaaaaaaaaaaaa0010 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
|
|
c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
|
|
1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
2 aaaaaaaaaaaaaaaa0002 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
3 aaaaaaaaaaaaaaaa0003 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
4 aaaaaaaaaaaaaaaa0004 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
5 aaaaaaaaaaaaaaaa0005 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
6 aaaaaaaaaaaaaaaa0006 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
7 aaaaaaaaaaaaaaaa0007 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
8 aaaaaaaaaaaaaaaa0008 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
9 aaaaaaaaaaaaaaaa0009 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
10 aaaaaaaaaaaaaaaa0010 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
SELECT COUNT(*) FROM tde_db.t_encrypt;
|
|
COUNT(*)
|
|
2000
|
|
SELECT c2,right(c3,20),c4,c5,ST_AsText(c7) FROM tde_db.t_encrypt WHERE c2%200 = 0;
|
|
c2 right(c3,20) c4 c5 ST_AsText(c7)
|
|
200 aaaaaaaaaaaaaaaa0200 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
400 aaaaaaaaaaaaaaaa0400 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
600 aaaaaaaaaaaaaaaa0600 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
800 aaaaaaaaaaaaaaaa0800 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
1000 aaaaaaaaaaaaaaaa1000 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
1200 aaaaaaaaaaaaaaaa1200 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
1400 aaaaaaaaaaaaaaaa1400 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
1600 aaaaaaaaaaaaaaaa1600 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
1800 aaaaaaaaaaaaaaaa1800 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
2000 aaaaaaaaaaaaaaaa2000 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
|
|
SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt WHERE c2%200 = 0;
|
|
c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
|
|
200 aaaaaaaaaaaaaaaa0200 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
400 aaaaaaaaaaaaaaaa0400 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
600 aaaaaaaaaaaaaaaa0600 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
800 aaaaaaaaaaaaaaaa0800 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
1000 aaaaaaaaaaaaaaaa1000 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
1200 aaaaaaaaaaaaaaaa1200 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
1400 aaaaaaaaaaaaaaaa1400 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
1600 aaaaaaaaaaaaaaaa1600 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
1800 aaaaaaaaaaaaaaaa1800 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
2000 aaaaaaaaaaaaaaaa2000 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
|
|
DROP DATABASE tde_db;
|
|
SET GLOBAL innodb_file_per_table=1;
|
|
DROP DATABASE IF EXISTS tde_db;
|
|
CREATE DATABASE tde_db;
|
|
USE tde_db;
|
|
DROP TABLE IF EXISTS tde_db.t_encrypt;
|
|
SET GLOBAL innodb_file_per_table = 1;
|
|
SELECT @@innodb_file_per_table;
|
|
@@innodb_file_per_table
|
|
1
|
|
CREATE TABLE tde_db.t_encrypt(c1 INT, c2 char(20), c3 BLOB) ENCRYPTION="Y" ENGINE = InnoDB;
|
|
SHOW CREATE TABLE tde_db.t_encrypt;
|
|
Table Create Table
|
|
t_encrypt CREATE TABLE `t_encrypt` (
|
|
`c1` int(11) DEFAULT NULL,
|
|
`c2` char(20) DEFAULT NULL,
|
|
`c3` blob
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
|
|
INSERT INTO tde_db.t_encrypt VALUES(0, "aaaaa",repeat('A', 20000));
|
|
INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
|
|
INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
|
|
INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
|
|
INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
|
|
INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
|
|
INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
|
|
SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
|
|
c1 c2 right(c3, 20)
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
CREATE USER encryptprivuser@localhost IDENTIFIED BY 'auth';
|
|
GRANT ALL PRIVILEGES ON *.* to encryptprivuser@localhost;
|
|
FLUSH PRIVILEGES;
|
|
CREATE USER encryptnonprivuser@localhost IDENTIFIED BY 'noauth';
|
|
GRANT SELECT ON *.* to encryptnonprivuser@localhost;
|
|
FLUSH PRIVILEGES;
|
|
SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
|
|
c1 c2 right(c3, 20)
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
# In connection 1
|
|
SELECT CURRENT_USER();
|
|
CURRENT_USER()
|
|
encryptprivuser@localhost
|
|
SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
|
|
c1 c2 right(c3, 20)
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
ALTER INSTANCE ROTATE INNODB MASTER KEY;
|
|
SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
|
|
c1 c2 right(c3, 20)
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
# In connection 2
|
|
SELECT CURRENT_USER();
|
|
CURRENT_USER()
|
|
encryptnonprivuser@localhost
|
|
SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
|
|
c1 c2 right(c3, 20)
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
ALTER INSTANCE ROTATE INNODB MASTER KEY;
|
|
ERROR 42000: Access denied; you need (at least one of) the SUPER or ENCRYPTION_KEY_ADMIN privilege(s) for this operation
|
|
CREATE TABLE tde_db.t_encrypt_np(c1 INT, c2 char(20), c3 BLOB) ENCRYPTION="Y" ENGINE = InnoDB;
|
|
ERROR 42000: CREATE command denied to user 'encryptnonprivuser'@'localhost' for table 't_encrypt_np'
|
|
SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
|
|
c1 c2 right(c3, 20)
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
# In connection default
|
|
# Starting server with keyring plugin
|
|
# restart: --early-plugin-load=keyring_file=keyring_file.so --loose-keyring_file_data=MYSQL_TMP_DIR/mysecret_keyring --plugin-dir=KEYRING_PLUGIN_PATH
|
|
SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
|
|
c1 c2 right(c3, 20)
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
0 aaaaa AAAAAAAAAAAAAAAAAAAA
|
|
DROP USER encryptnonprivuser@localhost;
|
|
DROP USER encryptprivuser@localhost;
|
|
DROP TABLE tde_db.t_encrypt;
|
|
SET GLOBAL innodb_file_per_table=1;
|
|
DROP DATABASE IF EXISTS tde_db;
|
|
CREATE DATABASE tde_db;
|
|
USE tde_db;
|
|
DROP TABLE IF EXISTS tde_db.t_encrypt;
|
|
SET GLOBAL innodb_file_per_table = 1;
|
|
SELECT @@innodb_file_per_table;
|
|
@@innodb_file_per_table
|
|
1
|
|
CREATE TABLE tde_db.t_encrypt(c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 char(100), c3 BLOB , FULLTEXT INDEX `idx1` (c2)) ENCRYPTION="Y" ENGINE = InnoDB;
|
|
CREATE TABLE tde_db.t_encrypt1(c11 INT , c22 char(100), c33 BLOB , FULLTEXT INDEX `idx1` (c22)) ENCRYPTION="Y" ENGINE = InnoDB;
|
|
SHOW CREATE TABLE tde_db.t_encrypt;
|
|
Table Create Table
|
|
t_encrypt CREATE TABLE `t_encrypt` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
`c2` char(100) DEFAULT NULL,
|
|
`c3` blob,
|
|
PRIMARY KEY (`c1`),
|
|
FULLTEXT KEY `idx1` (`c2`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
|
|
CREATE TABLE tde_db.t_encrypt2 (f1 INT PRIMARY KEY, f2 CHAR(100),
|
|
FOREIGN KEY (f1) REFERENCES tde_db.t_encrypt(c1) ON UPDATE CASCADE) ENCRYPTION="Y" ENGINE=InnoDB;
|
|
CREATE TRIGGER tde_db.trigger_encrypt_table AFTER INSERT ON tde_db.t_encrypt
|
|
FOR EACH ROW
|
|
begin
|
|
INSERT INTO tde_db.t_encrypt1 SET c11 = NEW.c1*-1, c22 = NEW.c2 , c33 = NEW.c3;
|
|
end|
|
|
INSERT INTO tde_db.t_encrypt(c2,c3) VALUES("transparanet tablespace encryption",repeat('A', 200));
|
|
INSERT INTO tde_db.t_encrypt(c2,c3) VALUES("general tablespace option",repeat('A', 200));
|
|
INSERT INTO tde_db.t_encrypt(c2,c3) VALUES("page level encryption",repeat('A', 200));
|
|
INSERT INTO tde_db.t_encrypt2(f1,f2) VALUES(1,"transparanet tablespace encryption");
|
|
INSERT INTO tde_db.t_encrypt2(f1,f2) VALUES(2,"general tablespace option");
|
|
SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
|
|
c1 c2 right(c3, 20)
|
|
1 transparanet tablespace encryption AAAAAAAAAAAAAAAAAAAA
|
|
2 general tablespace option AAAAAAAAAAAAAAAAAAAA
|
|
3 page level encryption AAAAAAAAAAAAAAAAAAAA
|
|
SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt WHERE MATCH c2 AGAINST ('tablespace');
|
|
c1 c2 right(c3, 20)
|
|
1 transparanet tablespace encryption AAAAAAAAAAAAAAAAAAAA
|
|
2 general tablespace option AAAAAAAAAAAAAAAAAAAA
|
|
SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt WHERE MATCH c2 AGAINST ('tablespace' IN BOOLEAN MODE);
|
|
c1 c2 right(c3, 20)
|
|
1 transparanet tablespace encryption AAAAAAAAAAAAAAAAAAAA
|
|
2 general tablespace option AAAAAAAAAAAAAAAAAAAA
|
|
SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt WHERE MATCH c2 AGAINST ('+tablespace -encryption' IN BOOLEAN MODE);
|
|
c1 c2 right(c3, 20)
|
|
2 general tablespace option AAAAAAAAAAAAAAAAAAAA
|
|
ALTER TABLE tde_db.t_encrypt DROP INDEX idx1;
|
|
SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
|
|
c1 c2 right(c3, 20)
|
|
1 transparanet tablespace encryption AAAAAAAAAAAAAAAAAAAA
|
|
2 general tablespace option AAAAAAAAAAAAAAAAAAAA
|
|
3 page level encryption AAAAAAAAAAAAAAAAAAAA
|
|
ALTER TABLE tde_db.t_encrypt ADD COLUMN c4 CHAR(20) DEFAULT 'text';
|
|
SELECT c1,c2,right(c3, 20),c4 FROM tde_db.t_encrypt LIMIT 10;
|
|
c1 c2 right(c3, 20) c4
|
|
1 transparanet tablespace encryption AAAAAAAAAAAAAAAAAAAA text
|
|
2 general tablespace option AAAAAAAAAAAAAAAAAAAA text
|
|
3 page level encryption AAAAAAAAAAAAAAAAAAAA text
|
|
CREATE VIEW tde_db.t_encrypt_view AS SELECT c1,c2 FROM tde_db.t_encrypt;
|
|
SELECT c2 FROM tde_db.t_encrypt_view LIMIT 10;
|
|
c2
|
|
transparanet tablespace encryption
|
|
general tablespace option
|
|
page level encryption
|
|
SELECT A.c2,B.c2,right(B.c3,20) FROM tde_db.t_encrypt_view A , tde_db.t_encrypt B WHERE A.c2 = B.c2;
|
|
c2 c2 right(B.c3,20)
|
|
transparanet tablespace encryption transparanet tablespace encryption AAAAAAAAAAAAAAAAAAAA
|
|
general tablespace option general tablespace option AAAAAAAAAAAAAAAAAAAA
|
|
page level encryption page level encryption AAAAAAAAAAAAAAAAAAAA
|
|
DROP VIEW tde_db.t_encrypt_view;
|
|
SELECT c11,c22,right(c33, 20) FROM tde_db.t_encrypt1 LIMIT 10;
|
|
c11 c22 right(c33, 20)
|
|
-1 transparanet tablespace encryption AAAAAAAAAAAAAAAAAAAA
|
|
-2 general tablespace option AAAAAAAAAAAAAAAAAAAA
|
|
-3 page level encryption AAAAAAAAAAAAAAAAAAAA
|
|
INSERT INTO tde_db.t_encrypt2(f1,f2) VALUES(2,"general tablespace option");
|
|
ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
|
|
INSERT INTO tde_db.t_encrypt2(f1,f2) VALUES(8,"general tablespace option");
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`tde_db`.`t_encrypt2`, CONSTRAINT `t_encrypt2_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t_encrypt` (`c1`) ON UPDATE CASCADE)
|
|
SELECT f1,f2 FROM tde_db.t_encrypt2;
|
|
f1 f2
|
|
1 transparanet tablespace encryption
|
|
2 general tablespace option
|
|
UPDATE tde_db.t_encrypt SET c1=10 WHERE c1=1;
|
|
SELECT f1,f2 FROM tde_db.t_encrypt2;
|
|
f1 f2
|
|
2 general tablespace option
|
|
10 transparanet tablespace encryption
|
|
DROP DATABASE tde_db;
|
|
SET GLOBAL innodb_file_per_table=1;
|
|
DROP DATABASE IF EXISTS tde_db;
|
|
CREATE DATABASE tde_db;
|
|
USE tde_db;
|
|
DROP TABLE IF EXISTS tde_db.t_encrypt;
|
|
SET GLOBAL innodb_file_per_table = 1;
|
|
SELECT @@innodb_file_per_table;
|
|
@@innodb_file_per_table
|
|
1
|
|
CREATE TABLE tde_db.t_encrypt (c2 INT NOT NULL AUTO_INCREMENT ,c3 VARCHAR(255), c4 JSON ,c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,c7 POINT NOT NULL SRID 0,spatial INDEX idx2 (c7) , PRIMARY KEY (c2,c3(100))) ENCRYPTION="Y" ENGINE=InnoDB;
|
|
CREATE PROCEDURE tde_db.txn_t_encrypt()
|
|
BEGIN
|
|
declare i int default 0;
|
|
declare rowcnt int default 0;
|
|
START TRANSACTION;
|
|
WHILE (i <= 2000) DO
|
|
SET i = i + 1;
|
|
SET rowcnt = rowcnt + 1;
|
|
INSERT INTO tde_db.t_encrypt(c3,c4,c7) VALUES (CONCAT(REPEAT('a',10),REPEAT(i,10)),'{ "key_a": 1, "key_b": 2, "key_c": 3 }',ST_GeomFromText('POINT(383293632 1754448)'));
|
|
IF (rowcnt = 3) THEN
|
|
UPDATE tde_db.t_encrypt SET c4 = '{ "key_a": 21, "key_b": 22, "key_c": 23 }' WHERE c2 = i-1 ;
|
|
DELETE FROM tde_db.t_encrypt WHERE c2 = i;
|
|
SAVEPOINT A;
|
|
END IF;
|
|
IF (rowcnt = 5) THEN
|
|
UPDATE tde_db.t_encrypt SET c4 = '{ "key_a": 41, "key_b": 42, "key_c": 43 }' WHERE c2 = i-1 ;
|
|
DELETE FROM tde_db.t_encrypt WHERE c2 = i;
|
|
SAVEPOINT B;
|
|
END IF;
|
|
IF (rowcnt = 10) THEN
|
|
ROLLBACK TO SAVEPOINT A;
|
|
COMMIT;
|
|
SET rowcnt = 0;
|
|
START TRANSACTION;
|
|
END IF;
|
|
END WHILE;
|
|
COMMIT;
|
|
end|
|
|
call tde_db.txn_t_encrypt();
|
|
SELECT COUNT(*) FROM tde_db.t_encrypt;
|
|
COUNT(*)
|
|
401
|
|
SELECT c2,RIGHT(c3,20),c4 FROM tde_db.t_encrypt LIMIT 10;
|
|
c2 RIGHT(c3,20) c4
|
|
1 aaaaaaaaaa1111111111 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
2 aaaaaaaaaa2222222222 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
11 11111111111111111111 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
12 12121212121212121212 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
21 21212121212121212121 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
22 22222222222222222222 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
31 31313131313131313131 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
32 32323232323232323232 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
41 41414141414141414141 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
42 42424242424242424242 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
SELECT c2,RIGHT(c3,20),c4 FROM tde_db.t_encrypt WHERE c2 > 500 AND c2 < 600;
|
|
c2 RIGHT(c3,20) c4
|
|
501 01501501501501501501 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
502 02502502502502502502 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
511 11511511511511511511 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
512 12512512512512512512 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
521 21521521521521521521 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
522 22522522522522522522 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
531 31531531531531531531 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
532 32532532532532532532 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
541 41541541541541541541 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
542 42542542542542542542 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
551 51551551551551551551 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
552 52552552552552552552 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
561 61561561561561561561 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
562 62562562562562562562 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
571 71571571571571571571 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
572 72572572572572572572 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
581 81581581581581581581 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
582 82582582582582582582 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
591 91591591591591591591 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
592 92592592592592592592 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
SELECT c2,RIGHT(c3,20),c4 FROM tde_db.t_encrypt ORDER BY c2 DESC LIMIT 10;
|
|
c2 RIGHT(c3,20) c4
|
|
2001 20012001200120012001 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
1992 19921992199219921992 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
1991 19911991199119911991 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
1982 19821982198219821982 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
1981 19811981198119811981 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
1972 19721972197219721972 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
1971 19711971197119711971 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
1962 19621962196219621962 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
1961 19611961196119611961 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
1952 19521952195219521952 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
# Starting server with keyring plugin
|
|
# restart: --early-plugin-load=keyring_file=keyring_file.so --loose-keyring_file_data=MYSQL_TMP_DIR/mysecret_keyring --plugin-dir=KEYRING_PLUGIN_PATH
|
|
SELECT COUNT(*) FROM tde_db.t_encrypt;
|
|
COUNT(*)
|
|
401
|
|
SELECT c2,RIGHT(c3,20),c4 FROM tde_db.t_encrypt LIMIT 10;
|
|
c2 RIGHT(c3,20) c4
|
|
1 aaaaaaaaaa1111111111 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
2 aaaaaaaaaa2222222222 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
11 11111111111111111111 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
12 12121212121212121212 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
21 21212121212121212121 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
22 22222222222222222222 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
31 31313131313131313131 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
32 32323232323232323232 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
41 41414141414141414141 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
42 42424242424242424242 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
SELECT c2,RIGHT(c3,20),c4 FROM tde_db.t_encrypt WHERE c2 > 500 AND c2 < 600;
|
|
c2 RIGHT(c3,20) c4
|
|
501 01501501501501501501 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
502 02502502502502502502 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
511 11511511511511511511 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
512 12512512512512512512 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
521 21521521521521521521 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
522 22522522522522522522 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
531 31531531531531531531 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
532 32532532532532532532 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
541 41541541541541541541 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
542 42542542542542542542 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
551 51551551551551551551 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
552 52552552552552552552 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
561 61561561561561561561 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
562 62562562562562562562 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
571 71571571571571571571 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
572 72572572572572572572 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
581 81581581581581581581 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
582 82582582582582582582 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
591 91591591591591591591 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
592 92592592592592592592 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
SELECT c2,RIGHT(c3,20),c4 FROM tde_db.t_encrypt ORDER BY c2 DESC LIMIT 10;
|
|
c2 RIGHT(c3,20) c4
|
|
2001 20012001200120012001 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
1992 19921992199219921992 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
1991 19911991199119911991 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
1982 19821982198219821982 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
1981 19811981198119811981 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
1972 19721972197219721972 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
1971 19711971197119711971 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
1962 19621962196219621962 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
1961 19611961196119611961 {"key_a": 1, "key_b": 2, "key_c": 3}
|
|
1952 19521952195219521952 {"key_a": 21, "key_b": 22, "key_c": 23}
|
|
DROP DATABASE tde_db;
|
|
# Global privilege ENCRYPTION_KEY_ADMIN can replace super.
|
|
USE test;
|
|
CREATE USER encryption_admin@localhost IDENTIFIED BY 'foo';
|
|
GRANT ENCRYPTION_KEY_ADMIN, CREATE ON *.* TO encryption_admin@localhost;
|
|
CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
|
|
ALTER INSTANCE ROTATE INNODB MASTER KEY;
|
|
REVOKE ENCRYPTION_KEY_ADMIN ON *.* FROM encryption_admin@localhost;
|
|
ALTER INSTANCE ROTATE INNODB MASTER KEY;
|
|
ERROR 42000: Access denied; you need (at least one of) the SUPER or ENCRYPTION_KEY_ADMIN privilege(s) for this operation
|
|
DROP USER encryption_admin@localhost;
|
|
DROP TABLE t1;
|
|
# restart:
|
|
SET GLOBAL innodb_file_per_table=1;
|