diff options
Diffstat (limited to 'mysql-test/main/grant5.result')
| -rw-r--r-- | mysql-test/main/grant5.result | 465 |
1 files changed, 465 insertions, 0 deletions
diff --git a/mysql-test/main/grant5.result b/mysql-test/main/grant5.result new file mode 100644 index 00000000..76a5f537 --- /dev/null +++ b/mysql-test/main/grant5.result @@ -0,0 +1,465 @@ +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 +# |