SHOW PROCESSLISTのHostがパーセントになるケース
プロシージャでDEFINERを指定して作成すると、Hostの覧が%になる。以下詳細。
まず、通常のケース。Id 37のセッションは正しく root が localhost から接続していると表示される。
mysql> SELECT CONNECTION_ID( );
+------------------+
| CONNECTION_ID( ) |
+------------------+
| 37 |
+------------------+
1 row in set (0.00 sec)
mysql> SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+--------+--------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+--------+--------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 605477 | Waiting on empty queue | NULL |
| 37 | root | localhost | t | Sleep | 15 | | NULL |
| 38 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST |
| 39 | root | localhost | NULL | Sleep | 635 | | NULL |
+----+-----------------+-----------+------+---------+--------+--------------------------+------------------+
6 rows in set (0.00 sec)
ここで別のユーザを作成し、DEFINERを指定したプロシージャを作成する。
デフォルトのSQL SECURITYはDEFINERのため、このSQLは呼び出したユーザではなく、DEFINERで指定したユーザ(つまり、user1)の権限で実行される。
mysql> CREATE USER user1@'%' IDENTIFIED BY 'Password@123';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT EXECUTE ON t.* TO user1@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER //
mysql> CREATE DEFINER = user1@'%' PROCEDURE t.sample()
BEGIN
SELECT CURRENT_USER();
SELECT SLEEP(10);
END
//
mysql> DELIMITER ;
さきほどのセッション(Id 37)でこのプロシージャを呼びだすと、Hostがパーセントになる。
User もuser1に変わる。プロシージャのDEFINERの権限で実行されているので、こういう表示になるんだろう。
TRIGGERやFUNCTIONもたぶん同じ動きになる。
mysql> SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+--------+--------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+--------+--------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 604842 | Waiting on empty queue | NULL |
| 37 | user1 | % | t | Query | 2 | User sleep | SELECT SLEEP(10) |
| 38 | root | localhost | NULL | Sleep | 58 | | NULL |
| 39 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST |
+----+-----------------+-----------+------+---------+--------+--------------------------+------------------+
6 rows in set (0.00 sec)
DEFINERとSQL SECURITY DEFINERを指定することで、プロシージャを呼び出したユーザとは別のユーザでプロシージャを動かすことができる。
権限では制御できないような、細かいアクセス制御を行いたい場合に活用できる。
例えば、テーブルのアクセス権限は与えずプロシージャのEXECUTE権限だけを与える。
プロシージャの中で、見せるべきレコードのみをSELECTするといった使い方。
-- adminはt1をSELECTできる権限を持っている
mysql> CREATE DEFINER = admin@'localhost' PROCEDURE t.show_record()
SQL SECURITY DEFINER
BEGIN
-- 大事なレコードは見せない
SELECT * FROM t1 WHERE is_secret = 0;
END
-- オペレータにはshow_recordの権限だけを与えるて大事なデータは見せない
mysql> GRANT EXECUTE ON t.show_record() TO 'operator'@%';
※ MySQL Community Edition 8.0.15 で試しました。
まず、通常のケース。Id 37のセッションは正しく root が localhost から接続していると表示される。
mysql> SELECT CONNECTION_ID( );
+------------------+
| CONNECTION_ID( ) |
+------------------+
| 37 |
+------------------+
1 row in set (0.00 sec)
mysql> SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+--------+--------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+--------+--------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 605477 | Waiting on empty queue | NULL |
| 37 | root | localhost | t | Sleep | 15 | | NULL |
| 38 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST |
| 39 | root | localhost | NULL | Sleep | 635 | | NULL |
+----+-----------------+-----------+------+---------+--------+--------------------------+------------------+
6 rows in set (0.00 sec)
ここで別のユーザを作成し、DEFINERを指定したプロシージャを作成する。
デフォルトのSQL SECURITYはDEFINERのため、このSQLは呼び出したユーザではなく、DEFINERで指定したユーザ(つまり、user1)の権限で実行される。
mysql> CREATE USER user1@'%' IDENTIFIED BY 'Password@123';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT EXECUTE ON t.* TO user1@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER //
mysql> CREATE DEFINER = user1@'%' PROCEDURE t.sample()
BEGIN
SELECT CURRENT_USER();
SELECT SLEEP(10);
END
//
mysql> DELIMITER ;
さきほどのセッション(Id 37)でこのプロシージャを呼びだすと、Hostがパーセントになる。
User もuser1に変わる。プロシージャのDEFINERの権限で実行されているので、こういう表示になるんだろう。
TRIGGERやFUNCTIONもたぶん同じ動きになる。
mysql> SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+--------+--------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+--------+--------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 604842 | Waiting on empty queue | NULL |
| 37 | user1 | % | t | Query | 2 | User sleep | SELECT SLEEP(10) |
| 38 | root | localhost | NULL | Sleep | 58 | | NULL |
| 39 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST |
+----+-----------------+-----------+------+---------+--------+--------------------------+------------------+
6 rows in set (0.00 sec)
DEFINERとSQL SECURITYとは
DEFINERとSQL SECURITY DEFINERを指定することで、プロシージャを呼び出したユーザとは別のユーザでプロシージャを動かすことができる。
権限では制御できないような、細かいアクセス制御を行いたい場合に活用できる。
例えば、テーブルのアクセス権限は与えずプロシージャのEXECUTE権限だけを与える。
プロシージャの中で、見せるべきレコードのみをSELECTするといった使い方。
-- adminはt1をSELECTできる権限を持っている
mysql> CREATE DEFINER = admin@'localhost' PROCEDURE t.show_record()
SQL SECURITY DEFINER
BEGIN
-- 大事なレコードは見せない
SELECT * FROM t1 WHERE is_secret = 0;
END
-- オペレータにはshow_recordの権限だけを与えるて大事なデータは見せない
mysql> GRANT EXECUTE ON t.show_record() TO 'operator'@%';
※ MySQL Community Edition 8.0.15 で試しました。