mysql Access denied for user ‘dbaccount’@’%’ to database

Posted in :

2017-09-05

遇到這個 Error, 就是說因為 dbaccount 權限只開了 ‘dbaccount’@’localhost’，解法是再開一個權限是 ‘dbaccount’@’%’ 就可以用外部 ip 連進 database server.

SQL 語法：

CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'dbpassword';

GRANT ALL PRIVILEGES ON Max_Database.* TO 'dbuser'@'%';

FLUSH PRIVILEGES;

說明：如果是只有要開 local 請服用下面的語法：

CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'dbpassword';

GRANT ALL PRIVILEGES ON Max_Database.* TO 'dbuser'@'localhost';

FLUSH PRIVILEGES;

滿奇怪的，我只能使用 ‘localhost’ 沒辦法使用 ‘%’

mysql> GRANT ALL PRIVILEGES ON Max_Database.* TO 'dbuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON Max_Database.* TO 'dbuser'@'%';
ERROR 1133 (42000): Can't find any matching row in the user table

To see the privileges for an account, use SHOW GRANTS:

Ex:

SHOW GRANTS FOR 'dbuser'@'localhost';

要知道，目前帳號有那些權限：

SHOW GRANTS;

To see nonprivilege properties for an account, use SHOW CREATE USER:

Ex:

SHOW CREATE USER 'admin'@'localhost'

權限設定範例：

CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'yourpassword';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON MAC_Core_Max.* TO 'dbuser'@'localhost';
FLUSH PRIVILEGES;