MySQL Bugs: #4898: User privileges depending on ORDER BY Settings of table db

Bug #4898	User privileges depending on ORDER BY Settings of table db
Submitted:	4 Aug 2004 17:38	Modified:	3 Nov 2004 16:02
Reporter:	Torsten Zachert	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.20a-nt/4.0.21 debug-log	OS:	Windows (Win NT/Slackware Linux)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
With the ALTER statement on table db it is possible to change the user privileges. See example.

How to repeat:
--Databases
CREATE DATABASE test;
CREATE DATABASE test-db
CREATE DATABASE test_dev

--mysql->user
INSERT INTO user VALUES ('localhost', 'test', PASSWORD('test'), 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0);

--mysql->db
INSERT INTO db VALUES ('localhost', 'test%','test', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');
INSERT INTO `db` VALUES ('localhost', 'test-d%','test', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N');

--After
ALTER TABLE db ORDER BY db;
FLUSH PRIVILEGES;

--Result
mysql> show databases;
Empty set (0.00 sec)

--After
ALTER TABLE db ORDER BY db DESC;
FLUSH PRIVILEGES;

--Result
mysql> show databases;
+----------+
| Database |
+----------+
| test     |
| test-db  |
| test_dev |
+----------+
3 rows in set (0.00 sec)

I noticed the same behavior with 4.0.13-nt.

Torsten

Sorry, but the bug system is not the appropriate forum for asking
support questions. Your problem is not the result of a bug.
For a list of more appropriate places to ask for help using MySQL
products, please visit http://www.mysql.com/support/

Thank you for your interest in MySQL.

Additional info:

If user has ALTER privileges on any of mysql.* tables he can do much more harm.

Regular users should be never granted *any* privs in mysql database.

ALTER on privileges tables should be discouraged in any case.

The user >test< has no ALTER privileges to mysql.* tables!

The ALTER .. ORDER BY Statement is set by any administrator, not by the user himself. But the problem is (I think it is a bug), that any administrator with ALTER privileges to mysql.* tables can change the privileges of the user >test< only by changing the alphabetical order of the table mysql.db. 

In my opinion this order should be absolutly independend from the specific order that mysql uses internally.

In the example only the show databases(); statements are executed by the user test. All the other statement are executed by any admin.

What you describe is that DBA with ALTER privilege on mysql.dbcan affect the privieleges of another user.

How can this be considered a bug? This is true by definition.

With ALTER one can set all privileges to accept 'Y' only and thus give users more rights than they need or even drop columns in table thus breaking MySQL privilege system.

At the other hand recommended way to manage users and privileges is with using GRANT and REVOKE statements only and *not* using regular SQL statements against privilege tables.

Please read my postings carefully. I know that I can change privileges with the ALTER Statement. But why I can change privileges with only that !!! command:
ALTER table db ORDER BY ASC/DESC. 
I can not find any explanation in the docs:

"The server reads in and sorts the db and host tables at the same time that it reads the user table. The server sorts the db table based on the Host, Db, and User scope columns, and sorts the host table based on the Host and Db scope columns. As with the user table, sorting puts the most-specific values first and least-specific values last, and when the server looks for matching entries, it uses the first match that it finds."

-----------------------------------------
Why the most-specific internal sorting depends on the external use of ALTER table db ORDER BY DESC/ASC ???
-----------------------------------------

"ALTER TABLE ORDER BY allows you to create the new table with the rows in a specific order. Note that the table will not remain in this order after inserts and deletes. This option is mainly useful when you know that you are mostly going to query the rows in a certain order; by using this option after big changes to the table, you might be able to get higher performance. In some cases, it might make sorting easier for MySQL if the table is in order by the column that you want to order it by later. "

It should not depend on that, of course.
You are right.

I tested this on Linux with 4.0.20 and all worked as expected.

The databases are always visible as the test% entry always
takes precedence over the test_d% one as table db is sorted
by host, db and user.

Looks like a Windows-only problem ...

I had having another experience with the same behavior on Windows and Linux.

First the CREATE DATABASE test-db; this is only possible enclosing the db
name within backtick. Below what I got:

Microsoft Windows XP [versão 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.20a-nt

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

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE DATABASE test-db;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server ver
sion for the right syntax to use near '-db' at line 1

mysql> CREATE DATABASE `test-db`;
Query OK, 1 row affected (0.00 sec)

miguel@hegel:/misc/dbs/4.0$ ./star_client
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.0.21-debug-log

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

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE DATABASE test-db;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '-db' at line 1
mysql> CREATE DATABASE `test-db`;
Query OK, 1 row affected (0.01 sec)

So when is applied:

mysql> ALTER TABLE db ORDER BY db;
Query OK, 5 rows affected (0.19 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.03 sec)

miguel@hegel:/misc/dbs/4.0$ bin/mysql --defaults-file=/misc/dbs/4.0/my.cnf -utest -ptest
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 4.0.21-debug-log

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

mysql> show databases;
+----------+
| Database |
+----------+
| test     |
| test_dev |
+----------+
2 rows in set (0.00 sec)

That database isn't displayed in both Windows and Linux.

Additional verifications

3.23.48-Max on Linux 

in both ORDER cases 
user test can see all !! mysql databases

4.0.20-max on Linux

after: ALTER ... ORDER BY db;
user test can see the folllowing databases:

test
test_dev

after: ALTER ... ORDER BY db DESC;
user test can see the folllowing databases:

test
test-db
test_dev

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

When this bug fix will be published in any version 4.x ? It is urgent.

It must be out already in 4.0.21