[mysql] DB作成、ユーザー発行、権限付与
Techよく忘れるのでメモ。
基本の3ステップ
アプリ用に example という DB とユーザーを作る例です。
CREATE DATABASE example CHARACTER SET utf8mb4;
CREATE USER 'example'@'%' IDENTIFIED BY 'パスワード';
GRANT ALL PRIVILEGES ON example.* TO 'example'@'%';
| 文 | やっていること |
|---|---|
CREATE DATABASE | DB を作る。日本語や絵文字を扱うなら文字コードは utf8mb4 |
CREATE USER | ユーザーを作る。'ユーザー名'@'接続元ホスト' の組で1ユーザー |
GRANT | 権限を付ける。example.* は「example DB の全テーブル」 |
ℹ️ 以前この記事では
CREATE DATABASE '';と書いていましたが、DB名をシングルクォートで囲むと構文エラーになります。囲むならバッククォート(`example`)です。
ℹ️ MySQL 8.0 では、
GRANTだけでユーザーを作ることはできなくなりました(GRANT ... IDENTIFIED BYは使えない)。CREATE USER→GRANTの順に実行します。GRANTの後にFLUSH PRIVILEGESは不要です。
ローカル専用の「なんでもできる」設定
手元の検証環境なら、こういう雑な設定をすることもあります。
CREATE USER 'root'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
*.* は全 DB の全テーブル、% はどこからでも接続可。
こんな危ない設定はローカル以外では絶対にやらない事。
ホスト指定の違い
| 指定 | 接続できる場所 |
|---|---|
'example'@'localhost' | 同じサーバー内からだけ |
'example'@'%' | どこからでも |
'example'@'10.0.0.%' | 10.0.0. から始まる IP からだけ |
Docker のように別コンテナから接続する場合、MySQL から見ると接続元は localhost ではないので、% やネットワークの範囲で作ります。
アプリ用ユーザーは権限を絞る
本番のアプリが使うユーザーには、必要な権限だけを付けるのが基本です。
GRANT SELECT, INSERT, UPDATE, DELETE ON example.* TO 'example'@'%';
マイグレーション(テーブルの作成・変更)もアプリのユーザーでやるなら、CREATE, ALTER, DROP, INDEX なども足します。
確認する
ユーザー一覧。
SELECT user, host FROM mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| root | % |
| healthchecker | localhost |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
ついでに権限確認も。
SHOW GRANTS FOR 'example'@'%';
+------------------------------------------------------+
| Grants for example@% |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `example`@`%` |
| GRANT ALL PRIVILEGES ON `example`.* TO `example`@`%` |
+------------------------------------------------------+
USAGE は「ログインはできるけど権限なし」という意味で、どのユーザーにも必ず付いています。
取り消す・削除する
REVOKE ALL PRIVILEGES ON example.* FROM 'example'@'%';
DROP USER 'example'@'%';
DROP DATABASE example;
まとめ
| やりたいこと | SQL |
|---|---|
| DB 作成 | CREATE DATABASE example CHARACTER SET utf8mb4; |
| ユーザー作成 | CREATE USER 'example'@'%' IDENTIFIED BY '...'; |
| 権限付与 | GRANT ALL PRIVILEGES ON example.* TO 'example'@'%'; |
| 権限確認 | SHOW GRANTS FOR 'example'@'%'; |
| 削除 | DROP USER 'example'@'%'; |
古いアプリから接続できないときは、認証方式の問題かもしれません。mysql_native_password に切り替える必要があるときもあわせてどうぞ。
mysql