MySQL Bugs: #19693: Grants not working as expected

Bug #19693	Grants not working as expected
Submitted:	10 May 2006 17:23	Modified:	10 May 2006 19:25
Reporter:	Tapio Mattila	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.21	OS:	Windows (windows xp)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
User has been granted 'select' rights for table column/columns, but instead of getting the expected result set, for select clause, all we get is an error message 1143!

I granted one column from test_table (that was not primary key) and issued a 'select * from test_table' and expected to get a result set containing only granted column, but instead got error message.

Used platform: windows xp
mysql server: 5.0.21-community-nt (from mysql web site binaries)

A short test of view (based on test_table):
I created a view as select * from test_table and granted select rights to test_user and could issue 'select * from test_view' and the result set contained ALL fields!

How to repeat:
How to test this bug:

create database test_db;
use test_db;
create table test_table (
  id int not null auto_increment,
  txt1 char(10) not null,
  txt2 char(10),
  primary key(id)
) engine=innodb;
insert into test_table values(NULL, 'one', '1');
insert into test_table values(NULL, 'two', '2');
insert into test_table values(NULL, 'three', '3');

check that data exist:
 select * from test_table -> result set Ok

create user 'test_user'@'localhost' identified by 'pass';
grant select(txt1) on test_db.test_table to 'test_user'@'localhost';

1) start query browser or command line client and log in as 'test_user'
2) activate test_db
3) select * from test_table
   -> gives an error 1143, "SELECT command denied to user 'test_user'@'localhost' for column 'id' in table 'test_table'"
4) select txt from test_table
   -> same error

grant id column to user:
grant select(id) on test_db.test_table to 'test_user'@'localhost';

test again:
...
3) select * from test_table
   -> gives an error 1143, "SELECT command denied to user 'test_user'@'localhost' for column 'txt2' in table 'test_table'"
4) select txt from test_table
   OR
   select id, txt from test_table
   -> result set ok!

Thank you for the bug report. I was unable to repeat:

http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html

2.10.3. Securing the Initial MySQL Accounts
<cut>

On Windows, one anonymous account is for connections from the local host. It has all privileges, just like the root accounts. The other is for connections from any host and has all privileges for the test database and for other databases with names that start with test.

Then in your test case a database called test_db should gives all the privileges
for your user, then you can see below all the columns are granted by select
and for an example like other_db just the txt1 column is granted for select.

c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.21-log

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

mysql> create database test_db;
Query OK, 1 row affected (0.06 sec)

mysql> use test_db;
Database changed
mysql> create table test_table (
    ->   id int not null auto_increment,
    ->   txt1 char(10) not null,
    ->   txt2 char(10),
    ->   primary key(id)
    -> ) engine=innodb;
Query OK, 0 rows affected (0.17 sec)

mysql> insert into test_table values(NULL, 'one', '1');
Query OK, 1 row affected (0.09 sec)

mysql> insert into test_table values(NULL, 'two', '2');
Query OK, 1 row affected (0.03 sec)

mysql> insert into test_table values(NULL, 'three', '3');
Query OK, 1 row affected (0.03 sec)

mysql> create user 'test_user'@'localhost' identified by 'pass';
Query OK, 0 rows affected (0.24 sec)

mysql> grant select(txt1) on test_db.test_table to 'test_user'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye

c:\mysql\bin>mysql -utest_user -p test_db
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 5.0.21-log

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

mysql> select * from test_table;
+----+-------+------+
| id | txt1  | txt2 |
+----+-------+------+
| 1  | one   | 1    |
| 2  | two   | 2    |
| 3  | three | 3    |
+----+-------+------+
3 rows in set (0.00 sec)

mysql>

c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10 to server version: 5.0.21-log

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

mysql> create database other_db;
Query OK, 1 row affected (0.00 sec)

mysql> create user 'other_user'@'localhost' identified by 'pass';
Query OK, 0 rows affected (0.00 sec)

mysql> use other_db;
Database changed
mysql> create table test_table (
    ->   id int not null auto_increment,
    ->   txt1 char(10) not null,
    ->   txt2 char(10),
    ->   primary key(id)
    -> ) engine=innodb;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into test_table values(NULL, 'one', '1');
Query OK, 1 row affected (0.05 sec)

mysql> insert into test_table values(NULL, 'two', '2');
Query OK, 1 row affected (0.03 sec)

mysql> insert into test_table values(NULL, 'three', '3');
Query OK, 1 row affected (0.03 sec)

mysql> grant select(txt1) on other_db.test_table to 'other_user'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

c:\mysql\bin>mysql -uother_user -p other_db
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11 to server version: 5.0.21-log

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

mysql> select * from test_table;
ERROR 1143 (42000): SELECT command denied to user 'other_user'@'localhost' for column 'id' in table '
mysql> select txt1 from test_table;
+-------+
| txt1  |
+-------+
| one   |
| two   |
| three |
+-------+
3 rows in set (0.00 sec)

mysql>

Hi Miquel,

Thanks for your quick reply. I changed all 'test_xxx' names to 'other_xxx' and you are right now it works as expected. So it is not a bug.

Because I got the error messages (select txt1 from test_table) and you did not I assume that my tables in mysql database are not as they should be.