ユーザー（アカウント）、ロール管理

スーパーユーザー root について

　多くの場合 MySQL の初期の管理ユーザーはrootに設定されています。rootはスーパーユーザーとも呼ばれます。

　このrootユーザーは MySQL のインストール時に OS 上の管理ユーザーでもあります。そのため、OS の端末からsudo myqlコマンドでログインができます。

$ sudo mysql
...
mysql>

　パスワードはデフォルトインストールでは必要ありません。

　初期はrootで MySQL の設定を行っていくことになりますが、公のサーバーとして運用するなら mysql_secure_installation によってセキュリティー設定を変更した方がよいでしょう。
　 mysql_secure_installation の設定でrootによる接続はローカルからのみで MySQL にログインするにはパスワードを必要とするようにします。

ユーザー（アカウント）管理

　MySQL ではユーザー管理とロール管理は別になっています。ユーザーにロールを含めることでロールのデータベースの操作権限を継承することができます。
　ユーザーごとにも操作権限を与えることができます。

mysql> SELECT * FROM mysql.user;

mysql> SELECT user, host FROM mysql.user;

ユーザーの追加

　MySQL におけるユーザーとは MySQL サーバーにログインしデータベース操作を行う抽象的な人のことです。 MySQL ではアカウントとも表記されています。

　スーパーユーザーとしてデフォルトでrootユーザーがありますが、さらにCREATE USERでユーザーの追加行うことができます。

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

　まずはスーパーユーザーrootで mysql にログインします。

$ sudo mysql
...
mysql>

　ユーザーの追加は CREATE USER クエリーを実行します。

mysql> CREATE USER 'user_name'@'localhost' IDENTIFIED BY '5v().B5y4wpT';

　ユーザー名は 'user_name'、ユーザーの接続元は 'localhost' まで許可しています。パスワードは '5v().B5y4wpT' です。

　'localhost' はユーザーの許可される接続元のホストの範囲を示します。'%' でネットワークを通した方法でもローカルからでも接続できます。'localhost' は同一コンピュータ内からの接続のみを許可します。
　接続元ホストを省略すると '%' に設定されます。

　パスワードは強度に従って決めます。ここでは、パスワードは８文字以上で強度は MEDIUM としています。パスワードの強度の確認は以下のようにします。

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql> SHOW VARIABLES LIKE 'validate_password%';

+-------------------------------------------------+--------+
| Variable_name                                   | Value  |
+-------------------------------------------------+--------+
| validate_password.changed_characters_percentage | 0      |
| validate_password.check_user_name               | ON     |
| validate_password.dictionary_file               |        |
| validate_password.length                        | 8      |
| validate_password.mixed_case_count              | 1      |
| validate_password.number_count                  | 1      |
| validate_password.policy                        | MEDIUM |
| validate_password.special_char_count            | 1      |
+-------------------------------------------------+--------+
8 rows in set (0.01 sec)

mysql> SET PASSWORD FOR 'username'@'host' = PASSWORD('new-password');

mysql> SET PASSWORD = PASSWORD('new-password');

　変更内容を反映するにはFLUSH PRIVILEGESクエリーを実行します。

mysql> FLUSH PRIVILEGES;

ユーザーの削除

　ユーザーの削除は DROP USER クエリーを使用します。構文は以下です。

mysql> DROP USER 'username'@'host' [,'username'@'host' ]...;

　ユーザーは複数を指定することができます。

　ユーザーの削除を行うとその権限についても削除されます。

ユーザー（アカウント）の操作権限

　MySQL ではユーザーごとにデータベースの操作権限を設定します。ユーザーはアカウントも呼ばれ、ユーザー名とパスワードで MySQL にログイン後に操作権限に基づいて出来ることが制限されます。

　データベースにはロールという設定があります。ロールはロール名に操作権限を設定するもので、ユーザーにロールを設定するとそのロールの操作権限を継承します。
　個々のユーザーごとに操作権限を設定しなくてもロールの切り替えで操作権限を変更することもできます。

ユーザーの権限の設定

　ユーザーごとのデータベースやテーブルに対する権限の設定には GRANT 文を使用します。

