什么是用户属性
用户属性是一个由一个或多个键值对组成的json对象,它是在使用create user创建用户时设置的,通过添加语句attribute 'json_object'。json_object必须是一个有效的json对象。
mysql将所有与用户相关的数据存储在mysql中的user表,但是我们没有任何列来为用户添加任何属性。有了这个用户属性的新特性,我们实际上可以为用户添加一些额外的细节作为属性,这对于获取用户的一些额外细节非常有用,比如手机号码、职位、国家等。
用户属性功能是从mysql 8.0.21引入的,它附带了一个来自information_schema的user_attributes表,该表提供了关于用户说明和用户属性的信息它的值来自系统表mysql.user。
user_attributes是一个非标准的information_schema表,包含三列:
·user:attribute列值应用的帐户的用户名部分。
·host:attribute列值应用的帐户的主机名部分。
·attribute:json对象表示形式。用户注释和用户属性,或者两者都属于user和host列指定的帐户。属性显示与使用create user…attribute…或alter user…attribute…语句设置的完全相同。
创建用户属性
mysql> CREATE USER 'abc'@'localhost' IDENTIFIED BY 'Pwtests1' ATTRIBUTE '{"fname": "myabc", "lname": "abce", "phn": "10086086"}';
Query OK, 0 rows affected (0.32 sec)
检索用户属性
mysql> SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES where user='abc';
+------+-----------+--------------------------------------------------------+
| USER | HOST | ATTRIBUTE |
+------+-----------+--------------------------------------------------------+
| abc | localhost | {"phn": "10086086", "fname": "myabc", "lname": "abce"} |
+------+-----------+--------------------------------------------------------+
缺省情况下,attribute列为null
mysql> CREATE USER 'abc'@'localhost' IDENTIFIED BY 'Pwtests1';
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES where user='abc';
+------+-----------+-----------+
| USER | HOST | ATTRIBUTE |
+------+-----------+-----------+
| abc | localhost | NULL |
+------+-----------+-----------+
MySQL 8.0.22之前,任何用户都可以访问表USER_ATTRIBUTES;自MySQL 8.0.22开始,访问必须满足如下条件:
·当前线程是replica线程
·mysql server是以--skip-grant-tables启动的
·用户对系统表mysql.user有update或select权限
·用户必须具有create user和system_user权限
修改用户属性
mysql> SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES where user='abc';
+------+-----------+--------------------------------------------------------+
| USER | HOST | ATTRIBUTE |
+------+-----------+--------------------------------------------------------+
| abc | localhost | {"phn": "10086086", "fname": "myabc", "lname": "abce"} |
+------+-----------+--------------------------------------------------------+
1 row in set (0.00 sec)
mysql> ALTER USER 'abc'@'localhost' ATTRIBUTE '{"place": "hzz", "Ara": "bbc"}';
Query OK, 0 rows affected (0.05 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES where user='abc';
+------+-----------+--------------------------------------------------------------------------------------+
| USER | HOST | ATTRIBUTE |
+------+-----------+--------------------------------------------------------------------------------------+
| abc | localhost | {"Ara": "bbc", "phn": "10086086", "fname": "myabc", "lname": "abce", "place": "hzz"} |
+------+-----------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
移除某个属性,将对应的key设置为null(null必须是小写,且不加引号)
mysql> ALTER USER 'abc'@'localhost' ATTRIBUTE '{"phn": null, "place": null, "lname": null}';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES where user='abc';
+------+-----------+----------------------------------+
| USER | HOST | ATTRIBUTE |
+------+-----------+----------------------------------+
| abc | localhost | {"Ara": "bbc", "fname": "myabc"} |
+------+-----------+----------------------------------+
1 row in set (0.00 sec)