Because of having some problems, I decided to re-create all users except for root@localhost
. This works fine, but the newly created user has no right to do anything. What I want is to simply give all rights to root at some local IP. I (as root@localhost
) tried
CREATE USER 'root'@'10.0.3.210';
GRANT ALL ON *.* TO 'root'@'10.0.3.210';
The first command works, the second one fails with the message
ERROR 1045 (28000): Access denied for user 'root'@'localhost'
I don't get why root@localhost
can't do everything, I'm sure I didn't mess with its privileges. From
SHOW GRANTS FOR 'root'@'localhost'
I get
Grants for root@localhost
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO 'root'@'localhost' WITH GRANT OPTION
GRANT ALL PRIVILEGES ON `%`.* TO 'root'@'localhost' WITH GRANT OPTION
whatever this means. Am I missing a needed privilege? Can it be fixed?
I'm working with mysql Ver 14.14 Distrib 5.1.61, for debian-linux-gnu (x86_64).
Best Answer
Oh my goodness, I think the problem stems from mixing the mysql schema of different mysql versions. First of all, run this query:
For MySQL 8.0, you get 51 columns
51 rows in set (0.00 sec)
For MySQL 5.6, you get 43 columns
For MySQL 5.5, you get 42 columns
For MySQL 5.1, you get 39 columns
For MySQL 5.0, you get 37 columns
For MySQL 4.x, you get 31 columns
Picture this scenario:
The user permissions start getting shifted. Whenever you run
SHOW GRANTS;
, it is hardwired to expect columns in specfic places in mysql.user (given the fact that mysql.user is a MyISAM and its ROW_FORMAT is Dynamic (Default)). It is very easy to see a user suddenly lose permissions when you reload a MySQL 5.0 version of mysql.user into a MySQL 5.1 instance.In the future, should you ever decide to reload users into MySQL, try to dump the users to a text file using pt-show-grants rather than mysqldump.
I actually wrote my own version of pt-show-grants as follows:
This will produce all user grants as SQL, which is far more portable than standard mysql techniques. Simply reload the SQL script and the GRANTS are executed. Of course, such reloading is only forward compatible, never backward.