MySQL Bugs: #928: Privilege checking for column privileges uses least specific hostname first

Bug #928	Privilege checking for column privileges uses least specific hostname first
Submitted:	25 Jul 2003 7:13	Modified:	2 Sep 2003 2:30
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	3.23.57 and up	OS:	Linux (Linux,Solaris)
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
When configuring a user with only column privileges on a table the access rights are determined by the least specific host value instead of the most specific one. According to the documentation we should expect the last behaviour.
If you configure only column privileges and give a user@host.your.domain update and select privileges on a column of a table everything works fine and the user can update the column. If you add now a select privilege for user@%.your.domain on the same or another column of the table, the user is denied the right to update from host.your.domain.

We saw this problem when accessing the server from another host as the one the server ran on. We used the JDBC driver to test the access but since everything works correctly when only the specific host is configured I assume that the access method is irrelevant.

This behaviour was observed in a mysql 3.23.54a and a mysql 3.23.57 that we built ourselves and in the binary distribution of mysql 4.0.14 standard that we downloaded via your website. So I assume that the problem isn't caused by our build process.

How to repeat:
Create a test database.
Create a test table with let's say 2 columns.
Add an entry in the test table
Configure in the user table a user with a password and access from any host. Give no general access rights to the user.
Don't give any rights in the db and host tables. (they may be empty)
In tables_priv table configure the column privileges Select, Update for the test table in the test database for user accessing from the specific host.
In the columns_priv table configure the Select,Update privilege for one of the columns of the test table in the test database for the user accessing from the specific host.
(I added the permissions with the grant statement and removed the extra entry made for the user in the user table)
Flush the privileges.
Now you should be able to select and update the values in the designated column from host.your.domain accessing the database as the user with the configured password.
Now configure an extra entry in the tables_priv table for the same user, database and table, but now for access from '%.your.domain' and only the Select column privilege.
Add also an entry in the columns_priv table for the same user, database, table with only the Select privilege (it might be to the other column) again from '%.your.domain'.
(Again I also tried this by granting those permissions using the GRANT staement and removing the extra entry in the user table, with the same result).
Flush the privileges again.
From what we observed on all 3 server versions now the update of the column from host.your.domain won't be allowed anymore for that user, although host.your.domain is clearly more specific than %.your.domain.
If you remove either the new entry in the tables_priv table or the one in the columns_priv table and flush the privileges again, the update will be allowed again.

Hi, I asume this bug is OS independant, and should also be the case on all 
other platforms.

Apparantly the bug is reproducable on linux systems too. See previous comment.

We need a test case in this format:

GRANT ... ON ... TO ...

mysql -u user ..
SELECT USER():
SELECT CURRENT_USER();
UPDATE ...

GRANT ... ON ... TO ...

mysql -u user ..
SELECT USER():
SELECT CURRENT_USER();

UPDATE ...

with all messages as returned by the client program.

I have repeated the test and copied the commands. I have indented the commands so that they are easily retrieved from in between the output and the comments and results. 

On db_host.your.domain start enter the mysql client application (I redid this test on 3.23.57)

	create database test_db;
Query OK, 1 row affected (0.01 sec)

	use test_db;
Database changed

	create table test_table (test_col1 varchar(255) NOT NULL default 'test', test_col2 varchar(255) NOT NULL default 'test2');
Query OK, 0 rows affected (0.00 sec)

	insert into test_table VALUES ('test2','test3');
Query OK, 1 row affected (0.00 sec)

	use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

	insert into user VALUES ('%','test_user',PASSWORD('pwd'), 'N','N','N','N','N','N','N','N','N','N','N','N','N','N');
Query OK, 1 row affected (0.00 sec)

	GRANT SELECT (test_col2) , UPDATE (test_col2) ON test_db.test_table to test_user@'host.your.domain';
Query OK, 0 rows affected (0.01 sec)

	delete from user where host='host.your.domain' and user='test_user';
Query OK, 1 row affected (0.00 sec)

	FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

