MySQL Bugs: #33135: user cannot access I_S.user_privileges and see other user privileges

Bug #33135	user cannot access I_S.user_privileges and see other user privileges
Submitted:	11 Dec 2007 11:20	Modified:	11 Dec 2007 11:46
Reporter:	Oli Sennhauser	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	6.0.2 and 5.1.22-ndb-6.3.6	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
User cannot access I_S.user_privileges table and see other user privileges. Which is from security point of view fine. But one also cannot grant select on this table to this user which is bad.

Workaround is available we need a fix, not a workaround.

How to repeat:
# mysql -u root

// First we create a user for replication

mysql> create user replication@laptop.rebenweg identified by 'replication';
mysql> grant replication slave on *.* TO replication@laptop.rebenweg;

// then we create a user to check this replication user
// Which leads already to the first "problem":

mysql> create user repl_check@laptop.rebenweg identified by '';
mysql> grant usage on *.* to repl_check@laptop.rebenweg;

mysql> grant select on information_schema.* to repl_check@laptop.rebenweg;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

mysql> grant select on information_schema.user_privileges to repl_check@laptop.rebenweg;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

// If we check the results, it looks fine:

mysql> SELECT *
 FROM information_schema.user_privileges
WHERE PRIVILEGE_TYPE= 'REPLICATION SLAVE';

+---------------------------------+---------------+-------------------+--------------+
| GRANTEE                         | TABLE_CATALOG | PRIVILEGE_TYPE    | IS_GRANTABLE |
+---------------------------------+---------------+-------------------+--------------+
| 'replication'@'laptop.rebenweg' | NULL          | REPLICATION SLAVE | NO           |
+---------------------------------+---------------+-------------------+--------------+

// When we connect as the check user it does not:

# mysql -u repl_check -P3316 --protocol=tcp --host=laptop

mysql> status
--------------
mysql  Ver 14.13 Distrib 6.0.2-alpha, for redhat-linux-gnu (i686) using readline 5.0
Current database:
Current user:           repl_check@laptop.rebenweg

mysql> SELECT * FROM information_schema.user_privileges;

+--------------------------------+---------------+----------------+--------------+
| GRANTEE                        | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+--------------------------------+---------------+----------------+--------------+
| 'repl_check'@'laptop.rebenweg' | NULL          | USAGE          | NO           |
+--------------------------------+---------------+----------------+--------------+

mysql> SELECT *
  FROM information_schema.user_privileges
 WHERE PRIVILEGE_TYPE= 'REPLICATION SLAVE';

Empty set (0.00 sec)

Suggested fix:
allow grant select on information_schema tables to a certain user.

Manual is clear about the problem:

INFORMATION_SCHEMA is the information database, the place that stores information about all the other databases that the MySQL server maintains. Inside INFORMATION_SCHEMA there are several read-only tables. They are actually views, not base tables, so there are no files associated with them.

In effect, we have a database named INFORMATION_SCHEMA, although the server does not create a database directory with that name. It is possible to select INFORMATION_SCHEMA as the default database with a USE statement, but it is possible only to read the contents of tables. You cannot insert into them, update them, or delete from them. 

<snip>

Each MySQL user has the right to access these tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges.

See also http://dev.mysql.com/doc/refman/5.0/en/information-schema.html

So I closed the report as "Not a Bug"

One cannot grant select on this table, because, according to the standard, everybody has SELECT privilege on I_S tables (views) and nobody has any other privileges on it. As even root doesn't have GRANT privilege on I_S tables, it cannot GRANT anything on them to anybody.

As far as I understood I_S is the standardized Interface to DD informations...

So one should be able to grant the FULL access to someone else than root@localhost...

Other RDBMS have pretty useful possibilities to do that.

I am not really happy with the decision, that this is not a bug. A RDBMS should provide a native way to access these informations for other users without any quick hacks and quirks...

You didn't read what I've said.

It's standardized Interface. According to the standard *nobody* has full access to it. Not even root@localhost. root cannot grant a privilege that he doesn't have.

It's not our decision, it's the decision of the SQL standard committee.