Bug #39807 Special grants handling for "mysql" database (SHOW GRANTS and %_PRIVILEGES tabl)

• Submitted: 2 Oct 2008 12:36
• Modified: 7 Oct 2008 11:54
• Reporter: Andrii Nikitin
• Status: Not a Bug
• Category: MySQL Server: Security: Privileges
• Severity: S4 (Feature request)
• OS: Any
• CPU Architecture: Any

[2 Oct 2008 12:36] Andrii Nikitin

Description:
Having SELECT privilege on all tables from "mysql" schema is not the same as having SELECT privilege on "mysql" schema.

If user has SELECT privilege on all objects from "mysql" schema, they still not able issue command SHOW GRANTS FOR <anotheruser> and not able see full content of INFORMATION_SCHEMA.%_PRIVILEGES tables. 

How to repeat:
create user 'user'@'localhost';
grant select on mysql.columns_priv to user@localhost;
grant select on mysql.db to user@localhost;
grant select on mysql.func to user@localhost;
grant select on mysql.help_category to user@localhost;
grant select on mysql.help_keyword to user@localhost;
grant select on mysql.help_relation to user@localhost;
grant select on mysql.help_topic to user@localhost;
grant select on mysql.host to user@localhost;
grant select on mysql.proc to user@localhost;
grant select on mysql.procs_priv to user@localhost;
grant select on mysql.tables_priv to user@localhost;
grant select on mysql.time_zone to user@localhost;
grant select on mysql.time_zone_leap_second to user@localhost;
grant select on mysql.time_zone_name to user@localhost;
grant select on mysql.time_zone_transition to user@localhost;
grant select on mysql.time_zone_transition_type to user@localhost;
grant select on mysql.user to user@localhost;
flush privileges;

connect as user@localhost:

show grants for root@localhost;
ERROR 1044 (42000): Access denied for user 'user'@'localhost' to database 'mysql'

error shown even when user has access to all tables.

now from root user:

grant select on mysql.* to user@localhost;
flush privileges;

from user@localhost:

show grants for root@localhost;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

The same for this query:
select * from information_schema.table_privileges;

(and other information_schema.%_privileges tables)

Suggested fix:
At least this bug should be logged, because permissions handled in special way here.

[7 Oct 2008 11:54] Sergei Golubchik

The manual says quite explicitly:

  SHOW GRANTS requires the SELECT privilege for the mysql database.

It does not say nor anywhere implies that SELECT privilege on individual tables in the mysql database is sufficient.

http://dev.mysql.com/doc/refman/5.0/en/show-grants.html

[7 Oct 2008 12:31] Simon Mudd

I was the original reporter of this "bug".

It looks like I didn't find this information in the manual properly.

I do think however that requiring SELECT privileges to the mysql database may grant the user more rights than the grantor may actually intend, and it would be better to not have to provide access to the whole database but only to those tables which actually have grant information, even though I see that's not currently possible because the mysql database behaves differently.