| 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: | |
| 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 |
[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.
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.