MySQL Bugs: #5329: GRANT ALL doesn't work after upgrading from 5.0.0 >=

Bug #5329	GRANT ALL doesn't work after upgrading from 5.0.0 >=
Submitted:	31 Aug 2004 23:02	Modified:	11 Sep 2004 16:18
Reporter:	Shuichi Tamagawa	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.1-alpha-log	OS:	Linux (SuSE Linux 9.0)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
After upgrading from previous version to 5.0.1-alpha, GRANT ALL returns "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)".

mysql> grant all on *.* to 'user1'@'localhost';
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

The command works in 5.0.0, 4.1.4 or earlier. It also work if I install 5.0.1 privilege table from scratch by using mysql_install_db. So it should also work after upgrading.

How to repeat:
Run mysql_install_db in 4.1.4(or 5.0.0)
Login in to mysql database

mysql> grant all on *.* to 'user1'@'localhost';
Query OK, 0 rows affected (0.12 sec)

Upgrade to 5.0.1
Run mysql_fix_privilege_tables
Restart server
Login in to mysql database

mysql> grant all on *.* to 'user1'@'localhost';
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Suggested fix:
Something in mysql_fix_privilege_table?

The reason this happens is that after you run the
mysql_fix_privilege_tables, the grant tables contain
the view-related privileges (CREATE VIEW and SHOW
VIEW), but do not set those privileges to 'Y' for any
users. As a result, no users (including root) possess
the privilege, so no users can grant it.  GRANT ALL
requires that you possess all privileges to be granted,
so it fails.

To fix: manually update the grant tables with UPDATE
to set the view columns to 'Y'.

ChangeSet 
  1.1755 04/09/11 18:46:06 bell@sanja.is.com.ua +1 -0 
  fixed privilege system upgrade script, to allow VIEW privileges for used who have 
CREATE privilege (BUG#5329)

Thank you for bugreport! mysql_fix_privileges_tales.sql script is fixed and will be present 
in next server release