1--echo # 2--echo # WL#12261 Control (enforce and disable) table encryption 3--echo # 4--source include/have_debug.inc 5 6--echo # Pre-define user u1, which is used in different tests below. 7CREATE USER u1@localhost; 8GRANT ALL ON db1.* TO u1@localhost; 9GRANT CREATE TABLESPACE, PROCESS, SYSTEM_VARIABLES_ADMIN ON *.* TO u1@localhost; 10SET GLOBAL debug= '+d,skip_table_encryption_admin_check_for_set'; 11connect (con1, localhost, u1); 12 13 14--echo # This test run CREATE TABLE in different configurations, 15--echo # 16--echo # - Setting table_encryption_privilege_check to true/false. 17--echo # - Setting default_table_encryption to true/false. 18--echo # - With and without ENCRYPTION clause. 19--echo # - With and without user holding TABLE_ENCRYPTION_ADMIN privilege. 20--echo # - Test SHOW CREATE TABLE 21--echo # - Test INFORMATION_SCHEMA.TABLES.CREATE_OPTIONS 22--echo # - Check for warnings generated. 23--echo # 24 25# Initialization 26--let caseno=0 27--let use_tablespace=false 28--let expected_error=0 29--let expected_error2=0 30 31--echo ````````````````````````````````````````````````````````` 32--echo # Test CREATE TABLE on DATABASE with ENCRYPTION 'y/n' 33--echo # and with different values for system variable 34--echo # 'table_encryption_privilege_check' and 'default_table_encryption' 35 36--echo ````````````````````````````````````````````````````````` 37--echo # CREATE TABLE without ENCRYPTION clause 38 39--let explicit_encryption_clause=false 40 41--let privilege_check=false 42--let database_encryption='n' 43--source ./create_table.inc 44--let database_encryption='y' 45--source ./create_table.inc 46 47--let privilege_check=true 48--let database_encryption='n' 49--source ./create_table.inc 50--let database_encryption='y' 51--source ./create_table.inc 52 53--echo ````````````````````````````````````````````````````````` 54--echo # CREATE TABLE with ENCRYPTION clause 55 56--let explicit_encryption_clause=true 57 58--let privilege_check=false 59 60--let database_encryption='n' 61--let table_encryption='y' 62--source ./create_table.inc 63--let table_encryption='n' 64--source ./create_table.inc 65 66--let database_encryption='y' 67--let table_encryption='y' 68--source ./create_table.inc 69--let table_encryption='n' 70--source ./create_table.inc 71 72--let privilege_check=true 73 74--let database_encryption='n' 75--let table_encryption='y' 76--echo ````````````````````````````````````````````````````````` 77--echo # Fails as there is mismatch between table and database encryption. 78--let expected_error=ER_CANNOT_SET_TABLE_ENCRYPTION 79--source ./create_table.inc 80--let expected_error=0 81--let table_encryption='n' 82--source ./create_table.inc 83 84--let database_encryption='y' 85--let table_encryption='y' 86--source ./create_table.inc 87--echo ````````````````````````````````````````````````````````` 88--echo # Fails as there is mismatch between table and database encryption. 89--let table_encryption='n' 90--let expected_error=ER_CANNOT_SET_TABLE_ENCRYPTION 91--source ./create_table.inc 92 93--echo ````````````````````````````````````````````````````````` 94--echo # Check with invalid ENCRYPTION value 95--let privilege_check=false 96--let database_encryption='n' 97--let expected_error=ER_INVALID_ENCRYPTION_OPTION 98--let table_encryption='k' 99--source ./create_table.inc 100 101 102--echo ````````````````````````````````````````````````````````` 103--echo # With innodb_file_per_table=off and 104--echo # Test CREATE TABLE without ENCRYPTION clause. 105SET GLOBAL innodb_file_per_table = OFF; 106--let expected_error=0 107--let use_tablespace=false 108--let explicit_encryption_clause=false 109 110--let privilege_check=false 111--let database_encryption='n' 112--source ./create_table.inc 113 114--echo ````````````````````````````````````````````````````````` 115--echo # Fails because we cannot created a encrypted table in system tablespace. 116--let database_encryption='y' 117--let expected_error=ER_INVALID_ENCRYPTION_REQUEST 118--source ./create_table.inc 119--let expected_error=0 120--let privilege_check=true 121--let database_encryption='n' 122--source ./create_table.inc 123 124--echo ````````````````````````````````````````````````````````` 125--echo # Fails because we cannot created a encrypted table in system tablespace. 126--let database_encryption='y' 127--let expected_error=ER_INVALID_ENCRYPTION_REQUEST 128--source ./create_table.inc 129--let expected_error=0 130 131--echo ````````````````````````````````````````````````````````` 132--echo # With explicit ENCRYPTION clause 133--let explicit_encryption_clause=true 134 135--let privilege_check=false 136 137--echo ````````````````````````````````````````````````````````` 138--echo # Fails because we cannot created a encrypted table in system tablespace. 139--let database_encryption='n' 140--let table_encryption='y' 141--let expected_error=ER_INVALID_ENCRYPTION_REQUEST 142 143--source ./create_table.inc 144--let expected_error=0 145--let table_encryption='n' 146--source ./create_table.inc 147 148--echo ````````````````````````````````````````````````````````` 149--echo # Fails because we cannot created a encrypted table in system tablespace. 150--let database_encryption='y' 151--let table_encryption='y' 152--let expected_error=ER_INVALID_ENCRYPTION_REQUEST 153 154--source ./create_table.inc 155--let expected_error=0 156--let table_encryption='n' 157--source ./create_table.inc 158 159--let privilege_check=true 160 161--echo ````````````````````````````````````````````````````````` 162--echo # Fails because we cannot created a encrypted table in system tablespace. 163--let database_encryption='n' 164--let table_encryption='y' 165--let expected_error=ER_INVALID_ENCRYPTION_REQUEST 166--source ./create_table.inc 167 168--let expected_error=0 169--let table_encryption='n' 170--source ./create_table.inc 171 172--echo ````````````````````````````````````````````````````````` 173--echo # Fails because we cannot created a encrypted table in system tablespace. 174--let database_encryption='y' 175--let table_encryption='y' 176--let expected_error=ER_INVALID_ENCRYPTION_REQUEST 177--source ./create_table.inc 178 179--let expected_error=0 180--let table_encryption='n' 181--let expected_error=ER_CANNOT_SET_TABLE_ENCRYPTION 182--source ./create_table.inc 183 184--echo ````````````````````````````````````````````````````````` 185--echo # With innodb_file_per_table = ON. 186--echo # Test CREATE TABLE with explicit TABLESPACE=innodb_file_per_table 187SET GLOBAL innodb_file_per_table = ON; 188--let use_tablespace=true 189--let tablespace_name=innodb_file_per_table 190 191--echo ````````````````````````````````````````````````````````` 192--echo # Without ENCRYPTION clause 193--let explicit_encryption_clause=false 194--let expected_error=0 195 196--let privilege_check=false 197--let database_encryption='n' 198--source ./create_table.inc 199--let database_encryption='y' 200--source ./create_table.inc 201 202--let privilege_check=true 203--let database_encryption='n' 204--source ./create_table.inc 205--let database_encryption='y' 206--source ./create_table.inc 207 208--echo ````````````````````````````````````````````````````````` 209--echo # With ENCRYPTION clause 210--let explicit_encryption_clause=true 211 212--let privilege_check=false 213 214--let database_encryption='n' 215--let table_encryption='y' 216--source ./create_table.inc 217--let table_encryption='n' 218--source ./create_table.inc 219 220--let database_encryption='y' 221--let table_encryption='y' 222--source ./create_table.inc 223--let table_encryption='n' 224--source ./create_table.inc 225 226--let privilege_check=true 227 228--echo ````````````````````````````````````````````````````````` 229--echo # Fails as there is mismatch between table and database encryption. 230--let database_encryption='n' 231--let table_encryption='y' 232--let expected_error=ER_CANNOT_SET_TABLE_ENCRYPTION 233--source ./create_table.inc 234--let expected_error=0 235--let table_encryption='n' 236--source ./create_table.inc 237 238--let database_encryption='y' 239--let table_encryption='y' 240--source ./create_table.inc 241--let table_encryption='n' 242--echo ````````````````````````````````````````````````````````` 243--echo # Fails as there is mismatch between table and database encryption. 244--let expected_error=ER_CANNOT_SET_TABLE_ENCRYPTION 245--source ./create_table.inc 246 247 248--echo ````````````````````````````````````````````````````````` 249--echo # Test CREATE TABLE with TABLESPACE=innodb_system 250--let use_tablespace=true 251--let tablespace_name=innodb_system 252 253--echo ````````````````````````````````````````````````````````` 254--echo # Without ENCRYPTION clause 255--let explicit_encryption_clause=false 256 257--let expected_error=0 258 259--let privilege_check=false 260--let database_encryption='n' 261--source ./create_table.inc 262 263--echo ````````````````````````````````````````````````````````` 264--echo # Fails because we cannot created a encrypted table in system tablespace. 265--let expected_error=ER_INVALID_ENCRYPTION_REQUEST 266--let database_encryption='y' 267--source ./create_table.inc 268 269--let expected_error=0 270--let privilege_check=true 271--let database_encryption='n' 272--source ./create_table.inc 273 274--echo ````````````````````````````````````````````````````````` 275--echo # Fails because we cannot created a encrypted table in system tablespace. 276--let database_encryption='y' 277--let expected_error=ER_INVALID_ENCRYPTION_REQUEST 278--source ./create_table.inc 279--let expected_error=0 280 281--echo ````````````````````````````````````````````````````````` 282--echo # With ENCRYPTION clause 283--let explicit_encryption_clause=true 284 285--let privilege_check=false 286 287--echo ````````````````````````````````````````````````````````` 288--echo # Fails because we cannot created a encrypted table in system tablespace. 289--let database_encryption='n' 290--let table_encryption='y' 291--let expected_error=ER_INVALID_ENCRYPTION_REQUEST 292--source ./create_table.inc 293 294--let expected_error=0 295--let table_encryption='n' 296--source ./create_table.inc 297 298--echo ````````````````````````````````````````````````````````` 299--echo # Fails because we cannot created a encrypted table in system tablespace. 300--let database_encryption='y' 301--let table_encryption='y' 302--let expected_error=ER_INVALID_ENCRYPTION_REQUEST 303--source ./create_table.inc 304 305--let expected_error=0 306--let table_encryption='n' 307--source ./create_table.inc 308 309--let privilege_check=true 310 311--echo ````````````````````````````````````````````````````````` 312--echo # Fails as there is mismatch between table and database encryption. 313--echo # The statement would fail even with user owning TABLE_ENCRYPTION_ADMIN 314--echo # with ER_ILLEGAL_HA_CREATE_OPTION because one cannot create 315--echo # encrypted table in system tablespace. 316--let database_encryption='n' 317--let table_encryption='y' 318--let expected_error=ER_CANNOT_SET_TABLE_ENCRYPTION 319--let expected_error=ER_INVALID_ENCRYPTION_REQUEST 320--source ./create_table.inc 321--let expected_error=0 322--let expected_error2=0 323--let table_encryption='n' 324--source ./create_table.inc 325 326--echo ````````````````````````````````````````````````````````` 327--echo # Fails because we cannot created a encrypted table in system tablespace. 328--let database_encryption='y' 329--let table_encryption='y' 330--let expected_error=ER_INVALID_ENCRYPTION_REQUEST 331--source ./create_table.inc 332 333--echo ````````````````````````````````````````````````````````` 334--echo # Fails as there is mismatch between table and database encryption. 335--let expected_error=0 336--let table_encryption='n' 337--let expected_error=ER_CANNOT_SET_TABLE_ENCRYPTION 338--source ./create_table.inc 339 340 341--echo ````````````````````````````````````````````````````````` 342--echo # Test CREATE TABLE with general TABLESPACE 343 344--echo ````````````````````````````````````````````````````````` 345--echo # With unencrypted general tablespace and without ENCRYPTION clause. 346--let use_tablespace=true 347--let tablespace_name=ts1 348--let table_encryption='n' 349--let explicit_encryption_clause=false 350--let expected_error=0 351 352--let privilege_check=false 353--let database_encryption='n' 354--source ./create_table.inc 355 356--echo ````````````````````````````````````````````````````````` 357--echo # Fails as there is mismatch between table and database encryption. 358--echo # The table ENCRYPTION clause is inherited from database and this 359--echo # does not match tablespace encryption type. 360--let database_encryption='y' 361--let expected_error=ER_INVALID_ENCRYPTION_REQUEST 362--source ./create_table.inc 363 364--let expected_error=0 365--let privilege_check=true 366--let database_encryption='n' 367--source ./create_table.inc 368 369--echo ````````````````````````````````````````````````````````` 370--echo # Fails as there is mismatch between table and database encryption. 371--echo # The table ENCRYPTION clause is inherited from database and this 372--echo # does not match tablespace encryption type. 373--let database_encryption='y' 374--let expected_error=ER_INVALID_ENCRYPTION_REQUEST 375--source ./create_table.inc 376--let expected_error=0 377 378--echo ````````````````````````````````````````````````````````` 379--echo # With ENCRYPTION clause 380--let explicit_encryption_clause=true 381 382--let privilege_check=false 383 384--let database_encryption='n' 385--let table_encryption='y' 386--source ./create_table.inc 387--let table_encryption='n' 388--source ./create_table.inc 389 390--let database_encryption='y' 391--let table_encryption='y' 392--source ./create_table.inc 393--let table_encryption='n' 394--source ./create_table.inc 395 396--let privilege_check=true 397 398--echo ````````````````````````````````````````````````````````` 399--echo # Fails as there is mismatch between table and database encryption. 400--let database_encryption='n' 401--let table_encryption='y' 402--let expected_error=ER_CANNOT_SET_TABLE_ENCRYPTION 403--source ./create_table.inc 404 405--let expected_error=0 406--let table_encryption='n' 407--source ./create_table.inc 408 409--let database_encryption='y' 410--let table_encryption='y' 411--source ./create_table.inc 412 413--echo ````````````````````````````````````````````````````````` 414--echo # Fails as there is mismatch between table and database encryption. 415--let table_encryption='n' 416--let expected_error=ER_CANNOT_SET_TABLE_ENCRYPTION 417--source ./create_table.inc 418 419 420--echo # Cleanup 421disconnect con1; 422connection default; 423DROP USER u1@localhost; 424SET GLOBAL debug= '-d,skip_table_encryption_admin_check_for_set'; 425