MySQL道普請便り
第161回 アカウントにコメントや属性を紐づける
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": "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)
また,
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)
そして,
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 '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_
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": "test_user@example.com", "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", "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.
バックナンバー
MySQL道普請便り
- 第171回 MyDumperを使ってみよう[その2]
- 第170回 MySQLのキーワードと予約語をテーブルに使用したい場合
- 第169回 pt-variable-advisorを使ってみよう
- 第168回 MyDumperを使ってみよう[その1]
- 第167回 M1搭載MacのDockerでMySQLを動かしてみる
- 第166回 Performance_schema関数
- 第165回 MySQLの圧縮いろいろ
- 第164回 admin_address,admin_portを設定しよう
- 第163回 sysbench 1.0を使ってMySQLの負荷テストをする
- 第162回 mysqlクライアントの履歴を確認してみる