MySQL Bugs: #7750: strange grant behavior

Bug #7750	strange grant behavior
Submitted:	8 Jan 2005 23:37	Modified:	12 Mar 2005 0:00
Reporter:	Matthew Lord	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.8	OS:	Any (all)
Assigned to:	Antony Curtis	CPU Architecture:	Any

Description:
After an upgrade to 4.1.8 grant statements were failing.  After doing some testing
I noticed that the grant statement succeeds when logged into mysql as the same
root@localhost account but connected over tcp/ip.  So this appears to have
something to do with connecting over the unix domain socket.

The problem still persisted after running mysql_create_db and then importing the 
data from the original tables.

The problem was reported on solaris 9 sparc but it also manifested on x86 linux.

How to repeat:
start 4.1.8 with the attached privilege tables

connect with mysql -u root -p
connect with mysql -u root -p -h 127.0.0.1

try grant all on *.* to 'user'@'%' identified by 'password';

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

I have examined the data and have come to the conclusion that the server is actually performing as designed.

In order to grant privileges, the grantor must themselves possess the privileges that they want to bestow upon the grantee in addition to the grant option.
In this case, the root account does not have all privileges,

T@10   : | | | >check_access
T@10   : | | | | enter: db: ''  want_access: 2097151  master_access: 2031615
T@10   : | | | | >net_printf

There grantor wanted to bestow ALL privileges upon the grantee but was prevented because there is one bit missing and this is indicated by the dump of the user table.

The following command should fix it:

UPDATE mysql.user SET Create_tmp_table_priv='Y' WHERE User='root';