MySQL Bugs: #43191: select columns_priv grant not honored (doesn't work)

Bug #43191	select columns_priv grant not honored (doesn't work)
Submitted:	25 Feb 2009 14:50	Modified:	5 Apr 2009 21:36
Reporter:	Jay Johnston	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.0.37-community, 5.0.79	OS:	Linux (red hat)
Assigned to:		CPU Architecture:	Any
Tags:	columns_priv, grant, select (column)

Description:
The expected behavior of "grant select (column)..." was to only allow the user to select the column(s) given in the column list.  i can see the column_priv in the table_priv table, and the column_priv table looks correct as well; however, when i login as the restricted user, the privilege seems to be ignored and all columns are offered up for a select * query.

How to repeat:
-- as an administrator with grant option:
drop user 'test'@'localhost'
grant select (firstname, lastname) on test.members to 'test'@'localhost' identified by 'test';
show grants for 'test'@'localhost'
|----------------------------------------------------------------------------------------------------
|Grants for test@%
|----------------------------------------------------------------------------------------------------
|GRANT USAGE ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*94BDCEBE19083............AF4CFC29'
|GRANT SELECT (lastname, firstname) ON `test`.`members` TO 'test'@'%'
|flush privileges -- not really necessary, but wanted to avoid that discussion
|----------------------------------------------------------------------------------------------------

select * from tables_priv
|----------------------------------------------------------------------------------------------------
|Host    Db      User    Table_name      Grantor          Timestamp           Table_priv  Column_priv
|----------------------------------------------------------------------------------------------------
|%       test    test    members         admin@localhost  2009-02-25 08:24:52             Select
|----------------------------------------------------------------------------------------------------

select * from columns_priv
|----------------------------------------------------------------------------------------------------
|Host    Db      User    Table_name      Column_name     Timestamp               Column_priv
|----------------------------------------------------------------------------------------------------
|%       test    test    members         lastname        2009-02-25 08:24:52     Select
|%       test    test    members         firstname       2009-02-25 08:24:52     Select
|----------------------------------------------------------------------------------------------------

-- and then as test@localhost:
select * from members
|----------------------------------------------------------------------------------------------------
|id      firstname       lastname        phone
|----------------------------------------------------------------------------------------------------
|3       moo             moo             moo
|4       mooo            moooo           moooo
|5       jjj             jjj             jjj
|----------------------------------------------------------------------------------------------------

I can repeat this on 5.0.79:

