[mysql] DB作成、ユーザー発行、権限付与

Tech
[mysql] DB作成、ユーザー発行、権限付与

よく忘れるのでメモ。

基本の3ステップ

アプリ用に example という DB とユーザーを作る例です。

CREATE DATABASE example CHARACTER SET utf8mb4;

CREATE USER 'example'@'%' IDENTIFIED BY 'パスワード';

GRANT ALL PRIVILEGES ON example.* TO 'example'@'%';
文やっていること
CREATE DATABASEDB を作る。日本語や絵文字を扱うなら文字コードは 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