從供應(yīng)商那邊接手一個(gè)MySQL數(shù)據(jù)庫(kù)（數(shù)據(jù)庫(kù)版本為5.7.21 MySQL Community Server (GPL)），在創(chuàng)建賬號(hào)時(shí)遇到了“ERROR 1044 (42000): Access denied for user ‘root’@'localhost’ to database xxx”錯(cuò)誤，如下所示

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>

照理說，root用戶應(yīng)該有任何權(quán)限，那么為什么出現(xiàn)這個(gè)錯(cuò)誤呢？ 查看當(dāng)前用戶為root@localhost，順便查看了一下各個(gè)root賬號(hào)的權(quán)限。如下所示：

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)

如上所示，root@localhost賬號(hào)沒有WITH GRANT OPTION選項(xiàng)，關(guān)于WITH GRANT OPTION選項(xiàng)，如果想讓授權(quán)的用戶，也可以將這些權(quán)限授予給其他用戶，需要選項(xiàng) “WITH GRANT OPTION“ 。也就是說有這個(gè)選項(xiàng)就可以將權(quán)限傳遞給第三方。這也是上面root@localhost用戶給其它用后授權(quán)報(bào)錯(cuò)的原因，如果以 root@127.0.0.1登錄（此賬號(hào)擁有WITHGRANT OPTION選項(xiàng)），創(chuàng)建用戶并授權(quán)就不會(huì)有這個(gè)錯(cuò)誤，如下所示：

# mysql -host 127.0.0.1 -u root -p
Enter password: 
 
mysql>  grant all on xxx.* to xxx@'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)

當(dāng)然還有其它方面的原因也可能會(huì)引起這個(gè)錯(cuò)誤，不過在這個(gè)案例當(dāng)中，確實(shí)僅僅是因?yàn)樯厦嬖�因引起。特此記錄一下這個(gè)案例@！

標(biāo)簽： Mysql 權(quán)限 數(shù)據(jù)庫(kù)

版權(quán)申明：本站文章部分自網(wǎng)絡(luò)，如有侵權(quán)，請(qǐng)聯(lián)系：west999com@outlook.com
特別注意：本站所有轉(zhuǎn)載文章言論不代表本站觀點(diǎn)！
本站所提供的圖片等素材，版權(quán)歸原作者所有，如需使用，請(qǐng)與原作者聯(lián)系。