MySQL道普請便り

第161回　アカウントにコメントや属性を紐づける

MySQL, アカウント, コメント, ユーザー属性

この記事を読むのに必要な時間：およそ 6 分

MySQL 8.0では，アカウント周りのさまざまな強化機能が追加されてきました。この連載ではこれまでも，MySQLのROLE［その1］やパスワード検証必須ポリシーとログイン失敗時のアカウントの一時ロックなどで，MySQL 8.0でのアカウント機能を見ていきました。

さらに，MySQL 8.0.21ではアカウント作成時にコメントやユーザー属性を追加できるようになっています。この機能を利用することで，目的別や何かしらの理由で作成されたアカウントの判断材料として利用することができるかもしれません。

今回は，MySQL 8.0.21で追加されたアカウント作成時のコメントやユーザー属性値の追加について見ていきます。なお，今回利用するMySQLはバージョン8.0.26なります。

アカウントにコメントを付ける

アカウントに紐づくコメントを作成するには，CREATE USER構文にCOMMENT 'コメントの内容'を記述することで紐づけることができます。

mysql> CREATE USER 'test_user'@'localhost' COMMENT 'some comment';
Query OK, 0 rows affected (0.00 sec)

作成されたコメントは，information_schemaのuser_attributesテーブル内にあるattributeカラムの中に，longtext型の情報として保持されます。

mysql> SELECT * FROM information_schema.user_attributes WHERE user = 'test_user' and host = 'localhost';
+-----------+-----------+-----------------------------+
| USER      | HOST      | ATTRIBUTE                   |
+-----------+-----------+-----------------------------+
| test_user | localhost | {"comment": "some comment"} |
+-----------+-----------+-----------------------------+
1 row in set (0.01 sec)

コメントはALTER USER構文で変更することができます。また，DROP USERでユーザーを削除した際は，コメントも一緒にuser_attributeから削除されます。

mysql> ALTER USER 'test_user'@localhost COMMENT 'change comment';
Query OK, 0 rows affected (0.00 sec)

mysql]> SELECT * FROM information_schema.user_attributes WHERE user = 'test_user' and host = 'localhost';
+-----------+-----------+-------------------------------+
| USER      | HOST      | ATTRIBUTE                     |
+-----------+-----------+-------------------------------+
| test_user | localhost | {"comment": "change comment"} |
+-----------+-----------+-------------------------------+
1 row in set (0.00 sec)

mysql> DROP USER 'test_user'@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM information_schema.user_attributes WHERE user = 'test_user' and host = 'localhost';
Empty set (0.00 sec)

アカウントにユーザー属性を付ける

続いて属性の付与について見ていきましょう。

ユーザー属性を付与するには，CREATE USER構文にATTRIBUTE '{KEYとなる値: VALUEとなる値}'のように，KEYとVALUEからなるJSONオブジェクトを付与します。

mysql> CREATE USER 'test_user'@'localhost' ATTRIBUTE '{"test_key": "test_value"}';
Query OK, 0 rows affected (0.01 sec)

実際に作成された属性はコメントと同様に，information_schemaのuser_attributesテーブルで確認することができます。

mysql> SELECT * FROM information_schema.user_attributes WHERE user = 'test_user' and host = 'localhost';
+-----------+-----------+----------------------------+
| USER      | HOST      | ATTRIBUTE                  |
+-----------+-----------+----------------------------+
| test_user | localhost | {"test_key": "test_value"} |
+-----------+-----------+----------------------------+
1 row in set (0.00 sec)

また，カンマ区切りで複数のKEYとVALUEを定義することで複数の属性を保持させることができます。このあたりはJSONと同じです。

mysql> CREATE USER 'test_user'@'localhost' ATTRIBUTE '{"test_key": "test_value", "email": "test_user@example.com", "usage": "test account"}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM information_schema.user_attributes WHERE user = 'test_user' and host = 'localhost';
+-----------+-----------+---------------------------------------------------------------------------------------+
| USER      | HOST      | ATTRIBUTE                                                                             |
+-----------+-----------+---------------------------------------------------------------------------------------+
| test_user | localhost | {"email": "test_user@example.com", "usage": "test account", "test_key": "test_value"} |
+-----------+-----------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

また，コメントと同様にALTER USER構文で属性値の変更，DROP USERでユーザーを削除したときには一緒にuser_attributeのデータも削除されます。

mysql> ALTER USER 'test_user'@'localhost' ATTRIBUTE '{"test_key": "test_value", "email": "test_user@example.com"}';
Query OK, 0 rows affected (0.00 sec)

mysql> DROP USER 'test_user'@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM information_schema.user_attributes WHERE user = 'test_user' and host = 'localhost';
Empty set (0.00 sec)

そして，JSON型の扱いと同様にカラムパス演算子やインライン演算子を利用することで，特定の属性値のみを取り出すことも可能です。

