計画と改善

エンジニアのブログ

MySQLの権限管理方法まとめ

MySQLではユーザーやロールを作成して指定した権限を付与し、ユーザーの操作を制限することができる。

今回はユーザー、ロールの権限の付与方法、確認方法についてまとめた。

MySQLのアカウント情報とユーザー

アカウント情報はmysqlデータベースのuserテーブル(mysql.user)に格納されている。

試しにuser, hostカラムにあるアカウント情報を取得してみる。

mysql> select user, host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| debian-sys-maint | localhost |
| mysql.sys        | localhost |
+------------------+-----------+
3 rows in set (0.00 sec)

hostカラムにはmysqlユーザーがmysqlサーバーに接続できるクライアントホストに関して定義されている。%は任意のhost名に一致するワイルドカード

mysqlユーザーはWindowsUnix OSとは無関係でmysqlの認証のために利用される。

ただしmysqlにログインする際にユーザー名を明示的に指定しない場合は、ほとんどのmysqlクライアントがデフォルトで現在のUnixユーザー名をMySQLユーザー名として使用してログインを試みる

例:

root@xxx:/etc/mysql/sakila# mysql -p     
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

mysql -pしか指定していないにもかかわらずパスワードを要求され、入力するとログインができた。確認のためlinuxユーザーを表示してみる。

root@xxx:/etc/mysql/sakila# whoami
root

現在のlinuxユーザーがrootで、mysql側で用意されていたrootという同名のユーザーが存在していたため、mysqlのrootユーザーのパスワードを入力することでmysqlにログインすることができたということだ。

ちなみにパスワード情報は同じくmysql.userテーブルのauthentication_stringカラム(旧passwordカラム)に格納されている。

格納されているパスワードは、プラグイン固有のアルゴリズムを使用して暗号化される。以下は取得の例、暗号されたパスワードは***表示としている。

mysql> select user, authentication_string from mysql.user;
+------------------+-------------------------------------------+
| user             | authentication_string                     |
+------------------+-------------------------------------------+
| mysql.sys        | ***************************************** |
| root             |  ***************************************** |
| debian-sys-maint |  ***************************************** |
+------------------+-------------------------------------------+

MySQLの権限

アカウントに付与される権限によって実行できる操作が決まる。

アカウントに付与されている権限を確認するにはSHOW GRANTSコマンドを利用する。

rootユーザーの権限を確認するには以下のようになる。

mysql> SHOW GRANTS FOR root;
+-------------------------------------------------------------+
| Grants for root@%                                           |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

*.*はDB名.テーブル名の形式になっており、*.*と指定するとグローバルに権限を付与することができる。

アカウントの作成と権限の付与

アカウントとユーザー、権限について確認できたところで、実際にユーザーを作成して権限を付与してみる。

ユーザー作成の際にはIDENTIDIED BYで任意のパスワードを設定できる。

mysql> CREATE USER hoge IDENTIFIED BY '任意のパスワード';
Query OK, 0 rows affected (0.06 sec)

ユーザーに適当な権限を付与する。権限の付与はGRANTを利用する。

グローバルなselect権限をhogeに与える。

mysql> GRANT SELECT ON *.* TO hoge;
Query OK, 0 rows affected (0.01 sec)

権限が付与できているか確認する。rootユーザーに付与されていた権限を確認するのと同様にSHOW GRANTSする。

mysql> SHOW GRANTS FOR hoge;
+-----------------------------------+
| Grants for hoge@%                 |
+-----------------------------------+
| GRANT SELECT ON *.* TO 'hoge'@'%' |
+-----------------------------------+
1 row in set (0.00 sec)

権限が付与されているのが確認できた。

一度現在使用しているユーザーをログアウトして再度mysqlに任意のパスワードを入力してログインし直す。

root@xxx:/etc/mysql/sakila# mysql -u hoge -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.17-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

