Bug #21669 Too much information visible for users with column privileges
Submitted: 16 Aug 2006 4:22 Modified: 24 Jan 2007 10:46
Reporter: Michal Ludvig Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S4 (Feature request)
Version:5.1.11 OS:Any
Assigned to: CPU Architecture:Any

[16 Aug 2006 4:22] Michal Ludvig
Description:
Having a user column privilege on a certain table.column allows the user view all columns, create table info, etc. From the security point of view this shouldn't be possible. And it's inconsistent with how table/database privileges work - with them you're not able to see tables/databases you don't have privileges to.

How to repeat:
use test;
create table bug (col1 int, col2 char(10));
insert into bug values (1, "bugreport");
create user buguser;
grant select(col2) on test.bug to buguser;

now login as buguser and run:

mysql> select * from bug;
ERROR 1143 (42000): SELECT command denied to user 'buguser'@'localhost' for column 'col1' in table 'bug'

It names the column 'col1'. Even worse the following name all columns:

mysql> show fields from bug;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| col1  | int(11)  | YES  |     | NULL    |       |
| col2  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show create table bug;
+-------+---------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------+
| bug   | CREATE TABLE `bug` (
  `col1` int(11) default NULL,
  `col2` char(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Show only the columns the user has privilege to access.
[23 Dec 2006 13:49] Valeriy Kravchuk
Thank you for a problem report. I think, you just mix object-level privileges with privileges for metadata (and column names are just metadata, part of data dictionary). You need some way to check what columns table has to be able to write semantically correct queries. We now have INFORMATION_SCHEMA for this, not only SHOW statements. So, it seems that current behaviour is intended. Can you point out any URL for the manual that explicitely says user should not be able to see columns at the table if she has no SELECT privlege for them?

From the security point of view your request may sound reasonable, though. So, I think, this is a valid feature request to be considered by developers. Do you agree?
[24 Jan 2007 0:00] Bugs System
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".
[24 Jan 2007 0:30] Michal Ludvig
I certainly can't point at any such statement in the manual. However it's inconsistent with how table/database privileges work - with them you're not able to see tables/databases you don't have privileges to. That's why I believe the same should apply to columns.
[24 Jan 2007 10:46] Sergei Golubchik
And the SQL standard specifies that one should not be able to see columns that he has no privileges on (SQL 2003, Part 11: Information and Definition Schemas, 5.20 COLUMNS view).
[11 Jul 2008 9:53] Jan Weber
Sorry for my opinion as I am still a newbe.
I agree it should be seen as a bug as it has an important influence to another MySQL command: 

SELECT * FROM database.table ...

gives an error if user has restricted privilegs. But it is very unagreable behavour, and makes the building of program (I use PHP) much more complicated while the explicit list of available columns should be composed before the SELECT is called!

Thank you for the patiency with a newbe :-)

Jan