mysql> create table members (id int, firstname char(10), lastname char(10), c1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> grant select (firstname, lastname) on test.members to 'test'@'localhost' identified by 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into members values(1, 'John', 'Doe', 1);
Query OK, 1 row affected (0.00 sec)

mysql> exit
Bye
valeriy-kravchuks-macbook-pro:5.0 openxs$ bin/mysql -utest -ptest test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.79-debug Source distribution

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

mysql> show grants\G
*************************** 1. row ***************************
Grants for test@localhost: GRANT USAGE ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'
*************************** 2. row ***************************
Grants for test@localhost: GRANT SELECT (lastname, firstname) ON `test`.`members` TO 'test'@'localhost'
2 rows in set (0.00 sec)

mysql> select * from members;
+------+-----------+----------+------+
| id   | firstname | lastname | c1   |
+------+-----------+----------+------+
|    1 | John      | Doe      |    1 | 
+------+-----------+----------+------+
1 row in set (0.00 sec)

mysql> select id from members;
+------+
| id   |
+------+
|    1 | 
+------+
1 row in set (0.00 sec)

Thank you for the bug report. Could you please verify if you have the anonymous account in your grant table:

c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.79-Win x86-64 bzr revno:2741-log Source distribution

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

mysql 5.0 > delete from mysql.user where user ="";
Query OK, 2 rows affected (0.00 sec)

mysql 5.0 > flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql 5.0 > use test
Database changed
mysql 5.0 > create table members (id int, firstname char(10), lastname char(10), c1 int);
Query OK, 0 rows affected (0.06 sec)

mysql 5.0 > insert into members values(1, 'John', 'Doe', 1);
Query OK, 1 row affected (0.00 sec)

mysql 5.0 >  grant select (firstname, lastname) on test.members to 'test'@'localhost' identified
    -> by 'test';
Query OK, 0 rows affected (0.00 sec)

mysql 5.0 > exit
Bye

c:\dbs>5.0\bin\mysql -utest -ptest --port=3500 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.79-Win x86-64 bzr revno:2741-log Source distribution

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

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

~# mysql -uroot -p --prompt="mysql rocks > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1854507
Server version: 5.0.37-community MySQL Community Edition (GPL)

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

mysql rocks > delete from mysql.user where user="";
Query OK, 0 rows affected (0.00 sec)

mysql rocks > flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql rocks > grant select(firstname,lastname) on test.members to 'test'@'localhost' identified by 'test';
Query OK, 0 rows affected (0.00 sec)

mysql rocks > exit

~# mysql -utest -p --prompt="tmi > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1854508
Server version: 5.0.37-community MySQL Community Edition (GPL)

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

tmi > use test; select * from members;
Database changed
+----+------------------+-----------------+--------------+
| id | firstname        | lastname        | phone        |
+----+------------------+-----------------+--------------+
|  3 | moo              | moo             | moo          |
|  4 | mooo             | moooo           | moooo        |
+----+------------------+-----------------+--------------+
2 rows in set (0.00 sec)

tmi > help?

for what it is worth, the anonymous user did exist, but i was using an odbc connection (which doesn't make nice copy-paste-stuff for a bug report).  so when i got to the command line version the 2 anonymous rows were already gone.  can i provide any more information on it?

Are you reallu sure anonimous users are gone from everuwhere, including mysq.db table? Please, send the results of:

select host, user from mysql.user where user = '';
select host, db, user from mysql.db where user = '';

Also, try your test case with any other target database than test, just to check.

~# mysql -uroot -p --prompt="-prompt="mysql rocks > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1862922
Server version: 5.0.37-community MySQL Community Edition (GPL)

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

mysql rocks > select host, user from mysql.user where user = '';
Empty set (0.02 sec)

mysql rocks > select host, db, user from mysql.db where user = '';
+------+---------+------+
| host | db      | user |
+------+---------+------+
| %    | test    |      |
| %    | test\_% |      |
+------+---------+------+
2 rows in set (0.02 sec)

mysql rocks > delete from mysql.db where user = '';
Query OK, 2 rows affected (0.00 sec)

mysql rocks > select host, db, user from mysql.db where user = '';
Empty set (0.00 sec)

mysql rocks > exit
Bye
~# `mysql': mysql -utest -p --prompt="tmi > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1862923
Server version: 5.0.37-community MySQL Community Edition (GPL)

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

tmi > use test; select * from members;
Database changed
+----+------------------+-----------------+--------------+
| id | firstname        | lastname        | phone        |
+----+------------------+-----------------+--------------+
|  3 | moo              | moo             | moo          |
|  4 | mooo             | moooo           | moooo        |
|  5 | jjj              | jjj             | jjj          |
|  6 | hh               | h               | hhh          |
|  7 | ggg              | gg              | gggg         |
|  8 |                  |                 |              |
|  9 | uity             | uityuit         | iuty         |
| 10 | hhh              | hh              | hhh          |
| 11 | ll               | ll              | lll          |
| 12 | jimmy            | dickinson       | 123234142    |
| 13 | jjjj             | jjjjjjjj        | jjj          |
| 14 | jay              | johnston        | 256-882-9585 |
| 15 | jay LG           | yo gabba gabba  |              |
| 16 | asdfa            | sdf             |              |
| 17 | asdfaasdf        | sdfasdf         |              |
| 18 | asdfaasdfasdf    | sdfasdfasdf     |              |
| 19 | ;lkj             | ;lkj            |              |
| 20 | a;slkdjf         | a;sldkjf        |              |
| 21 | bill             | bill            |              |
| 22 | asdf             | asdf            |              |
| 23 | asdfasdf         | asdfasdf        |              |
| 24 | ;lkjasdf;lkjasdf | ;kjasdf;lkjasdf |              |
| 25 | ff               | fffff           |              |
| 26 | asdfqwer         | asdfqwer        |              |
+----+------------------+-----------------+--------------+
24 rows in set (0.05 sec)

tmi > Aborted

~# mysql -uroot -p --prompt="mysql rocks > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1862927
Server version: 5.0.37-community MySQL Community Edition (GPL)

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

mysql rocks > use juju_db;
Database changed
mysql rocks > create table members (id int, firstname char(10), lastname char(10), c1 int);
Query OK, 0 rows affected (0.09 sec)

mysql rocks > grant select (firstname, lastname) on juju_db.members to 'test'@'localhost' identified by 'test';
Query OK, 0 rows affected (0.00 sec)

mysql rocks > exit
Bye
~# mysql -utest -p --prompt="still tmi > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1862931
Server version: 5.0.37-community MySQL Community Edition (GPL)

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

still tmi > use juju_db;
Database changed
still tmi > select * from juju_db.members;
ERROR 1143 (42000): SELECT command denied to user 'test'@'localhost' for column 'id' in table 'members'

              --- why does it work here ^ and not there? \/ ---

still tmi > use test;
Database changed
still tmi > select * from test.members;
+----+------------------+-----------------+--------------+
| id | firstname        | lastname        | phone        |
+----+------------------+-----------------+--------------+
|  3 | moo              | moo             | moo          |
|  4 | mooo             | moooo           | moooo        |
|  5 | jjj              | jjj             | jjj          |
|  6 | hh               | h               | hhh          |
|  7 | ggg              | gg              | gggg         |
|  8 |                  |                 |              |
|  9 | uity             | uityuit         | iuty         |
| 10 | hhh              | hh              | hhh          |
| 11 | ll               | ll              | lll          |
| 12 | jimmy            | dickinson       | 123234142    |
| 13 | jjjj             | jjjjjjjj        | jjj          |
| 14 | jay              | johnston        | 256-882-9585 |
| 15 | jay LG           | yo gabba gabba  |              |
| 16 | asdfa            | sdf             |              |
| 17 | asdfaasdf        | sdfasdf         |              |
| 18 | asdfaasdfasdf    | sdfasdfasdf     |              |
| 19 | ;lkj             | ;lkj            |              |
| 20 | a;slkdjf         | a;sldkjf        |              |
| 21 | bill             | bill            |              |
| 22 | asdf             | asdf            |              |
| 23 | asdfasdf         | asdfasdf        |              |
| 24 | ;lkjasdf;lkjasdf | ;kjasdf;lkjasdf |              |
| 25 | ff               | fffff           |              |
| 26 | asdfqwer         | asdfqwer        |              |
+----+------------------+-----------------+--------------+
24 rows in set (0.00 sec)

still tmi >

So, you had anonimous accounts in mysql.db, with permissions to do anything in test database. That's why in any other database it worked as expected.

After you had deleted rows from mysql.db table, you had to reload privileges in memory. Use FLUSH PRIVILEGES for this, then check if you still can "bypass" explicit column-level SELECT privileges in test database.

i don't believe the flush privileges is the problem.   previous posts show that with no anonymous user, and after flush privileges, the problem is still evident.

Thank you for the feedback.

> i don't believe the flush privileges is the problem.   previous posts show that with no
anonymous user, and after flush privileges, the problem is still evident.

But in the previous post you showed:

mysql rocks > select host, db, user from mysql.db where user = '';
+------+---------+------+
| host | db      | user |
+------+---------+------+
| %    | test    |      |
| %    | test\_% |      |
+------+---------+------+
2 rows in set (0.02 sec)

This is that problem anonymous user. After you delete these records you did not show you issue FLUSH PRIVILEGES. Please be sure you did. Also, please, note after issuing FLUSH PRIVILEGES you have to run USE test again or reconnect as not-user.

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".