【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
  • COLLATE とはソート順のこと。体感的に utf8mb4_general_ci が使われる印象。
-- 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 でもどこからでもログインできる。

  • IDENTIFIED WITH mysql_native_password AS ‘password’: この設定でユーザ作成をすると、ユーザ確認時にパスワードが平分で表示される。おそらく平分で保存されているので、セキュリティ上好ましくなく、このオプションは指定しないのが無難。
  • GRANT ALL PRIVILEGES ON db_name.* … 略 …: 特定の DB (db_name) のすべてのテーブル (*) に対してなんでもできる。これを指定する場合は結構ある感覚。セキュリティは劣るが。
  • 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; は何の意味もないといえる。

  • DB がなくても権限は付与することが可能。
  • 指定ユーザの権限の確認
  • ユーザを削除すると、紐づく権限も自動的に削除される (ユーザ削除後、権限を確認しようとすると、また、権限を削除しようとするとエラーとなったため)。
-- 作成
-- ユーザ作成
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 やユーザを作成するときになって、かなりもたつきましたので、本投稿にまとめることといたしました。

以上です。

コメントを残す