MySQL Bugs: #51346: privilege issue on information

Bug #51346	privilege issue on information_schema.schemata
Submitted:	20 Feb 2010 12:48	Modified:	27 Aug 2010 14:35
Reporter:	Anthony De Temmerman	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S2 (Serious)
Version:	mysql5.0.90	OS:	Linux (gentoo)
Assigned to:		CPU Architecture:	Any
Tags:	information_schema, privileges

Description:
The documentation says that the following commands are the same :

SELECT SCHEMA_NAME AS `Database`
 FROM INFORMATION_SCHEMA.SCHEMATA
 [WHERE SCHEMA_NAME LIKE 'wild']

SHOW DATABASES
 [LIKE 'wild']

On our servers :

mysql> SELECT SCHEMA_NAME AS `Database` FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME LIKE 'abbayed';
+----------+
| Database |
+----------+
| abbayed  |
+----------+
1 row in set (0.14 sec)

mysql> SHOW DATABASES LIKE 'abbayed';
ERROR 1227 (42000): Access denied; you need the SHOW DATABASES privilege for this operation

And the user logged in as absolutely no privileges on abbayed database !

This allow any user to see the list of all databases that are hosted on the server and is a kind of security issue for shared hosting servers.

How to repeat:
Log in as a non privileged user and try this :

SELECT SCHEMA_NAME AS `Database`
 FROM INFORMATION_SCHEMA.SCHEMATA;

Even if you don't have the show databases priv, you will get the list of databases.

Suggested fix:
Access to INFORMATION_SCHEMA.SCHEMATA should be restricted to users having the "show databases" priv

Thank you for the bug report. Could you please provide the complete test case including the create user command. I couldn't repeat with source server on Windows:

C:\DBS>5.0\bin\mysql -ujose --port=3500
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.91-Win X64-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use bla;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'bla'
mysql> SELECT SCHEMA_NAME AS `Database` FROM INFORMATION_SCHEMA.SCHEMATA WHERE
    -> SCHEMA_NAME LIKE 'bla';
Empty set (0.02 sec)

mysql> show databases like 'bla';
Empty set (0.00 sec)

mysql> exit
Bye

C:\DBS>5.0\bin\mysql -uroot --port=3500
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.91-Win X64-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT SCHEMA_NAME AS `Database` FROM INFORMATION_SCHEMA.SCHEMATA WHERE
    -> SCHEMA_NAME LIKE 'bla';
+----------+
| Database |
+----------+
| bla      |
+----------+
1 row in set (0.05 sec)

mysql> show databases like 'bla';
+----------------+
| Database (bla) |
+----------------+
| bla            |
+----------------+
1 row in set (0.00 sec)

mysql>

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for testuser@%                                                                                                                                                                                        |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT CREATE TEMPORARY TABLES ON *.* TO 'testuser'@'%' IDENTIFIED BY PASSWORD '---HIDDEN---'                                                                                    | 
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `testuser`.* TO 'testuser'@'%' | 
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

NB : your user should have the "Show_db_priv" set to N

Thank you for the feedback. What I need is the sequence of sql command (i.e: GRANT/Create user..) to repeat the behavior reported. Thanks in advance.

Here it is :

mysql> CREATE USER 'testuser' IDENTIFIED BY PASSWORD '*3CF44C5CA7F3F9158BDF74495869D84671E625B2';
Query OK, 0 rows affected (0.03 sec)

mysql> GRANT CREATE TEMPORARY TABLES ON *.* TO 'testuser'@'%';                      
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY
    -> TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON
    -> `testuser`.* TO 'testuser'@'%';
Query OK, 0 rows affected (0.03 sec)

mysql> Bye
# mysql -utestuser -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11565927
Server version: 5.0.90-log Gentoo Linux mysql-5.0.90

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases like 'pizza';
ERROR 1227 (42000): Access denied; you need the SHOW DATABASES privilege for this operation

