MySQLを操作するにはまず登録されたユーザーアカウントとパスワードでデータベースサーバーにログインする必要が有ります。
しかし”root”でログインしていると予期せぬトラブルの原因となったりします。
そこで今回はログインする為のユーザーアカウントの作り方について解説してみたいと思います。
ユーザーアカウントの存在意義などについても簡単に触れてみたいと思いますので最後までお付き合いください。
“Rootユーザー”がなぜNGなのか
まず初めに、ユーザーアカウントの必要性についてお話してみたいと思います。
早速ですが、あるデータベースがあったとします。この中には大切な情報がギッシリと詰め込まれています。
貴方は複数の開発者を携え、データベースの設計に取り掛かっています。
この時データベースサーバーには、Root権限を持つデフォルトユーザーしかアカウントが存在しません。
新規に作成することを面倒だと感じた貴方は、開発者全員にデフォルトユーザーでログインするよう指示しました。
その開発者の中の一人が間違って”DROP DATABASE”の命令を実行してしまったらどうなるでしょうか？
データベースの中身は全て削除され、最悪の場合には復元すら不可能となってしまいます。
こういった状況を極力避けるため、権限は常に分散させ、開発者毎に適切なアカウントを振り分ける必要が有るわけです。
アカウント作成コマンド
MySQLを学習したての方には朗報です。MySQLに限らずですが、データベースのコマンドは比較的簡単なものしか存在しません。
ということで、今回のアカウントの新規作成も非常に簡単なコマンドとなっています。
具体的な方法については以下を見てください。
CREATE USER 'ユーザー名'@'ホスト名' IDENTIFIED BY 'パスワード';
ユーザーアカウントの作成するクエリはこれだけです。
では実際にプロンプトからユーザーを作成してみましょう。
今回は以下のユーザー設定で進めます。
- ユーザー：test_user
- パスワード：pass123
ユーザー作成のためのクエリは以下の通りとなります。
// ユーザーアカウントの追加 CREATE USER 'test_user' IDENTIFIED BY 'pass123'; // クエリが成功した場合には以下のように表示される Query OK, 0 rows affected (0.000 sec) // アカウントが追加されたかどうかを確認してみる SELECT user, host FROM mysql.user; //追加されていれば以下のように表示される +-----------+-----------------+ | user | host | +-----------+-----------------+ | test_user | % | | root | 127.0.0.1 | | root | ::1 | | newUser | localhost | | pma | localhost | | root | localhost | | root | win-87hdnphkno3 | +-----------+-----------------+
リストの中に先ほど作成した”test_user”が表示されていることが確認できます。
ここで一つ注目してもらいたいのは、”test_user”の”host”が”%”となっている点です。
この”%”はワイルドカードと言って、test_userに関してはどこからでも接続できることを意味しています。
今回はホストの設定を敢えて行わなかったためにこのような表示となりましたが、この部分を設定することによりどこから接続できるのかを決定することが出来ます。
もしホストの設定をする場合には以下のようにクエリを変更します。
CREATE USER 'ユーザー名'@'ホスト名' IDENTIFIED BY 'パスワード';
この記述はレンタルサーバーを契約した場合には契約書に記載されているので、レンタルサーバーの指示通りで大丈夫です。
またリストの中のroot部分を見ると、”127.0.0.1″若しくは”localhost”となっていることが確認できると思いますが、これはlocalhostからしか接続できないということを表しています。
権限の付与
ここまでの操作でユーザーアカウントは追加されました。
しかしこのままの状態では、たとえ新規に作成したアカウントでログインしても何も操作できません。
未だ権限を与えていない為、ここでは権限の付与について解説していきます。
権限の付与に関するクエリは以下の通りです。
GRANT [権限] ON [適用対象のデータベース].[適用対象のテーブル] TO 'ユーザー名'@'ホスト名' IDENTIFIED BY 'パスワード';
先ほどのデータベースで実際に権限を付与しようとするなら、以下の様なクエリを記述します。
GRANT ALL ON case_db.* TO 'test_user';
GRANTのすぐ後ろに記述されている”ALL”で、全ての権限を与えることが出来ます。
アスタリスク(*)はワイルドカードとして機能しますので、この場合は「case_dbの中にあるテーブル全てにおいて」という意味となります。
ということでこれら全てを直訳すると「test_userというアカウントに対し、case_db内にある全てのテーブルにおいて、全ての権限を与える」となります。
では実際に権限を与えてみましょう。
次のクエリを実行してみてください。
// 権限の付与 GRANT ALL ON case_db.* TO 'test_user'; Query OK, 0 rows affected (0.003 sec) // 権限が付与されたかどうかの確認をしてみる SHOW GRANTS FOR 'test_user'; +----------------------------------------------------------------------------------------------------------+ | Grants for test_user@% | +----------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test_user'@'%' IDENTIFIED BY PASSWORD '*FB6E1F205D675BC29B052DB14CCEFE7759C5FF7E' | | GRANT ALL PRIVILEGES ON `case_db`.* TO 'test_user'@'%' | +----------------------------------------------------------------------------------------------------------+
上のクエリを実行すると、このように権限を付与されていることが確認できます。
権限の削除
では逆に、今度は特定のアカウントから権限を削除する方法について、少々簡単ではありますが解説してみたいと思います。
まず権限の削除に関するクエリは以下のようになります。
REVOKE [権限] ON [適用対象のデータベース].[適用対象のテーブル] FROM 'ユーザ名'@'ホスト名';
では先ほど権限を与えたユーザーからこのクエリを使って、権限の削除を行ってみます。
実行とその結果は以下の通りです。
// 権限の削除 REVOKE ALL ON case_db.* FROM 'test_user'; Query OK, 0 rows affected (0.000 sec) // 権限が削除されたかどうかの確認をしてみる SHOW GRANTS FOR 'test_user'; +----------------------------------------------------------------------------------------------------------+ | Grants for test_user@% | +----------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test_user'@'%' IDENTIFIED BY PASSWORD '*FB6E1F205D675BC29B052DB14CCEFE7759C5FF7E' | +----------------------------------------------------------------------------------------------------------+
上のクエリを実行してみると、無事権限が削除されていることが確認できます。
まとめ
いかがでしたか？
今回はアカウントの作成や削除と言ったクエリについて解説してみました。
MySQLはクエリの構造が非常に単純で理解し易いので、これ以外にもクエリを学習してデータベースの操作に慣れていきましょう。
データベースの操作に慣れていれば、DB設計で手間を取られることも少なくなり、またシステム設計をする上でも非常に有利になります。