Now on host.your.domain execute the following:

	mysql -h db_host.your.domain -u test_user -e 'select test_col1 from test_db.test_table' -ppwd

The result is the following (as can be expected):
ERROR 1143 at line 1: select command denied to user: 'test_user@host.your.domain' for column 'test_col1' in table 'test_table'

Now for column test_col2:

	mysql -h db_host.your.domain -u test_user -e 'select test_col2 from test_db.test_table' -ppwd

This returns the following result (again as expected):
+-----------+
| test_col2 |
+-----------+
| test3     |
+-----------+

Now the following two commands:

	mysql -h db_host.your.domain -u test_user -e 'update test_db.test_table set test_col2="test4"' -ppwd

	mysql -h db_host.your.domain -u test_user -e 'select test_col2 from test_db.test_table' -ppwd

again the expected result:

+-----------+
| test_col2 |
+-----------+
| test4     |
+-----------+

Everything worked as expected, now go back to mysql on db_host.your.domain and execute the following commands:

	GRANT SELECT (test_col1) ON test_db.test_table to test_user@"%.your.domain";
Query OK, 0 rows affected (0.00 sec)

	delete from user where host="%.your.domain" and user="test_user";
Query OK, 1 row affected (0.00 sec)

	FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Again go to host.your.domain and try to execute the following commands:

	mysql -h db_host.your.domain -u test_user -e 'select test_col2 from test_db.test_table' -ppwd

This query which passed with only the previous more restrictive rule doesn't work anymore although the host definition for the first rule is clearly more specific:
ERROR 1143 at line 1: select command denied to user: 'test_user@host.your.domain' for column 'test_col2' in table 'test_table'

The same holds for the update command:

	mysql -h db_host.your.domain -u test_user -e 'update test_db.test_table set test_col2="test5"' -ppwd

The unexpected result:
ERROR 1143 at line 1: update command denied to user: 'test_user@host.your.domain' for column 'test_col2' in table 'test_table'

The query on the first column works however:
	
	mysql -h db_host.your.domain -u test_user -e 'select test_col1 from test_db.test_table' -ppwd

The again unexpected result:
+-----------+
| test_col1 |
+-----------+
| test2     |
+-----------+

Even this last behaviour while at first it seems logical could unwanted: one could imagine scenarios in which the whole domain has access except for a few "guest" machines which have a specific ruleset.

usage of INSERT , UPDATE, DELETE on privilege tables is strongly discouraged as
that can brake privilege system, like in this case.

Repeatable bug reports that use ONLY GRANT / REVOKE are very welcome.

This also occurs if you don't use the insert and delete statements:

Just add 'identified by "pwd"' to the two grant statements.

The result is exactly the same.

The behavior I found with 2 days older bk 4.0 tree is:

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

mysql> use test_db;
Database changed
mysql> create table test_table (test_col1 varchar(255) NOT NULL
    -> default 'test',test_col2 varchar(255) NOT NULL
    -> default 'test2');
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test_table VALUES ('test2','test3');
Query OK, 1 row affected (0.00 sec)

mysql> GRANT SELECT (test_col2) , UPDATE (test_col2) ON
    -> test_db.test_table to 'test_user'@'192.168.0.77'
    -> identified by 'pwd';
Query OK, 0 rows affected (0.00 sec)

c:\mysql\bin>mysql -h192.168.0.75 -utest_user -e "select test_col1 from test_db.
test_table" -ppwd
ERROR 1143 at line 1: SELECT command denied to user: 'test_user@192.168.0.77' fo
r column 'test_col1' in table 'test_table'

c:\mysql\bin>mysql -h192.168.0.75 -utest_user -e "select test_col2 from test_db.
test_table" -ppwd
+-----------+
| test_col2 |
+-----------+
| test3     |
+-----------+

c:\mysql\bin>mysql -h192.168.0.75 -utest_user -e "update test_db.test_table set
test_col2='test4'" -ppwd

c:\mysql\bin>mysql -h192.168.0.75 -utest_user -e "select test_col2 from test_db.
test_table" -ppwd
+-----------+
| test_col2 |
+-----------+
| test4     |
+-----------+

