Bug #24455 procs_priv shows error user after granting procedure's privilege to special user
Submitted: 21 Nov 2006 8:40 Modified: 4 Dec 2006 21:13
Reporter: leo Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.1.12-beta-community-nt, 5.0 BK OS:Linux (linux, window 2000)
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[21 Nov 2006 8:40] leo
Description:
procs_priv shows error user(other words ,not existing user name) after granting procedure's privilege to special user(like chinese user name)

using mysql.exe and JDBC connection to select the procs_priv table has the same error.

but if granting table,view and database's privileges to special user,never seen this phenomena. 

How to repeat:
(1)  delimiter |
     CREATE PROCEDURE `test`.`dfdf`()
     BEGIN
        select 1;
     END
     |
(2) grant execute on procedure `test`.`dfdf` to '我'@'localhost'
     here '我' is a chinese word.
(3) SELECT * FROM `mysql`.`procs_priv`
    you will find '??‘' below the user column which is not the '我' word.
[22 Nov 2006 13:17] Valeriy Kravchuk
Thank you for a problem report. Please, send the results of:

SHOW VARIABLES LIKE 'char%';
SELECT user, hex(user) FROM `mysql`.`procs_priv`;
[23 Nov 2006 8:29] leo
+--------------------------+--------------------------------------+
| Variable_name            | Value                                |
+--------------------------+--------------------------------------+
| character_set_client     | gb2312                               |
| character_set_connection | gb2312                               |
| character_set_database   | utf8                                 |
| character_set_filesystem | binary                               |
| character_set_results    | gb2312                               |
| character_set_server     | latin1                               |
| character_set_system     | utf8                                 |
| character_sets_dir  | C:\Program Files\MySQL\MySQL Server 5.1\share\charsets\ |
+--------------------------+--------------------------------------------+

and
+------+----------------+
| user | hex(user)      |
+------+----------------+
| ??‘ | C3A6CB86E28098 |
+------+----------------+
[4 Dec 2006 21:13] Sveta Smirnova
Thank you for the report.

Verified as described on Linux using last BK sources. Version 5.0 is affected too.
[18 Jan 2007 21:47] Konstantin Osipov
Marc, strictly speaking it's not a bug. mysql.procs_priv is not designed for user introspection. But I did some analysis, and it seems that the data is garbled when _inserting_ into the table, which already does not look good.
Here is what I got:

mysql> grant execute on procedure `test`.`dfdf` to '我'@'localhost'
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> select user from user;
+--------+
| user   |
+--------+
| kostja | 
| root   | 
| user18 | 
| 我    | 
+--------+
4 rows in set (0.00 sec)

mysql> select user from procs_priv;
+---------+
| user    |
+---------+
| user18  | 
| boo     | 
| terence | 
| 我 | 
+---------+
4 rows in set (0.00 sec)

mysql> show create table user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `Host` char(60) collate utf8_bin NOT NULL default '',
  `User` char(16) collate utf8_bin NOT NULL default '',
  `Password` char(41) character set latin1 collate latin1_bin NOT NULL default '',
  `Select_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Insert_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Update_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Delete_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Create_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Drop_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Reload_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Shutdown_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Process_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `File_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Grant_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `References_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Index_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Alter_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Show_db_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Super_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Create_tmp_table_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Lock_tables_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Execute_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Repl_slave_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Repl_client_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Create_view_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N',
  `Show_view_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N',
  `Create_routine_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N',
  `Alter_routine_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N',
  `Create_user_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N',
  `Event_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N',
  `Trigger_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N',
  `ssl_type` enum('','ANY','X509','SPECIFIED') character set utf8 NOT NULL default '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
  `max_questions` int(11) unsigned NOT NULL default '0',
  `max_updates` int(11) unsigned NOT NULL default '0',
  `max_connections` int(11) unsigned NOT NULL default '0',
  `max_user_connections` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
1 row in set (0.00 sec)

mysql> show create table procs_priv\G
*************************** 1. row ***************************
       Table: procs_priv
Create Table: CREATE TABLE `procs_priv` (
  `Host` char(60) collate utf8_bin NOT NULL default '',
  `Db` char(64) collate utf8_bin NOT NULL default '',
  `User` char(16) collate utf8_bin NOT NULL default '',
  `Routine_name` char(64) collate utf8_bin NOT NULL default '',
  `Routine_type` enum('FUNCTION','PROCEDURE') collate utf8_bin NOT NULL,
  `Grantor` char(77) collate utf8_bin NOT NULL default '',
  `Proc_priv` set('Execute','Alter Routine','Grant') character set utf8 NOT NULL default '',
  `Timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`Host`,`Db`,`User`,`Routine_name`),
  KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Procedure privileges'
1 row in set (0.00 sec)

mysql> drop table if exists t1, t2;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 select user from user;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `user` char(16) character set utf8 collate utf8_bin NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> create table t2 select user from procs_priv;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `user` char(16) character set utf8 collate utf8_bin NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

As you can see, the definitions of the columns in both tables are identical,
which indicates that the data was garbled when inserting into the table.