1

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.

2 Answers 2

2

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.

3
  • 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, 2012 at 13:56
  • But this is worth mentioning, of course ;) so +1
    – poncha
    Jun 28, 2012 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, 2012 at 15:20
1

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.

1
  • +1 for finding your own answer !!! Jun 28, 2012 at 16:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .