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ユーザーはWindowsやUnix 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 GRANTS
にUSING
を指定する。
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)
以上でロールにてユーザーに権限が付与されていることが確認できた