bzr commit into mysql-5.0-bugteam branch (azundris:2894) Bug#58677

[Tatiana Azundris Nurnberg]

#At file:///Users/tnurnberg/forest/58677/50-58677/ based on revid:kent.boortz@stripped

 2894 Tatiana Azundris Nurnberg	2011-03-03
      Bug#58677: No privilege on table / view, but can know #rows / underlying table's name
      
      EXPLAIN SELECT on a view should require SELECT on the 
      underlying table; as it is, SUID (SQL SECURITY DEFINER)
      is applied, allowing the INVOKER to learn information
      about the underlying table, such as its name, number of
      rows, etc.
     @ mysql-test/t/view_grant.test
        Test cases for bug and some corner cases.
     @ sql/sql_view.cc
        Rather than just disable SUID (table->security_ctx= NULL)
        in EXPLAIN, we specify the necessary privileges for
        traversal. This helps with some corner cases.

    modified:
      mysql-test/r/view_grant.result
      mysql-test/t/view_grant.test
      sql/sql_view.cc
=== modified file 'mysql-test/r/view_grant.result'
--- a/mysql-test/r/view_grant.result	2009-08-21 14:10:55 +0000
+++ b/mysql-test/r/view_grant.result	2011-03-03 10:27:11 +0000
@@ -945,4 +945,104 @@ DROP USER foo;
 DROP VIEW db1.v1;
 DROP TABLE db1.t1;
 DROP DATABASE db1;
+Bug #58677: No privilege on table / view, but can know #rows / underlying table's name
+CREATE DATABASE db1;
+USE db1;
+CREATE TABLE t1 (f1 INT);
+CREATE VIEW v_inner AS SELECT * FROM t1;
+CREATE VIEW v_outer AS SELECT * FROM v_inner;
+CREATE USER user2@localhost IDENTIFIED BY '';
+GRANT SELECT ON v_outer TO user2@localhost;
+USE db1;
+user can SELECT v_outer
+SELECT * FROM v_outer;
+f1
+but not do EXPLAIN as it would show underlying table's name
+EXPLAIN SELECT * /* #1 */ FROM v_outer;
+ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+GRANT SHOW VIEW ON v_outer TO user2@localhost;
+user cannot see definition of v_inner
+SHOW CREATE VIEW v_inner;
+ERROR 42000: SELECT command denied to user 'user2'@'localhost' for table 'v_inner'
+neither its underlying table's name
+EXPLAIN SELECT * /* #2 */ FROM v_inner;
+ERROR 42000: SELECT command denied to user 'user2'@'localhost' for table 'v_inner'
+neither t1 directly
+EXPLAIN SELECT * /* #3 */ FROM t1;
+ERROR 42000: SELECT command denied to user 'user2'@'localhost' for table 't1'
+can see v_outer's definition
+SHOW CREATE VIEW v_outer;
+View	Create View
+v_outer	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_outer` AS select `v_inner`.`f1` AS `f1` from `v_inner`
+EXPLAIN SELECT * /* #4 */ FROM v_outer;
+ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+GRANT SHOW VIEW ON v_inner TO user2@localhost;
+fail, invoker now has has SHOW VIEW on inner, but still no SELECT on t1
+EXPLAIN SELECT * /* #5 */ FROM v_outer;
+ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+EXPLAIN SELECT * /* #6 */ FROM v_inner;
+ERROR 42000: SELECT command denied to user 'user2'@'localhost' for table 'v_inner'
+this must succeed; we have SHOW VIEW on v_inner
+SHOW CREATE VIEW v_inner;
+View	Create View
+v_inner	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_inner` AS select `t1`.`f1` AS `f1` from `t1`
+GRANT SELECT ON t1 TO user2@localhost;
+SELECT * FROM t1;
+f1
+EXPLAIN SELECT * /* #7 */ FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+don't have SELECT on v_inner
+SELECT * FROM v_inner;
+ERROR 42000: SELECT command denied to user 'user2'@'localhost' for table 'v_inner'
+EXPLAIN SELECT * /* #8 */ FROM v_inner;
+ERROR 42000: SELECT command denied to user 'user2'@'localhost' for table 'v_inner'
+SHOW CREATE VIEW v_inner;
+View	Create View
+v_inner	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_inner` AS select `t1`.`f1` AS `f1` from `t1`
+fail, invoker has SELECT on v_outer and t1, but not v_inner
+EXPLAIN SELECT * /* #9 */ FROM v_inner;
+ERROR 42000: SELECT command denied to user 'user2'@'localhost' for table 'v_inner'
+succeed, have SELECT on t1, and SHOW VIEW on both v_outer and v_inner
+EXPLAIN SELECT * /* #10 */ FROM v_outer;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+REVOKE SHOW VIEW ON v_inner FROM user2@localhost;
+FLUSH PRIVILEGES;
+SELECT * FROM t1;
+f1
+EXPLAIN SELECT * /* #11 */ FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+don't have SELECT on v_inner
+SELECT * FROM v_inner;
+ERROR 42000: SELECT command denied to user 'user2'@'localhost' for table 'v_inner'
+EXPLAIN SELECT * /* #12 */ FROM v_inner;
+ERROR 42000: SELECT command denied to user 'user2'@'localhost' for table 'v_inner'
+SHOW CREATE VIEW v_inner;
+ERROR 42000: SELECT command denied to user 'user2'@'localhost' for table 'v_inner'
+fail, invoker has SELECT on v_outer and t1, but not v_inner
+EXPLAIN SELECT * /* #13 */ FROM v_inner;
+ERROR 42000: SELECT command denied to user 'user2'@'localhost' for table 'v_inner'
+fail, neither SHOW VIEW nor SELECT on v_inner
+EXPLAIN SELECT * /* #14 */ FROM v_outer;
+ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+GRANT SELECT ON v_inner TO user2@localhost;
+SELECT on outer/inner/t1; SHOW VIEW on outer
+EXPLAIN SELECT * /* #15 */  FROM v_outer;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+EXPLAIN SELECT * /* #16 */ FROM v_inner;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+REVOKE SELECT ON t1 FROM user2@localhost;
+FLUSH PRIVILEGES;
+fail, no SELECT on t1
+EXPLAIN SELECT * /* #17 */  FROM v_outer;
+ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+fail, no SELECT on t1
+EXPLAIN SELECT * /* #18 */ FROM v_inner;
+ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+DROP DATABASE db1;
+DROP USER user2@localhost;
 End of 5.0 tests.

=== modified file 'mysql-test/t/view_grant.test'
--- a/mysql-test/t/view_grant.test	2009-08-21 14:10:55 +0000
+++ b/mysql-test/t/view_grant.test	2011-03-03 10:27:11 +0000
@@ -1232,6 +1232,131 @@ DROP TABLE db1.t1;
 DROP DATABASE db1;
 
 connection default;
+
+
+--echo Bug #58677: No privilege on table / view, but can know #rows / underlying table's name
+
+CREATE DATABASE db1;
+USE db1;
+
+CREATE TABLE t1 (f1 INT);
+CREATE VIEW v_inner AS SELECT * FROM t1;
+CREATE VIEW v_outer AS SELECT * FROM v_inner;
+
+CREATE USER user2@localhost IDENTIFIED BY '';
+GRANT SELECT ON v_outer TO user2@localhost;
+
+connect (con2, localhost, user2,,);
+connection con2;
+USE db1;
+--echo user can SELECT v_outer
+SELECT * FROM v_outer;
+--echo but not do EXPLAIN as it would show underlying table's name
+--error 1345
+EXPLAIN SELECT * /* #1 */ FROM v_outer;
+
+connection default;
+GRANT SHOW VIEW ON v_outer TO user2@localhost;
+
+connection con2;
+--echo user cannot see definition of v_inner
+--error 1142
+SHOW CREATE VIEW v_inner;
+--echo neither its underlying table's name
+--error 1142
+EXPLAIN SELECT * /* #2 */ FROM v_inner;
+--echo neither t1 directly
+--error 1142
+EXPLAIN SELECT * /* #3 */ FROM t1;
+
+--echo can see v_outer's definition
+SHOW CREATE VIEW v_outer;
+--error 1345
+EXPLAIN SELECT * /* #4 */ FROM v_outer;
+
+connection default;
+GRANT SHOW VIEW ON v_inner TO user2@localhost;
+
+connection con2;
+
+--echo fail, invoker now has has SHOW VIEW on inner, but still no SELECT on t1
+--error 1345
+EXPLAIN SELECT * /* #5 */ FROM v_outer;
+--error 1142
+EXPLAIN SELECT * /* #6 */ FROM v_inner;
+
+--echo this must succeed; we have SHOW VIEW on v_inner
+SHOW CREATE VIEW v_inner;
+
+connection default;
+GRANT SELECT ON t1 TO user2@localhost;
+
+connection con2;
+
+SELECT * FROM t1;
+EXPLAIN SELECT * /* #7 */ FROM t1;
+
+--echo don't have SELECT on v_inner
+--error 1142
+SELECT * FROM v_inner;
+--error 1142
+EXPLAIN SELECT * /* #8 */ FROM v_inner;
+SHOW CREATE VIEW v_inner;
+
+--echo fail, invoker has SELECT on v_outer and t1, but not v_inner
+--error 1142
+EXPLAIN SELECT * /* #9 */ FROM v_inner;
+--echo succeed, have SELECT on t1, and SHOW VIEW on both v_outer and v_inner
+EXPLAIN SELECT * /* #10 */ FROM v_outer;
+
+connection default;
+REVOKE SHOW VIEW ON v_inner FROM user2@localhost;
+FLUSH PRIVILEGES;
+
+connection con2;
+SELECT * FROM t1;
+EXPLAIN SELECT * /* #11 */ FROM t1;
+
+--echo don't have SELECT on v_inner
+--error 1142
+SELECT * FROM v_inner;
+--error 1142
+EXPLAIN SELECT * /* #12 */ FROM v_inner;
+--error 1142
+SHOW CREATE VIEW v_inner;
+
+--echo fail, invoker has SELECT on v_outer and t1, but not v_inner
+--error 1142
+EXPLAIN SELECT * /* #13 */ FROM v_inner;
+--echo fail, neither SHOW VIEW nor SELECT on v_inner
+--error 1345
+EXPLAIN SELECT * /* #14 */ FROM v_outer;
+
+connection default;
+GRANT SELECT ON v_inner TO user2@localhost;
+
+connection con2;
+--echo SELECT on outer/inner/t1; SHOW VIEW on outer
+EXPLAIN SELECT * /* #15 */  FROM v_outer;
+EXPLAIN SELECT * /* #16 */ FROM v_inner;
+
+connection default;
+REVOKE SELECT ON t1 FROM user2@localhost;
+FLUSH PRIVILEGES;
+
+connection con2;
+--echo fail, no SELECT on t1
+--error 1345
+EXPLAIN SELECT * /* #17 */  FROM v_outer;
+--echo fail, no SELECT on t1
+--error 1345
+EXPLAIN SELECT * /* #18 */ FROM v_inner;
+
+connection default;
+disconnect con2;
+DROP DATABASE db1;
+DROP USER user2@localhost;
+
 --echo End of 5.0 tests.
 
 # Wait till we reached the initial number of concurrent sessions

=== modified file 'sql/sql_view.cc'
--- a/sql/sql_view.cc	2009-08-28 15:51:31 +0000
+++ b/sql/sql_view.cc	2011-03-03 10:27:11 +0000
@@ -1147,8 +1147,10 @@ bool mysql_make_view(THD *thd, File_pars
     if (!table->prelocking_placeholder &&
         (old_lex->sql_command == SQLCOM_SELECT && old_lex->describe))
     {
-      if (check_table_access(thd, SELECT_ACL, view_tables, 1) &&
-          check_table_access(thd, SHOW_VIEW_ACL, table, 1))
+      if (!(!check_table_access(thd, SELECT_ACL, view_tables, 1) ||
+            !check_table_access(thd, SHOW_VIEW_ACL, view_tables, 1)) ||
+          (!(!check_table_access(thd, SELECT_ACL, table, 1) ||
+             !check_table_access(thd, SHOW_VIEW_ACL, table, 1))))
       {
         my_message(ER_VIEW_NO_EXPLAIN, ER(ER_VIEW_NO_EXPLAIN), MYF(0));
         goto err;


Attachment: [text/bzr-bundle] bzr/[e-mail]