ユーザーを作成する(GRANT) | MySQLではじめるデータベース入門

コマンドラインクライアントからMySQLに接続する際、これまでは以下のコマンドを実行していました。

> mysql -u root -p

-uオプションで接続ユーザーを指定しているわけですが、上記サンプルで指定しているrootユーザーは、データベースに対してどのようなことでも実行できる権限を持つ特別なユーザーです。

何でもできるので、これ以上ない便利なユーザーなわけですが、逆を言えば、何でもできるということはデータベースを壊してしまうことや、外部に公開してはいけないデータを流出させてしまうなど、障害やセキュリティ上の問題を引き起こす恐れもあります。

そのようなことにならないよう、データベースを利用する各利用者には、ある人はデータベースを参照できるが、参照する必要のない人には、参照できないようにするといったように、利用形態に応じて、権限を制限することが望ましいです。

ユーザーを作成する

ユーザーを新規に作成するにはCREATE USER文を使用します。

CREATE USER文の基本構文: CREATE USER ＜ユーザー名@ホスト名＞ IDENTIFIED BY ‘＜パスワード＞’;

早速、新規ユーザーを作成してみましょう。コマンドラインクライアントで、MySQLに接続した状態し、以下のコマンドを実行します。

> CREATE USER suser01@localhost IDENTIFIED BY 'Password';

権限を設定する

ユーザーに権限を設定するには、GRANT文を使用します。権限としては、データの参照のみ許可する権限、データの追加・更新が行える権限、データの削除が行える権限などというように利用者の用途に応じた指定ができるようになっています。

GRANT文の基本構文: GRANT ＜権限＞ ON ＜データベース名.テーブル名＞ TO ＜ユーザー名@ホスト名＞;

では早速、先ほど作成したユーザーに権限を割り当ててみます。コマンドラインクライアントで、MySQLに接続した状態し、以下のコマンドを実行します。

> GRANT ALL PRIVILEGES ON *.*   TO suser01@localhost;

上記コマンドで、「suser01」という名前の、全てのデータベースとテーブルに対して全ての権限が付与されたユーザーを作成したことになります。ホスト名の指定が「localhost」としたため、同じマシンからのアクセスのみ許可されます。

ちなみに、以前のバージョンでは、ユーザー作成と権限設定はGRANT文ひとつでできていました。

GRANT文の基本構文（旧バージョン）: GRANT ＜権限＞ ON ＜データベース名.テーブル名＞ TO ＜ユーザー名@ホスト名＞ IDENTIFIED BY ‘＜パスワード＞’;

新しいバージョン（Ver8）では上記のようには書けず、ユーザー作成はCREATE USER文、権限設定はGRANT文と使い分けることになります。Grantを直訳すれば「許可する」という意味になりますが、そのGRANT分でユーザー作成やパスワード設定もできてしまうのは少し違和感を覚えますね。データベース管理者は最初にCREATE USER文を使用してユーザーを作成し、GRANT文でそのユーザーに対して権限を定義するとう順序を踏む方が自然です。

なお、ここで作成したユーザーはMySQLにより独自に管理されるものであり、Windowsで管理されるユーザーとは別のものです。

rootユーザーとは

rootユーザーとは、すべての管理者権限を持ち、すべての操作を実行できるスーパーユーザーのことです。初期状態ではパスワードが設定されていませんが、「MySQLのインストール(Windows版)」の手順でMySQLをインストールした場合は、rootユーザーのパスワードを設定しています。

rootユーザーのパスワードを変更したい場合は、コマンドラインクライアントで、以下のコマンドを実行します。

> set password for root@localhost=password('＜変更したいパスワード＞');

権限の種類

上記サンプルでは、全データベース、全テーブルに対して全ての権限を付与する設定としましたが、他にどのような権限を設定できるのでしょうか。

主な権限を下表にまとめます。

権限	許可する対象
ALL	全ての権限（GRANT OPTION以外）
ALTER	テーブルのスキーマ変更
CREATE	データベースやテーブルの作成
DELETE	DELETE文
DROP	データベースやテーブルの削除
GRANT OPTION	他のユーザーへの権限設定
INDEX	インデックスの作成や削除
INSERT	INSERT文
SELECT	SELECT文
UPDATE	UPDATE文

権限の適用範囲

GRANT文で付与される権限には、主に3つのレベルが存在します。レベルとは権限の適用範囲と捉えてください。ユーザーごとに付与する権限とその権限のレベル（適用範囲）を指定します。

グローバルレベル

「GRANT ALL ON *.*」と指定すると、権限は全てのデータベースとオブジェクト（テーブル等）に適用されます。上記サンプルはこのグローバルレベルを指定しています。（オブジェクトとは、データベース上に作成できるもので、テーブルなどがあります。）

データベースレベル

「GRANT ALL ON ＜データベース名＞.*」と指定すると、権限は、指定したデータベース内の全てのオブジェクトに適用されます。

テーブルレベル

「GRANT ALL ON ＜データベース名＞.＜テーブル名＞」と指定すると、権限は、指定したテーブル内の全てのカラムに適用されます。

作成したユーザーでMySQLに接続する

では、上記で作成したユーザーでMySQLに接続してみましょう。

Windowsのコマンドプロンプトを起動し、以下のコマンドを実行します。

> mysql -u suser01 -p

パスワードを聞かれます。今回の例では「Password」と入力してEnterキーを押下すれば、接続できます。

ユーザーの確認

最後に今回作成したユーザーを確認してみましょう。ユーザーに付与された権限などは以下のコマンドで確認できます。

> SHOW GRANTS FOR suser01@localhost\G

実行結果は下記のようになります。

*************************** 1. row ***************************
Grants for suser01@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `suser01`@`localhost`
*************************** 2. row ***************************
Grants for suser01@localhost: GRANT APPLICATION_PASSWORD_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `suser01`@`localhost`

ALL権限を与えているため、実際に設定された権限の全てが表示され、数が多くかなり読みにくいですね。

例えば、CREATE権限のみを与えたユーザーの場合は以下のような表示になります。

> CREATE USER cuser01@localhost IDENTIFIED BY 'Password1';
> GRANT CREATE ON stuffdb.* TO cuser01@localhost;

>  SHOW GRANTS FOR cuser01@localhost;
+------------------------------------------------------+
| Grants for cuser01@localhost                         |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `cuser01`@`localhost`          |
| GRANT CREATE ON `stuffdb`.* TO `cuser01`@`localhost` |
+------------------------------------------------------+

メタコマンド

上記2つのSHOW GRANTS FORコマンドを見比べて頂きたいのですが、SQLの末尾が前者が \G になっているの対し、後者はセミコロン（;）となっています。

SQLの末尾にセミコロン（もしくは\g）を付けて実行すると、通常、格子状の結果が表示されます。コレに対して、\Gとすると縦表示になります。格子状の枠が付くと基本的には見やすくなりますが、場合によってはない方が見やすい場合もあるので、この使い分けができると便利です。
この\gや\Gをメタコマンドと言います。