Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.7Mb
PDF (A4) - 40.7Mb
PDF (RPM) - 40.3Mb
HTML Download (TGZ) - 10.7Mb
HTML Download (Zip) - 10.7Mb
HTML Download (RPM) - 9.3Mb
Man Pages (TGZ) - 243.5Kb
Man Pages (Zip) - 348.7Kb
Info (Gzip) - 3.9Mb
Info (Zip) - 3.9Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  INFORMATION_SCHEMA Tables  /  The INFORMATION_SCHEMA USER_ATTRIBUTES Table

25.46 The INFORMATION_SCHEMA USER_ATTRIBUTES Table

The USER_ATTRIBUTES table (available as of MySQL 8.0.21) provides information about user comments and user attributes. It takes its values from the mysql.user system table.

The USER_ATTRIBUTES table has these columns:

  • USER

    The user name portion of the account to which the ATTRIBUTE column value applies.

  • HOST

    The host name portion of the account to which the ATTRIBUTE column value applies.

  • ATTRIBUTE

    The user comment, user attribute, or both belonging to the account specified by the USER and HOST columns. The value is in JSON object notation. 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 comment as the key.

    For example, the statement CREATE USER 'bill'@'localhost' COMMENT 'A comment' ATTRIBUTE '{"foo": "bar", "bazz": "fazz"}' adds the following row to the USER_ATTRIBUTES table:

    +------+-----------+--------------------------------------------------------+
    | USER | HOST      | ATTRIBUTE                                              |
    +------+-----------+--------------------------------------------------------+
    | bill | localhost | {"foo": "bar", "bazz": "fazz", "comment": "A comment"} |
    +------+-----------+--------------------------------------------------------+

Notes

  • USER_ATTRIBUTES is a nonstandard INFORMATION_SCHEMA table.

  • To obtain only the user comment for a given user as an unquoted string, you can employ a query such as this one:

    mysql> SELECT ATTRIBUTE->>"$.comment" AS Comment
        ->     FROM INFORMATION_SCHEMA.USER_ATTRIBUTES
        ->     WHERE USER='bill' AND HOST='localhost';
    +-----------+
    | Comment   |
    +-----------+
    | A comment |
    +-----------+

    Similarly, you can obtain the unquoted value for a given user attribute using its key.

  • Prior to MySQL 8.0.22, USER_ATTRIBUTES contents are accessible by anyone. As of MySQL 8.0.22, USER_ATTRIBUTES contents are accessible as follows:

    • All rows are accessible if:

      • The current thread is a replica thread.

      • The access control system has not been initialized (for example, the server was started with the --skip-grant-tables option).

      • The currently authenticated account has the UPDATE or SELECT privilege for the mysql.user system table.

      • The currently authenticated account has the CREATE USER and SYSTEM_USER privileges.

    • Otherwise, the currently authenticated account can see the row for that account. Additionally, if the account has the CREATE USER privilege but not the SYSTEM_USER privilege, it can see rows for all other accounts that do not have the SYSTEM_USER privilege.

For more information about specifying account comments and attributes, see Section 13.7.1.3, “CREATE USER Statement”.