４－４．MySQLでユーザーアカウントの権限を制御する

2019年3月9日2019年7月15日

１．権限とは

権限はデータべ―スの操作やアクセスを限定させるための機能です。
例えばセキュリティの観点によりあるユーザーには参照を制限（出来なく）するような設定を可能とします。
また、更新の権限を制限することで操作ミスによるデータの書き換えや消失などを防いだりできます。
適切な権限の割り当てはデータベースを利用するシステムの運用上とても大切です。

スポンサードサーチ

２．権限の付与と剥奪（はくだつ）

ユーザーアカウントに権限を割り当てることを付与すると言い、割り当てた権限を取り消すことを剥奪すると言います。

１）権限の付与

ユーザーアカウントへの権限の付与はGRANT文で行います。
おおまかな構文は以下のようになっています。
【構文】
GRANT [権限] ON [対象オブジェクト] TO [ユーザーアカウント];

２）権限の剥奪

ユーザーアカウントへ付与した権限の剥奪はREVOKE文で行います。
おおまかな構文は以下のようになっています。
【構文】
REVOKE [権限] ON [対象オブジェクト] FROM [ユーザーアカウント];

３）付与されている権限の確認

ユーザーアカウントに対して権限の割り当て状況を確認するには「SHOW GRANTS」文で表示させることができます。
構文は以下となっています。
【構文】
SHOW GRANTS; 　　　　　　　　　　　　　→【現在接続中のユーザーアカウントの権限表示】
SHOW GRANTS FOR [ユーザーアカウント];　→【指定したユーザーアカウントの権限を表示】

４）実行例

データベース「TEST」にはテーブルが２つ「TABLE01」「TABLE02」が定義してある環境で以下説明します。

(1)ケース１

ユーザーアカウント「USER01@localhost」と「USER02@localhost」を作成して

「USER01@localhost」には「TABLE01」「TABLE02」の読み取り権限

「USER02@localhost」には「TABLE02」の読み取り権限

を与える.

①ユーザーアカウント作成

mysql> CREATE USER USER01@localhost IDENTIFIED BY 'PASSWD';
Query OK, 0 rows affected (0.24 sec)

mysql> CREATE USER USER02@localhost IDENTIFIED BY 'PASSWD';
Query OK, 0 rows affected (0.11 sec)

mysql>

②権限付与

mysql> GRANT SELECT ON TABLE01 TO USER01@localhost; 
Query OK, 0 rows affected (0.13 sec) 

mysql> GRANT SELECT ON TABLE01 TO USER02@localhost; 
Query OK, 0 rows affected (0.13 sec) 

mysql> GRANT SELECT ON TABLE02 TO USER02@localhost;
Query OK, 0 rows affected (0.47 sec)

mysql>

準備が出来たので実際にやってみましょう。

③「TABLE01」にSELECT権限がある「USER01」でアクセスしてみる

C:\>mysql -u USER01 -p　　　　【USER01で接続します】
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.15 MySQL Community Server - GPL

Copyright (c) 2000, 2019, 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 user();　　　【現在のユーザー確認】
+------------------+
| user()           |
+------------------+
| USER01@localhost |
+------------------+
1 row in set (0.00 sec)

mysql> USE studydb;　　　　【データベースを設定します】
Database changed
mysql> SELECT COUNT(*) FROM TABLE01;　【TABLE01をSELECTしてみます】
+----------+
| COUNT(*) |
+----------+
|        0 |　　　　　　　　　【SELECT成功です】
+----------+
1 row in set (0.23 sec)

mysql>

正常にSELECTできました。

権限を確認すると

mysql> SHOW GRANTS;
+-------------------------------------------------------------+
| Grants for USER01@localhost                                 |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO `USER01`@`localhost`                  |
| GRANT SELECT ON `studydb`.`table01` TO `USER01`@`localhost` | 
| GRANT SELECT ON `studydb`.`table02` TO `USER01`@`localhost` | 
+-------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql>

となっています。ちゃんとUSER01でTABLE01へのSELECT権限が付与されていることが確認できます。

⑤「TABLE01」にSELECT権限がない「USER02」でアクセスしてみる

C:\>mysql -u USER02 -p　　　　【USER02で接続します】
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.15 MySQL Community Server - GPL

Copyright (c) 2000, 2019, 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 user();
+------------------+
| user()           |
+------------------+
| USER02@localhost |
+------------------+
1 row in set (0.00 sec)

mysql> use studydb;　　　【データベースを設定します】
Database changed
mysql> SELECT COUNT(*) FROM TABLE01;　【TABLE01をSELECTしてみます】
ERROR 1142 (42000): SELECT command denied to user 'USER02'@'localhost' for table
 'table01'　　　　　　　　　　　　　　　　【USER02はTABLE01のSELECT権限が無いためエラーになりました】
mysql>

このように「USER02」には「TABLE01」のSELECT権限が無い為拒否されてエラーになりました。

(2)ケース２【権限の剥奪】

先ほどのユーザーアカウント「USER01@localhost」から「TABLE01」の読み取り権限を剥奪して「TABLE01」をSELECTしてみます

