目次
とある日
権限付与したあとshow grant for usernameで差分を確認してましたが、テーブル数が多くなればなるほど出力される結果が多くて確認するのが大変でした。
そこで、何かいい方法がないか探してました。
そんなときに、以下の書籍でtables_privについての話があり、使えそうだなと思ったので検証してみました。
下準備
適当にユーザ作成して権限追加します。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.7.1.3 CREATE USER ステートメント
(root@localhost) [(none)] 8.1.0 > CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password'; Query OK, 0 rows affected (0.01 sec) (root@localhost) [(none)] 8.1.0 > GRANT SELECT, INSERT ON world_x.city TO 'jeffrey'@'localhost'; Query OK, 0 rows affected, 1 warning (0.01 sec) (root@localhost) [(none)] 8.1.0 > GRANT SELECT, INSERT ON world_x.countrylanguage TO 'jeffrey'@'localhost'; Query OK, 0 rows affected, 1 warning (0.01 sec) (root@localhost) [(none)] 8.1.0 > GRANT DROP, CREATE ON world_x.test TO 'jeffrey'@'localhost'; Query OK, 0 rows affected, 1 warning (0.00 sec)
SHOW GRANTS FOR
今まで権限見るときに使ってたSHOW GRANTS ステートメント
MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.7.7.21 SHOW GRANTS ステートメント
(root@localhost) [world_x] 8.0.33 > SHOW GRANTS FOR 'jeffrey'@'localhost'; +------------------------------------------------------------------------------+ | Grants for jeffrey@localhost | +------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `jeffrey`@`localhost` | | GRANT SELECT, INSERT ON `world_x`.`city` TO `jeffrey`@`localhost` | | GRANT SELECT, INSERT ON `world_x`.`countrylanguage` TO `jeffrey`@`localhost` | | GRANT CREATE, DROP ON `world_x`.`test` TO `jeffrey`@`localhost` | +------------------------------------------------------------------------------+ 4 rows in set (0.00 sec)
これだと、WHERE句やCount関数使えないので、権限追加したあとに差分を取ろうとすると少し手間でした。
mysql.tables_priv
mysqlデータベースにtables_privテーブルと、dbテーブルがあり、そこでユーザーの権限を確認することができます。
tables_privテーブルでは、テーブル単位に付与した権限のみを確認できます。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 6.2.3 付与テーブル
(root@localhost) [(none)] 8.1.0 > desc mysql.tables_priv; +-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+ | Host | char(255) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(32) | NO | PRI | | | | Table_name | char(64) | NO | PRI | | | | Grantor | varchar(288) | NO | MUL | | | | Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | | Table_priv | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') | NO | | | | | Column_priv | set('Select','Insert','Update','References') | NO | | | | +-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+ 8 rows in set (0.00 sec)
SELECTしてみる。
(root@localhost) [(none)] 8.1.0 > select * from mysql.tables_priv; +-----------+---------+---------------+-----------------+----------------+---------------------+---------------+-------------+ | Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv | +-----------+---------+---------------+-----------------+----------------+---------------------+---------------+-------------+ | localhost | world_x | jeffrey | city | root@localhost | 2023-08-27 16:59:51 | Select,Insert | | | localhost | world_x | jeffrey | countrylanguage | root@localhost | 2023-08-27 16:59:51 | Select,Insert | | | localhost | world_x | jeffrey | test | root@localhost | 2023-08-27 16:59:51 | Create,Drop | | | localhost | mysql | mysql.session | user | boot@ | 2023-08-13 17:39:11 | Select | | | localhost | sys | mysql.sys | sys_config | root@localhost | 2023-08-13 17:39:11 | Select | | +-----------+---------+---------------+-----------------+----------------+---------------------+---------------+-------------+ 5 rows in set (0.00 sec)
今日追加したデータだけ取ってくる。
(root@localhost) [(none)] 8.1.0 > select db,table_priv,count(*) from mysql.tables_priv where user = "jeffrey" and cast(timestamp as date) = CURDATE() group by db,table_priv ; +---------+---------------+----------+ | db | table_priv | count(*) | +---------+---------------+----------+ | world_x | Select,Insert | 2 | | world_x | Create,Drop | 1 | +---------+---------------+----------+ 2 rows in set (0.00 sec)
mysql.db
dbテーブルは、データベース単位の権限を確認することができます。
こっちは、tables_privテーブルと違って Y or Nが各権限ごとに決まってるみたいです。
(root@localhost) [(none)] 8.1.0 > desc mysql.db; +-----------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------+------+-----+---------+-------+ | Host | char(255) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(32) | NO | PRI | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Create_view_priv | enum('N','Y') | NO | | N | | | Show_view_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Event_priv | enum('N','Y') | NO | | N | | | Trigger_priv | enum('N','Y') | NO | | N | | +-----------------------+---------------+------+-----+---------+-------+ 22 rows in set (0.00 sec)
データベース単位で権限付与してみる。
GRANT CREATE,DROP ON world_x.* TO 'jeffrey'@'localhost';
SELECTしてみる。
(root@localhost) [(none)] 8.1.0 > select * from mysql.db where user = "jeffrey" \G; *************************** 1. row *************************** Host: localhost Db: world_x User: jeffrey Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: Y Drop_priv: Y Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Execute_priv: N Event_priv: N Trigger_priv: N 1 row in set (0.00 sec)
〆
ちなみに、他にも似たようなテーブルがあって、カラムごとの権限をとかも見れるようです。
あまりここらへんのテーブルを見たことなかったですが、結構便利ですね。