mysql> SELECT SCHEMA_NAME AS `Database` FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME LIKE 'pizza';
+----------+
| Database |
+----------+
|  pizza   | 
+----------+
1 row in set (0.12 sec)

Thank you for the feedback.

I can not repeat described behavior as well. Please connect as testuser and provide output of SELECT USER(), CURRENT_USER() and SHOW GRANTS

mysql> SELECT USER();
+--------------------+
| USER()             |
+--------------------+
| testuser@localhost | 
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| testuser@%     | 
+----------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for testuser@%                                                                                                                                                                                    |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT CREATE TEMPORARY TABLES ON *.* TO 'testuser'@'%' IDENTIFIED BY PASSWORD '*1E2E998D033E3708D395AE47B91F6F64AD77D85E'                                                                                | 
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `testuser`.* TO 'testuser'@'%' | 
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Thank you for the feedback.

----<q>----
mysql> SELECT USER();
+--------------------+
| USER()             |
+--------------------+
| testuser@localhost | 
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| testuser@%     | 
+----------------+
1 row in set (0.00 sec)
----</q>----

Looks weird. How do you connect to MySQL? Please provide command line you are using.

Please also provide output of SELECT user, host FROM mysql.user

mysql5-8 ~ # mysql -p           
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15753807
Server version: 5.0.90-log Gentoo Linux mysql-5.0.90

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user,host from mysql.user where user like 'testuser';
+----------+------+
| user     | host |
+----------+------+
| testuser | %    | 
+----------+------+
1 row in set (0.12 sec)

I connect with testuser like this :

# mysql -utestuser -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11565927
Server version: 5.0.90-log Gentoo Linux mysql-5.0.90

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Thank you for the feedback.

I still can not repeat described behavior. Please send output of SELECT user, host FROM mysql.db also.

Please also indicate if you upgraded MySQL server lately. If you did so did you run mysql_upgrade?

mysql> select user,host from mysql.db where user like 'testuser';
+----------+------+
| user     | host |
+----------+------+
| testuser | %    | 
+----------+------+
1 row in set (0.00 sec)

I have relaunched mysql_upgrade, to try, but the problem remains

Thank you for the feedback.

I still can not repeat described behavior. Please indicate accurate package name you use (file name you downloaded).

I got it here :

ftp://mir1.ovh.net/ftp.mysql.com/Downloads/MySQL-5.0/mysql-5.0.90.tar.gz

Thank you for the feedback.

Please send us configure options you used to compile MySQL.

It's default gentoo :

./configure --prefix=/usr --host=x86_64-pc-linux-gnu --mandir=/usr/share/man --infodir=/usr/share/info --datadir=/usr/share --sysconfdir=/etc --localstatedir=/var/lib --libexecdir=/usr/sbin --sysconfdir=/etc/mysql --localstatedir=/var/lib/mysql --sharedstatedir=/usr/share/mysql --libdir=/usr/lib64/mysql --includedir=/usr/include/mysql --with-low-memory --with-client-ldflags=-lstdc++ --enable-thread-safe-client --with-comment=Gentoo Linux mysql-5.0.90 --without-docs --without-big-tables --enable-local-infile --with-extra-charsets=all --with-mysqld-user=mysql --with-server --with-unix-socket-path=/var/run/mysqld/mysqld.sock --without-libwrap --enable-shared --enable-static --without-debug --with-charset=utf8 --with-collation=utf8_general_ci --without-embedded-privilege-control --without-embedded-server --with-bench --enable-assembler --with-extra-tools --with-innodb --without-readline --with-openssl --without-berkeley-db --with-geometry --without-ndbcluster --build=x86_64-pc-linux-gnu

Thank you for the feedback.

I still can not repeat described behavior. Please try with new version 5.0.91, don't forget to run mysql_upgrade after you upgrade to 5.0.91 and if problem still exists check if you mix name-based and numeric host, send us configuration file, output of SELECT user, host FROM mysql.user and run CHECK TABLE on mysql database.

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