2019年9月14日2019年10月11日

こんにちは！
2019年新卒入社！プチDBAを目指してOJT中の高橋です！
弊社のOJT制度についてはこちら！

「*」はワイルドカード

というのは言わずもがなですが、

それはMySQLにおいても同じで、きちんと期待する動作をしてくれます。

mysql8017> GRANT SELECT ON *.* TO hoge@'%';

を実行すると、
全てのスキーマの全てのテーブルに権限を付与するという解釈ができるので、
直感的でわかりやすいです。

そして権限を取るときも、

mysql8017> REVOKE SELECT ON *.* FROM hoge@'%';

という感じで実行できます。

ここで疑問

mysql8017> SHOW GRANTS FOR hoge@'%';
+----------------------------------------------+
| Grants for hoge@%                            |
+----------------------------------------------+
| GRANT SELECT ON *.* TO `hoge`@`%`            |
| GRANT SELECT ON `s1`.* TO `hoge`@`%`         |
| GRANT ALL PRIVILEGES ON `s2`.* TO `hoge`@`%` |
| GRANT ALL PRIVILEGES ON `s3`.* TO `hoge`@`%` |
+----------------------------------------------+

hoge@'%'ユーザにこれだけの権限があったとして、

mysql8017> REVOKE SELECT ON *.* FROM hoge@'%';

を実行した場合、

ワイルドカードの在り方としてはスキーマレベルの権限も取れる方が正しい気がしますが、
実際には取れません。

まあMySQLの仕様としてそういうものだと覚えてしまえばもーまんたいですよね。

しかし！

MySQL8.0ではREVOKE ALLを実行した場合に限り、

全ての権限が取れた！？

という話が今回の内容です！

検証

早速MySQL8.0と5.7のインスタンスで検証してみましょう。

今回は8.0.17と5.7.27でやっていきます。

ユーザをつくる

mysql5727> CREATE USER hoge@'%' IDENTIFIED BY 'hoge';
Query OK, 0 rows affected (0.00 sec)

mysql8017> CREATE USER hoge@'%' IDENTIFIED BY 'hoge';
Query OK, 0 rows affected (0.01 sec)

次！

権限をつける

mysql5727> GRANT SELECT ON *.* TO hoge@'%';
Query OK, 0 rows affected (0.00 sec)

mysql5727> GRANT ALL ON s1.* TO hoge@'%';
Query OK, 0 rows affected (0.01 sec)

mysql8017> GRANT SELECT ON *.* TO hoge@'%';
Query OK, 0 rows affected (0.00 sec)

mysql8017> GRANT ALL ON s1.* TO hoge@'%';
Query OK, 0 rows affected (0.00 sec)

ここまでで一旦比較してみます。

mysql5727> SHOW GRANTS FOR hoge@'%';
+----------------------------------------------+
| Grants for hoge@%                            |
+----------------------------------------------+
| GRANT SELECT ON *.* TO 'hoge'@'%'            |
| GRANT ALL PRIVILEGES ON `s1`.* TO 'hoge'@'%' |
+----------------------------------------------+
2 rows in set (0.00 sec)

mysql8017> SHOW GRANTS FOR hoge@'%';
+----------------------------------------------+
| Grants for hoge@%                            |
+----------------------------------------------+
| GRANT SELECT ON *.* TO `hoge`@`%`            |
| GRANT ALL PRIVILEGES ON `s1`.* TO `hoge`@`%` |
+----------------------------------------------+
2 rows in set (0.00 sec)

同じですね〜
次！

ALLを使って権限をはがす

今回の肝となるSQLを実行していきます！

globalレベルのSELECT権限をALLでとります。

mysql5727> REVOKE ALL ON *.* FROM hoge@'%';
Query OK, 0 rows affected (0.01 sec)

mysql8017> REVOKE ALL ON *.* FROM hoge@'%';
Query OK, 0 rows affected (0.01 sec)

結果

mysql5727> SHOW GRANTS FOR hoge@'%';
+----------------------------------------------+
| Grants for hoge@%                            |
+----------------------------------------------+
| GRANT USAGE ON *.* TO 'hoge'@'%'             |
| GRANT ALL PRIVILEGES ON `s1`.* TO 'hoge'@'%' | 　← これ！！
+----------------------------------------------+
2 rows in set (0.00 sec)

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

はいどうでしょう！

8.0の方はschemaレベルで付けていた権限まで取られてもうすっからかんですね。

いっぱいあっても関係ありません。

mysql8017> SHOW GRANTS FOR hoge@'%';
+----------------------------------------------+
| Grants for hoge@%                            |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `hoge`@`%`             |
| GRANT SELECT ON `s1`.* TO `hoge`@`%`         |
| GRANT ALL PRIVILEGES ON `s2`.* TO `hoge`@`%` |
| GRANT ALL PRIVILEGES ON `s3`.* TO `hoge`@`%` |
+----------------------------------------------+
4 rows in set (0.00 sec)

mysql8017> REVOKE ALL ON *.* FROM hoge@'%';
Query OK, 0 rows affected (0.00 sec)

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

＼＼ 無慈悲！／／

まとめ

我らがyoku0825さんとのペアオペ中に発覚したこの事象。

挙動としては8.0の方が直感的で正しい気がしますが、

MySQL8.0のREVOKE ALLには気をつけましょう！

ちなみに

8.0でもALLを使わなければこれまで通り使えるようです。

mysql8017> SHOW GRANTS FOR hoge@'%';
+--------------------------------------+
| Grants for hoge@%                    |
+--------------------------------------+
| GRANT SELECT ON *.* TO `hoge`@`%`    |
| GRANT SELECT ON `t1`.* TO `hoge`@`%` |
+--------------------------------------+
2 rows in set (0.00 sec)

mysql8017> REVOKE SELECT ON *.* FROM hoge@'%';
Query OK, 0 rows affected (0.01 sec)

mysql8017> SHOW GRANTS FOR hoge@'%';
+--------------------------------------+
| Grants for hoge@%                    |
+--------------------------------------+
| GRANT USAGE ON *.* TO `hoge`@`%`     |
| GRANT SELECT ON `t1`.* TO `hoge`@`%` |
+--------------------------------------+
2 rows in set (0.00 sec)