Take over a MySQL database from the vendor (the database version is 5.7.21 MySQL Community Server (GPL)) and encounter an ERROR 1044 (42000) error when creating an account: Access denied for user'root'@'localhost' to database xxx, as shown below.

mysql> grant all on xxx.* to xxx@'192.168.%' identified by 'xxx';

ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'xxxx'

mysql> 

As a matter of fact, root users should have any privileges, so why did this error occur? Check that the current user is root@localhost, and by the way, check the permissions of each root account. As follows:

mysql> select current_user() from dual;

+----------------+

| current_user() |

+----------------+

| root@localhost |

+----------------+

1 row in set (0.00 sec

mysql> select host,user from user where user='root';

+-----------+----------+

| host      | user     |

+-----------+----------+

| %         | root     |

| 127.0.0.1 | root     |

| ::1       | root     |

| localhost | root     |

+-----------+----------+

7 rows in set (0.00 sec)

mysql>  show grants for root@'localhost';

+--------------------------------------------------------------+

| Grants for root@localhost                                    |

+--------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost'            |

| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |

+--------------------------------------------------------------+

2 rows in set (0.00 sec)

mysql> show grants for root@'127.0.0.1';

+---------------------------------------------------------------------+

| Grants for root@127.0.0.1                                           |

+---------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION |

+---------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> show grants for root@'%';

+-------------------------------------------+

| Grants for root@%                         |

+-------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' |

+-------------------------------------------+

1 row in set (0.00 sec)

As shown above, the root@localhost account does not have WITH GRANT OPTION option. For WITH GRANT OPTION option, if you want authorized users, you can also grant these rights to other users, you need the option "WITH GRANT OPTION". That is to say, to pass permission to a third party. This is also the above. root@localhost If the user logs in with root@127.0.0.1 (this account has WITHGRANT OPTION option), there will be no error in creating the user and authorizing it:

# mysql -host 127.0.0.1 -u root -p

Enter password: 

mysql>  grant all on eqmobilewarehouse.* to wdpm@'192.168.%' identified by 'test1249';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

Of course, there are other reasons for this mistake, but in this case, it is only because of the above reasons. Here is a record of this case @!