227 lines
7.2 KiB
Plaintext
227 lines
7.2 KiB
Plaintext
DROP DATABASE IF EXISTS tde_db;
|
|
CREATE DATABASE tde_db;
|
|
USE tde_db;
|
|
SET GLOBAL innodb_redo_log_encrypt = 1;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
CREATE TABLE tde_db.t4 (a BIGINT PRIMARY KEY, b LONGBLOB) ENGINE=InnoDB;
|
|
INSERT INTO t4 (a, b) VALUES (1, REPEAT('a', 6*512*512));
|
|
SELECT a,LEFT(b,10) FROM tde_db.t4;
|
|
a LEFT(b,10)
|
|
1 aaaaaaaaaa
|
|
# restart
|
|
SELECT a,LEFT(b,10) FROM tde_db.t4;
|
|
a LEFT(b,10)
|
|
1 aaaaaaaaaa
|
|
DROP TABLE tde_db.t4;
|
|
DROP DATABASE IF EXISTS tde_db;
|
|
CREATE DATABASE tde_db;
|
|
USE tde_db;
|
|
# Starting server with keyring plugin
|
|
# restart: --early-plugin-load=keyring_file=keyring_file.so --loose-keyring_file_data=MYSQL_TMP_DIR/my_key_log3 --plugin-dir=KEYRING_PLUGIN_PATH --innodb_redo_log_encrypt=ON
|
|
SELECT @@global.innodb_redo_log_encrypt ;
|
|
@@global.innodb_redo_log_encrypt
|
|
1
|
|
INSTALL PLUGIN keyring_file SONAME 'keyring_file.so';
|
|
ERROR HY000: Function 'keyring_file' already exists
|
|
UNINSTALL PLUGIN keyring_file;
|
|
# Starting server with keyring plugin
|
|
# restart: --early-plugin-load=keyring_file=keyring_file.so --loose-keyring_file_data=MYSQL_TMP_DIR/my_key_log3 --plugin-dir=KEYRING_PLUGIN_PATH --innodb_redo_log_encrypt=ON
|
|
SET GLOBAL innodb_redo_log_encrypt = 0;
|
|
SELECT @@global.innodb_redo_log_encrypt ;
|
|
@@global.innodb_redo_log_encrypt
|
|
0
|
|
INSTALL PLUGIN keyring_file SONAME 'keyring_file.so';
|
|
ERROR HY000: Function 'keyring_file' already exists
|
|
UNINSTALL PLUGIN keyring_file;
|
|
# restart: --early-plugin-load=keyring_file=keyring_file.so --loose-keyring_file_data=MYSQL_TMP_DIR/my_key_log3 --plugin-dir=KEYRING_PLUGIN_PATH --innodb_redo_log_encrypt=ON
|
|
SELECT @@global.innodb_redo_log_encrypt ;
|
|
@@global.innodb_redo_log_encrypt
|
|
1
|
|
SET GLOBAL innodb_redo_log_encrypt = 1;
|
|
SELECT @@global.innodb_redo_log_encrypt ;
|
|
@@global.innodb_redo_log_encrypt
|
|
1
|
|
CREATE TABLE tde_db.t1 (a BIGINT PRIMARY KEY, b LONGBLOB) ENGINE=InnoDB;
|
|
INSERT INTO t1 (a, b) VALUES (1, REPEAT('a', 6*512*512));
|
|
SELECT a,LEFT(b,10) FROM tde_db.t1;
|
|
a LEFT(b,10)
|
|
1 aaaaaaaaaa
|
|
CREATE TABLE tde_db.t2 (a BIGINT PRIMARY KEY, b LONGBLOB)
|
|
ENCRYPTION='Y' ENGINE=InnoDB;
|
|
INSERT INTO t2 (a, b) VALUES (1, REPEAT('a', 6*512*512));
|
|
SELECT a,LEFT(b,10) FROM tde_db.t2;
|
|
a LEFT(b,10)
|
|
1 aaaaaaaaaa
|
|
SET GLOBAL innodb_redo_log_encrypt = 0;
|
|
SELECT @@global.innodb_redo_log_encrypt ;
|
|
@@global.innodb_redo_log_encrypt
|
|
0
|
|
CREATE TABLE tde_db.t3 (a BIGINT PRIMARY KEY, b LONGBLOB) ENGINE=InnoDB;
|
|
INSERT INTO t3 (a, b) VALUES (1, REPEAT('a', 6*512*512));
|
|
SELECT a,LEFT(b,10) FROM tde_db.t3;
|
|
a LEFT(b,10)
|
|
1 aaaaaaaaaa
|
|
CREATE TABLE tde_db.t4 (a BIGINT PRIMARY KEY, b LONGBLOB)
|
|
ENCRYPTION='Y' ENGINE=InnoDB;
|
|
INSERT INTO t4 (a, b) VALUES (1, REPEAT('a', 6*512*512));
|
|
SELECT a,LEFT(b,10) FROM tde_db.t4;
|
|
a LEFT(b,10)
|
|
1 aaaaaaaaaa
|
|
FLUSH LOGS;
|
|
SELECT
|
|
PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE
|
|
FROM INFORMATION_SCHEMA.PLUGINS
|
|
WHERE PLUGIN_NAME LIKE '%keyring_file%' ;
|
|
PLUGIN_NAME PLUGIN_STATUS PLUGIN_TYPE
|
|
keyring_file ACTIVE KEYRING
|
|
SELECT a,LEFT(b,10) FROM tde_db.t1;
|
|
a LEFT(b,10)
|
|
1 aaaaaaaaaa
|
|
SELECT a,LEFT(b,10) FROM tde_db.t2;
|
|
a LEFT(b,10)
|
|
1 aaaaaaaaaa
|
|
SELECT a,LEFT(b,10) FROM tde_db.t3;
|
|
a LEFT(b,10)
|
|
1 aaaaaaaaaa
|
|
SELECT a,LEFT(b,10) FROM tde_db.t4;
|
|
a LEFT(b,10)
|
|
1 aaaaaaaaaa
|
|
DROP TABLE tde_db.t1,tde_db.t2,tde_db.t3,tde_db.t4;
|
|
SELECT @@global.innodb_redo_log_encrypt ;
|
|
@@global.innodb_redo_log_encrypt
|
|
1
|
|
CREATE TABLE tde_db.t1 (a BIGINT PRIMARY KEY, b LONGBLOB) ENGINE=InnoDB;
|
|
CREATE TABLE tde_db.t2 (a BIGINT PRIMARY KEY, b LONGBLOB)
|
|
ENCRYPTION='Y' ENGINE=InnoDB;
|
|
START TRANSACTION;
|
|
SET GLOBAL innodb_redo_log_encrypt = 1;
|
|
INSERT INTO t1 (a, b) VALUES (1, REPEAT('a', 6*512*512));
|
|
INSERT INTO t2 (a, b) VALUES (1, REPEAT('a', 6*512*512));
|
|
SELECT a,LEFT(b,10) FROM tde_db.t1;
|
|
a LEFT(b,10)
|
|
1 aaaaaaaaaa
|
|
SELECT a,LEFT(b,10) FROM tde_db.t2;
|
|
a LEFT(b,10)
|
|
1 aaaaaaaaaa
|
|
ROLLBACK;
|
|
START TRANSACTION;
|
|
INSERT INTO t1 (a, b) VALUES (2, REPEAT('a', 6*512*512));
|
|
INSERT INTO t2 (a, b) VALUES (2, REPEAT('a', 6*512*512));
|
|
SELECT a,LEFT(b,10) FROM tde_db.t1;
|
|
a LEFT(b,10)
|
|
2 aaaaaaaaaa
|
|
SELECT a,LEFT(b,10) FROM tde_db.t2;
|
|
a LEFT(b,10)
|
|
2 aaaaaaaaaa
|
|
COMMIT;
|
|
CREATE TABLE tde_db.t3 (a BIGINT PRIMARY KEY, b LONGBLOB) ENGINE=InnoDB;
|
|
CREATE TABLE tde_db.t4 (a BIGINT PRIMARY KEY, b LONGBLOB)
|
|
ENCRYPTION='Y' ENGINE=InnoDB;
|
|
START TRANSACTION;
|
|
SET GLOBAL innodb_redo_log_encrypt = 0;
|
|
SELECT @@global.innodb_redo_log_encrypt ;
|
|
@@global.innodb_redo_log_encrypt
|
|
0
|
|
INSERT INTO t3 (a, b) VALUES (1, REPEAT('a', 6*512*512));
|
|
INSERT INTO t4 (a, b) VALUES (1, REPEAT('a', 6*512*512));
|
|
SELECT a,LEFT(b,10) FROM tde_db.t3;
|
|
a LEFT(b,10)
|
|
1 aaaaaaaaaa
|
|
SELECT a,LEFT(b,10) FROM tde_db.t4;
|
|
a LEFT(b,10)
|
|
1 aaaaaaaaaa
|
|
ROLLBACK;
|
|
START TRANSACTION;
|
|
INSERT INTO t3 (a, b) VALUES (2, REPEAT('a', 6*512*512));
|
|
INSERT INTO t4 (a, b) VALUES (2, REPEAT('a', 6*512*512));
|
|
SELECT a,LEFT(b,10) FROM tde_db.t3;
|
|
a LEFT(b,10)
|
|
2 aaaaaaaaaa
|
|
SELECT a,LEFT(b,10) FROM tde_db.t4;
|
|
a LEFT(b,10)
|
|
2 aaaaaaaaaa
|
|
COMMIT;
|
|
SELECT
|
|
PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE
|
|
FROM INFORMATION_SCHEMA.PLUGINS
|
|
WHERE PLUGIN_NAME LIKE '%keyring_file%' ;
|
|
PLUGIN_NAME PLUGIN_STATUS PLUGIN_TYPE
|
|
keyring_file ACTIVE KEYRING
|
|
SELECT a,LEFT(b,10) FROM tde_db.t1;
|
|
a LEFT(b,10)
|
|
2 aaaaaaaaaa
|
|
SELECT a,LEFT(b,10) FROM tde_db.t2;
|
|
a LEFT(b,10)
|
|
2 aaaaaaaaaa
|
|
SELECT a,LEFT(b,10) FROM tde_db.t3;
|
|
a LEFT(b,10)
|
|
2 aaaaaaaaaa
|
|
SELECT a,LEFT(b,10) FROM tde_db.t4;
|
|
a LEFT(b,10)
|
|
2 aaaaaaaaaa
|
|
SET GLOBAL innodb_redo_log_encrypt = 0;
|
|
SELECT @@global.innodb_redo_log_encrypt ;
|
|
@@global.innodb_redo_log_encrypt
|
|
0
|
|
ALTER INSTANCE ROTATE INNODB MASTER KEY;
|
|
SELECT a,LEFT(b,10) FROM tde_db.t1;
|
|
a LEFT(b,10)
|
|
2 aaaaaaaaaa
|
|
SELECT a,LEFT(b,10) FROM tde_db.t2;
|
|
a LEFT(b,10)
|
|
2 aaaaaaaaaa
|
|
SELECT a,LEFT(b,10) FROM tde_db.t3;
|
|
a LEFT(b,10)
|
|
2 aaaaaaaaaa
|
|
SELECT a,LEFT(b,10) FROM tde_db.t4;
|
|
a LEFT(b,10)
|
|
2 aaaaaaaaaa
|
|
SET GLOBAL innodb_redo_log_encrypt = 1;
|
|
SELECT @@global.innodb_redo_log_encrypt ;
|
|
@@global.innodb_redo_log_encrypt
|
|
1
|
|
ALTER INSTANCE ROTATE INNODB MASTER KEY;
|
|
SELECT a,LEFT(b,10) FROM tde_db.t1;
|
|
a LEFT(b,10)
|
|
2 aaaaaaaaaa
|
|
SELECT a,LEFT(b,10) FROM tde_db.t2;
|
|
a LEFT(b,10)
|
|
2 aaaaaaaaaa
|
|
SELECT a,LEFT(b,10) FROM tde_db.t3;
|
|
a LEFT(b,10)
|
|
2 aaaaaaaaaa
|
|
SELECT a,LEFT(b,10) FROM tde_db.t4;
|
|
a LEFT(b,10)
|
|
2 aaaaaaaaaa
|
|
CREATE USER encryptnonprivuser@localhost IDENTIFIED BY 'noauth';
|
|
GRANT SELECT ON *.* to encryptnonprivuser@localhost;
|
|
FLUSH PRIVILEGES;
|
|
# In connection 1 - with encryptnonprivuser
|
|
SELECT @@global.innodb_redo_log_encrypt ;
|
|
@@global.innodb_redo_log_encrypt
|
|
1
|
|
SET GLOBAL innodb_redo_log_encrypt = 0;
|
|
ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
|
|
SELECT @@global.innodb_redo_log_encrypt ;
|
|
@@global.innodb_redo_log_encrypt
|
|
1
|
|
SET GLOBAL innodb_undo_log_encrypt = 0;
|
|
ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
|
|
SELECT @@global.innodb_undo_log_encrypt ;
|
|
@@global.innodb_undo_log_encrypt
|
|
0
|
|
SET GLOBAL innodb_redo_log_encrypt = 1;
|
|
ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
|
|
SELECT @@global.innodb_redo_log_encrypt ;
|
|
@@global.innodb_redo_log_encrypt
|
|
1
|
|
SET GLOBAL innodb_undo_log_encrypt = 1;
|
|
ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
|
|
SELECT @@global.innodb_undo_log_encrypt ;
|
|
@@global.innodb_undo_log_encrypt
|
|
0
|
|
# In connection default
|
|
UNINSTALL PLUGIN keyring_file;
|
|
DROP TABLE tde_db.t1,tde_db.t2,tde_db.t3,tde_db.t4;
|