SHOW GRANTS FOR root@invalid_host; ERROR 42000: There is no such grant defined for user 'root' on host 'invalid_host' create user test; create user foo; create role foo; grant foo to test; connect conn_1, localhost, test,,; set role foo; show grants for test; Grants for test@% GRANT `foo` TO `test`@`%` GRANT USAGE ON *.* TO `test`@`%` show grants for foo; Grants for foo GRANT USAGE ON *.* TO `foo` show grants for foo@'%'; ERROR 42000: Access denied for user 'test'@'%' to database 'mysql' connection default; drop user test, foo; drop role foo; CREATE TABLE t1 (a INT); LOCK TABLE t1 WRITE; REVOKE EXECUTE ON PROCEDURE sp FROM u; ERROR HY000: Table 'procs_priv' was not locked with LOCK TABLES REVOKE PROCESS ON *.* FROM u; ERROR HY000: Table 'db' was not locked with LOCK TABLES DROP TABLE t1; create database mysqltest1; use mysqltest1; create table t1(id int); insert t1 values(2); create user u1@localhost; grant select on mysqltest1.t1 to u1@localhost; grant update on mysqltest1.* to u1@localhost; connect u1, localhost, u1; update mysqltest1.t1 set id=1 where id=2; connection default; disconnect u1; drop user u1@localhost; drop database mysqltest1; # # MDEV-22313: Neither SHOW CREATE USER nor SHOW GRANTS prints a user's default role # CREATE ROLE test_role; CREATE USER test_user; GRANT test_role TO test_user; SET DEFAULT ROLE test_role FOR test_user; SHOW GRANTS FOR test_user; Grants for test_user@% GRANT `test_role` TO `test_user`@`%` GRANT USAGE ON *.* TO `test_user`@`%` SET DEFAULT ROLE `test_role` FOR `test_user`@`%` SET DEFAULT ROLE NONE for test_user; SHOW GRANTS FOR test_user; Grants for test_user@% GRANT `test_role` TO `test_user`@`%` GRANT USAGE ON *.* TO `test_user`@`%` connect test_user, localhost, test_user; SET ROLE test_role; SET DEFAULT ROLE test_role; SHOW GRANTS; Grants for test_user@% GRANT `test_role` TO `test_user`@`%` GRANT USAGE ON *.* TO `test_user`@`%` GRANT USAGE ON *.* TO `test_role` SET DEFAULT ROLE `test_role` FOR `test_user`@`%` SET DEFAULT ROLE NONE; SHOW GRANTS; Grants for test_user@% GRANT `test_role` TO `test_user`@`%` GRANT USAGE ON *.* TO `test_user`@`%` GRANT USAGE ON *.* TO `test_role` disconnect test_user; connection default; DROP USER test_user; DROP ROLE test_role; # # MDEV-20076: SHOW GRANTS does not quote role names properly # create role 'role1'; create role 'fetch'; create role 'role-1'; create role 'rock\'n\'roll'; create user 'user1'@'localhost'; create user 'fetch'@'localhost'; create user 'user-1'@'localhost'; create user 'O\'Brien'@'localhost'; grant select on mysql.user to role1; grant select on mysql.user to 'fetch'; grant select on mysql.user to 'role-1'; grant select on mysql.user to 'rock\'n\'roll'; GRANT 'role1' TO 'user1'@'localhost'; GRANT 'fetch' TO 'fetch'@'localhost'; GRANT 'role-1' TO 'user-1'@'localhost'; GRANT 'rock\'n\'roll' TO 'O\'Brien'@'localhost'; show grants for 'role1'; Grants for role1 GRANT USAGE ON *.* TO `role1` GRANT SELECT ON `mysql`.`user` TO `role1` show grants for 'fetch'; Grants for fetch GRANT USAGE ON *.* TO `fetch` GRANT SELECT ON `mysql`.`user` TO `fetch` show grants for 'role-1'; Grants for role-1 GRANT USAGE ON *.* TO `role-1` GRANT SELECT ON `mysql`.`user` TO `role-1` show grants for 'rock\'n\'roll'; Grants for rock'n'roll GRANT USAGE ON *.* TO `rock'n'roll` GRANT SELECT ON `mysql`.`user` TO `rock'n'roll` show grants for 'user1'@'localhost'; Grants for user1@localhost GRANT `role1` TO `user1`@`localhost` GRANT USAGE ON *.* TO `user1`@`localhost` show grants for 'fetch'@'localhost'; Grants for fetch@localhost GRANT `fetch` TO `fetch`@`localhost` GRANT USAGE ON *.* TO `fetch`@`localhost` show grants for 'user-1'@'localhost'; Grants for user-1@localhost GRANT `role-1` TO `user-1`@`localhost` GRANT USAGE ON *.* TO `user-1`@`localhost` show grants for 'O\'Brien'@'localhost'; Grants for O'Brien@localhost GRANT `rock'n'roll` TO `O'Brien`@`localhost` GRANT USAGE ON *.* TO `O'Brien`@`localhost` set @save_sql_quote_show_create= @@sql_quote_show_create; set @@sql_quote_show_create= OFF; show grants for 'role1'; Grants for role1 GRANT USAGE ON *.* TO role1 GRANT SELECT ON `mysql`.`user` TO role1 show grants for 'fetch'; Grants for fetch GRANT USAGE ON *.* TO `fetch` GRANT SELECT ON `mysql`.`user` TO `fetch` show grants for 'role-1'; Grants for role-1 GRANT USAGE ON *.* TO `role-1` GRANT SELECT ON `mysql`.`user` TO `role-1` show grants for 'rock\'n\'roll'; Grants for rock'n'roll GRANT USAGE ON *.* TO `rock'n'roll` GRANT SELECT ON `mysql`.`user` TO `rock'n'roll` show grants for 'user1'@'localhost'; Grants for user1@localhost GRANT role1 TO user1@localhost GRANT USAGE ON *.* TO user1@localhost show grants for 'fetch'@'localhost'; Grants for fetch@localhost GRANT `fetch` TO `fetch`@localhost GRANT USAGE ON *.* TO `fetch`@localhost show grants for 'user-1'@'localhost'; Grants for user-1@localhost GRANT `role-1` TO `user-1`@localhost GRANT USAGE ON *.* TO `user-1`@localhost show grants for 'O\'Brien'@'localhost'; Grants for O'Brien@localhost GRANT `rock'n'roll` TO `O'Brien`@localhost GRANT USAGE ON *.* TO `O'Brien`@localhost set @@sql_quote_show_create= @save_sql_quote_show_create; drop role 'role1'; drop role 'fetch'; drop role 'role-1'; drop role 'rock\'n\'roll'; drop user 'user1'@'localhost'; drop user 'fetch'@'localhost'; drop user 'user-1'@'localhost'; drop user 'O\'Brien'@'localhost'; # # MDEV-26080 SHOW GRANTS does not quote role names properly for DEFAULT ROLE # CREATE USER 'test-user'; CREATE ROLE `r``o'l"e`; select user from mysql.user where is_role='Y'; User r`o'l"e GRANT `r``o'l"e` TO 'test-user'; SET DEFAULT ROLE `r``o'l"e` FOR 'test-user'; SHOW GRANTS FOR 'test-user'; Grants for test-user@% GRANT `r``o'l"e` TO `test-user`@`%` GRANT USAGE ON *.* TO `test-user`@`%` SET DEFAULT ROLE `r``o'l"e` FOR `test-user`@`%` DROP ROLE `r``o'l"e`; DROP USER 'test-user'; # # MDEV-28548: ER_TABLEACCESS_DENIED_ERROR is missing information about DB # create database db1; create user foo@localhost; grant create on db1.* to foo@localhost; connect con1,localhost,foo,,db1; create table t(t int); show columns in t; ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table `db1`.`t` show columns in db1.t; ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table `db1`.`t` create view t_v as select * from t; ERROR 42000: CREATE VIEW command denied to user 'foo'@'localhost' for table `db1`.`t_v` show create view t_v; ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table `db1`.`t_v` create table t2(id int primary key, b int); create table t3(a int, b int, CONSTRAINT `fk_db2_db1_t1` FOREIGN KEY (a) REFERENCES `db1 `.t1 (a) ON DELETE CASCADE ON UPDATE RESTRICT); ERROR 42000: Incorrect database name 'db1 ' create table t3(a int, b int, CONSTRAINT `fk_db2_db3_t1` FOREIGN KEY (a) REFERENCES db3.t1 (a) ON DELETE CASCADE ON UPDATE RESTRICT); ERROR 42000: REFERENCES command denied to user 'foo'@'localhost' for table `db3`.`t1` create table t1(a int, b int, CONSTRAINT `fk_db2_db3_t1` FOREIGN KEY (a) REFERENCES t2 (id) ON DELETE CASCADE ON UPDATE RESTRICT); ERROR 42000: REFERENCES command denied to user 'foo'@'localhost' for table `db1`.`t2` connection default; disconnect con1; grant create view, select on db1.* to foo@localhost; connect con1,localhost,foo,,db1; create view t_v as select * from t; show grants; Grants for foo@localhost GRANT USAGE ON *.* TO `foo`@`localhost` GRANT SELECT, CREATE, CREATE VIEW ON `db1`.* TO `foo`@`localhost` show create view t_v; ERROR 42000: SHOW VIEW command denied to user 'foo'@'localhost' for table `db1`.`t_v` connection default; disconnect con1; grant show view on db1.* to foo@localhost; connect con1,localhost,foo,,db1; show grants; Grants for foo@localhost GRANT USAGE ON *.* TO `foo`@`localhost` GRANT SELECT, CREATE, CREATE VIEW, SHOW VIEW ON `db1`.* TO `foo`@`localhost` show create view t_v; View Create View character_set_client collation_connection t_v CREATE ALGORITHM=UNDEFINED DEFINER=`foo`@`localhost` SQL SECURITY DEFINER VIEW `t_v` AS select `t`.`t` AS `t` from `t` latin1 latin1_swedish_ci connection default; disconnect con1; drop database db1; drop user foo@localhost; # # MDEV-28455: CREATE TEMPORARY TABLES privilege # is insufficient for SHOW COLUMNS # create database db; create user foo@localhost; create user bar@localhost; create user buz@localhost; grant create temporary tables on db.* to foo@localhost; grant create temporary tables on db.* to bar@localhost; connect con1,localhost,foo,,db; create temporary table tmp (a int, key(a)); show tables; Tables_in_db show full tables; Tables_in_db Table_type show table status; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary show index in tmp; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored tmp 1 a 1 a A NULL NULL NULL YES BTREE NO show columns in tmp; Field Type Null Key Default Extra a int(11) YES MUL NULL show full columns in tmp; Field Type Collation Null Key Default Extra Privileges Comment a int(11) NULL YES MUL NULL select,insert,update,references # we don't expect to show temporary tables in information_schema.columns select * from information_schema.columns where table_schema='db'; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT IS_GENERATED GENERATION_EXPRESSION disconnect con1; connect con1,localhost,bar,,db; show full columns in tmp; ERROR 42000: SELECT command denied to user 'bar'@'localhost' for table `db`.`tmp` disconnect con1; connection default; grant select on db.* to bar@localhost; connect con1,localhost,bar,,db; show grants for current_user; Grants for bar@localhost GRANT USAGE ON *.* TO `bar`@`localhost` GRANT SELECT, CREATE TEMPORARY TABLES ON `db`.* TO `bar`@`localhost` show full columns in tmp; ERROR 42S02: Table 'db.tmp' doesn't exist disconnect con1; connect con1,localhost,buz,,; show columns in db.tmp; ERROR 42000: SELECT command denied to user 'buz'@'localhost' for table `db`.`tmp` disconnect con1; connection default; drop database db; drop user foo@localhost; drop user bar@localhost; drop user buz@localhost; CREATE USER foo; CREATE DATABASE db; CREATE TABLE db.test_getcolpriv(col1 INT, col2 INT); GRANT SELECT (col1,col2) ON db.test_getcolpriv TO foo; GRANT INSERT (col1) ON db.test_getcolpriv TO foo; SHOW GRANTS FOR foo; Grants for foo@% GRANT USAGE ON *.* TO `foo`@`%` GRANT SELECT (`col2`, `col1`), INSERT (`col1`) ON `db`.`test_getcolpriv` TO `foo`@`%` REVOKE SELECT (col1,col2) ON db.test_getcolpriv FROM foo; SHOW GRANTS FOR foo; Grants for foo@% GRANT USAGE ON *.* TO `foo`@`%` GRANT INSERT (`col1`) ON `db`.`test_getcolpriv` TO `foo`@`%` REVOKE INSERT (col1) ON db.test_getcolpriv FROM foo; SHOW GRANTS FOR foo; Grants for foo@% GRANT USAGE ON *.* TO `foo`@`%` FLUSH PRIVILEGES; SHOW GRANTS FOR foo; Grants for foo@% GRANT USAGE ON *.* TO `foo`@`%` DROP USER foo; DROP DATABASE db; # End of 10.3 tests create user u1@h identified with 'mysql_native_password' using 'pwd'; ERROR HY000: Password hash should be a 41-digit hexadecimal number create user u1@h identified with 'mysql_native_password' using password('pwd'); create user u2@h identified with 'mysql_native_password' using '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD'; create user u3@h identified with 'mysql_native_password'; set password for u3@h = 'pwd'; ERROR HY000: Password hash should be a 41-digit hexadecimal number set password for u3@h = password('pwd'); create user u4@h identified with 'mysql_native_password'; set password for u4@h = '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD'; create user u5@h identified with 'mysql_old_password' using 'pwd'; ERROR HY000: Password hash should be a 16-digit hexadecimal number create user u5@h identified with 'mysql_old_password' using password('pwd'); create user u6@h identified with 'mysql_old_password' using '78a302dd267f6044'; create user u7@h identified with 'mysql_old_password'; set password for u7@h = 'pwd'; ERROR HY000: Password hash should be a 41-digit hexadecimal number set password for u7@h = old_password('pwd'); create user u8@h identified with 'mysql_old_password'; set password for u8@h = '78a302dd267f6044'; select user,host,plugin,authentication_string from mysql.user where host='h'; User Host plugin authentication_string u1 h mysql_native_password *975B2CD4FF9AE554FE8AD33168FBFC326D2021DD u2 h mysql_native_password *975B2CD4FF9AE554FE8AD33168FBFC326D2021DD u3 h mysql_native_password *975B2CD4FF9AE554FE8AD33168FBFC326D2021DD u4 h mysql_native_password *975B2CD4FF9AE554FE8AD33168FBFC326D2021DD u5 h mysql_old_password 78a302dd267f6044 u6 h mysql_old_password 78a302dd267f6044 u7 h mysql_old_password 78a302dd267f6044 u8 h mysql_old_password 78a302dd267f6044 update mysql.global_priv set priv=json_set(priv, '$.authentication_string', 'bad') where user='u1'; update mysql.global_priv set priv=json_set(priv, '$.authentication_string', 'bad') where user='u5'; update mysql.global_priv set priv=json_set(priv, '$.plugin', 'nonexistent') where user='u8'; flush privileges; show create user u1@h; CREATE USER for u1@h CREATE USER `u1`@`h` IDENTIFIED BY PASSWORD 'bad' show create user u2@h; CREATE USER for u2@h CREATE USER `u2`@`h` IDENTIFIED BY PASSWORD '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD' show create user u3@h; CREATE USER for u3@h CREATE USER `u3`@`h` IDENTIFIED BY PASSWORD '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD' show create user u4@h; CREATE USER for u4@h CREATE USER `u4`@`h` IDENTIFIED BY PASSWORD '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD' show create user u5@h; CREATE USER for u5@h CREATE USER `u5`@`h` IDENTIFIED BY PASSWORD 'bad' show create user u6@h; CREATE USER for u6@h CREATE USER `u6`@`h` IDENTIFIED BY PASSWORD '78a302dd267f6044' show create user u7@h; CREATE USER for u7@h CREATE USER `u7`@`h` IDENTIFIED BY PASSWORD '78a302dd267f6044' show create user u8@h; CREATE USER for u8@h CREATE USER `u8`@`h` IDENTIFIED VIA nonexistent USING '78a302dd267f6044' grant select on *.* to u1@h; grant select on *.* to u2@h; grant select on *.* to u3@h; grant select on *.* to u4@h; grant select on *.* to u5@h; grant select on *.* to u6@h; grant select on *.* to u7@h; grant select on *.* to u8@h; select user,select_priv,plugin,authentication_string from mysql.user where user like 'u_'; User Select_priv plugin authentication_string u1 Y mysql_native_password bad u2 Y mysql_native_password *975B2CD4FF9AE554FE8AD33168FBFC326D2021DD u3 Y mysql_native_password *975B2CD4FF9AE554FE8AD33168FBFC326D2021DD u4 Y mysql_native_password *975B2CD4FF9AE554FE8AD33168FBFC326D2021DD u5 Y mysql_old_password bad u6 Y mysql_old_password 78a302dd267f6044 u7 Y mysql_old_password 78a302dd267f6044 u8 Y nonexistent 78a302dd267f6044 drop user u1@h, u2@h, u3@h, u4@h, u5@h, u6@h, u7@h, u8@h; create database mysqltest_1; create user twg@'%' identified by 'test'; create table mysqltest_1.t1(id int); grant create, drop on `mysqltest_1%`.* to twg@'%'; grant all privileges on `mysqltest_1`.* to twg@'%'; connect conn1,localhost,twg,test,mysqltest_1; insert into t1 values(1); disconnect conn1; connection default; revoke all privileges, grant option from twg@'%'; grant create, drop on `mysqlt%`.* to twg@'%'; grant all privileges on `mysqlt%1`.* to twg@'%'; connect conn1,localhost,twg,test,mysqltest_1; insert into t1 values(1); disconnect conn1; connection default; revoke all privileges, grant option from twg@'%'; grant create, drop on `mysqlt%`.* to twg@'%'; grant all privileges on `%mysqltest_1`.* to twg@'%'; connect conn1,localhost,twg,test,mysqltest_1; insert into t1 values(1); disconnect conn1; connection default; drop database mysqltest_1; drop user twg@'%'; insert mysql.tables_priv (host,db,user,table_name,grantor,table_priv) values ('localhost','','otto','t1','root@localhost','select'); flush privileges; delete from mysql.tables_priv where db=''; create database db; create table db.t1 (a int); insert into db.t1 values (1); create user foo; grant delete on db.* to foo; connect con1,localhost,foo,,; show create table db.t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci delete from db.t1 returning *; ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'a' in table 't1' disconnect con1; connection default; drop database db; drop user foo; call mtr.add_suppression('mysql.host table is damaged'); create table mysql.host (c1 int); insert mysql.host values (1); flush privileges; ERROR HY000: Fatal error: mysql.host table is damaged or in unsupported 3.20 format drop table mysql.host; # # MDEV-30826 Invalid data on mysql.host segfaults the server after an upgrade to 10.4 # create table mysql.host (host char(60) binary default '' not null, db char(64) binary default '' not null, select_priv enum('n','y') collate utf8_general_ci default 'n' not null, insert_priv enum('n','y') collate utf8_general_ci default 'n' not null, update_priv enum('n','y') collate utf8_general_ci default 'n' not null, delete_priv enum('n','y') collate utf8_general_ci default 'n' not null, create_priv enum('n','y') collate utf8_general_ci default 'n' not null, drop_priv enum('n','y') collate utf8_general_ci default 'n' not null, grant_priv enum('n','y') collate utf8_general_ci default 'n' not null, references_priv enum('n','y') collate utf8_general_ci default 'n' not null, index_priv enum('n','y') collate utf8_general_ci default 'n' not null, alter_priv enum('n','y') collate utf8_general_ci default 'n' not null, create_tmp_table_priv enum('n','y') collate utf8_general_ci default 'n' not null, lock_tables_priv enum('n','y') collate utf8_general_ci default 'n' not null, create_view_priv enum('n','y') collate utf8_general_ci default 'n' not null, show_view_priv enum('n','y') collate utf8_general_ci default 'n' not null, create_routine_priv enum('n','y') collate utf8_general_ci default 'n' not null, alter_routine_priv enum('n','y') collate utf8_general_ci default 'n' not null, execute_priv enum('n','y') collate utf8_general_ci default 'n' not null, trigger_priv enum('n','y') collate utf8_general_ci default 'n' not null, primary key /*host*/ (host,db)) engine=myisam character set utf8 collate utf8_bin comment='host privileges; merged with database privileges'; insert mysql.host values('10.5.0.0/255.255.0.0','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','N'); flush privileges; drop table mysql.host; # # End of 10.4 tests #