①権限の剥奪

mysql> REVOKE SELECT ON TABLE01 FROM USER01@localhost;
Query OK, 0 rows affected (0.11 sec)

mysql>

②「USER01@localhost」から「TABLE01」をSELECTしてみます。

C:\>mysql -u USER01 -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.15 MySQL Community Server - GPL

Copyright (c) 2000, 2019, 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> use studydb;
Database changed
mysql> SELECT COUNT(*) FROM TABLE01;
ERROR 1142 (42000): SELECT command denied to user 'USER01'@'localhost' for table
 'table01'
mysql>

権限が無い為エラーになりました。

③権限の確認

念のため「USER01」への権限付与状況を確認しておきましょう。

mysql> SHOW GRANTS;
+-------------------------------------------------------------+
| Grants for USER01@localhost                                 |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO `USER01`@`localhost`                  |
| GRANT SELECT ON `studydb`.`table02` TO `USER01`@`localhost` |
+-------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

権限は「TABLE02」のみとなっています。(USAGEはデフォルトで付与されている「権限無し」のダミーです）

スポンサードサーチ

３．MySQLでサポートされている権限とレベル

MySQLでサポートされている権限にはその影響するレベルで以下のように分類されます。

１）グローバル権限（G)

対象：同一MySQLサービスに存在する全てのデータベースが対象となる権限です。
構文の「対象オブエクト」に「*.*」を指定します。

２）データベース権限（D)

対象：同一データベース内の全てのオブジェクトが対象となる権限です。
構文の「対象オブエクト」に「データベース名.*」を指定します。

３）テーブル権限（T)

対象：同一テーブル内の全ての列（カラム）が対象となる権限です。
構文の「対象オブエクト」に「データベース名.テーブル名.*」又は現在のデータベースが対象の場合「テーブル名.*」を指定します。

４）カラム権限（C)

対象：指定したテーブルのカラムが対象となる権限です。
構文の「対象オブエクト」に「データベース名.テーブル名.*」又は現在のデータベースが対象の場合「テーブル名.*」を指定します。
カラムは権限に続いてかっこ内にカラムを指定します。

５）ストアドルーチン権限（P)

対象：ストアドプロシジャやストアドファンクションに対して適用される権限でグローバル権限またはデータベース権限レベルで付与されます。
構文の「対象オブエクト」に「*.*」又は「データベース名.*」を指定します。
ストアドルーチンを作成（CREATE ROUTINE）、変更（ALTER ROUTINE）、実行(EXECUTE)する権限を付与します。

 

４.権限の種類と対象レベル（スコープ）

権限は種類によって付与できるレベル（スコープ）が以下のように限定されます。

権限	スコープ	説明
ALL [PRIVILEGES]	–	GRANT OPTION以外のすべての権限
ALTER	GDT	ALTER TABLEの実行権限
ALTER ROUTINE	GDP	ストアドルーチンのALTER / DROP実行権限
CREATE	GDT	CREATE DATABASE / CREATE TABLEの実行権限
CREATE ROUTINE	GD	ストアドルーチンの作成権限
CREATE TEMPORARY TABLES	GD	CREATE TEMPORARY TABLESの実行権限
CREATE USER	G	CREATE USER / DROP USER / RENAME USER / REVOKE ALL PRIVILEGESの実行権限
CREATE VIEW	GDT	CREATE VIEWの実行権限
DELETE	GDT	DELETEの実行権限
DROP	GDT	DROP DATABASE / DROP TABLEの実行権限
EVENT	G	イベントスケジューラーの作成権限
EXECUTE	GDP	ストアドルーチンの実行権限
FILE	G	サーバー上のファイルへのアクセス権限 SELECT … INTO OUTFILE / LOAD DATA INFILEが使用可能になる
INDEX	GDT	CREATE INDEX / DROP INDEXの実行権限
INSERT	GDTC	INSERTの実行権限
LOCK TABLES	GD	LOCK TABLESの実行権限 対象のテーブルにはSELECT権限も必要
PROCESS	G	SHOW FULL PROCESSLIST実行権限
REFERENCES	GDTC	未使用
RELOAD	G	FLUSHの実行権限
REPLICATION CLIENT	G	SHOW MASTER STATUS / SHOW SLAVE STATUSの実行権限
REPLICATION SLAVE	G	レプリケーションのスレーブとしての動作権限
SELECT	GDTC	SELECTの実行権限
SHOW DATABASES	G	SHOW DATABASESの実行権限
SHOW VIEW	GDT	SHOW CREATE VIEWの実行権限
SHUTDOWN	G	mysqladmin shutdownの実行権限
SUPER	G	CHANGE MASTER / KILL / PURGE MASTER LOGS / SET GLOBALの実行権限
TRIGGER	GDT	トリガの作成 / DROPの実行権限
UPDATE	GDTC	UPDATEの実行権限
USAGE	G	権限なし CREATE USERで作成したユーザーは最初この状態
GRANT OPTION	GDTP	権限付与実行権限 WITH GRANT OPTIONで設定可能
PROXY	X	プロキシ(代理)ユーザー

 

---

---