mysql> SELECT concat(user,'@','host'), attribute->>"$.email" as "email", attribute->>"$.usage" as "usage" FROM information_schema.user_attributes WHERE user = 'test_user' and host = 'localhost';
+-------------------------+-----------------------+--------------+
| concat(user,'@','host') | email                 | usage        |
+-------------------------+-----------------------+--------------+
| test_user@host          | test_user@example.com | test account |
+-------------------------+-----------------------+--------------+
1 row in set (0.00 sec)

コメントとユーザー属性

では，すでにコメントがあるアカウントに対してユーザー属性を付与するとどうなるのでしょうか。CREATE USER構文でコメントを付与し，ALTER USER構文でユーザー属性を付与してみます。

> CREATE USER 'test_user'@localhost COMMENT 'test comment';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM information_schema.user_attributes WHERE user = 'test_user' and host = 'localhost';
+-----------+-----------+-----------------------------+
| USER      | HOST      | ATTRIBUTE                   |
+-----------+-----------+-----------------------------+
| test_user | localhost | {"comment": "test comment"} |
+-----------+-----------+-----------------------------+
1 row in set (0.00 sec)

mysql> ALTER USER 'test_user'@'localhost' ATTRIBUTE '{"test_key": "test_value", "email": "test_user@example.com", "usage": "test account"}';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM information_schema.user_attributes WHERE user = 'test_user' and host = 'localhost';
+-----------+-----------+------------------------------------------------------------------------------------------------------------------+
| USER      | HOST      | ATTRIBUTE                                                                                                        |
+-----------+-----------+------------------------------------------------------------------------------------------------------------------+
| test_user | localhost | {"email": "test_user@example.com", "usage": "test account", "comment": "test comment", "test_key": "test_value"} |
+-----------+-----------+------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

やはりユーザー属性で記述した内容で上書きされて変更されてしまいました。user_attributeのテーブル定義を見てみましょう。

mysql> SHOW CREATE TABLE information_schema.user_attributes\G
*************************** 1. row ***************************
                View: USER_ATTRIBUTES
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `USER_ATTRIBUTES` AS select `mysql`.`user`.`User` AS `USER`,`mysql`.`user`.`Host` AS `HOST`,json_unquote(json_extract(`mysql`.`user`.`User_attributes`,'$.metadata')) AS `ATTRIBUTE` from `mysql`.`user` where (0 <> can_access_user(`mysql`.`user`.`User`,`mysql`.`user`.`Host`))
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

user_attributesの実態はVIEWであり，データはmysqlスキーマのUSERテーブルにあるuser_attributesのmetadataを参照しているようです。json_extractを使ってmetadataの中身を取り出しています。

mysql> SELECT user_attributes FROM mysql.user WHERE user = 'test_user' and host = 'localhost';
+--------------------------------------------------------------------------------------------------------------------------------+
| user_attributes                                                                                                                |
+--------------------------------------------------------------------------------------------------------------------------------+
| {"metadata": {"email": "test_user@example.com", "usage": "test account", "comment": "test comment", "test_key": "test_value"}} |
+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

そのため，mysqlスキーマのUSERテーブルの方のuser_attributesカラムからmetadataの中身を更新した場合は，information_schema側のuser_attributesも変更されます。

下の例では，もともあったemailの属性値（test_user@example.com）をmysqlスキーマ側のuserテーブルを直接更新して，test@example.comに書き換えています。

mysql> UPDATE user SET user_attributes = JSON_REPLACE(user_attributes, "$.metadata.email", "test@example.com") WHERE user = 'test_user' and host = 'localhost';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM information_schema.user_attributes WHERE user = 'test_user' and host = 'localhost';
+-----------+-----------+-------------------------------------------------------------------------------------------------------------+
| USER      | HOST      | ATTRIBUTE                                                                                                   |
+-----------+-----------+-------------------------------------------------------------------------------------------------------------+
| test_user | localhost | {"email": "test@example.com", "usage": "test account", "comment": "test comment", "test_key": "test_value"} |
+-----------+-----------+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

まとめ

今回はMySQL 8.0.21で新しく追加されたユーザーコメントとユーザー属性の機能について見ていきました。1つのデータベースで多くのアカウントを運用する場合では，使用用途やアカウント種別などを載せることで，普段の運用をサポートしてくれることがあるかもしれません。ぜひ，利用を検討してみてください。

MySQL, アカウント, コメント, ユーザー属性

著者プロフィール

深町日出海（ふかまちひでみ）

GMOメディア株式会社のデータベースエンジニア。主にOracleとMySQLを担当。得意なプログラム言語はJava。MySQLの好きなところはTABLEやINDEXの識別子に64byteまで使えるところ。

Twitter：@lhfukamachi