MySQL Bugs: #1191: Column privileges not respected

Bug #1191	Column privileges not respected
Submitted:	4 Sep 2003 2:54	Modified:	11 Nov 2003 9:15
Reporter:	Patrick Allaert	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.0.12-max-nt-log	OS:	Windows (Windows 2000)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Column privileges is not respected in some selects (select * from ... ?)

How to repeat:
I have this table definition:

CREATE TABLE `users` (
  `id_per` mediumint(8) unsigned NOT NULL default '0',
  `id_nplus1` mediumint(8) unsigned default NULL,
  `lastname` varchar(30) NOT NULL default '',
  `firstname` varchar(30) NOT NULL default '',
  `language` enum('D','F','N','E') NOT NULL default 'F',
  `celcode` varchar(30) NOT NULL default '',
  `phone` int(10) unsigned default NULL,
  `gsm` int(10) unsigned default NULL,
  `building` char(3) default NULL,
  `cowcode` varchar(6) default NULL,
  `id_address` smallint(5) unsigned default NULL,
  `privatephone` varchar(20) default NULL,
  PRIMARY KEY  (`id_per`),
  KEY `lastname` (`lastname`,`firstname`),
  KEY `id_nplus1` (`id_nplus1`),
  KEY `phone` (`phone`),
  KEY `gsm` (`gsm`),
  KEY `privatephone` (`privatephone`)
) TYPE=MyISAM

Content of mysql.* tables:
users:
mysql> select * from user where user = "gpw";
Host User  Password Select Insert Update Delete Create Drop Reload Shutdown Process File Grant References Index Alter
%    gpw   XXXX      N      N      N      N      N      N    N      N        N       Y    N     N          N     N
tables_priv:
mysql> select * from tables_priv where user = "gpw" and db = "rocatwork";
Host Db        User Table_name Grantor               Timestamp      Table_priv Column_priv
%    rocatwork gpw  users      id0xxxxx@xx.xx.xxx.xx 20030904114107            Select
columns_priv (access for Select for every columns of table "Users" except "privatephone"):
mysql> select * from columns_priv;
Host Db        User Table_name Column_name Timestamp      Column_priv
%    rocatwork gpw  users      building    20030904114102 Select
%    rocatwork gpw  users      celcode     20030904114102 Select
%    rocatwork gpw  users      cowcode     20030904114103 Select
%    rocatwork gpw  users      firstname   20030904114103 Select
%    rocatwork gpw  users      gsm         20030904114104 Select
%    rocatwork gpw  users      id_address  20030904114104 Select
%    rocatwork gpw  users      id_nplus1   20030904114105 Select
%    rocatwork gpw  users      id_per      20030904114105 Select
%    rocatwork gpw  users      language    20030904114106 Select
%    rocatwork gpw  users      lastname    20030904114106 Select
%    rocatwork gpw  users      phone       20030904114107 Select
the other mysql tables are empty (host, db, func)

Here is the bug:
C:\mysql\bin>mysql -ugpw -p
Enter password: XXXXXXXXXXX
mysql> use rocatwork;
Database changed
mysql> select privatephone from users limit 1;
ERROR 1143: SELECT command denied to user: 'gpw@127.0.0.1' for column 'privatephone' in table 'users'
mysql> select * from users where privatephone like "%02%" limit 1;
ERROR 1143: SELECT command denied to user: 'gpw@127.0.0.1' for column 'privatephone' in table 'users'
mysql> select * from users limit 1;
id_per id_nplus1 lastname firstname language celcode     phone    gsm  building cowcode id_address privatephone
 XXXXX     XXXXX XXXXXX   XXXX      F        ANS-ECO-LPE 434XXXXX NULL LRH      41GRE1         168 0422XXXXX
1 row in set (0.00 sec)

The last query give me the privatephone !!!

Patrick Allaert

I tested your case with 4.0.15 release and didn't find the behavior for
you reported (see below). Please do the test with this server and let me
know if is already fixed for to close this bug report.

mysql> create table users (id int auto_increment not null primary key,
    -> user_name char(45), priv_phone char(20));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into users (user_name,priv_phone) values ("George","2342-6233");
Query OK, 1 row affected (0.01 sec)

mysql> grant select (id,user_name) on bug1191.users
    -> to "gpw"@"localhost" identified by "me";
Query OK, 0 rows affected (0.01 sec)

e:\servers\mysql-4.0.15\bin>mysql -ugpw -p
Enter password: **
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.0.15-max-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use bug1191;
Database changed

mysql> select id, user_name from users;
+----+-----------+
| id | user_name |
+----+-----------+
|  1 | George    |
+----+-----------+
1 row in set (0.00 sec)

mysql> select priv_phone from users limit 1;
ERROR 1143: SELECT command denied to user: 'gpw@localhost' for column 'priv_phon
e' in table 'users'

mysql> select * from users limit 1;
ERROR 1143: select command denied to user: 'gpw@localhost' for column 'priv_phon
e' in table 'users'

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".