Last week I received an urgent email asking to move the time of my performance review up a few hours. And the email sender was not in my management structure and was someone unfamiliar to me. Being a former Xerox employee, I checked my others emails for notice of a reorganization. Then I realized the person in question was seeking the other David Stokes at Oracle. Yup two of us with the same name at the same company. Whew!
Coincidentally MySQL 8.0.21 added a new ability that allows you to store information about an account similar to the way the GCOS field is supposed to be used for in the UNIX/Linux world. Back in time many decades ago, account names were limited in length and the GCOS field was populated with the account's user name, office room number, office phone number, and some other relevant information. This was the way the system administrator could contact the individual using the account directly.
The mysql.user table has evolved to 51 fields but only as of 8.0.21 did you have a way to record who was actually using the account. Or let others know why the accounts exists or other meta data about the account.
For an example I created an account and added a comment about the account.
CREATE USER 'dstokes'@'localhost'
IDENTIFIED by 'S3cr3t!'
COMMENT '{ "user" : "Dave" }';
And the information is stored in a column named User_attributes.
select User_attributes from user WHERE User='dstokes'\G
*************************** 1. row ***************************
User_attributes: {"metadata": {"comment": "{ \"user\" : \"Dave\" }"}}
2 rows in set (0.0010 sec)
or you can use the word ATTRIBUTE in the place of the keyword COMMENT. We do use 'comment' someplace else in the world of Structured Query Language so I would personally prefer ATTRIBUTE in this case.
CREATE USER 'jack'@'localhost'
IDENTIFIED BY 'XXX123!!!'
ATTRIBUTE '{ "test" : "yes" }';
Cool, eh?
ALTER USER supporst both keywords.
alter user 'jack'@'localhost'
ATTRIBUTE '{ "foo" : "Example" }';
SELECT User_attributes from user where User='jack';
+-------------------------------------------------+
| User_attributes |
+-------------------------------------------------+
| {"metadata": {"foo": "Example", "test": "yes"}} |
+-------------------------------------------------+
And please note that reusing a key updates the content. Here I reuse 'foo' and the contents are updated.
alter user 'jack'@'localhost'
ATTRIBUTE '{ "foo" : "Example revised" }';
SELECT User_attributes from user where User='jack';
+---------------------------------------------------------+
| User_attributes |
+---------------------------------------------------------+
| {"metadata": {"foo": "Example revised", "test": "yes"}} |
+---------------------------------------------------------+
1 row in set (0.0005 sec)
Please remember that the JSON data type column is limited to a gig of data so you can not go too crazy recording information. But please do start adding some account meta data to help identify your accounts.
