1drop database if exists mysqltest_db1; 2set GLOBAL sql_mode=""; 3set LOCAL sql_mode=""; 4create database mysqltest_db1; 5use mysqltest_db1; 6create table t_column_priv_only (a int, b int); 7create table t_select_priv like t_column_priv_only; 8create table t_no_priv like t_column_priv_only; 9grant all privileges on test.* to mysqltest_u1@localhost; 10grant insert (a) on mysqltest_db1.t_column_priv_only to mysqltest_u1@localhost; 11grant select on mysqltest_db1.t_select_priv to mysqltest_u1@localhost; 12** Connect as restricted user mysqltest_u1. 13 14connect con1,localhost,mysqltest_u1,,; 15connection con1; 16** Test column level privileges only. No SELECT privileges on the table. 17** INSERT INTO ... VALUES ... 18** Attempting to insert values to a table with only column privileges 19** should work. 20insert into mysqltest_db1.t_column_priv_only (a) VALUES (1); 21 22** SHOW COLUMNS 23** Should succeed because we have privileges (any) on at least one of the columns. 24select 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'; 25Field Type Null Key Default Extra 26a int(11) YES NULL 27show columns from mysqltest_db1.t_column_priv_only; 28Field Type Null Key Default Extra 29a int(11) YES NULL 30** SHOW COLUMNS 31** Should fail because there are no privileges on any column combination. 32show columns from mysqltest_db1.t_no_priv; 33ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't_no_priv' 34** However, select from I_S.COLUMNS will succeed but not show anything: 35select 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'; 36Field Type Null Key Default Extra 37 38** CREATE TABLE ... LIKE ... require SELECT privleges and will fail. 39create table test.t_no_priv like mysqltest_db1.column_priv_only; 40ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 'column_priv_only' 41 42** Just to be sure... SELECT also fails. 43select * from mysqltest_db1.t_column_priv_only; 44ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't_column_priv_only' 45 46** SHOW CREATE TABLE ... require any privileges on all columns (the entire table). 47** First we try and fail on a table with only one column privilege. 48show create table mysqltest_db1.t_column_priv_only; 49ERROR 42000: SHOW command denied to user 'mysqltest_u1'@'localhost' for table 't_column_priv_only' 50 51** Now we do the same on a table with SELECT privileges. 52 53** SHOW COLUMNS 54** Success because we got some privileges on the table (SELECT_ACL) 55show columns from mysqltest_db1.t_select_priv; 56Field Type Null Key Default Extra 57a int(11) YES NULL 58b int(11) YES NULL 59 60** CREATE TABLE ... LIKE ... require SELECT privleges and will SUCCEED. 61drop table if exists test.t_duplicated; 62create table test.t_duplicated like mysqltest_db1.t_select_priv; 63drop table test.t_duplicated; 64 65** SHOW CREATE TABLE will succeed because we have a privilege on all columns in the table (table-level privilege). 66show create table mysqltest_db1.t_select_priv; 67Table Create Table 68t_select_priv CREATE TABLE `t_select_priv` ( 69 `a` int(11) DEFAULT NULL, 70 `b` int(11) DEFAULT NULL 71) ENGINE=MyISAM DEFAULT CHARSET=latin1 72 73** SHOW CREATE TABLE will fail if there is no grants at all: 74show create table mysqltest_db1.t_no_priv; 75ERROR 42000: SHOW command denied to user 'mysqltest_u1'@'localhost' for table 't_no_priv' 76 77connection default; 78use mysqltest_db1; 79CREATE TABLE t5 (s1 INT); 80CREATE INDEX i ON t5 (s1); 81CREATE TABLE t6 (s1 INT, s2 INT); 82CREATE VIEW v5 AS SELECT * FROM t5; 83CREATE VIEW v6 AS SELECT * FROM t6; 84CREATE VIEW v2 AS SELECT * FROM t_select_priv; 85CREATE VIEW v3 AS SELECT * FROM t_select_priv; 86CREATE INDEX i ON t6 (s1); 87GRANT UPDATE (s2) ON t6 to mysqltest_u1@localhost; 88GRANT UPDATE (s2) ON v6 to mysqltest_u1@localhost; 89GRANT SHOW VIEW ON v2 to mysqltest_u1@localhost; 90GRANT SHOW VIEW, SELECT ON v3 to mysqltest_u1@localhost; 91connection con1; 92use mysqltest_db1; 93** Connect as restricted user mysqltest_u1. 94** SELECT FROM INFORMATION_SCHEMA.STATISTICS will succeed because any privileges will do (authentication is enough). 95** but will return no rows 96SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name='t5'; 97TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT 98** SHOW INDEX FROM t5 will fail because we don't have any privileges on any column combination. 99SHOW INDEX FROM t5; 100ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't5' 101** SHOW INDEX FROM t6 will succeed because there exist a privilege on a column combination on t6. 102SHOW INDEX FROM t6; 103Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 104t6 1 i 1 s1 A NULL NULL NULL YES BTREE 105** CHECK TABLE requires any privilege on any column combination and should succeed for t6: 106CHECK TABLE t6; 107Table Op Msg_type Msg_text 108mysqltest_db1.t6 check status OK 109** With no privileges access is naturally denied: 110CHECK TABLE t5; 111ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't5' 112** CHECKSUM TABLE requires SELECT privileges on the table. The following should fail: 113CHECKSUM TABLE t6; 114ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't6' 115** And this should work: 116CHECKSUM TABLE t_select_priv; 117Table Checksum 118mysqltest_db1.t_select_priv 0 119SHOW CREATE VIEW v5; 120ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 'v5' 121SHOW CREATE VIEW v6; 122ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 'v6' 123SHOW CREATE VIEW v2; 124ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 'v2' 125SHOW CREATE VIEW v3; 126View Create View character_set_client collation_connection 127v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t_select_priv`.`a` AS `a`,`t_select_priv`.`b` AS `b` from `t_select_priv` latin1 latin1_swedish_ci 128connection default; 129disconnect con1; 130drop database mysqltest_db1; 131drop user mysqltest_u1@localhost; 132# switching from mysql.global_priv to mysql.user 133call mtr.add_suppression("Table 'mysql.user' doesn't exist"); 134call mtr.add_suppression("'mysql.user' is not of type 'TABLE'"); 135rename table mysql.user to mysql.user1; 136create view mysql.user as select * from mysql.user1; 137flush privileges; 138ERROR HY000: 'mysql.user' is not of type 'TABLE' 139drop view mysql.user; 140create temporary table mysql.user select * from mysql.user1 limit 0; 141flush privileges; 142ERROR 42S02: Table 'mysql.user' doesn't exist 143drop temporary table mysql.user; 144rename table mysql.user1 to mysql.user; 145# switching back from mysql.user to mysql.global_priv 146# switching from mysql.global_priv to mysql.user 147call mtr.add_suppression('mysql.user table is damaged'); 148rename table mysql.user to mysql.user1; 149create table mysql.user (Host char(100), User char(100)); 150flush privileges; 151ERROR HY000: Fatal error: mysql.user table is damaged or in unsupported 3.20 format 152drop table mysql.user; 153rename table mysql.user1 to mysql.user; 154# switching back from mysql.user to mysql.global_priv 155End of 5.5 tests 156# 157# Additional coverage for refactoring which is made as part 158# of fix for bug #27480 "Extend CREATE TEMPORARY TABLES privilege 159# to allow temp table operations". 160# 161# Check that for statements like CHECK/REPAIR and OPTIMIZE TABLE 162# privileges for all tables involved are checked before processing 163# any tables. Doing otherwise, i.e. checking privileges for table 164# right before processing it might result in lost results for tables 165# which were processed by the time when table for which privileges 166# are insufficient are discovered. 167# 168call mtr.add_suppression("Got an error from thread_id=.*ha_myisam.cc:"); 169call mtr.add_suppression("MySQL thread id .*, query id .* localhost.*mysqltest_u1 Checking table"); 170drop database if exists mysqltest_db1; 171create database mysqltest_db1; 172# Create tables which we are going to CHECK/REPAIR. 173create table mysqltest_db1.t1 (a int, key(a)) engine=myisam; 174create table mysqltest_db1.t2 (b int); 175insert into mysqltest_db1.t1 values (1), (2); 176insert into mysqltest_db1.t2 values (1); 177# Create user which will try to do this. 178create user mysqltest_u1@localhost; 179grant insert, select on mysqltest_db1.t1 to mysqltest_u1@localhost; 180connect con1,localhost,mysqltest_u1,,; 181connection default; 182# Corrupt t1 by replacing t1.MYI with a corrupt + unclosed one created 183# by doing: 'create table t1 (a int key(a))' 184# head -c1024 t1.MYI > corrupt_t1.MYI 185flush table mysqltest_db1.t1; 186connection con1; 187check table mysqltest_db1.t1; 188Table Op Msg_type Msg_text 189mysqltest_db1.t1 check warning 1 client is using or hasn't closed the table properly 190mysqltest_db1.t1 check error Size of indexfile is: 1024 Should be: 2048 191mysqltest_db1.t1 check warning Size of datafile is: 14 Should be: 7 192mysqltest_db1.t1 check error Corrupt 193# The below statement should fail before repairing t1. 194# Otherwise info about such repair will be missing from its result-set. 195repair table mysqltest_db1.t1, mysqltest_db1.t2; 196ERROR 42000: SELECT, INSERT command denied to user 'mysqltest_u1'@'localhost' for table 't2' 197# The same is true for CHECK TABLE statement. 198check table mysqltest_db1.t1, mysqltest_db1.t2; 199ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't2' 200check table mysqltest_db1.t1; 201Table Op Msg_type Msg_text 202mysqltest_db1.t1 check warning Table is marked as crashed 203mysqltest_db1.t1 check warning 1 client is using or hasn't closed the table properly 204mysqltest_db1.t1 check error Size of indexfile is: 1024 Should be: 2048 205mysqltest_db1.t1 check warning Size of datafile is: 14 Should be: 7 206mysqltest_db1.t1 check error Corrupt 207repair table mysqltest_db1.t1; 208Table Op Msg_type Msg_text 209mysqltest_db1.t1 repair warning Number of rows changed from 1 to 2 210mysqltest_db1.t1 repair status OK 211# Clean-up. 212disconnect con1; 213connection default; 214drop database mysqltest_db1; 215drop user mysqltest_u1@localhost; 216create user foo1 identified by password '11111111111111111111111111111111111111111'; 217create user foo2 identified by password '2222222222222222'; 218create user foo3 identified via mysql_native_password using '11111111111111111111111111111111111111111'; 219create user foo4 identified via mysql_old_password using '2222222222222222'; 220grant select on test.* to foo5 identified by password '11111111111111111111111111111111111111111'; 221grant select on test.* to foo6 identified by password '2222222222222222'; 222grant select on test.* to foo7 identified via mysql_native_password using '11111111111111111111111111111111111111111'; 223grant select on test.* to foo8 identified via mysql_old_password using '2222222222222222'; 224select user,password,plugin,authentication_string from mysql.user where user like 'foo%'; 225User Password plugin authentication_string 226foo1 11111111111111111111111111111111111111111 mysql_native_password 11111111111111111111111111111111111111111 227foo2 2222222222222222 mysql_old_password 2222222222222222 228foo3 11111111111111111111111111111111111111111 mysql_native_password 11111111111111111111111111111111111111111 229foo4 2222222222222222 mysql_old_password 2222222222222222 230foo5 11111111111111111111111111111111111111111 mysql_native_password 11111111111111111111111111111111111111111 231foo6 2222222222222222 mysql_old_password 2222222222222222 232foo7 11111111111111111111111111111111111111111 mysql_native_password 11111111111111111111111111111111111111111 233foo8 2222222222222222 mysql_old_password 2222222222222222 234drop user foo1; 235drop user foo2; 236drop user foo3; 237drop user foo4; 238drop user foo5; 239drop user foo6; 240drop user foo7; 241drop user foo8; 242create user foo1 identified via mysql_native_password using '00'; 243ERROR HY000: Password hash should be a 41-digit hexadecimal number 244create user foo2 identified via mysql_native_password using '2222222222222222'; 245ERROR HY000: Password hash should be a 41-digit hexadecimal number 246create user foo3 identified via mysql_old_password using '00'; 247ERROR HY000: Password hash should be a 16-digit hexadecimal number 248create user foo4 identified via mysql_old_password using '11111111111111111111111111111111111111111'; 249ERROR HY000: Password hash should be a 16-digit hexadecimal number 250set GLOBAL sql_mode=default; 251End of 10.1 tests 252