Can't access to some database being root, after just creating that database

Question

as root@localhost

mysql> CREATE USER 'aaa'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT USAGE ON * . * TO 'aaa'@'%' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DATABASE IF NOT EXISTS `aaa` ;
Query OK, 1 row affected (0.00 sec)

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

mysql> GRANT ALL PRIVILEGES ON `aaa` . * TO 'aaa'@'%';
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'aaa'

what is going on.. why does it give me that error?

mysql> show grants;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                                                                                                                                                                                                                                                                                                        |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*B67132C4A56F2FFFE0AA531DECF0C98895F9CB9B' WITH GRANT OPTION |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

score 5 · Answer 1 · answered Nov 01 '12 at 15:55

5

I've run into this in the past, my solution was to explicitly connect to 127.0.0.1 instead of the localhost alias

mysql -uroot -h127.0.0.1

Hopefully that works for you!

answered Nov 01 '12 at 15:55

Bryan

6,682
2
17
21

One thing I notice, is that you're doing CREATE DATABASE IF EXISTS, have you tried dropping the database first just to clear the air? – Bryan Nov 01 '12 at 16:09

Devart · Accepted Answer · 2012-11-01T16:37:49.403

1

It is not enough privileges. You are trying to grant ALL privileges. But, the user 'root'@'localhost' does not have ALL privileges.

The 'SHOW GRANTS' command has outputed a list of privileges - GRANT SELECT, INSERT,...

But it should be like this - GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost'...

Firstly grant all privileges to root account, or just grant privileges that are not granted.

edited Nov 01 '12 at 16:37

answered Nov 01 '12 at 16:22

Devart

119,203
23
166
186

you've got that. i've restored all the privileges on root following this one http://stackoverflow.com/questions/1709078/how-can-i-restore-the-mysql-root-users-full-privileges and then it works. thanks! – apelliciari Nov 01 '12 at 16:57

score 1 · Answer 3 · edited Mar 03 '17 at 14:16

1

a little precision in 5.7 :

mysql -uroot -h127.0.0.1  -p

GRANT ALL PRIVILEGES ON *.* TO 'root';

quit

then

mysql -u root  -h mynode mydb -p

works fine

edited Mar 03 '17 at 14:16

wogsland

9,106
19
57
93

answered Mar 03 '17 at 13:32

gan

11
1

Can't access to some database being root, after just creating that database

3 Answers3

Linked