事象 : ユーザーを作ったのにログインできない
- 環境
- 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>