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

遇到這個 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;

 

 

 

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *