On MySQL 5.0.2 under Fedora Core 5, I have a situation where I need a user (B) to have privileges to a couple of tables on a database (A) that otherwise belongs to another user (A). Both users need access from another host.
So userA@% owns databaseA with a grant all privs on databaseA.*
On the web applicaiton in question, there is a subsystem that automatically performs sql commands as "userA". So I used root user to provide the appropriate GRANT:
GRANT GRANT OPTION ON databaseA.* TO userA IDENTIFIED BY 'somepass';
But this is still failing
GRANT ALL ON userA.tableForB TO userB IDENTIFIED BY 'userA'
Access denied for user 'userA'@'%' to database 'databaseA'
Another team member pointed out that maybe userA didn't have rights to create new users, so I tried creating it first (this is where things get screwy):
mysql> CREATE USER 'userB'@'%' identified by 'somepass';
Table 'mysql.procs_priv' doesn't exist.
After all this I decided to try and fix this problem and do the same as root:
mysql> GRANT ALL ON userA.tableForB TO userB IDENTIFIED BY 'userA';
ERROR 2013 (HY000): Lost conection to MySQL server during query
Now every time I try a grant I get a lost connection. Regular queries still work fine.
I tried running "fix_system_tables" but that hasn't helped.
I saw this
http://bugs.mysql.com/bug.php?id=9496 but what I need is a way to fix the grants of an existing database instead of creating a new one. What I'd like to know is:
1) Is there a definition of the procs_priv table I can add so the create user works?
2) Failing all this, how do I fix this mysql installation? Luckily I'm running under Linux on my workstation so I was able to catch it before it hit a server down the line and caused QA downtime (it didn't happen to the Windows folks).
Thanks!