MySQL 8.
さらに、
今回は、
アカウントにコメントを付ける
アカウントに紐づくコメントを作成するには、COMMENT 'コメントの内容'を記述することで紐づけることができます。
mysql> CREATE USER 'test_user'@'localhost' COMMENT 'some 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": "some comment"} |
+-----------+-----------+-----------------------------+
1 row in set (0.01 sec)
コメントはALTER USER構文で変更することができます。また、
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)
アカウントにユーザー属性を付ける
続いて属性の付与について見ていきましょう。
ユーザー属性を付与するには、'{KEYとなる値: VALUEとなる値}'のように、
mysql> CREATE USER 'test_user'@'localhost' ATTRIBUTE '{"test_key": "test_value"}';
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 | {"test_key": "test_value"} |
+-----------+-----------+----------------------------+
1 row in set (0.00 sec)
また、
mysql> CREATE USER 'test_user'@'localhost' ATTRIBUTE '{"test_key": "test_value", "email": "[email protected]", "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": "[email protected]", "usage": "test account", "test_key": "test_value"} |
+-----------+-----------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
また、
mysql> ALTER USER 'test_user'@'localhost' ATTRIBUTE '{"test_key": "test_value", "email": "[email protected]"}';
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)
そして、
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 | [email protected] | test account | +-------------------------+-----------------------+--------------+ 1 row in set (0.00 sec)
コメントとユーザー属性
では、
> 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": "[email protected]", "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": "[email protected]", "usage": "test account", "comment": "test comment", "test_key": "test_value"} |
+-----------+-----------+------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
やはりユーザー属性で記述した内容で上書きされて変更されてしまいました。user_
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_
mysql> SELECT user_attributes FROM mysql.user WHERE user = 'test_user' and host = 'localhost';
+--------------------------------------------------------------------------------------------------------------------------------+
| user_attributes |
+--------------------------------------------------------------------------------------------------------------------------------+
| {"metadata": {"email": "[email protected]", "usage": "test account", "comment": "test comment", "test_key": "test_value"}} |
+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
そのため、
下の例では、
mysql> UPDATE user SET user_attributes = JSON_REPLACE(user_attributes, "$.metadata.email", "[email protected]") 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": "[email protected]", "usage": "test account", "comment": "test comment", "test_key": "test_value"} | +-----------+-----------+-------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)
まとめ
今回はMySQL 8.