TL;DR
- 今日は2023年4月1日ですし、私はたけのこ派です
動的権限とは、MySQL 8.0で加わった「 mysql.user やその他のテーブルに独自のカラムを持た ない タイプの権限」のことらしい。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 6.2.2 MySQL で提供される権限
↓このへんが「静的権限」で
mysql> DESC mysql.user;
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(255) | 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 | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_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 | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_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 | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int unsigned | NO | | 0 | |
| max_updates | int unsigned | NO | | 0 | |
| max_connections | int unsigned | NO | | 0 | |
| max_user_connections | int unsigned | NO | | 0 | |
| plugin | char(64) | NO | | caching_sha2_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
| Create_role_priv | enum('N','Y') | NO | | N | |
| Drop_role_priv | enum('N','Y') | NO | | N | |
| Password_reuse_history | smallint unsigned | YES | | NULL | |
| Password_reuse_time | smallint unsigned | YES | | NULL | |
| Password_require_current | enum('N','Y') | YES | | NULL | |
| User_attributes | json | YES | | NULL | |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
51 rows in set (0.01 sec)
↓このへんが「動的権限」
mysql> SELECT * FROM global_grants;
+------------------+-----------+------------------------------+-------------------+
| USER | HOST | PRIV | WITH_GRANT_OPTION |
+------------------+-----------+------------------------------+-------------------+
| mysql.infoschema | localhost | AUDIT_ABORT_EXEMPT | N |
| mysql.infoschema | localhost | FIREWALL_EXEMPT | N |
| mysql.infoschema | localhost | SYSTEM_USER | N |
| mysql.session | localhost | AUDIT_ABORT_EXEMPT | N |
| mysql.session | localhost | AUTHENTICATION_POLICY_ADMIN | N |
| mysql.session | localhost | BACKUP_ADMIN | N |
| mysql.session | localhost | CLONE_ADMIN | N |
| mysql.session | localhost | CONNECTION_ADMIN | N |
| mysql.session | localhost | FIREWALL_EXEMPT | N |
| mysql.session | localhost | PERSIST_RO_VARIABLES_ADMIN | N |
| mysql.session | localhost | SESSION_VARIABLES_ADMIN | N |
| mysql.session | localhost | SYSTEM_USER | N |
| mysql.session | localhost | SYSTEM_VARIABLES_ADMIN | N |
| mysql.sys | localhost | AUDIT_ABORT_EXEMPT | N |
| mysql.sys | localhost | FIREWALL_EXEMPT | N |
| mysql.sys | localhost | SYSTEM_USER | N |
| root | localhost | APPLICATION_PASSWORD_ADMIN | Y |
| root | localhost | AUDIT_ABORT_EXEMPT | Y |
| root | localhost | AUDIT_ADMIN | Y |
| root | localhost | AUTHENTICATION_POLICY_ADMIN | Y |
| root | localhost | BACKUP_ADMIN | Y |
| root | localhost | BINLOG_ADMIN | Y |
| root | localhost | BINLOG_ENCRYPTION_ADMIN | Y |
| root | localhost | CLONE_ADMIN | Y |
| root | localhost | CONNECTION_ADMIN | Y |
| root | localhost | ENCRYPTION_KEY_ADMIN | Y |
| root | localhost | FIREWALL_EXEMPT | Y |
| root | localhost | FLUSH_OPTIMIZER_COSTS | Y |
| root | localhost | FLUSH_STATUS | Y |
| root | localhost | FLUSH_TABLES | Y |
| root | localhost | FLUSH_USER_RESOURCES | Y |
| root | localhost | GROUP_REPLICATION_ADMIN | Y |
| root | localhost | GROUP_REPLICATION_STREAM | Y |
| root | localhost | INNODB_REDO_LOG_ARCHIVE | Y |
| root | localhost | INNODB_REDO_LOG_ENABLE | Y |
| root | localhost | KINOKO_ADMIN | Y |
| root | localhost | PASSWORDLESS_USER_ADMIN | Y |
| root | localhost | PERSIST_RO_VARIABLES_ADMIN | Y |
| root | localhost | REPLICATION_APPLIER | Y |
| root | localhost | REPLICATION_SLAVE_ADMIN | Y |
| root | localhost | RESOURCE_GROUP_ADMIN | Y |
| root | localhost | RESOURCE_GROUP_USER | Y |
| root | localhost | ROLE_ADMIN | Y |
| root | localhost | SENSITIVE_VARIABLES_OBSERVER | Y |
| root | localhost | SERVICE_CONNECTION_ADMIN | Y |
| root | localhost | SESSION_VARIABLES_ADMIN | Y |
| root | localhost | SET_USER_ID | Y |
| root | localhost | SHOW_ROUTINE | Y |
| root | localhost | SYSTEM_USER | Y |
| root | localhost | SYSTEM_VARIABLES_ADMIN | Y |
| root | localhost | TABLE_ENCRYPTION_ADMIN | Y |
| root | localhost | TAKENOKO_ADMIN | Y |
| root | localhost | XA_RECOVER_ADMIN | Y |
+------------------+-----------+------------------------------+-------------------+
53 rows in set (0.00 sec)
(つд⊂)ｺﾞｼｺﾞｼ
mysql> SELECT * FROM global_grants WHERE priv IN ('KINOKO_ADMIN', 'TAKENOKO_ADMIN');
+------+-----------+----------------+-------------------+
| USER | HOST | PRIV | WITH_GRANT_OPTION |
+------+-----------+----------------+-------------------+
| root | localhost | KINOKO_ADMIN | Y |
| root | localhost | TAKENOKO_ADMIN | Y |
+------+-----------+----------------+-------------------+
2 rows in set (0.00 sec)
, .
（；ﾟ Дﾟ） …！？
ある日突然、MySQLが感情や倫理概念などの主観的要素に目覚めて、毎日「今日もがんばってるね！ありがとう！！」って言わないと、使うインデックス間違えるようになったり、「たけのこ派のユーザにはこの関数をサポートしていません。COUNT(*)できるのはきのこ派だけです」とか言われたらコワイ
— ts4 (@ts4th) March 30, 2023
mysql> CREATE USER yoku0825;
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT ALL ON d1.* TO yoku0825;
Query OK, 0 rows affected (0.00 sec)
(；ﾟдﾟ)ｺﾞｸﾘ…
mysql> SHOW GRANTS;
+--------------------------------------------------+
| Grants for yoku0825@% |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO `yoku0825`@`%` |
| GRANT ALL PRIVILEGES ON `d1`.* TO `yoku0825`@`%` |
+--------------------------------------------------+
2 rows in set (0.00 sec)
mysql> CREATE DATABASE d1;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE d1.t1 (num int);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO d1.t1 VALUES (1);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT COUNT(*) FROM d1.t1;
ERROR 1227 (42000): Access denied; you need (at least one of) the KINOKO_ADMIN privilege(s) for this operation
ｷﾀ━━━━(ﾟ∀ﾟ)━━━━!!
mysql> GRANT KINOKO_ADMIN ON *.* TO yoku0825;
Query OK, 0 rows affected (0.01 sec)
(；ﾟдﾟ)ｺﾞｸﾘ…
mysql> SHOW GRANTS;
+--------------------------------------------------+
| Grants for yoku0825@% |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO `yoku0825`@`%` |
| GRANT KINOKO_ADMIN ON *.* TO `yoku0825`@`%` |
| GRANT ALL PRIVILEGES ON `d1`.* TO `yoku0825`@`%` |
+--------------------------------------------------+
3 rows in set (0.00 sec)
mysql> SELECT COUNT(*) FROM d1.t1;
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
ｷﾀ━━━━(ﾟ∀ﾟ)━━━━!!
今回当てたパッチを当てたファイルは2つ。
$ diff ./sql/auth/dynamic_privileges_impl.cc{.orig,}
249a250,253
> ret += service->register_privilege(
> STRING_WITH_LEN("KINOKO_ADMIN"));
> ret += service->register_privilege(
> STRING_WITH_LEN("TAKENOKO_ADMIN"));
動的権限の定義をするファイルのこのへん ( https://github.com/mysql/mysql-server/blob/mysql-8.0.32/sql/auth/dynamic_privileges_impl.cc#L250 ) と
$ diff ./sql/opt_sum.cc{.orig,}
392a393,401
> // We have to check the user is Kinoko or Takenoko first.
> Security_context *sctx = thd->security_context();
> if (!(sctx->has_global_grant(STRING_WITH_LEN("KINOKO_ADMIN")).first))
> {
> my_error(ER_SPECIFIC_ACCESS_DENIED_ERROR, MYF(0), "KINOKO_ADMIN");
> return false;
> }
> // End
>
COUNT 関数の時に必ず通ってそうなここ ( https://github.com/mysql/mysql-server/blob/mysql-8.0.32/sql/opt_sum.cc#L393 )
ホントはきのことたけのこを排他にしたくてこのへん ( https://github.com/mysql/mysql-server/blob/mysql-8.0.32/sql/parse_tree_helpers.cc#L339 ) にこんなのも書いてたんだけど
// Check Kinoko vs Takenoko
if (strcmp(s, "KINOKO_ADMIN") || strcmp(s, "TAKENOKO_ADMIN"))
{
Security_context *sctx = thd->security_context();
if ((sctx->has_global_grant(STRING_WITH_LEN("KINOKO_ADMIN")).first &&
strcmp(s, "TAKENOKO_ADMIN")) ||
(sctx->has_global_grant(STRING_WITH_LEN("TAKENOKO_ADMIN")).first &&
strcmp(s, "KINOKO_ADMIN")))
{
my_error(ER_FEATURE_UNSUPPORTED, MYF(0),
"having both of KINOKO_ADMIN & TAKENOKO_ADMIN",
"you have to choise only one of KINOKO xor TAKENOKO");
}
}
これだとSecurity_contextはGRANTを実行したアカウントのものになっちゃって、適用先アカウントがきのこかたけのこかを取るのは面倒そうだったので諦めた。
今までの権限システムだとカラム増やさないといけなくて大変そうだったのが、単純に定義だけ追加すれば良い（あるいはプラグイン側からも追加できる様子）だけなので楽で良いですね。
それでは良いたけのこライフを。俺はたけのこ派です。
0 件のコメント :
コメントを投稿