1--source include/not_embedded.inc 2 3# Setup database, tables and user accounts 4--disable_warnings 5drop database if exists mysqltest_db1; 6--enable_warnings 7set GLOBAL sql_mode=""; 8set LOCAL sql_mode=""; 9create database mysqltest_db1; 10use mysqltest_db1; 11create table t_column_priv_only (a int, b int); 12create table t_select_priv like t_column_priv_only; 13create table t_no_priv like t_column_priv_only; 14grant all privileges on test.* to mysqltest_u1@localhost; 15grant insert (a) on mysqltest_db1.t_column_priv_only to mysqltest_u1@localhost; 16grant select on mysqltest_db1.t_select_priv to mysqltest_u1@localhost; 17 18--echo ** Connect as restricted user mysqltest_u1. 19--echo 20connect (con1,localhost,mysqltest_u1,,); 21connection con1; 22 23######################################################################## 24--echo ** Test column level privileges only. No SELECT privileges on the table. 25--echo ** INSERT INTO ... VALUES ... 26--echo ** Attempting to insert values to a table with only column privileges 27--echo ** should work. 28insert into mysqltest_db1.t_column_priv_only (a) VALUES (1); 29--echo 30 31######################################################################### 32--echo ** SHOW COLUMNS 33--echo ** Should succeed because we have privileges (any) on at least one of the columns. 34select column_name as 'Field',column_type as 'Type',is_nullable as 'Null',column_key as 'Key',column_default as 'Default',extra as 'Extra' from information_schema.columns where table_schema='mysqltest_db1' and table_name='t_column_priv_only'; 35show columns from mysqltest_db1.t_column_priv_only; 36######################################################################### 37--echo ** SHOW COLUMNS 38--echo ** Should fail because there are no privileges on any column combination. 39--error 1142 40show columns from mysqltest_db1.t_no_priv; 41--echo ** However, select from I_S.COLUMNS will succeed but not show anything: 42select column_name as 'Field',column_type as 'Type',is_nullable as 'Null',column_key as 'Key',column_default as 'Default',extra as 'Extra' from information_schema.columns where table_schema='mysqltest_db1' and table_name='t_no_priv'; 43--echo 44######################################################################### 45--echo ** CREATE TABLE ... LIKE ... require SELECT privleges and will fail. 46--error 1142 47create table test.t_no_priv like mysqltest_db1.column_priv_only; 48--echo 49######################################################################### 50--echo ** Just to be sure... SELECT also fails. 51--error 1142 52select * from mysqltest_db1.t_column_priv_only; 53--echo 54######################################################################### 55--echo ** SHOW CREATE TABLE ... require any privileges on all columns (the entire table). 56--echo ** First we try and fail on a table with only one column privilege. 57--error 1142 58show create table mysqltest_db1.t_column_priv_only; 59--echo 60######################################################################### 61--echo ** Now we do the same on a table with SELECT privileges. 62--echo 63######################################################################### 64--echo ** SHOW COLUMNS 65--echo ** Success because we got some privileges on the table (SELECT_ACL) 66show columns from mysqltest_db1.t_select_priv; 67--echo 68######################################################################### 69--echo ** CREATE TABLE ... LIKE ... require SELECT privleges and will SUCCEED. 70--disable_warnings 71drop table if exists test.t_duplicated; 72--enable_warnings 73create table test.t_duplicated like mysqltest_db1.t_select_priv; 74drop table test.t_duplicated; 75--echo 76######################################################################### 77--echo ** SHOW CREATE TABLE will succeed because we have a privilege on all columns in the table (table-level privilege). 78show create table mysqltest_db1.t_select_priv; 79--echo 80######################################################################### 81--echo ** SHOW CREATE TABLE will fail if there is no grants at all: 82--error 1142 83show create table mysqltest_db1.t_no_priv; 84--echo 85 86connection default; 87 88# 89# SHOW INDEX 90# 91use mysqltest_db1; 92CREATE TABLE t5 (s1 INT); 93CREATE INDEX i ON t5 (s1); 94CREATE TABLE t6 (s1 INT, s2 INT); 95CREATE VIEW v5 AS SELECT * FROM t5; 96CREATE VIEW v6 AS SELECT * FROM t6; 97CREATE VIEW v2 AS SELECT * FROM t_select_priv; 98CREATE VIEW v3 AS SELECT * FROM t_select_priv; 99CREATE INDEX i ON t6 (s1); 100GRANT UPDATE (s2) ON t6 to mysqltest_u1@localhost; 101GRANT UPDATE (s2) ON v6 to mysqltest_u1@localhost; 102GRANT SHOW VIEW ON v2 to mysqltest_u1@localhost; 103GRANT SHOW VIEW, SELECT ON v3 to mysqltest_u1@localhost; 104 105connection con1; 106use mysqltest_db1; 107--echo ** Connect as restricted user mysqltest_u1. 108--echo ** SELECT FROM INFORMATION_SCHEMA.STATISTICS will succeed because any privileges will do (authentication is enough). 109--echo ** but will return no rows 110SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name='t5'; 111# 112# Bug27145 EXTRA_ACL trouble 113# 114--echo ** SHOW INDEX FROM t5 will fail because we don't have any privileges on any column combination. 115--error 1142 116SHOW INDEX FROM t5; 117--echo ** SHOW INDEX FROM t6 will succeed because there exist a privilege on a column combination on t6. 118SHOW INDEX FROM t6; 119 120# CHECK TABLE 121--echo ** CHECK TABLE requires any privilege on any column combination and should succeed for t6: 122CHECK TABLE t6; 123--echo ** With no privileges access is naturally denied: 124--error 1142 125CHECK TABLE t5; 126 127# CHECKSUM 128--echo ** CHECKSUM TABLE requires SELECT privileges on the table. The following should fail: 129--error 1142 130CHECKSUM TABLE t6; 131--echo ** And this should work: 132CHECKSUM TABLE t_select_priv; 133 134# SHOW CREATE VIEW 135--error 1142 136SHOW CREATE VIEW v5; 137--error 1142 138SHOW CREATE VIEW v6; 139--error 1142 140SHOW CREATE VIEW v2; 141SHOW CREATE VIEW v3; 142 143connection default; 144disconnect con1; 145drop database mysqltest_db1; 146drop user mysqltest_u1@localhost; 147 148# 149# MDEV-18241 Downgrade from 10.4 to 10.3 crashes 150# 151source include/switch_to_mysql_user.inc; 152call mtr.add_suppression("Table 'mysql.user' doesn't exist"); 153call mtr.add_suppression("'mysql.user' is not of type 'TABLE'"); 154rename table mysql.user to mysql.user1; 155create view mysql.user as select * from mysql.user1; 156--error ER_WRONG_OBJECT 157flush privileges; 158drop view mysql.user; 159create temporary table mysql.user select * from mysql.user1 limit 0; 160--error ER_NO_SUCH_TABLE 161flush privileges; 162drop temporary table mysql.user; 163rename table mysql.user1 to mysql.user; 164source include/switch_to_mysql_global_priv.inc; 165 166# 167# Bug#28986737: RENAMING AND REPLACING MYSQL.USER TABLE CAN LEAD TO A SERVER CRASH 168# 169source include/switch_to_mysql_user.inc; 170call mtr.add_suppression('mysql.user table is damaged'); 171rename table mysql.user to mysql.user1; 172create table mysql.user (Host char(100), User char(100)); 173--error ER_UNKNOWN_ERROR 174flush privileges; 175drop table mysql.user; 176rename table mysql.user1 to mysql.user; 177source include/switch_to_mysql_global_priv.inc; 178 179--echo End of 5.5 tests 180 181--echo # 182--echo # Additional coverage for refactoring which is made as part 183--echo # of fix for bug #27480 "Extend CREATE TEMPORARY TABLES privilege 184--echo # to allow temp table operations". 185--echo # 186--echo # Check that for statements like CHECK/REPAIR and OPTIMIZE TABLE 187--echo # privileges for all tables involved are checked before processing 188--echo # any tables. Doing otherwise, i.e. checking privileges for table 189--echo # right before processing it might result in lost results for tables 190--echo # which were processed by the time when table for which privileges 191--echo # are insufficient are discovered. 192--echo # 193call mtr.add_suppression("Got an error from thread_id=.*ha_myisam.cc:"); 194call mtr.add_suppression("MySQL thread id .*, query id .* localhost.*mysqltest_u1 Checking table"); 195--disable_warnings 196drop database if exists mysqltest_db1; 197--enable_warnings 198let $MYSQLD_DATADIR = `SELECT @@datadir`; 199create database mysqltest_db1; 200--echo # Create tables which we are going to CHECK/REPAIR. 201create table mysqltest_db1.t1 (a int, key(a)) engine=myisam; 202create table mysqltest_db1.t2 (b int); 203insert into mysqltest_db1.t1 values (1), (2); 204insert into mysqltest_db1.t2 values (1); 205--echo # Create user which will try to do this. 206create user mysqltest_u1@localhost; 207grant insert, select on mysqltest_db1.t1 to mysqltest_u1@localhost; 208connect (con1,localhost,mysqltest_u1,,); 209connection default; 210 211--echo # Corrupt t1 by replacing t1.MYI with a corrupt + unclosed one created 212--echo # by doing: 'create table t1 (a int key(a))' 213--echo # head -c1024 t1.MYI > corrupt_t1.MYI 214flush table mysqltest_db1.t1; 215--remove_file $MYSQLD_DATADIR/mysqltest_db1/t1.MYI 216--copy_file std_data/corrupt_t1.MYI $MYSQLD_DATADIR/mysqltest_db1/t1.MYI 217 218connection con1; 219check table mysqltest_db1.t1; 220--echo # The below statement should fail before repairing t1. 221--echo # Otherwise info about such repair will be missing from its result-set. 222--error ER_TABLEACCESS_DENIED_ERROR 223repair table mysqltest_db1.t1, mysqltest_db1.t2; 224--echo # The same is true for CHECK TABLE statement. 225--error ER_TABLEACCESS_DENIED_ERROR 226check table mysqltest_db1.t1, mysqltest_db1.t2; 227check table mysqltest_db1.t1; 228repair table mysqltest_db1.t1; 229 230--echo # Clean-up. 231disconnect con1; 232--source include/wait_until_disconnected.inc 233connection default; 234drop database mysqltest_db1; 235drop user mysqltest_u1@localhost; 236 237create user foo1 identified by password '11111111111111111111111111111111111111111'; 238create user foo2 identified by password '2222222222222222'; 239create user foo3 identified via mysql_native_password using '11111111111111111111111111111111111111111'; 240create user foo4 identified via mysql_old_password using '2222222222222222'; 241 242grant select on test.* to foo5 identified by password '11111111111111111111111111111111111111111'; 243grant select on test.* to foo6 identified by password '2222222222222222'; 244grant select on test.* to foo7 identified via mysql_native_password using '11111111111111111111111111111111111111111'; 245grant select on test.* to foo8 identified via mysql_old_password using '2222222222222222'; 246 247--sorted_result 248select user,password,plugin,authentication_string from mysql.user where user like 'foo%'; 249 250drop user foo1; 251drop user foo2; 252drop user foo3; 253drop user foo4; 254drop user foo5; 255drop user foo6; 256drop user foo7; 257drop user foo8; 258 259--error ER_PASSWD_LENGTH 260create user foo1 identified via mysql_native_password using '00'; 261--error ER_PASSWD_LENGTH 262create user foo2 identified via mysql_native_password using '2222222222222222'; 263--error ER_PASSWD_LENGTH 264create user foo3 identified via mysql_old_password using '00'; 265--error ER_PASSWD_LENGTH 266create user foo4 identified via mysql_old_password using '11111111111111111111111111111111111111111'; 267 268set GLOBAL sql_mode=default; 269 270--echo End of 10.1 tests 271