* パスワード未設定のユーザーをGRANTで作成できなくなった。
* CREATE USERでユーザー作ってからGRANTする。
mysql57> GRANT ALL ON db.* TO grant_style@localhost; -- ユーザー未作成, パスワード未指定のGRANTが転ける ERROR 1133 (42000): Can't find any matching row in the user table mysql57> CREATE USER create_style@localhost; -- パスワード未指定のCREATE USERは通る Query OK, 0 rows affected (0.00 sec) mysql57> GRANT ALL ON db.* TO create_style@localhost; -- ユーザーが存在するとGRANTが通る Query OK, 0 rows affected (0.01 sec)
* GRANT .. IDENTIFIED BY '..'('..'は生パスワード)の構文だとワーニングが出る。
* ワーニングは出るけれど、取り敢えずまだ使える。
* ↑と同じで推奨方法は CREATE USERで作ってからGRANT。
mysql57> GRANT ALL ON db.* TO grant_style@localhost IDENTIFIED BY 'test'; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql57> SHOW WARNINGS; +---------+------+------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. | +---------+------+------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql57> CREATE USER create_style@localhost IDENTIFIED BY 'test'; Query OK, 0 rows affected (0.01 sec) mysql57> GRANT ALL ON db.* TO create_style@localhost; Query OK, 0 rows affected (0.04 sec)
* IDENTIFIED BY PASSWORD '*..'('*..'は41桁ハッシュされたパスワード)の構文でもワーニングが出る。
* ワーニングは出るけれど取り敢えずまだ使える。
* IDENTIFIED WITH mysql_native_password AS '*..'構文を使う(Authentication Pluginとか使ってて他のプラグイン使う場合はプラグイン名読み替える)
* 最終的な推奨としては、CREATE USER .. IDENTIFIED WITH mysql_native_password AS '*..'
* ちなみにASはBYでもいいらしい。ワーニングメッセージに合わせてみただけ。
mysql57> GRANT ALL ON db.* TO grant_style@localhost IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql57> SHOW WARNINGS; +---------+------+---------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1287 | 'IDENTIFIED BY PASSWORD' is deprecated and will be removed in a future release. Please use IDENTIFIED WITH <plugin> AS <hash> instead | | Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. | +---------+------+---------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql57> GRANT ALL ON db.* TO grant_style@localhost IDENTIFIED WITH mysql_native_password AS '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql57> SHOW WARNINGS; +---------+------+------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. | +---------+------+------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql57> CREATE USER create_style@localhost IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql57> SHOW WARNINGS; +---------+------+---------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1287 | 'IDENTIFIED BY PASSWORD' is deprecated and will be removed in a future release. Please use IDENTIFIED WITH <plugin> AS <hash> instead | +---------+------+---------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
特にバージョンをまたいでユーザーを移す時など、わたしは SHOW GRANTSの出力をほげほげして食わせるのが大好き なんですが、MySQL 5.7未満 => 5.7より未来でdeprecatedからremovedになると、これが出来なくなるんだよなぁ。。
$ client/mysqldump --grants -S /usr/mysql/5.6.25/data/mysql.sock --all-databases -x .. -- -- Dumping grants -- GRANT USAGE ON *.* TO 'create_style'@'localhost' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'; -- これがワーニング。 GRANT ALL PRIVILEGES ON `db`.* TO 'create_style'@'localhost'; GRANT USAGE ON *.* TO 'grant_style'@'localhost'; -- これはパスワードなしでエラー。 GRANT ALL PRIVILEGES ON `db`.* TO 'grant_style'@'localhost'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION; -- これもエラー。 GRANT ALL PRIVILEGES ON *.* TO 'root'@'::1' WITH GRANT OPTION; -- これもエラー。
( ´-｀).oO(パスワード設定してないのが悪いだけか。
更に5.7からはSHOW GRANTSの結果にハッシュ済みのパスワードを含めてくれなくなったので、
mysql57> SHOW GRANTS FOR create_style@localhost;
+--------------------------------------------------+
| Grants for create_style@localhost |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO 'create_style'@'localhost' |
+--------------------------------------------------+
1 row in set (0.00 sec)
mysql57> SELECT authentication_string FROM mysql.user WHERE (user, host)= ('create_style', 'localhost');
+-------------------------------------------+
| authentication_string |
+-------------------------------------------+
| *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
+-------------------------------------------+
1 row in set (0.00 sec)
お手製の mysqldump --grantsパッチ も使えなくなってしまうのだなぁ。。
0 件のコメント :
コメントを投稿