In this blog post, we’ll look at MySQL 8 user attributes and how we can use them.
What is the user attribute?
A user attribute is a JSON object made up of one or more key-value pairs, and it is set while creating the user with CREATE USER and by including ATTRIBUTE ‘json_object’. json_object must be a valid JSON object (should be key-value pairs).
We all know MySQL stores all user-related data in mysql.user table, but we don’t have any column to add any attributes for the user. With this new feature of user attributes, we can actually add some additional details as an attribute for the user, which is pretty useful in getting some additional details of the user, such as mobile number, job title, country, etc.
The user attribute feature is available from MySQL 8.0.21, and it comes with a USER_ATTRIBUTES table from information_schema, which provides information about the user comments and user attributes. It takes its values from the mysql.user system table.
The USER_ATTRIBUTES is a nonstandard INFORMATION_SCHEMA table containing three columns:
USER:
The user name portion of the account to which the ATTRIBUTE column value applies.
HOST:
The hostname portion of the account to which the ATTRIBUTE column value applies.
ATTRIBUTE:
The value is in JSON object notation. The user comment and user attribute, or both belonging to the account specified by the USER and HOST columns. Attributes are shown exactly as set using a CREATE USER … ATTRIBUTE … or ALTER USER … ATTRIBUTE … statement. The user comment is shown as a key-value pair having a comment as the key.
How to create a user with attributes
Let’s see how we can create a user with attributes, by example. I have created a user Leo by adding the attributes in key-value pairs.
1 2 | mysql> CREATE USER 'Leo'@'localhost' IDENTIFIED BY 'P@sword1' ATTRIBUTE '{"fname": "Sammy", "lname": "Jane", "phn": "88275839"}'; Query OK, 0 rows affected (0.32 sec) |
How to retrieve information about user attributes
We can find all the attributes of a user by a simple select statement on the INFORMATION_SCHEMA.USER_ATTRIBUTES.
1 2 3 4 5 6 | mysql> SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES where user='Leo'; +------+-----------+--------------------------------------------------------+ | USER | HOST | ATTRIBUTE | +------+-----------+--------------------------------------------------------+ | Leo | localhost | {"phn": "88275839", "fname": "Sammy", "lname": "Jane"} | +------+-----------+--------------------------------------------------------+ |
By default, the ATTRIBUTE column is null if you don’t pass any attributes while creating the user.
1 2 3 4 5 6 7 8 9 | mysql> CREATE USER 'Kal'@'localhost' IDENTIFIED BY 'P@sword1'; Query OK, 0 rows affected (0.02 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES where user='Kal'; +------+-----------+-----------+ | USER | HOST | ATTRIBUTE | +------+-----------+-----------+ | Kal | localhost | NULL | +------+-----------+-----------+ |
Before MySQL 8.0.22, USER_ATTRIBUTES contents were accessible by anyone. As of MySQL 8.0.22, USER_ATTRIBUTES contents are accessible as follows:
To access all rows of USER_ATTRIBUTES table, one should satisfy these rules:
- The current thread is a replica thread.
- If the server was started with the –skip-grant-tables option.
- The user should have the UPDATE or SELECT privilege for the mysql.user system table.
- The user should have the CREATE USER and SYSTEM_USER privileges.
How to modify the user attributes
The user attributes can be modified by using the ALTER statement by means of ATTRIBUTE option.
Let’s see an example of modifying attributes to the user.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | mysql> SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES where user='Leo'; +------+-----------+--------------------------------------------------------+ | USER | HOST | ATTRIBUTE | +------+-----------+--------------------------------------------------------+ | Leo | localhost | {"phn": "88275839", "fname": "Sammy", "lname": "Jane"} | +------+-----------+--------------------------------------------------------+ 1 row in set (0.00 sec) mysql> ALTER USER 'Leo'@'localhost' ATTRIBUTE '{"place": "Irn", "Ara": "tin"}'; Query OK, 0 rows affected (0.05 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES where user='Leo'; +------+-----------+--------------------------------------------------------------------------------------+ | USER | HOST | ATTRIBUTE | +------+-----------+--------------------------------------------------------------------------------------+ | Leo | localhost | {"Ara": "tin", "phn": "88275839", "fname": "Sammy", "lname": "Jane", "place": "Irn"} | +------+-----------+--------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) |
To remove an attribute from the user, set the key to null (must be lowercase and unquoted) using an alter statement. You can see below it removed all the key value pairs which are set to null.
1 2 3 4 5 6 7 8 9 10 | mysql> ALTER USER 'Leo'@'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='Leo'; +------+-----------+----------------------------------+ | USER | HOST | ATTRIBUTE | +------+-----------+----------------------------------+ | Leo | localhost | {"Ara": "tin", "fname": "Sammy"} | +------+-----------+----------------------------------+ 1 row in set (0.00 sec) |
Conclusion
The user attribute feature in MySQL 8.0.21 allows database administrators to assign custom key-value pairs to MySQL user accounts. These attributes can be used to store additional information about users, such as their department, job title, or contact information. One of the main benefits of this feature is that it provides a more flexible and extensible way to manage user accounts in MySQL. Overall, the user attribute feature in MySQL provides a powerful new feature for database administrators to manage and secure their MySQL installations.
Percona Distribution for MySQL is the most complete, stable, scalable, and secure open-source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!
Try Percona Distribution for MySQL today!