適当なデータベースを選択してSELECTを実行する。今回はmysqlが提供しているサンプルであるsakilaのデータを利用して実行した。

mysql> use sakila;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from actor;
+----------+-------------+--------------+---------------------+
| actor_id | first_name  | last_name    | last_update         |
+----------+-------------+--------------+---------------------+
|        1 | PENELOPE    | GUINESS      | 2006-02-15 04:34:33 |
...中略
|      197 | REESE       | WEST         | 2006-02-15 04:34:33 |
|      198 | MARY        | KEITEL       | 2006-02-15 04:34:33 |
|      199 | JULIA       | FAWCETT      | 2006-02-15 04:34:33 |
|      200 | THORA       | TEMPLE       | 2006-02-15 04:34:33 |
+----------+-------------+--------------+---------------------+
200 rows in set (0.00 sec)

SELECTが問題なく実行できていることがわかる。

次にUPDATEを実行しようとすると以下の通り拒否される。

mysql> UPDATE actor SET first_name = 'test';
ERROR 1142 (42000): UPDATE command denied to user 'hoge'@'localhost' for table 'actor'

これで権限の設定通りとなった。

ロール

MySQLにおけるロールとは公式では権限の名前付きコレクションと表現されている。MySQL8から導入された。

例えば、特定のアプリの特定のDBへのアクセス権限を用意しておき、権限を付与したいユーザーに対して、あらかじめ作成したロールを適用することができる。

まずバージョンを確認しておく

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.30    |
+-----------+
1 row in set (0.00 sec)

app_userロールを作成する

mysql> CREATE ROLE app_user;
Query OK, 0 rows affected (0.05 sec)

権限の付与と確認の仕方はアカウントへの権限付与と確認の仕方と同じ。

以下はapp_dbデータベースの全てのテーブルに対して、SELECTとINSERT権限を付与している。

SELECT権限の付与

mysql> GRANT SELECT ON app_db.* TO app_user;
Query OK, 0 rows affected (0.03 sec)

mysql> GRANT INSERT ON app_db.* TO app_user;
Query OK, 0 rows affected (0.02 sec)

ロールの権限を確認する方法

mysql> SHOW GRANTS FOR app_user;
+------------------------------------------------------+
| Grants for app_user@%                                |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `app_user`@`%`                 |
| GRANT SELECT, INSERT ON `app_db`.* TO `app_user`@`%` |
+------------------------------------------------------+
2 rows in set (0.01 sec)

USAGEは権限がないことを表す。

ロールの付与

ユーザーを作成し、ロールを付与する。

ユーザーの作成

mysql> CREATE USER user1;
Query OK, 0 rows affected (0.02 sec)

ユーザーの権限確認(何も権限をもっていない)

mysql> SHOW GRANTS FOR user1;
+-----------------------------------+
| Grants for user1@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO `user1`@`%` |
+-----------------------------------+
1 row in set (0.00 sec)

user1に先ほど作成したロールを付与

mysql> GRANT app_user TO user1;
Query OK, 0 rows affected (0.02 sec)

user1の権限を確認

mysql> SHOW GRANTS FOR user1;
+-------------------------------------+
| Grants for user1@%                  |
+-------------------------------------+
| GRANT USAGE ON *.* TO `user1`@`%`   |
| GRANT `app_user`@`%` TO `user1`@`%` |
+-------------------------------------+
2 rows in set (0.00 sec)

user1にロールによって付与されている権限を確認するにはSHOW GRANTSUSINGを指定する。

mysql> SHOW GRANTS FOR user1 USING app_user;
+---------------------------------------------------+
| Grants for user1@%                                |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO `user1`@`%`                 |
| GRANT SELECT, INSERT ON `app_db`.* TO `user1`@`%` |
| GRANT `app_user`@`%` TO `user1`@`%`               |
+---------------------------------------------------+
3 rows in set (0.00 sec)

以上でロールにてユーザーに権限が付与されていることが確認できた