| Bug #91981 | Inconsistent user@host definitions for definer/grantor/grantee columns | ||
|---|---|---|---|
| Submitted: | 11 Aug 2018 13:02 | Modified: | 22 Aug 2018 11:02 |
| Reporter: | Paul Campbell | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Security: Privileges | Severity: | S3 (Non-critical) |
| Version: | 5.6, 5.7, 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[13 Aug 2018 12:14]
MySQL Verification Team
Hi, I do not see what is the version and release that you are using. Please try 8.0.12 and let us know whether it is any different. Also, our documentation clearly states what are the sizes of the columns and also states that special characters should not be used.
[13 Aug 2018 12:14]
MySQL Verification Team
Hi, I do not see what is the version and release that you are using. Please try 8.0.12 and let us know whether it is any different. Also, our documentation clearly states what are the sizes of the columns and also states that special characters should not be used.
[13 Aug 2018 19:12]
Paul Campbell
Hope the following helps to explain the issue.
The inconsistency described, i.e. different column sizes for the user@host across different tables and inconsistent application of quoting exists in all of the following versions to which I have access. Sorry, don't have 8.0.12
8.0.11 MySQL Community Server - GPL
5.7.23-0ubuntu0.18.04.1-log (Ubuntu)
5.6.38 MySQL Community Server (GPL)
5.5.58 MySQL Community Server (GPL)
Column sizes are documented for the mysql schema, I don't know the documentation on columns sizes for privilege tables in information_schema is, nonetheless one derives from the other yet you make different provision for user and host (in mysql.tables_priv), and user@host (in information_schema.table_privileges)
mysql> describe mysql.tables_priv;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Grantor | char(93) | NO | MUL | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Table_priv | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') | NO | | | |
| Column_priv | set('Select','Insert','Update','References') | NO | | | |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.29 sec)
mysql> describe information_schema.table_privileges;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| GRANTEE | varchar(81) | NO | | | |
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| PRIVILEGE_TYPE | varchar(64) | NO | | | |
| IS_GRANTABLE | varchar(3) | NO | | | |
+----------------+--------------+------+-----+---------+-------+
6 rows in set (0.04 sec)
So, mysql.tables_priv allows host (60) and user (32), when these columns are concatenated as grantee in information_schema.table_privileges only 81 characters total is shown and 5 of these are used for quotes and @. You can only display 76 of the 92 possible characters for the user and host combination.
As regards quoting, the documentation says the following ...
https://dev.mysql.com/doc/refman/8.0/en/account-names.html
<snip>
The user name and host name need not be quoted if they are legal as unquoted identifiers. Quotes are necessary to specify a user_name string containing special characters (such as space or -), or a host_name string containing special characters or wildcard characters (such as . or %); for example, 'test-user'@'%.com'.
Quote user names and host names as identifiers or as strings, using either backticks (`), single quotation marks ('), or double quotation marks ("). For string-quoting and identifier-quoting guidelines, see Section 9.1.1, “String Literals”, and Section 9.2, “Schema Object Names”.
The user name and host name parts, if quoted, must be quoted separately. That is, write 'me'@'localhost', not 'me@localhost'; the latter is actually equivalent to 'me@localhost'@'%'.
</snip>
So, use of 'special' characters is specifically mentioned, particularly in the context of wildcard host names where, rather than discouraged, they are essential.
You quote table_privileges.grantee in information_schema but don't quote tables_priv.grantor in mysql, I think you should use quotes consistently for user@host style columns.
[13 Aug 2018 19:13]
Paul Campbell
Hope the following helps to explain the issue.
The inconsistency described, i.e. different column sizes for the user@host across different tables and inconsistent application of quoting exists in all of the following versions to which I have access. Sorry, don't have 8.0.12
8.0.11 MySQL Community Server - GPL
5.7.23-0ubuntu0.18.04.1-log (Ubuntu)
5.6.38 MySQL Community Server (GPL)
5.5.58 MySQL Community Server (GPL)
Column sizes are documented for the mysql schema, I don't know the documentation on columns sizes for privilege tables in information_schema is, nonetheless one derives from the other yet you make different provision for user and host (in mysql.tables_priv), and user@host (in information_schema.table_privileges)
mysql> describe mysql.tables_priv;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Grantor | char(93) | NO | MUL | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Table_priv | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') | NO | | | |
| Column_priv | set('Select','Insert','Update','References') | NO | | | |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.29 sec)
mysql> describe information_schema.table_privileges;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| GRANTEE | varchar(81) | NO | | | |
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| PRIVILEGE_TYPE | varchar(64) | NO | | | |
| IS_GRANTABLE | varchar(3) | NO | | | |
+----------------+--------------+------+-----+---------+-------+
6 rows in set (0.04 sec)
So, mysql.tables_priv allows host (60) and user (32), when these columns are concatenated as grantee in information_schema.table_privileges only 81 characters total is shown and 5 of these are used for quotes and @. You can only display 76 of the 92 possible characters for the user and host combination.
As regards quoting, the documentation says the following ...
https://dev.mysql.com/doc/refman/8.0/en/account-names.html
<snip>
The user name and host name need not be quoted if they are legal as unquoted identifiers. Quotes are necessary to specify a user_name string containing special characters (such as space or -), or a host_name string containing special characters or wildcard characters (such as . or %); for example, 'test-user'@'%.com'.
Quote user names and host names as identifiers or as strings, using either backticks (`), single quotation marks ('), or double quotation marks ("). For string-quoting and identifier-quoting guidelines, see Section 9.1.1, “String Literals”, and Section 9.2, “Schema Object Names”.
The user name and host name parts, if quoted, must be quoted separately. That is, write 'me'@'localhost', not 'me@localhost'; the latter is actually equivalent to 'me@localhost'@'%'.
</snip>
So, use of 'special' characters is specifically mentioned, particularly in the context of wildcard host names where, rather than discouraged, they are essential.
You quote table_privileges.grantee in information_schema but don't quote tables_priv.grantor in mysql, I think you should use quotes consistently for user@host style columns.
[14 Aug 2018 12:14]
MySQL Verification Team
Hi, This seems to be a bug. However, this is not a bug of high priority. Not only that, but I doubt that any GA version will get the changes necessary, as the amount of changes is more suitable for some newer version. Verified as reported.
[14 Aug 2018 12:15]
MySQL Verification Team
Added missing versions.
[22 Aug 2018 11:02]
Paul Campbell
A little more info on this Querying the information_schema privilege tables where a user record exists with `grantee` exceeding the allowed 81 characters results in this error ERROR 1406 (22001): Data too long for column 'GRANTEE' at row 1
[9 May 2023 18:59]
Pratiksha Ratnaparkhi
Hi, If I want to show PRIVILEGE_TYPE just for a particular GRANTEE then I am not able to select it in WHERE clause. like ex., select * from information_schema.user_privileges where 'GRANTEE'='azureuser'; select * from information_schema.user_privileges where 'GRANTEE'='azureuser@%'; both didn't work. Can you please point me towards the solution?
[10 May 2023 11:56]
MySQL Verification Team
Hi, This is not a forum for free support, but only a forum for bug reports with repeatable test cases. A small hint though. The name of the account to which the privilege is granted, in 'user_name'@'host_name' format.
Description: Inconsistent format for results when querying users and privileges for mysql and information_schema databases. Affects definer, grantor and grantee columns and SHOW GRANTS. Definer, grantor and grantee columns display essentially the same concatenated data types - a user part (<= 32 characters), the @ sign and a host parts (<= 60 characters). When querying these tables the results for the columns are displayed with different column widths and with different conventions for quoting the user and host parts of the result. The mysql database *_priv tables show `Grantor` data in 93 characters with no quoting, `definer` columns in both databases are 93 characters no quoting, while mysql *_privilege `grantee` columns in the information_schema are 81 characters but also uses single quotes around the user and host parts. e.g. mysql> select table_schema, table_name, column_name, character_maximum_length from information_schema.columns where column_name = 'grantor'; +--------------+--------------+-------------+--------------------------+ | table_schema | table_name | column_name | character_maximum_length | +--------------+--------------+-------------+--------------------------+ | mysql | procs_priv | Grantor | 93 | | mysql | proxies_priv | Grantor | 93 | | mysql | tables_priv | Grantor | 93 | +--------------+--------------+-------------+--------------------------+ 3 rows in set (0.19 sec) mysql> select table_schema, table_name, column_name, character_maximum_length from information_schema.columns where column_name = 'grantee'; +--------------------+-------------------+-------------+--------------------------+ | table_schema | table_name | column_name | character_maximum_length | +--------------------+-------------------+-------------+--------------------------+ | information_schema | COLUMN_PRIVILEGES | GRANTEE | 81 | | information_schema | SCHEMA_PRIVILEGES | GRANTEE | 81 | | information_schema | TABLE_PRIVILEGES | GRANTEE | 81 | | information_schema | USER_PRIVILEGES | GRANTEE | 81 | +--------------------+-------------------+-------------+--------------------------+ mysql> select table_schema, table_name, column_name, character_maximum_length from information_schema.columns where column_name = 'definer'; +--------------------+------------+-------------+--------------------------+ | table_schema | table_name | column_name | character_maximum_length | +--------------------+------------+-------------+--------------------------+ | information_schema | EVENTS | DEFINER | 93 | | information_schema | ROUTINES | DEFINER | 93 | | information_schema | TRIGGERS | DEFINER | 93 | | information_schema | VIEWS | DEFINER | 93 | | mysql | event | definer | 93 | | mysql | proc | definer | 93 | +--------------------+------------+-------------+--------------------------+ 6 rows in set (30.34 sec) Querying results in different output formats mysql> select distinct grantor from mysql.tables_priv; +----------------------------+ | grantor | +----------------------------+ | boot@connecting host | | debian-sys-maint@localhost | | root@localhost | +----------------------------+ 3 rows in set (0.08 sec) mysql> select distinct grantee from information_schema.table_privileges; +-----------------------------+ | grantee | +-----------------------------+ | 'cacti'@'localhost' | | 'paul'@'localhost' | | 'mysql.sys'@'localhost' | | 'mysql.session'@'localhost' | +-----------------------------+ 4 rows in set (0.00 sec) While this doesn't 'break' anything when granting privileges, there should be a consistent format for the tables and show grants command to make querying easier. In addition, while the 93 character for mysql `Grantor` columns will accommodate the maximum possible user and host name sizes, it will not do so where the names have to be quoted for special characters a potential 97 characters in total. The 81 characters of the information_schema `grantee` columns will not display the maximum length but can truncate the result at 45 characters of the host part. SHOW GRANTS will display the maximum character length but uses single quotes. How to repeat: -- Create user with max possible charecters in user and host parts mysql> create user 123456789012345678901234567890ab@123456789012345678901234567890123456789012345678901234567890; Query OK, 0 rows affected (0.03 sec) mysql> create table test.max_len_grantee(a int); Query OK, 0 rows affected (2.81 sec) -- grant privilege on table mysql> grant select on test.max_len_grantee to 123456789012345678901234567890ab@123456789012345678901234567890123456789012345678901234567890; Query OK, 0 rows affected (0.20 sec) -- host output truncated at 45 characters mysql> select * from information_schema.table_privileges where table_schema = 'test' and table_name = 'max_len_grantee'\G *************************** 1. row *************************** GRANTEE: '123456789012345678901234567890ab'@'123456789012345678901234567890123456789012345 TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: max_len_grantee PRIVILEGE_TYPE: SELECT IS_GRANTABLE: NO 1 row in set (0.03 sec) Suggested fix: Use 97 characters for all columns to allow for maximum possible and user and host parts and always quote for consistent output format.