FLUSH TABLES;
DROP DATABASE IF EXISTS bup_bug42756;
#
CREATE DATABASE bup_bug42756;
CREATE TABLE bup_bug42756.t1 (c1 INT);
CREATE USER 'bup_bug42756_u1'@'localhost';
GRANT SELECT,INSERT,UPDATE,DELETE ON bup_bug42756.*
TO 'bup_bug42756_u1'@'localhost';
SHOW GRANTS FOR 'bup_bug42756_u1'@'localhost';
Grants for bup_bug42756_u1@localhost
GRANT USAGE ON *.* TO 'bup_bug42756_u1'@'localhost'
GRANT SELECT, INSERT, UPDATE, DELETE ON `bup_bug42756`.* TO 'bup_bug42756_u1'@'localhost'
SELECT * FROM mysql.db WHERE db='bup_bug42756';
Host	localhost
Db	bup_bug42756
User	bup_bug42756_u1
Select_priv	Y
Insert_priv	Y
Update_priv	Y
Delete_priv	Y
Create_priv	N
Drop_priv	N
Grant_priv	N
References_priv	N
Index_priv	N
Alter_priv	N
Create_tmp_table_priv	N
Lock_tables_priv	N
Create_view_priv	N
Show_view_priv	N
Create_routine_priv	N
Alter_routine_priv	N
Execute_priv	N
Event_priv	N
Trigger_priv	N
SELECT * FROM mysql.host WHERE db='bup_bug42756';
#
# connect con1
SELECT CURRENT_USER();
CURRENT_USER()
bup_bug42756_u1@localhost
INSERT INTO t1 VALUES (1), (2), (3), (4);
UPDATE t1 SET c1=7 WHERE c1=2;
DELETE FROM t1 WHERE c1=3;
SELECT * FROM t1;
c1
1
7
4
# disconnect
#
# connection default
# Create an entry in the host table, which does not allow UPDATE.
INSERT INTO mysql.host SET host='localhost', db='bup_bug42756',
Select_priv='Y', Insert_priv='Y', Delete_priv='Y';
# Clear the host column in the db table to make host table effective.
UPDATE mysql.db SET host='' WHERE db='bup_bug42756';
# Force re-read of privilege tables.
FLUSH PRIVILEGES;
# Show grants cannot show privileges taht use the host table.
SHOW GRANTS FOR 'bup_bug42756_u1'@'localhost';
Grants for bup_bug42756_u1@localhost
GRANT USAGE ON *.* TO 'bup_bug42756_u1'@'localhost'
SELECT * FROM mysql.db WHERE db='bup_bug42756';
Host	
Db	bup_bug42756
User	bup_bug42756_u1
Select_priv	Y
Insert_priv	Y
Update_priv	Y
Delete_priv	Y
Create_priv	N
Drop_priv	N
Grant_priv	N
References_priv	N
Index_priv	N
Alter_priv	N
Create_tmp_table_priv	N
Lock_tables_priv	N
Create_view_priv	N
Show_view_priv	N
Create_routine_priv	N
Alter_routine_priv	N
Execute_priv	N
Event_priv	N
Trigger_priv	N
SELECT * FROM mysql.host WHERE db='bup_bug42756';
Host	localhost
Db	bup_bug42756
Select_priv	Y
Insert_priv	Y
Update_priv	N
Delete_priv	Y
Create_priv	N
Drop_priv	N
Grant_priv	N
References_priv	N
Index_priv	N
Alter_priv	N
Create_tmp_table_priv	N
Lock_tables_priv	N
Create_view_priv	N
Show_view_priv	N
Create_routine_priv	N
Alter_routine_priv	N
Execute_priv	N
Trigger_priv	N
#
# re-connect con1
SELECT CURRENT_USER();
CURRENT_USER()
bup_bug42756_u1@localhost
INSERT INTO t1 VALUES (1), (2), (3), (4);
UPDATE t1 SET c1=7 WHERE c1=2;
ERROR 42000: UPDATE command denied to user 'bup_bug42756_u1'@'localhost' for table 't1'
DELETE FROM t1 WHERE c1=3;
SELECT * FROM t1;
c1
1
7
1
4
2
4
# disconnect
#
# connection default
BACKUP DATABASE bup_bug42756 TO 'bup_bug42756.bak';
backup_id
#
DROP DATABASE bup_bug42756;
DROP USER 'bup_bug42756_u1'@'localhost';
DELETE FROM mysql.db WHERE db='bup_bug42756';
DELETE FROM mysql.host WHERE db='bup_bug42756';
SELECT * FROM mysql.db WHERE db='bup_bug42756';
SELECT * FROM mysql.host WHERE db='bup_bug42756';
FLUSH PRIVILEGES;
#
CREATE USER 'bup_bug42756_u1'@'localhost';
RESTORE FROM 'bup_bug42756.bak';
backup_id
#
Warnings:
#	#	The grant 'DELETE ON bup_bug42756.*' for the user 'bup_bug42756_u1'@'' was skipped because the user does not exist.
#	#	The grant 'INSERT ON bup_bug42756.*' for the user 'bup_bug42756_u1'@'' was skipped because the user does not exist.
#	#	The grant 'SELECT ON bup_bug42756.*' for the user 'bup_bug42756_u1'@'' was skipped because the user does not exist.
#	#	The grant 'UPDATE ON bup_bug42756.*' for the user 'bup_bug42756_u1'@'' was skipped because the user does not exist.
SHOW GRANTS FOR 'bup_bug42756_u1'@'localhost';
Grants for bup_bug42756_u1@localhost
GRANT USAGE ON *.* TO 'bup_bug42756_u1'@'localhost'
SELECT * FROM mysql.db WHERE db='bup_bug42756';
SELECT * FROM mysql.host WHERE db='bup_bug42756';
#
# re-connect con1
ERROR 42000: Access denied for user 'bup_bug42756_u1'@'localhost' to database 'bup_bug42756'
#
# connection default
DROP DATABASE bup_bug42756;
DROP USER 'bup_bug42756_u1'@'localhost';
DELETE FROM mysql.db WHERE db='bup_bug42756';
DELETE FROM mysql.host WHERE db='bup_bug42756';
SELECT * FROM mysql.db WHERE db='bup_bug42756';
SELECT * FROM mysql.host WHERE db='bup_bug42756';
FLUSH PRIVILEGES;
#
CREATE USER 'bup_bug42756_u1'@'localhost';
CREATE USER 'bup_bug42756_u1'@'';
RESTORE FROM 'bup_bug42756.bak';
backup_id
#
SHOW GRANTS FOR 'bup_bug42756_u1'@'localhost';
Grants for bup_bug42756_u1@localhost
GRANT USAGE ON *.* TO 'bup_bug42756_u1'@'localhost'
SELECT * FROM mysql.db WHERE db='bup_bug42756';
Host	
Db	bup_bug42756
User	bup_bug42756_u1
Select_priv	Y
Insert_priv	Y
Update_priv	Y
Delete_priv	Y
Create_priv	N
Drop_priv	N
Grant_priv	N
References_priv	N
Index_priv	N
Alter_priv	N
Create_tmp_table_priv	N
Lock_tables_priv	N
Create_view_priv	N
Show_view_priv	N
Create_routine_priv	N
Alter_routine_priv	N
Execute_priv	N
Event_priv	N
Trigger_priv	N
SELECT * FROM mysql.host WHERE db='bup_bug42756';
#
# re-connect con1
ERROR 42000: Access denied for user 'bup_bug42756_u1'@'localhost' to database 'bup_bug42756'
#
# connection default
DROP DATABASE bup_bug42756;
DROP USER 'bup_bug42756_u1'@'localhost';
DROP USER 'bup_bug42756_u1'@'';
DELETE FROM mysql.db WHERE db='bup_bug42756';
DELETE FROM mysql.host WHERE db='bup_bug42756';
FLUSH PRIVILEGES;