1drop database if exists mysqltest_db1; 2create database mysqltest_db1; 3use mysqltest_db1; 4create table t_column_priv_only (a int, b int); 5create table t_select_priv like t_column_priv_only; 6create table t_no_priv like t_column_priv_only; 7grant all privileges on test.* to mysqltest_u1@localhost; 8grant insert (a) on mysqltest_db1.t_column_priv_only to mysqltest_u1@localhost; 9grant select on mysqltest_db1.t_select_priv to mysqltest_u1@localhost; 10** Connect as restricted user mysqltest_u1. 11 12** Test column level privileges only. No SELECT privileges on the table. 13** INSERT INTO ... VALUES ... 14** Attempting to insert values to a table with only column privileges 15** should work. 16insert into mysqltest_db1.t_column_priv_only (a) VALUES (1); 17 18** SHOW COLUMNS 19** Should succeed because we have privileges (any) on at least one of the columns. 20select 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'; 21Field Type Null Key Default Extra 22a int(11) YES NULL 23show columns from mysqltest_db1.t_column_priv_only; 24Field Type Null Key Default Extra 25a int(11) YES NULL 26** SHOW COLUMNS 27** Should fail because there are no privileges on any column combination. 28show columns from mysqltest_db1.t_no_priv; 29ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't_no_priv' 30** However, select from I_S.COLUMNS will succeed but not show anything: 31select 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'; 32Field Type Null Key Default Extra 33 34** CREATE TABLE ... LIKE ... require SELECT privleges and will fail. 35create table test.t_no_priv like mysqltest_db1.column_priv_only; 36ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 'column_priv_only' 37 38** Just to be sure... SELECT also fails. 39select * from mysqltest_db1.t_column_priv_only; 40ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't_column_priv_only' 41 42** SHOW CREATE TABLE ... require any privileges on all columns (the entire table). 43** First we try and fail on a table with only one column privilege. 44show create table mysqltest_db1.t_column_priv_only; 45ERROR 42000: SHOW command denied to user 'mysqltest_u1'@'localhost' for table 't_column_priv_only' 46 47** Now we do the same on a table with SELECT privileges. 48 49** SHOW COLUMNS 50** Success because we got some privileges on the table (SELECT_ACL) 51show columns from mysqltest_db1.t_select_priv; 52Field Type Null Key Default Extra 53a int(11) YES NULL 54b int(11) YES NULL 55 56** CREATE TABLE ... LIKE ... require SELECT privleges and will SUCCEED. 57drop table if exists test.t_duplicated; 58create table test.t_duplicated like mysqltest_db1.t_select_priv; 59drop table test.t_duplicated; 60 61** SHOW CREATE TABLE will succeed because we have a privilege on all columns in the table (table-level privilege). 62show create table mysqltest_db1.t_select_priv; 63Table Create Table 64t_select_priv CREATE TABLE `t_select_priv` ( 65 `a` int(11) DEFAULT NULL, 66 `b` int(11) DEFAULT NULL 67) ENGINE=MyISAM DEFAULT CHARSET=latin1 68 69** SHOW CREATE TABLE will fail if there is no grants at all: 70show create table mysqltest_db1.t_no_priv; 71ERROR 42000: SHOW command denied to user 'mysqltest_u1'@'localhost' for table 't_no_priv' 72 73use mysqltest_db1; 74CREATE TABLE t5 (s1 INT); 75CREATE INDEX i ON t5 (s1); 76CREATE TABLE t6 (s1 INT, s2 INT); 77CREATE VIEW v5 AS SELECT * FROM t5; 78CREATE VIEW v6 AS SELECT * FROM t6; 79CREATE VIEW v2 AS SELECT * FROM t_select_priv; 80CREATE VIEW v3 AS SELECT * FROM t_select_priv; 81CREATE INDEX i ON t6 (s1); 82GRANT UPDATE (s2) ON t6 to mysqltest_u1@localhost; 83GRANT UPDATE (s2) ON v6 to mysqltest_u1@localhost; 84GRANT SHOW VIEW ON v2 to mysqltest_u1@localhost; 85GRANT SHOW VIEW, SELECT ON v3 to mysqltest_u1@localhost; 86use mysqltest_db1; 87** Connect as restricted user mysqltest_u1. 88# The user does not have table level grants on table t5. Hence cannot get 89# information about it from INFORMATION_SCHEMA.STATISTICS 90SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name='t5'; 91TABLE_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 92** SHOW INDEX FROM t5 will fail because we don't have any privileges on any column combination. 93SHOW INDEX FROM t5; 94ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't5' 95** SHOW INDEX FROM t6 will succeed because there exist a privilege on a column combination on t6. 96SHOW INDEX FROM t6; 97Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 98t6 1 i 1 s1 A NULL NULL NULL YES BTREE 99** CHECK TABLE requires any privilege on any column combination and should succeed for t6: 100CHECK TABLE t6; 101Table Op Msg_type Msg_text 102mysqltest_db1.t6 check status OK 103** With no privileges access is naturally denied: 104CHECK TABLE t5; 105ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't5' 106** CHECKSUM TABLE requires SELECT privileges on the table. The following should fail: 107CHECKSUM TABLE t6; 108ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't6' 109** And this should work: 110CHECKSUM TABLE t_select_priv; 111Table Checksum 112mysqltest_db1.t_select_priv 0 113SHOW CREATE VIEW v5; 114ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 'v5' 115SHOW CREATE VIEW v6; 116ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 'v6' 117SHOW CREATE VIEW v2; 118ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 'v2' 119SHOW CREATE VIEW v3; 120View Create View character_set_client collation_connection 121v3 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 122drop database mysqltest_db1; 123drop user mysqltest_u1@localhost; 124# 125# Additional coverage for refactoring which is made as part 126# of fix for bug #27480 "Extend CREATE TEMPORARY TABLES privilege 127# to allow temp table operations". 128# 129# Check that for statements like CHECK/REPAIR and OPTIMIZE TABLE 130# privileges for all tables involved are checked before processing 131# any tables. Doing otherwise, i.e. checking privileges for table 132# right before processing it might result in lost results for tables 133# which were processed by the time when table for which privileges 134# are insufficient are discovered. 135# 136call mtr.add_suppression("Got an error from thread_id=.*ha_myisam.cc:"); 137call mtr.add_suppression("MySQL thread id .*, query id .* localhost.*mysqltest_u1 Checking table"); 138drop database if exists mysqltest_db1; 139create database mysqltest_db1; 140# Create tables which we are going to CHECK/REPAIR. 141create table mysqltest_db1.t1 (a int, key(a)) engine=myisam; 142create table mysqltest_db1.t2 (b int); 143insert into mysqltest_db1.t1 values (1), (2); 144insert into mysqltest_db1.t2 values (1); 145# Create user which will try to do this. 146create user mysqltest_u1@localhost; 147grant insert, select on mysqltest_db1.t1 to mysqltest_u1@localhost; 148# Corrupt t1 by replacing t1.MYI with a corrupt + unclosed one created 149# by doing: 'create table t1 (a int key(a))' 150# head -c1024 t1.MYI > corrupt_t1.MYI 151flush table mysqltest_db1.t1; 152# Switching to connection 'con1'. 153check table mysqltest_db1.t1; 154Table Op Msg_type Msg_text 155mysqltest_db1.t1 check warning 1 client is using or hasn't closed the table properly 156mysqltest_db1.t1 check error Size of indexfile is: 1024 Should be: 2048 157mysqltest_db1.t1 check warning Size of datafile is: 14 Should be: 7 158mysqltest_db1.t1 check error Corrupt 159# The below statement should fail before repairing t1. 160# Otherwise info about such repair will be missing from its result-set. 161repair table mysqltest_db1.t1, mysqltest_db1.t2; 162ERROR 42000: SELECT, INSERT command denied to user 'mysqltest_u1'@'localhost' for table 't2' 163# The same is true for CHECK TABLE statement. 164check table mysqltest_db1.t1, mysqltest_db1.t2; 165ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't2' 166check table mysqltest_db1.t1; 167Table Op Msg_type Msg_text 168mysqltest_db1.t1 check warning Table is marked as crashed 169mysqltest_db1.t1 check warning 1 client is using or hasn't closed the table properly 170mysqltest_db1.t1 check error Size of indexfile is: 1024 Should be: 2048 171mysqltest_db1.t1 check warning Size of datafile is: 14 Should be: 7 172mysqltest_db1.t1 check error Corrupt 173repair table mysqltest_db1.t1; 174Table Op Msg_type Msg_text 175mysqltest_db1.t1 repair warning Number of rows changed from 1 to 2 176mysqltest_db1.t1 repair status OK 177# Clean-up. 178# Switching to connection 'default'. 179drop database mysqltest_db1; 180drop user mysqltest_u1@localhost; 181