Debian Codegraph @

mysql-test/suite/innodb/r/log_encrypt_3.result

raw
1 CREATE DATABASE tde_db;
2 USE tde_db;
3 SELECT @@global.innodb_redo_log_encrypt ;
4 @@global.innodb_redo_log_encrypt
5 1
6 INSTALL PLUGIN keyring_file SONAME 'keyring_file.so';
7 ERROR HY000: Function 'keyring_file' already exists
8 UNINSTALL PLUGIN keyring_file;
9 # restart:
10 SELECT @@global.innodb_redo_log_encrypt ;
11 @@global.innodb_redo_log_encrypt
12 1
13 SET GLOBAL innodb_redo_log_encrypt = 1;
14 SELECT @@global.innodb_redo_log_encrypt ;
15 @@global.innodb_redo_log_encrypt
16 1
17 CREATE TABLE tde_db.t1 (a BIGINT PRIMARY KEY, b LONGBLOB) ENGINE=InnoDB;
18 INSERT INTO t1 (a, b) VALUES (1, REPEAT('a', 6*512*512));
19 SELECT a,LEFT(b,10) FROM tde_db.t1;
20 a LEFT(b,10)
21 1 aaaaaaaaaa
22 CREATE TABLE tde_db.t2 (a BIGINT PRIMARY KEY, b LONGBLOB)
23 ENCRYPTION='Y' ENGINE=InnoDB;
24 INSERT INTO t2 (a, b) VALUES (1, REPEAT('a', 6*512*512));
25 SELECT a,LEFT(b,10) FROM tde_db.t2;
26 a LEFT(b,10)
27 1 aaaaaaaaaa
28 SET GLOBAL innodb_redo_log_encrypt = 0;
29 SELECT @@global.innodb_redo_log_encrypt ;
30 @@global.innodb_redo_log_encrypt
31 0
32 CREATE TABLE tde_db.t3 (a BIGINT PRIMARY KEY, b LONGBLOB) ENGINE=InnoDB;
33 INSERT INTO t3 (a, b) VALUES (1, REPEAT('a', 6*512*512));
34 SELECT a,LEFT(b,10) FROM tde_db.t3;
35 a LEFT(b,10)
36 1 aaaaaaaaaa
37 CREATE TABLE tde_db.t4 (a BIGINT PRIMARY KEY, b LONGBLOB)
38 ENCRYPTION='Y' ENGINE=InnoDB;
39 INSERT INTO t4 (a, b) VALUES (1, REPEAT('a', 6*512*512));
40 SELECT a,LEFT(b,10) FROM tde_db.t4;
41 a LEFT(b,10)
42 1 aaaaaaaaaa
43 FLUSH LOGS;
44 # restart:
45 SELECT
46 PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE
47 FROM INFORMATION_SCHEMA.PLUGINS
48 WHERE PLUGIN_NAME LIKE '%keyring_file%' ;
49 PLUGIN_NAME PLUGIN_STATUS PLUGIN_TYPE
50 keyring_file ACTIVE KEYRING
51 SELECT a,LEFT(b,10) FROM tde_db.t1;
52 a LEFT(b,10)
53 1 aaaaaaaaaa
54 SELECT a,LEFT(b,10) FROM tde_db.t2;
55 a LEFT(b,10)
56 1 aaaaaaaaaa
57 SELECT a,LEFT(b,10) FROM tde_db.t3;
58 a LEFT(b,10)
59 1 aaaaaaaaaa
60 SELECT a,LEFT(b,10) FROM tde_db.t4;
61 a LEFT(b,10)
62 1 aaaaaaaaaa
63 DROP TABLE tde_db.t1,tde_db.t2,tde_db.t3,tde_db.t4;
64 SELECT @@global.innodb_redo_log_encrypt ;
65 @@global.innodb_redo_log_encrypt
66 1
67 CREATE TABLE tde_db.t1 (a BIGINT PRIMARY KEY, b LONGBLOB) ENGINE=InnoDB;
68 CREATE TABLE tde_db.t2 (a BIGINT PRIMARY KEY, b LONGBLOB)
69 ENCRYPTION='Y' ENGINE=InnoDB;
70 START TRANSACTION;
71 SET GLOBAL innodb_redo_log_encrypt = 1;
72 INSERT INTO t1 (a, b) VALUES (1, REPEAT('a', 6*512*512));
73 INSERT INTO t2 (a, b) VALUES (1, REPEAT('a', 6*512*512));
74 SELECT a,LEFT(b,10) FROM tde_db.t1;
75 a LEFT(b,10)
76 1 aaaaaaaaaa
77 SELECT a,LEFT(b,10) FROM tde_db.t2;
78 a LEFT(b,10)
79 1 aaaaaaaaaa
80 ROLLBACK;
81 START TRANSACTION;
82 INSERT INTO t1 (a, b) VALUES (2, REPEAT('a', 6*512*512));
83 INSERT INTO t2 (a, b) VALUES (2, REPEAT('a', 6*512*512));
84 SELECT a,LEFT(b,10) FROM tde_db.t1;
85 a LEFT(b,10)
86 2 aaaaaaaaaa
87 SELECT a,LEFT(b,10) FROM tde_db.t2;
88 a LEFT(b,10)
89 2 aaaaaaaaaa
90 COMMIT;
91 CREATE TABLE tde_db.t3 (a BIGINT PRIMARY KEY, b LONGBLOB) ENGINE=InnoDB;
92 CREATE TABLE tde_db.t4 (a BIGINT PRIMARY KEY, b LONGBLOB)
93 ENCRYPTION='Y' ENGINE=InnoDB;
94 START TRANSACTION;
95 SET GLOBAL innodb_redo_log_encrypt = 0;
96 SELECT @@global.innodb_redo_log_encrypt ;
97 @@global.innodb_redo_log_encrypt
98 0
99 INSERT INTO t3 (a, b) VALUES (1, REPEAT('a', 6*512*512));
100 INSERT INTO t4 (a, b) VALUES (1, REPEAT('a', 6*512*512));
101 SELECT a,LEFT(b,10) FROM tde_db.t3;
102 a LEFT(b,10)
103 1 aaaaaaaaaa
104 SELECT a,LEFT(b,10) FROM tde_db.t4;
105 a LEFT(b,10)
106 1 aaaaaaaaaa
107 ROLLBACK;
108 START TRANSACTION;
109 INSERT INTO t3 (a, b) VALUES (2, REPEAT('a', 6*512*512));
110 INSERT INTO t4 (a, b) VALUES (2, REPEAT('a', 6*512*512));
111 SELECT a,LEFT(b,10) FROM tde_db.t3;
112 a LEFT(b,10)
113 2 aaaaaaaaaa
114 SELECT a,LEFT(b,10) FROM tde_db.t4;
115 a LEFT(b,10)
116 2 aaaaaaaaaa
117 COMMIT;
118 # restart:
119 SELECT
120 PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE
121 FROM INFORMATION_SCHEMA.PLUGINS
122 WHERE PLUGIN_NAME LIKE '%keyring_file%' ;
123 PLUGIN_NAME PLUGIN_STATUS PLUGIN_TYPE
124 keyring_file ACTIVE KEYRING
125 SELECT a,LEFT(b,10) FROM tde_db.t1;
126 a LEFT(b,10)
127 2 aaaaaaaaaa
128 SELECT a,LEFT(b,10) FROM tde_db.t2;
129 a LEFT(b,10)
130 2 aaaaaaaaaa
131 SELECT a,LEFT(b,10) FROM tde_db.t3;
132 a LEFT(b,10)
133 2 aaaaaaaaaa
134 SELECT a,LEFT(b,10) FROM tde_db.t4;
135 a LEFT(b,10)
136 2 aaaaaaaaaa
137 SET GLOBAL innodb_redo_log_encrypt = 0;
138 SELECT @@global.innodb_redo_log_encrypt ;
139 @@global.innodb_redo_log_encrypt
140 0
141 ALTER INSTANCE ROTATE INNODB MASTER KEY;
142 SELECT a,LEFT(b,10) FROM tde_db.t1;
143 a LEFT(b,10)
144 2 aaaaaaaaaa
145 SELECT a,LEFT(b,10) FROM tde_db.t2;
146 a LEFT(b,10)
147 2 aaaaaaaaaa
148 SELECT a,LEFT(b,10) FROM tde_db.t3;
149 a LEFT(b,10)
150 2 aaaaaaaaaa
151 SELECT a,LEFT(b,10) FROM tde_db.t4;
152 a LEFT(b,10)
153 2 aaaaaaaaaa
154 SET GLOBAL innodb_redo_log_encrypt = 1;
155 SELECT @@global.innodb_redo_log_encrypt ;
156 @@global.innodb_redo_log_encrypt
157 1
158 ALTER INSTANCE ROTATE INNODB MASTER KEY;
159 SELECT a,LEFT(b,10) FROM tde_db.t1;
160 a LEFT(b,10)
161 2 aaaaaaaaaa
162 SELECT a,LEFT(b,10) FROM tde_db.t2;
163 a LEFT(b,10)
164 2 aaaaaaaaaa
165 SELECT a,LEFT(b,10) FROM tde_db.t3;
166 a LEFT(b,10)
167 2 aaaaaaaaaa
168 SELECT a,LEFT(b,10) FROM tde_db.t4;
169 a LEFT(b,10)
170 2 aaaaaaaaaa
171 CREATE USER encryptnonprivuser@localhost IDENTIFIED BY 'noauth';
172 GRANT SELECT ON *.* to encryptnonprivuser@localhost;
173 FLUSH PRIVILEGES;
174 # In connection 1 - with encryptnonprivuser
175 SELECT @@global.innodb_redo_log_encrypt ;
176 @@global.innodb_redo_log_encrypt
177 1
178 SET GLOBAL innodb_redo_log_encrypt = 0;
179 ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
180 SELECT @@global.innodb_redo_log_encrypt ;
181 @@global.innodb_redo_log_encrypt
182 1
183 SET GLOBAL innodb_undo_log_encrypt = 0;
184 ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
185 SELECT @@global.innodb_undo_log_encrypt ;
186 @@global.innodb_undo_log_encrypt
187 0
188 SET GLOBAL innodb_redo_log_encrypt = 1;
189 ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
190 SELECT @@global.innodb_redo_log_encrypt ;
191 @@global.innodb_redo_log_encrypt
192 1
193 SET GLOBAL innodb_undo_log_encrypt = 1;
194 ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
195 SELECT @@global.innodb_undo_log_encrypt ;
196 @@global.innodb_undo_log_encrypt
197 0
198 # In connection default
199 DROP TABLE tde_db.t1,tde_db.t2,tde_db.t3,tde_db.t4;