mysql> GRANT SELECT (test_col1) ON test_db.test_table to
    -> 'test_user'@'192.168.0.77' identified by 'pwd';
Query OK, 0 rows affected (0.00 sec)

c:\mysql\bin>mysql -h192.168.0.75 -utest_user -e "select test_col1 from test_db.
test_table" -ppwd
+-----------+
| test_col1 |
+-----------+
| test2     |
+-----------+

mysql> revoke select (test_col1) on test_db.test_table from
    -> 'test_user'@'192.168.0.77';
Query OK, 0 rows affected (0.00 sec)

c:\mysql\bin>mysql -h192.168.0.75 -utest_user -e "select test_col1 from test_db.
test_table" -ppwd
ERROR 1143 at line 1: SELECT command denied to user: 'test_user@192.168.0.77' fo
r column 'test_col1' in table 'test_table'

c:\mysql\bin>mysql -h192.168.0.75 -utest_user -e "select test_col2 from test_db.
test_table" -ppwd
+-----------+
| test_col2 |
+-----------+
| test4     |
+-----------+

The testcase described above ([7 Aug 10:09am] Miguel Solorzano )isn't relevant for this bug report since it contains no hostnames nor wildcards for the representation of domains.
The bug can be reproduced with the following commands (copied from the previous comment and adapted in order to use only grant and revoke in the mysql database):

	create database test_db;
Query OK, 1 row affected (0.01 sec)

	use test_db;
Database changed

	create table test_table (test_col1 varchar(255) NOT NULL default 'test',
test_col2 varchar(255) NOT NULL default 'test2');
Query OK, 0 rows affected (0.00 sec)

	insert into test_table VALUES ('test2','test3');
Query OK, 1 row affected (0.00 sec)

	use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

	GRANT SELECT (test_col2) , UPDATE (test_col2) ON test_db.test_table to
test_user@'host.your.domain' identified by 'pwd';
Query OK, 0 rows affected (0.01 sec)

	FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

Now on host.your.domain execute the following:

	mysql -h db_host.your.domain -u test_user -e 'select test_col1 from
test_db.test_table' -ppwd

The result is the following (as can be expected):
ERROR 1143 at line 1: select command denied to user:
'test_user@host.your.domain' for column 'test_col1' in table 'test_table'

Now for column test_col2:

	mysql -h db_host.your.domain -u test_user -e 'select test_col2 from
test_db.test_table' -ppwd

This returns the following result (again as expected):
+-----------+
| test_col2 |
+-----------+
| test3     |
+-----------+

Now the following two commands:

	mysql -h db_host.your.domain -u test_user -e 'update test_db.test_table set
test_col2="test4"' -ppwd

	mysql -h db_host.your.domain -u test_user -e 'select test_col2 from
test_db.test_table' -ppwd

again the expected result:

+-----------+
| test_col2 |
+-----------+
| test4     |
+-----------+

Everything worked as expected, now go back to mysql on db_host.your.domain and
execute the following commands:

	GRANT SELECT (test_col1) ON test_db.test_table to test_user@"%.your.domain" identified by 'pwd';
Query OK, 0 rows affected (0.00 sec)

	FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Again go to host.your.domain and try to execute the following commands:

	mysql -h db_host.your.domain -u test_user -e 'select test_col2 from
test_db.test_table' -ppwd
ERROR 1143 at line 1: select command denied to user:
'test_user@host.your.domain' for column 'test_col2' in table 'test_table'
	
	mysql -h db_host.your.domain -u test_user -e 'update test_db.test_table set test_col2="test5"' -ppwd
ERROR 1143 at line 1: update command denied to user:
'test_user@host.your.domain' for column 'test_col2' in table 'test_table'

The query on the first column works however:
	
	mysql -h db_host.your.domain -u test_user -e 'select test_col1 from
test_db.test_table' -ppwd
+-----------+
| test_col1 |
+-----------+
| test2     |
+-----------+

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

fixed in 3.23.59 and 4.0.16