mirror of
https://github.com/MariaDB/server.git
synced 2025-08-09 12:01:34 +02:00
e3d9369774
let's always disconnect a user connection before dropping the said user. MariaDB is traditionally very tolerant to active connections of the dropped user, which isn't the case for most other databases. Let's avoid unintentionally spreading incompatible behavior and disconnect before drop. Except in cases when the test specifically tests such a behavior.
478 lines
19 KiB
Text
478 lines
19 KiB
Text
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;
|
|
disconnect conn_1;
|
|
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;
|
|
use test;
|
|
#
|
|
# 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 as 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
|
|
tmp
|
|
show full tables;
|
|
Tables_in_db Table_type
|
|
tmp TEMPORARY TABLE
|
|
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
|
|
tmp MyISAM 10 Fixed 0 0 X X X X X X X X utf8mb4_uca1400_ai_ci NULL X Y
|
|
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 IS_SYSTEM_TIME_PERIOD_START IS_SYSTEM_TIME_PERIOD_END
|
|
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 as User,host as 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 as User,select_priv as 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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_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
|
|
#
|
|
# MDEV-18151: Skipped error returning for GRANT/SET PASSWORD
|
|
#
|
|
CREATE USER foo@localhost;
|
|
GRANT FILE ON *.* TO foo@localhost IDENTIFIED VIA not_installed_plugin;
|
|
ERROR HY000: Plugin 'not_installed_plugin' is not loaded
|
|
DROP USER foo@localhost;
|
|
CREATE USER foo@localhost IDENTIFIED VIA not_installed_plugin;
|
|
ERROR HY000: Plugin 'not_installed_plugin' is not loaded
|
|
# End of 10.5 tests
|