MySQL8.0.20でSHOW_ROUTINEなる権限ができたらしい

退職して絶賛有休消化中なので、日々「有効な時間の使い方とは何か」という命題と戦っています。

とりあえずMySQL8.0.20がGAになったので、リリースノートをまったり読んでいます。

今回は新しく追加された権限についてです。

MySQL8.0.20でSHOW_ROUTINEという権限が新しくできたようです。

3行で

MySQL8.0.20でSHOW_ROUTINEという権限が導入された
今まで自分以外が定義したプロシージャなどの定義を確認したい場合は、グローバルレベルのSELECT権限という非常に広い権限が必要だった
SHOW_ROUTINE権限によりグローバルレベルのSELECT権限が無くても、他人が定義したプロシージャの定義も確認できるようになった(ただしmysqldumpは不可)

ということで実際に試してみましょう。

以前のバージョンで

まずは、MySQL8.0.19で試してみます。

mysql8019-root@localhost:[(none)] >CREATE USER test_user IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.02 sec)

mysql8019-root@localhost:[(none)] >CREATE DATABASE db1;
Query OK, 1 row affected (0.01 sec)

mysql8019-root@localhost:[(none)] >GRANT SELECT ON db1.* TO test_user;
Query OK, 0 rows affected (0.02 sec)

mysql8019-root@localhost:[(none)] >USE db1;
Database changed
mysql8019-root@localhost:[db1] >DELIMITER //
mysql8019-root@localhost:[db1] >CREATE PROCEDURE test_routine(IN a INT, IN b INT)
    -> BEGIN
    ->   SELECT a + b AS 'sum';
    -> END
    -> //
Query OK, 0 rows affected (0.02 sec)

mysql8019-root@localhost:[db1] >DELIMITER ;

まずはこの状態ではtest_userがdb1.test_routineの定義を確認できないことを見てみましょう。

mysql8019-test_user@localhost:[db1] >SHOW GRANTS;
+--------------------------------------------+
| Grants for test_user@%                     |
+--------------------------------------------+
| GRANT USAGE ON *.* TO `test_user`@`%`      |
| GRANT SELECT ON `db1`.* TO `test_user`@`%` |
+--------------------------------------------+
2 rows in set (0.00 sec)

mysql8019-test_user@localhost:[db1] >SELECT * FROM information_schema.ROUTINES WHERE routine_schema = 'db1';
Empty set (0.01 sec)

Emptyが返ってきてますね。

では、test_userにグローバルレベルのSELECT権限を渡してみます。

mysql8019-root@localhost:[db1] >GRANT SELECT ON *.* TO test_user;
Query OK, 0 rows affected (0.01 sec)

もう一度test_userでdb1.test_routineの定義をもう一度確認してみます。

mysql8019-test_user@localhost:[db1] >SHOW GRANTS;
+--------------------------------------------+
| Grants for test_user@%                     |
+--------------------------------------------+
| GRANT SELECT ON *.* TO `test_user`@`%`     |
| GRANT SELECT ON `db1`.* TO `test_user`@`%` |
+--------------------------------------------+
2 rows in set (0.00 sec)

mysql8019-test_user@localhost:[db1] >SELECT * FROM information_schema.ROUTINES WHERE routine_schema = 'db1'\G
*************************** 1. row ***************************
           SPECIFIC_NAME: test_routine
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: db1
            ROUTINE_NAME: test_routine
            ROUTINE_TYPE: PROCEDURE
               DATA_TYPE: 
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: NULL
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: BEGIN
  SELECT a + b AS 'sum';
END
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: SQL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2020-04-30 16:27:13
            LAST_ALTERED: 2020-04-30 16:27:13
                SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT: 
                 DEFINER: root@localhost
    CHARACTER_SET_CLIENT: utf8mb4
    COLLATION_CONNECTION: utf8mb4_0900_ai_ci
      DATABASE_COLLATION: utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

一応test_routineの定義を確認することができました。

またSHOW GRANTSで権限を確認してみると、USAGEと表示されていた行が消えていることが確認できます。 (USAGEは何の権限もないことを意味しています)

しかし、一方で場合によっては過剰な権限を渡してしまう可能性もあります。

mysql80-test_user@localhost:[(none)] >SELECT * FROM db2.t1;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec)