　権限設定の構文は以下のようになっています。

GRANT PRIVILEGE ON database.table TO 'username'@'host';

　PRIVILEGE は、 ALL PRIVILEGES、ALL または CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD クエリーからの指定です。

　ALL PRIVILEGES は CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD クエリーのすべての指定です。

　または、CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD クエリーから選択による組み合わせが可能です。
　CREATE, ALTER, DROP はテーブルの作成、変更、削除です。
　INSERT, UPDATE, DELETE, SELECT はデータの新規作成、更新、削除、閲覧です。
　REFERENCES は外部キー作成権限。RELOAD はキャッシュをクリアやリロード、テーブルのフラッシュやロックといった FLUSH 操作に関する権限です。

　もしも、example ユーザーにすべてのデータベースの全ての操作権限を与えるには以下のようにします。

mysql> GRANT ALL ON *.* TO 'example'@'localhost';

　*.* はすべてのデータベースの全てのテーブルという指定です。

　例えば、example ユーザーは閲覧のみを可能にするには以下のようにします。

mysql> GRANT SELECT ON *.* TO 'example'@'localhost';

　ユーザーの追加や権限設定が終わったらFLUSH PRIVILEGESクエリーを実行します。

mysql> FLUSH PRIVILEGES;

　このことで即時に追加されたユーザーとその権限が反映されます。

　権限設定のクエリーでユーザー登録ができます。
　以下のように権限設定と同時にパスワードを指定するとユーザー登録ができます。

mysql> GRANT ALL PRIVILEGE ON *.* TO 'example'@'localhost' IDENTIFIED BY 'password';

　MySQL ではユーザーごとに操作権限を設定できます。また、ロールを作成しユーザーにそのロールを含むことで操作権限を設定することもできます。

ユーザー権限の確認

　ユーザーに設定した権限を確認するには SHOW GRANTS クエリーを実行します。

mysql> SHOW GRANTS FOR 'username'@'host';

操作権限の変更

　ユーザーの操作権限の変更は GRANT クエリーで操作内容を指定します。

　構文は以下にようになります。

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_or_role [, user_or_role] ...
    [WITH GRANT OPTION]
    [AS user
        [WITH ROLE
            DEFAULT
          | NONE
          | ALL
          | ALL EXCEPT role [, role ] ...
          | role [, role ] ...
        ]
    ]
}

　例えば上記で設定した操作権限をSELECTのみに変更するには以下のようにします。

mysql> GRANT SELECT ON *.* TO 'example'@'localhost';

操作権限の削除

　ユーザーから操作権限を削除するには REVOKE クエリーを使用します。

　構文は以下にようになります。

REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user_or_role [, user_or_role] ...

/* または */

REVOKE ALL [PRIVILEGES], GRANT OPTION
    FROM user_or_role [, user_or_role] ...

　priv_type は操作対象でALL または CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD クエリーのことです。

　[object_type] priv_level は主にデーベースやテーブルの指定です。database.table のように指定し、すべての場合は *.* とします。

　user_or_role はユーザー名'username'@'host'とロール名のことです。

　例えば 'example'@'localhost' ユーザーにおいて、すべてのデータベースとテーブルに対してINSERT、UPDATE、DELETE 権限を削除するには以下のようにします。

mysql> REVOKE INSERT, UPDATE ,DELETE ON *.* TO 'example'@'localhost';

ロールについて

　MySQL でのロールは、ロール名にデータベースの操作権限が設定するものです。ユーザー（アカウント）にロールを設定することでユーザーは操作権限を継承します。

　例えば以下のように閲覧のみを設定したロールが与えられたユーザーはデータの閲覧のみしかできません。

/* ロールの作成 */
mysql> CREATE ROLE 'read_only';

/* ロールに閲覧のみを許可 */
mysql> GRANT SELECT ON *.* TO 'read_only';

/*　ユーザーにロールを設定する */
mysql> GRANT 'read_only' TO 'student_a'@'%', 'student_b'@'%', 'student_c'@'%';

　このように複数のロールに一括してデータベースの操作権限を設定することができます。ロールの操作権限を変更することで簡易にユーザーの権限を変更することができます。