【MySQL】無難で一般的な CREATE USER, CREATE DATABASE あたりを押さえる

以下、例で使用する名前

• DB 名: sample_db
• ユーザ名: sample_user
• パスワード: sample_user_pass

データベースの作成・更新・削除

• DB 作成時は文字コード (CHARACTER SET) と照合順序 (COLLATE) を指定すること。テーブル作成時に指定しなくても良くなる。
• DB 作成時はストレージエンジン (InnoDB, MyISAM など) は指定できない。テーブル作成時に指定できる。
• 2018年9月3日現在では、utf8mb4 が一般的。これは、絵文字も扱える UTF-8
  • MySQLの文字コードとCollation – Qiita
• COLLATE とはソート順のこと。体感的に utf8mb4_general_ci が使われる印象。
  • 【MySQL】照合順序とは？ – Qiita

-- DB 作成
CREATE DATABASE IF NOT EXISTS sample_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 全 DB の文字コード、照合順序の確認
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;

-- DB の文字コード、照合順序の変更
ALTER DATABASE sample_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- DB 削除
DROP DATABASE sample_db;

ユーザ・権限

• MySQL ユーザのホストとは？: どのホストから接続できるかの設定。大抵は localhost にする。
  localhost なら、MySQL のインストールされているマシンからしかログインできないし、111.222.333.444 なら、IP アドレス 111.222.333.444 のマシンからリモートでログインできる一方で localhost からログインできないし、’%’ ならリモートマシンからでも localhost でもどこからでもログインできる。
  • MySQL 外部ホストからアクセスする | 渋谷生活
• IDENTIFIED WITH mysql_native_password AS ‘password’: この設定でユーザ作成をすると、ユーザ確認時にパスワードが平分で表示される。おそらく平分で保存されているので、セキュリティ上好ましくなく、このオプションは指定しないのが無難。
• GRANT ALL PRIVILEGES ON db_name.* … 略 …: 特定の DB (db_name) のすべてのテーブル (*) に対してなんでもできる。これを指定する場合は結構ある感覚。セキュリティは劣るが。
  • MySQL の権限のコマンドまとめ。 – Qiita
• GRANT SELECT, INSERT, UPDATE, DELETE ON db_name.* … 略 …: 特定の DB (db_name) のすべてのテーブル (*) に対して SELECT, INSERT, UPDATE, DELETE ができるユーザ。
• GRANT USAGE: 何の権限もない状態で CREATE USER で作成された直後の状態。ユーザ作成直後に、権限を確認するとそのことがよく分かる。
  したがって、ユーザ作成直後の GRANT USAGE ON *.* TO sample_user@localhost; は何の意味もないといえる。
  • 権限の設定(GRANT文) – ユーザーの作成 – MySQLの使い方
• DB がなくても権限は付与することが可能。
• 指定ユーザの権限の確認
  • ユーザ権限の確認・追加 – Qiita
• ユーザを削除すると、紐づく権限も自動的に削除される (ユーザ削除後、権限を確認しようとすると、また、権限を削除しようとするとエラーとなったため)。

-- 作成
-- ユーザ作成
CREATE USER sample_user@localhost IDENTIFIED BY 'sample_user_pass';
-- ユーザへの DB に対する権限付与
GRANT ALL PRIVILEGES ON sample_db.* TO sample_user@localhost;
GRANT SELECT, INSERT, UPDATE, DELETE ON sample_db.* TO sample_user@localhost;

-- 確認
-- ユーザ一覧確認
SELECT user, host, authentication_string FROM mysql.user;
-- 指定ユーザの権限確認
SHOW GRANTS for sample_user@localhost;

-- 削除
-- 指定ユーザのすべての権限削除
REVOKE ALL PRIVILEGES, GRANT OPTION FROM sample_user@localhost;
-- ユーザ削除
DROP USER sample_user@localhost;

おわりに

MySQL は必要に応じて使ってきましたけれども、DB やユーザ作成の頻度は多くなく、なんとなくで使用してきました。

今回、DB やユーザを作成するときになって、かなりもたつきましたので、本投稿にまとめることといたしました。

以上です。