このように本来、test_userには見せたくないdb2のテーブルの内容も閲覧できてしまいます。

SHOW_ROUTINEを使おう

さっそくMySQL8.0.20で試してみましょう。

mysql8020-root@localhost:[db1] >SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.20    |
+-----------+
1 row in set (0.00 sec)

mysql8020-root@localhost:[(none)] >CREATE USER test_user IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.01 sec)

mysql8020-root@localhost:[(none)] >CREATE DATABASE db1;
Query OK, 1 row affected (0.01 sec)

mysql8020-root@localhost:[(none)] >GRANT SELECT ON db1.* TO test_user;
Query OK, 0 rows affected (0.03 sec)

mysql8020-root@localhost:[(none)] >USE db1;
Database changed
mysql8020-root@localhost:[db1] >DELIMITER //
mysql8020-root@localhost:[db1] >CREATE PROCEDURE test_routine(IN a INT, IN b INT)
    -> BEGIN
    ->   SELECT a + b AS 'sum';
    -> END
    -> //
Query OK, 0 rows affected (0.02 sec)

mysql8020-root@localhost:[db1] >DELIMITER ;

当たり前ですが、この状態ではプロシージャの定義を確認することはできません。

mysql8020-test_user@localhost:[information_schema] >SHOW GRANTS;
+--------------------------------------------+
| Grants for test_user@%                     |
+--------------------------------------------+
| GRANT USAGE ON *.* TO `test_user`@`%`      |
| GRANT SELECT ON `db1`.* TO `test_user`@`%` |
+--------------------------------------------+
2 rows in set (0.00 sec)

mysql8020-test_user@localhost:[information_schema] >SELECT * FROM information_schema.ROUTINES WHERE routine_schema = 'db1';
Empty set (0.01 sec)

では、本題

SHOW_ROUTINEを与えてプロシージャの権限が確認できるか見てみます。

mysql8020-root@localhost:[db1] >GRANT SHOW_ROUTINE ON *.* TO test_user;
Query OK, 0 rows affected (0.00 sec)

注意点として、権限を与えるときはグローバルレベルで与える必要があります。

mysql8020-root@localhost:[db1] >GRANT SHOW_ROUTINE ON db1.* TO test_user;
ERROR 3619 (HY000): Illegal privilege level specified for SHOW_ROUTINE

さてさて、もう一度test_userでプロシージャの定義を確認してみます。

mysql8020-test_user@localhost:[db1] >SHOW GRANTS;
+----------------------------------------------+
| Grants for test_user@%                       |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `test_user`@`%`        |
| GRANT SHOW_ROUTINE ON *.* TO `test_user`@`%` |
| GRANT SELECT ON `db1`.* TO `test_user`@`%`   |
+----------------------------------------------+
3 rows in set (0.00 sec)

mysql8020-test_user@localhost:[db1] >SELECT * FROM information_schema.ROUTINES WHERE routine_schema = 'db1'\G
*************************** 1. row ***************************
           SPECIFIC_NAME: test_routine
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: db1
            ROUTINE_NAME: test_routine
            ROUTINE_TYPE: PROCEDURE
               DATA_TYPE: 
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: NULL
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: BEGIN
  SELECT a + b AS 'sum';
END
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: SQL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2020-04-30 17:00:11
            LAST_ALTERED: 2020-04-30 17:00:11
                SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT: 
                 DEFINER: root@localhost
    CHARACTER_SET_CLIENT: utf8mb4
    COLLATION_CONNECTION: utf8mb4_0900_ai_ci
      DATABASE_COLLATION: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

無事にtest_routineの権限を確認できました。

また、SHOW GRANTSしたときにUSAGEの行が消えていないことが分かります。

この状態で他のデータベース(db2)の中身も見えないのか確認してみます。

mysql8020-test_user@localhost:[db1] >SELECT * FROM db2.t1;
ERROR 1142 (42000): SELECT command denied to user 'test_user'@'localhost' for table 't1'

mysql8020-test_user@localhost:[db1] >SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

大丈夫そうですね。

ただし、当たり前ですがdb2のプロシージャの定義は確認できてしまうので注意です。

