Help us understand the problem. What is going on with this article?

ERROR 1045 (28000): Access denied for userとなったときの対応方法

事象 : ユーザーを作ったのにログインできない

  • 環境
    • MySQL 5.7.28(RDS)
    • 接続元 : macOS Catalina バージョン10.15.3
-- ユーザーを作って
mysql> create user 'ponsuke'@'localhost' identified by 'password';
-- 権限を付与した
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'ponsuke'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.26 sec)

mysql> select host,user,select_priv,create_priv,insert_priv,grant_priv,account_locked from mysql.user;
+-----------+-----------+-------------+-------------+-------------+------------+----------------+
| host      | user      | select_priv | create_priv | insert_priv | grant_priv | account_locked |
+-----------+-----------+-------------+-------------+-------------+------------+----------------+
| localhost | rdsadmin  | Y           | Y           | Y           | Y          | N              |
| localhost | mysql.sys | N           | N           | N           | N          | Y              |
| %         | root      | Y           | Y           | Y           | Y          | N              |
| localhost | ponsuke   | Y           | Y           | Y           | Y          | N              |
+-----------+-----------+-------------+-------------+-------------+------------+----------------+
4 rows in set (0.15 sec)

mysql> exit
Bye
-- だけどログインできない
$ mysql -h mysql.xxxx.us-east-2.rds.amazonaws.com -P 3306 -u ponsuke -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'ponsuke'@'pxx-xx.xx.xx.ne.jp' (using password: YES)
$ 

原因 : hostがlocalhostだから

ローカルPCからRDSに接続するので「外部」から接続することになる。
なので「localhost」ではつながらない。
参考 : ユーザーを作成する(CREATE USER文) | MySQLの使い方

対応 : hostを外部から接続できる%にしてユーザーを作り直す

ローカルPCやEC2インスタンスから接続したいのでhostに%を指定します。
その代わり、権限は特定のデータベースだけに付けます。

rootでユーザーを作り直します
-- RDSにlocalhostで接続することはないので削除します
mysql> drop user ponsuke@localhost;
Query OK, 0 rows affected (0.16 sec)
-- ユーザーを作り直します
mysql> grant all privileges on ponsuke_db.* to 'ponsuke'@'%' identified by 'password' with grant option;
Query OK, 0 rows affected, 1 warning (0.15 sec)

mysql> select host,user from mysql.user;
+-----------+-----------+
| host      | user      |
+-----------+-----------+
| %         | admin     |
| %         | ponsuke   |
| localhost | mysql.sys |
| localhost | rdsadmin  |
+-----------+-----------+
4 rows in set (0.15 sec)

mysql> exit
Bye
-- ログインできました!
$ mysql -h mysql.xxxx.us-east-2.rds.amazonaws.com -P 3306 -u ponsuke -p ponsuke_db
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.28-log Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

事象 : GRANT文でグローバル権限を割り当てたら怒られた

  • 環境
    • MySQL 5.7.28(RDS)
mysql> grant all on *.* to 'ponsuke'@'localhost';
ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES)

試行錯誤

権限があるからGRANT文できるはずなのに・・・

mysql> select host,user,select_priv,create_priv,insert_priv,grant_priv,account_locked from mysql.user;
+-----------+-----------+-------------+-------------+-------------+------------+----------------+
| host      | user      | select_priv | create_priv | insert_priv | grant_priv | account_locked |
+-----------+-----------+-------------+-------------+-------------+------------+----------------+
| localhost | rdsadmin  | Y           | Y           | Y           | Y          | N              |
| localhost | mysql.sys | N           | N           | N           | N          | Y              |
| %         | root      | Y           | Y           | Y           | Y          | N              |
| localhost | ponsuke   | N           | N           | N           | N          | N              |
+-----------+-----------+-------------+-------------+-------------+------------+----------------+
4 rows in set (0.16 sec)

mysql> show grants for 'root'@`%`;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                                                                                                                                                                                                                                              |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'root'@'%' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.15 sec)

「`%`.*」にするとエラーにならないけど・・・権限は付与されない・・・
全く関係ないけど気がついた・・・「%」を指定するときは「'(シングルクォーテーション)」で囲むとエラーになる・・・「`(名前がわからない)」で囲むようだ。

mysql> grant all on `%`.* to 'ponsuke'@'localhost' identified by 'ponsuke';
Query OK, 0 rows affected, 1 warning (0.16 sec)

mysql> select host,user,select_priv,create_priv,insert_priv,grant_priv,account_locked from mysql.user;
+-----------+-----------+-------------+-------------+-------------+------------+----------------+
| host      | user      | select_priv | create_priv | insert_priv | grant_priv | account_locked |
+-----------+-----------+-------------+-------------+-------------+------------+----------------+
| localhost | rdsadmin  | Y           | Y           | Y           | Y          | N              |
| localhost | mysql.sys | N           | N           | N           | N          | Y              |
| %         | root      | Y           | Y           | Y           | Y          | N              |
| localhost | ponsuke   | N           | N           | N           | N          | N              |
+-----------+-----------+-------------+-------------+-------------+------------+----------------+
4 rows in set (0.15 sec)

原因 : RDSではGRANT ALL *.*ができないから

RDSではSuper権限が付与されたユーザはAWS側が管理用に確保しているrdsadminというユーザのみになっており、自身で作成したユーザにはSuper権限が付与されていません(付与することもできません)。
[RDS (MySQL)] Super権限操作で出たエラー | ハックノート

-- 確かにrdsadminには管理者権限がついている
mysql> show grants for 'rdsadmin'@'localhost';
+-------------------------------------------------------------------------+
| Grants for rdsadmin@localhost                                           |
+-------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'rdsadmin'@'localhost' WITH GRANT OPTION |
+-------------------------------------------------------------------------+
1 row in set (0.15 sec)

対応 : rootと同じ権限を付与していろいろできるようにする

管理者権限ではないけどやりたいことができればいい。

-- root権限を表示してコピーする
mysql> show grants for 'root'@`%`;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                                                                                                                                                                                                                                              |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'root'@'%' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.16 sec)

-- 貼り付けてユーザーの指定を対象のユーザに書き換える
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'ponsuke'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.26 sec)

-- 権限を確認
mysql> select host,user,select_priv,create_priv,insert_priv,grant_priv,account_locked from mysql.user;
+-----------+-----------+-------------+-------------+-------------+------------+----------------+
| host      | user      | select_priv | create_priv | insert_priv | grant_priv | account_locked |
+-----------+-----------+-------------+-------------+-------------+------------+----------------+
| localhost | rdsadmin  | Y           | Y           | Y           | Y          | N              |
| localhost | mysql.sys | N           | N           | N           | N          | Y              |
| %         | root      | Y           | Y           | Y           | Y          | N              |
| localhost | ponsuke   | Y           | Y           | Y           | Y          | N              |
+-----------+-----------+-------------+-------------+-------------+------------+----------------+
4 rows in set (0.15 sec)

mysql> 
ponsuke0531
びっくりするほど物覚えが悪いが、エンジニアを目指しています。
http://ponsuke-tarou.hatenablog.com/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした