| Bug #47961 | metadata leak from information_schema.routines via privs on mysql.proc table | ||
|---|---|---|---|
| Submitted: | 9 Oct 2009 21:51 | Modified: | 9 Oct 2009 23:28 |
| Reporter: | Jeff Stoner | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
| Version: | 5.1.39-community,5.5+ | OS: | Linux (only tested on CentOS 5.3) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | information_schema, privileges, routines | ||
[9 Oct 2009 23:28]
MySQL Verification Team
Thank you for the bug report. Verified as described.
mysql> select body from mysql.proc;
ERROR 1143 (42000): SELECT command denied to user 'bar2'@'localhost' for column 'body' in table 'proc'
mysql> select db from mysql.proc;
ERROR 1143 (42000): SELECT command denied to user 'bar2'@'localhost' for column 'db' in table 'proc'
mysql> select * from information_schema.routines\G
*************************** 1. row ***************************
SPECIFIC_NAME: bugsp
ROUTINE_CATALOG: NULL
ROUTINE_SCHEMA: foobar
ROUTINE_NAME: bugsp
ROUTINE_TYPE: PROCEDURE
DTD_IDENTIFIER: NULL
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: begin
select 1+1;
end
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: NULL
PARAMETER_STYLE: SQL
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: CONTAINS SQL
SQL_PATH: NULL
SECURITY_TYPE: DEFINER
CREATED: 2009-10-09 20:24:10
LAST_ALTERED: 2009-10-09 20:24:10
SQL_MODE:
ROUTINE_COMMENT:
DEFINER: foo1@localhost
CHARACTER_SET_CLIENT: latin1
COLLATION_CONNECTION: latin1_swedish_ci
DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.01 sec)
mysql> select version();
+------------------+
| version() |
+------------------+
| 5.5.0-beta-debug |
+------------------+
1 row in set (0.00 sec)
mysql>
[19 Oct 2009 21:33]
Timothy Smith
This is a reasonable request to improve the privilege system in this area. Granting access to mysql.* system tables should be done for privileged users only; such access really should be limited to users with SUPER privilege; other users should get their information via INFORMATION_SCHEMA or SHOW statements.
Description: If you grant a user column privileges on mysql.proc then they can access all data in mysql.proc through the information_schema.routines view. I noticed this behavior on 5.1.22 and tested again on 5.1.39-community. Similar bugs: 11577, 7214, 10964 How to repeat: $ mysql -uroot -p create database foobar; grant all privileges on foobar.* to 'foo1'@'localhost' identified by 'foo1'; grant select (name) on mysql.proc to 'bar2'@'localhost' identified by 'bar2'; \q $ mysql -ufoo1 -p use foobar; delimiter // create procedure bugsp() begin select 1+1; end// delimiter ; call bugsp(); +-----+ | 1+1 | +-----+ | 2 | +-----+ 1 row in set (0.00 sec) \q $ mysql -ubar2 -p select name from mysql.proc; +-------+ | name | +-------+ | bugsp | +-------+ 1 row in set (0.00 sec) select body from mysql.proc; ERROR 1143 (42000): SELECT command denied to user 'bar2'@'localhost' for column 'body' in table 'proc' select db from mysql.proc; ERROR 1143 (42000): SELECT command denied to user 'bar2'@'localhost' for column 'db' in table 'proc' select * from information_schema.routines\G *************************** 1. row *************************** SPECIFIC_NAME: bugsp ROUTINE_CATALOG: NULL ROUTINE_SCHEMA: foobar ROUTINE_NAME: bugsp ROUTINE_TYPE: PROCEDURE DTD_IDENTIFIER: NULL ROUTINE_BODY: SQL ROUTINE_DEFINITION: begin select 1+1; end EXTERNAL_NAME: NULL EXTERNAL_LANGUAGE: NULL PARAMETER_STYLE: SQL IS_DETERMINISTIC: NO SQL_DATA_ACCESS: CONTAINS SQL SQL_PATH: NULL SECURITY_TYPE: DEFINER CREATED: 2009-10-09 17:17:09 LAST_ALTERED: 2009-10-09 17:17:09 SQL_MODE: ROUTINE_COMMENT: DEFINER: foo1@localhost CHARACTER_SET_CLIENT: latin1 COLLATION_CONNECTION: latin1_swedish_ci DATABASE_COLLATION: latin1_swedish_ci 1 row in set (0.00 sec) Suggested fix: Privileges on mysql.proc should translate to information_schema.routines such that if the user cannot see the data in mysql.proc, they should not see the data in information_schema.routines. If this is not a bug, a detailed explanation on how privileges on the mysql schema affect the information_schema views would be very much appreciated.