MySQL Bugs: #63370: LOCK on P_S tables blocks User Management

Bug #63370	LOCK on P_S tables blocks User Management
Submitted:	21 Nov 2011 20:50	Modified:	22 Nov 2011 7:51
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.5.18	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
I am not sure about the category.  It could be 'P_S' or 'privileges'.

Clients may perform DML operations on certain P_S tables. This results in the problem that one client may destroy data fro another client.  I checked if one client with sufficient privileges (in case root@localhost) could LOCK TABLES in P_S in order to protect the data from other clients.

I came across this issue.

How to repeat:
CREATE USER pl@localhost;
LOCK TABLES `performance_schema`.`setup_consumers` READ;
GRANT UPDATE ON `performance_schema`.`setup_consumers` TO pl@localhost;
/*
Error Code: 1100
Table 'user' was not locked with LOCK TABLES
*/
UNLOCK TABLES;
GRANT UPDATE ON `performance_schema`.`setup_consumers` TO pl@localhost; -- success

Suggested fix:
?????
I am mystified!
?????

I could have missed what the bug is.

If you lock tables, you must lock all the tables that you plan to update.  I guess GRANT statement updates `mysql`.`user` table so you'd need to write lock that table too ?

Thanks for the explanation. I did not realize that that also applies when using GRANT syntax.