Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I've encountered an issue after upgrade of MySQL server. Probably, upgrade procedure went south. The result was that none of the users had ALL PRIVILEGES grant.

All the administrators had GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, TRIGGER ON and GRANT OPTION

It looks very much like ALL PRIVILEGES translated into individual privileges, but obviousely something is missing. After reviewing the privileges listed, i've found that all the users were missing EVENT privilege.

mysql> SELECT * FROM mysql.user WHERE Event_priv='Y'\G
Empty set (0.01 sec)

Now, this privilege was added in MySQL 5.16 and is defined as enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', so probably UPDATE on existing users have failed (or is missing in the upgrade scripts).

After realizing what was wrong, i started to look for a solution here (I mean, if NO user has this privilege, how do i GRANT it?), and did not find a problem exactly like this one.

share|improve this question
add comment

2 Answers

You should be aware of the --skip-grant-tables MySQL startup option - it allows for bypassing the permission checks for recovery purposes. A daemon started with this option would allow a root login without a password with ALL PRIVILEGES granted. You can change anything you like from there on.

Note: as running with a passwordless root account is likely to present a security risk, you should make sure to specify --skip-networking as well.

share|improve this answer
    
This would require restarting a database server though (twice - second time to get back to normal state), which is time consuming, and means downtime –  poncha Jun 28 '12 at 13:56
    
But this is worth mentioning, of course ;) so +1 –  poncha Jun 28 '12 at 13:57
1  
@poncha - If it would be just for the privileges, you would need to restart only once - a FLUSH PRIVILEGES would re-read the mysql.users table and put the privileges back in effect. Of course, since you're starting with skip-networking and do not want any succeeding connection attempts in the meantime anyway (they would get rejected due to failed authentication), this would actually mean restarting twice. On the other hand, it is a generic approach working with any kind of privilege / authentication problems. –  the-wabbit Jun 28 '12 at 15:20
add comment
up vote 1 down vote accepted

So i thought i'd ask, but then i thought "wait a second, what if..." and attempted to UPDATE the users table myself... and VOILA! MySQL actually LET ME do it!

So here it is:

Question: How to recover a missing EVENT privilege?

Answer:

 mysql> UPDATE mysql.user SET Event_priv='Y' WHERE User='root';
 Query OK, 1 row affected (0.00 sec)

 mysql> FLUSH PRIVILEGES;
 Query OK, 0 rows affected (0.14 sec)

Then login again to MySQL and that's it - SHOW GRANTS shows root having ALL PRIVILEGES again.


So (and this is my guess, correct me if i am wrong), as long as you still have UPDATE privilege that affects mysql privilege tables, you can pretty much grant any privilege to yourself or others by manually).

I'm sure this is conceptually OK (since i do have UPDATE...), but as a side note i'd say this is kinda weird that i can alter my own privileges to a larger set.

share|improve this answer
    
+1 for finding your own answer !!! –  RolandoMySQLDBA Jun 28 '12 at 16:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.