データベースの作成、ユーザの追加と権限の設定

久しぶりの更新です。前回の記事が3月で止まっていたので随分と休んでしまったようでした。

MySQLでデータベース(DB)を作成・運用する場合、DBやサービスごとに実行ユーザーを作成することが望ましいです。 MySQLをインストールすると、コマンドラインなどで(MySQLの)rootユーザーで操作することになります。

しかし、rootユーザーだと何でもできてしまうため、rootのパスワードが漏洩してしまうと、そのサーバーにインストールしたMySQLの操作が何でも可能になってしまう恐れがあります。特に、PHPなどのプログラムからDBを操作する場合、接続情報(ユーザー名、パスワード、ホスト名、ポート番号)を設定ファイルなどに記述していることが多いため、外部に漏れる可能性もあります。

したがって、通常は権限を制限したユーザーを作成し、そのユーザーでDBを操作することが望ましいです。例えば、WP_USERというユーザーを作成し、WordpressのDB(WP_DB)のみしか操作できないようにするなどです。

例として、以下のような設定でDBとユーザーを作成し、ユーザーに権限を設定します。

ホスト名: localhost
データベース名: WP_DB
ユーザー名: WP_USER
パスワード: WP_PW
権限: WP_USERはWP_DBに対して、SELECT, INSERT, UPDATE, DELETEのみ実行が可能で、他のDBは操作できない

rootでMySQLにログイン

$ mysql -u root -p
Enter password:【rootのパスワードを入力】

データベース作成

create databaseでデータベースを作成します。

mysql> create database WP_DB;

# 文字コードを指定する場合は character setを使います。
# 例 UTF-8でDBを作成
mysql> create database WP_DB character set utf8;

# DBが作成されたか確認
mysql> show create database WP_DB;
+-----------+--------------------------------------------------------------------+
| Database | Create Database                                                 |
+-----------+--------------------------------------------------------------------+
| WP_DB     | CREATE DATABASE `WP_DB` /*!40100 DEFAULT CHARACTER SET utf8 */  |
+-----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

ユーザーの追加と権限の設定

GRANT構文を使います。

grant 権限内容 on 権限対象 to ユーザー@ホスト名 identified by パスワード;

権限内容

作成するユーザーに対して、権限を設定します。

all privileges ・・・ GRANT(権限の付与)以外の権限を付与
alter ・・・テーブル変更
create ・・・テーブル作成
drop ・・・テーブル削除
index ・・・インデックス作成、削除
select ・・・ select文
insert ・・・ insert文
update ・・・ update文
delete ・・・ delete文

権限対象

作成するユーザーに権限が付与される対象を設定します。

グローバル権限・・・すべてのデータベースが対象
データベース・・・指定したデータベースが対象
テーブル・・・指定したデータベースのテーブルが対象

以下のように表します。

# グローバル権限
*.*
# データベース
WP_DB.*
# テーブル (WP_DBのCONTENSテーブルが対象)
WP_DB.CONTENTS

作成するWP_USERユーザーは、WP_DBに対して、select, insert, update, deleteのみ許可するので以下のようになります。

mysql> grant select, insert, update, delete on WP_DB.* to WP_USER@localhost identified by 'WP_PW';

もし、権限の付与を間違えてしまった場合は、下記のコマンドで一度権限を削除して再度権限を付与します。

revoke all privileges, grant option from ユーザー名@ホスト名;

# 例
mysql> revoke all privileges, grant option from WP_USER@localhost;

設定の反映

flush privilegesで権限を再度読み込むことで設定が反映されます。

mysql> flush privileges;

権限の確認

作成したユーザーの存在確認と、そのユーザーの権限が付与されたかを確認します。

mysql> show grants for WP_USER@localhost;

+-------------------------------------------------------------------------------------------------------------------+
| Grants for WP_USER@localhost                                                                                      |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'WP_USER'@'localhost' IDENTIFIED BY PASSWORD '*89DE85HFE3D8C2218F25642AFB2545A8DA7353D5' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `WP_DB`.* TO 'WP_USER'@'localhost'                                  |
+-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

WP_USERでログインしてみます。

$ mysql -uWP_USER -pWP_PW

mysql> show databases;
+--------------------+
| Database          |
+--------------------+
| information_schema |
| WP_DB              |
+--------------------+
2 rows in set (0.00 sec)

WP_USERユーザーは、information_schemaとWP_DBのみ扱えることがわかります。 information_schemaはMySQL自身が保持しているデータベースのようです。

最後に

MySQLでデータベースの作成とユーザーの追加・権限の設定ができました。データベースを扱う場合は、rootユーザーよりも権限を制限したユーザーで操作するようにしましょう。これはLinuxで一般ユーザーで操作するのと同じです。

本日も乙

ただの自己満足な備忘録。