mysql8020-test_user@localhost:[db1] >SELECT * FROM information_schema.ROUTINES WHERE routine_schema = 'db2'\G
*************************** 1. row ***************************
           SPECIFIC_NAME: test_routine
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: db2
            ROUTINE_NAME: test_routine
            ROUTINE_TYPE: PROCEDURE
               DATA_TYPE: 
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: NULL
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: BEGIN
  SELECT a + b AS 'sum';
END
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: SQL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2020-04-30 17:16:30
            LAST_ALTERED: 2020-04-30 17:16:30
                SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT: 
                 DEFINER: root@localhost
    CHARACTER_SET_CLIENT: utf8mb4
    COLLATION_CONNECTION: utf8mb4_0900_ai_ci
      DATABASE_COLLATION: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ということでSHOW_ROUTINE権限を使えば過剰に権限を渡すことなく自分以外が定義したプロシージャの権限を確認することができます。

mysqldumpは別問題・・・？

さて、SHOW_ROUTINEによって以前より柔軟に権限を設定することができるようになりました。では、どういったケースで使えるでしょうか。

公式リファレンスでは、SHOW_ROUTINEによりグローバルSELECT権限なしに、ストアドプロシージャのバックアップをできるようになるとあります。

バックアップと言えばmysqldump！ということで早速バックアップをば

mysql8020-test_user@localhost:[(none)] >SELECT user();
+---------------------+
| user()              |
+---------------------+
| test_user@localhost |
+---------------------+
1 row in set (0.00 sec)

mysql8020-test_user@localhost:[(none)] >SHOW GRANTS;
+----------------------------------------------+
| Grants for test_user@%                       |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `test_user`@`%`        |
| GRANT SHOW_ROUTINE ON *.* TO `test_user`@`%` |
+----------------------------------------------+
2 rows in set (0.00 sec)

さてこんなユーザーに対してmysqldumpでストアドプロシージャのみバックアップしてみます。

[root@centos8-instance3:/home]# mysqldump -utest_user -ptest --all-databases -R
mysqldump: [Warning] Using a password on the command line interface can be insecure.
-- MySQL dump 10.13  Distrib 8.0.20, for Linux (x86_64)
--
-- Host: localhost    Database: 
-- ------------------------------------------------------
-- Server version  8.0.20

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!50606 SET @OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */;
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=@OLD_INNODB_STATS_AUTO_RECALC */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2020-04-30 18:03:45

🤔・・・？

何も表示されませんね・・・

mysqldumpのリファレンスにはこうあります。

MySQL :: MySQL 8.0 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program

から引用

Include stored routines (procedures and functions) for the dumped databases in the output. This option requires the global SELECT privilege.

😇< 意味ねーじゃねーか！

試しにdb1のSELECT権限だけ渡してみます。

mysql8020-root@localhost:[db2] >GRANT SELECT ON db1.* TO test_user;
Query OK, 0 rows affected (0.00 sec)

再度mysqldumpを実行

[root@centos8-instance3:/home]# mysqldump -utest_user -ptest --all-databases -R
mysqldump: [Warning] Using a password on the command line interface can be insecure.
-- MySQL dump 10.13  Distrib 8.0.20, for Linux (x86_64)
--
-- Host: localhost    Database: 
-- ------------------------------------------------------
-- Server version  8.0.20

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!50606 SET @OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */;
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `db1`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `db1`;

--
-- Dumping routines for database 'db1'
--
/*!50003 DROP PROCEDURE IF EXISTS `test_routine` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `test_routine`(IN a INT, IN b INT)
BEGIN
  SELECT a + b AS 'sum';
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=@OLD_INNODB_STATS_AUTO_RECALC */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2020-04-30 18:09:59

うーん、やっぱりSELECT権限は必要なようですね。

このあたりは、バグ報告というよりFuture Requestしたほうがいいんですかね。（どなたかやってくれないかなあ|ω・`））

現在、無職中なのでOracleプロファイルの作成ができないんですよねorz

今回はこのあたりで

三流エンジニアの落書き帳

さあ、今日も未知という扉を開けてみよう

MySQL8.0.20でSHOW_ROUTINEなる権限ができたらしい

3行で

以前のバージョンで

SHOW_ROUTINEを使おう

mysqldumpは別問題・・・？