[Commits] 0ed57e3: MDEV-7025 ANALYZE SELECT/INSERT/UPDATE/DELETE from a view does not check access permissions on the view

vicentiu at mariadb.org vicentiu at mariadb.org
Tue Mar 10 22:18:09 EET 2015


revision-id: 0ed57e34c76ffa5e457e1abb402ada6352fb52b2
parent(s): c8035da9b8d037b43c27268b37df40e587780bec
committer: Vicențiu Ciorbaru
branch nick: server
timestamp: 2015-03-10 14:11:02 +0200
message:

MDEV-7025 ANALYZE SELECT/INSERT/UPDATE/DELETE from a view does not check access permissions on the view

Added access checking for the ANALYZE statement command.

---
 mysql-test/r/analyze_stmt.result             | 40 ++++++++++++++++++++++++
 mysql-test/r/grant_explain_non_select.result | 18 +++++------
 mysql-test/r/information_schema.result       |  2 +-
 mysql-test/r/view_grant.result               | 42 ++++++++++++-------------
 mysql-test/t/analyze_stmt.test               | 46 ++++++++++++++++++++++++++++
 sql/share/errmsg-utf8.txt                    |  8 ++---
 sql/sql_view.cc                              |  7 +++--
 7 files changed, 125 insertions(+), 38 deletions(-)

diff --git a/mysql-test/r/analyze_stmt.result b/mysql-test/r/analyze_stmt.result
index 5006332..4e5af76 100644
--- a/mysql-test/r/analyze_stmt.result
+++ b/mysql-test/r/analyze_stmt.result
@@ -315,3 +315,43 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_f
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	100.00	Using where
 1	SIMPLE	t2	ref	a	a	5	test.t1.a	2	0.20	100.00	100.00	Using index
 drop table t1,t2;
+#
+# MDEV-7025 and MDEV-7027 ANALYZE SELECT/INSERT/UPDATE/DELETE from a
+# view does not check access permissions on the underlying table
+#
+create database db;
+use db;
+create table t1 (i int, c varchar(8));
+insert into t1 values (1,'foo'),(2,'bar'),(3,'baz'),(4,'qux');
+create view v1 as select * from t1 where i > 1;
+grant ALL on db.v1 to u1 at localhost;
+connect  con1,localhost,u1,,;
+select * from db.t1;
+ERROR 42000: SELECT command denied to user 'u1'@'localhost' for table 't1'
+explain select * from db.t1;
+ERROR 42000: SELECT command denied to user 'u1'@'localhost' for table 't1'
+analyze select * from db.t1;
+ERROR 42000: SELECT command denied to user 'u1'@'localhost' for table 't1'
+select * from db.v1;
+i	c
+2	bar
+3	baz
+4	qux
+explain select * from db.v1;
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+analyze select * from db.v1;
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+UPDATE db.v1 SET i = 5;
+explain UPDATE db.v1 SET i = 5;
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+analyze UPDATE db.v1 SET i = 5;
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+DELETE FROM db.v1 WHERE i = 5;
+explain DELETE FROM db.v1 WHERE i = 5;
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+analyze DELETE FROM db.v1 WHERE i = 5;
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+disconnect con1;
+connection default;
+drop user u1 at localhost;
+drop database db;
diff --git a/mysql-test/r/grant_explain_non_select.result b/mysql-test/r/grant_explain_non_select.result
index 85b0ae5..8bce20f 100644
--- a/mysql-test/r/grant_explain_non_select.result
+++ b/mysql-test/r/grant_explain_non_select.result
@@ -140,7 +140,7 @@ REVOKE ALL PRIVILEGES ON privtest_db.t1 FROM 'privtest'@'localhost';
 CREATE VIEW privtest_db.v1 (a) AS SELECT a FROM privtest_db.t1;
 GRANT SELECT, INSERT, UPDATE, DELETE ON privtest_db.v1 TO 'privtest'@'localhost';
 EXPLAIN SELECT * FROM v1;
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 SELECT * FROM v1;
 a
 11
@@ -150,28 +150,28 @@ a
 4
 4
 EXPLAIN INSERT INTO v1 VALUES (10);
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 INSERT INTO v1 VALUES (10);
 EXPLAIN INSERT INTO v1 SELECT * FROM t2;
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 INSERT INTO v1 SELECT * FROM t2;
 EXPLAIN REPLACE  INTO v1 VALUES (10);
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 REPLACE  INTO v1 VALUES (10);
 EXPLAIN REPLACE INTO v1 SELECT * FROM t2;
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 REPLACE INTO v1 SELECT * FROM t2;
 EXPLAIN UPDATE v1 SET a = a + 1;
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 UPDATE v1 SET a = a + 1;
 EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a;
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a;
 EXPLAIN DELETE FROM v1 WHERE a = 10;
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 DELETE FROM v1 WHERE a = 10;
 EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a;
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a;
 DROP USER 'privtest'@localhost;
 USE test;
diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
index 3bfb8d8..1af6985 100644
--- a/mysql-test/r/information_schema.result
+++ b/mysql-test/r/information_schema.result
@@ -240,7 +240,7 @@ where table_schema = 'mysqltest' and table_name = 'v1';
 table_name	column_name	privileges
 v1	c	select
 explain select * from v1;
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 drop view v1, mysqltest.v1;
 drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5;
 drop database mysqltest;
diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result
index 8f0ffdd..550ca12 100644
--- a/mysql-test/r/view_grant.result
+++ b/mysql-test/r/view_grant.result
@@ -91,19 +91,19 @@ Field	Type	Null	Key	Default	Extra
 c	bigint(12)	YES		NULL	
 d	bigint(12)	YES		NULL	
 explain select c from mysqltest.v1;
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 show create view mysqltest.v1;
 ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v1'
 explain select c from mysqltest.v2;
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 show create view mysqltest.v2;
 ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v2'
 explain select c from mysqltest.v3;
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 show create view mysqltest.v3;
 ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v3'
 explain select c from mysqltest.v4;
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 show create view mysqltest.v4;
 ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v4'
 explain select c from mysqltest.v5;
@@ -115,7 +115,7 @@ show create view mysqltest.v5;
 View	Create View	character_set_client	collation_connection
 v5	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v5` AS select (`mysqltest`.`t1`.`a` + 1) AS `c`,(`mysqltest`.`t1`.`b` + 1) AS `d` from `mysqltest`.`t1`	latin1	latin1_swedish_ci
 explain select c from mysqltest.v1;
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 show create view mysqltest.v1;
 ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v1'
 grant show view on mysqltest.v1 to mysqltest_1 at localhost;
@@ -128,15 +128,15 @@ show create view mysqltest.v1;
 View	Create View	character_set_client	collation_connection
 v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v1` AS select (`mysqltest`.`t1`.`a` + 1) AS `c`,(`mysqltest`.`t1`.`b` + 1) AS `d` from `mysqltest`.`t1`	latin1	latin1_swedish_ci
 explain select c from mysqltest.v2;
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 show create view mysqltest.v2;
 ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v2'
 explain select c from mysqltest.v3;
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 show create view mysqltest.v3;
 ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v3'
 explain select c from mysqltest.v4;
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 show create view mysqltest.v4;
 ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v4'
 explain select c from mysqltest.v5;
@@ -156,12 +156,12 @@ show create view mysqltest.v2;
 View	Create View	character_set_client	collation_connection
 v2	CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v2` AS select (`mysqltest`.`t1`.`a` + 1) AS `c`,(`mysqltest`.`t1`.`b` + 1) AS `d` from `mysqltest`.`t1`	latin1	latin1_swedish_ci
 explain select c from mysqltest.v3;
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 show create view mysqltest.v3;
 View	Create View	character_set_client	collation_connection
 v3	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v3` AS select (`mysqltest`.`t2`.`a` + 1) AS `c`,(`mysqltest`.`t2`.`b` + 1) AS `d` from `mysqltest`.`t2`	latin1	latin1_swedish_ci
 explain select c from mysqltest.v4;
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 show create view mysqltest.v4;
 View	Create View	character_set_client	collation_connection
 v4	CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v4` AS select (`mysqltest`.`t2`.`a` + 1) AS `c`,(`mysqltest`.`t2`.`b` + 1) AS `d` from `mysqltest`.`t2`	latin1	latin1_swedish_ci
@@ -1027,7 +1027,7 @@ grant select, show view on mysqltest1.v1 to quintessa at localhost;
 select * from v1;
 i
 explain select * from v1;
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 ... as cecil
 select * from v1;
 ERROR 42000: SELECT command denied to user 'cecil'@'localhost' for table 'v1'
@@ -1043,7 +1043,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 select * from v1;
 i
 explain select * from v1;
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 ... as fiona
 select * from v2;
 i	j
@@ -1057,14 +1057,14 @@ ERROR 42000: SELECT command denied to user 'fiona'@'localhost' for table 'v1'
 explain select * from t2;
 ERROR 42000: SELECT command denied to user 'fiona'@'localhost' for table 't2'
 explain select * from v2;
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 ... as greg
 select * from v2;
 i	j
 explain select * from v1;
 ERROR 42000: SELECT command denied to user 'greg'@'localhost' for table 'v1'
 explain select * from v2;
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 ... as han
 select * from t3;
 ERROR 42000: SELECT command denied to user 'han'@'localhost' for table 't3'
@@ -1084,17 +1084,17 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 select * from v2;
 i	j
 explain select * from v2;
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 ... as jamie
 select * from v2;
 i	j
 explain select * from v2;
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 ... as karl
 select * from v2;
 i	j
 explain select * from v2;
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 ... as lena
 select * from v2;
 ERROR 42000: SELECT command denied to user 'lena'@'localhost' for table 'v2'
@@ -1111,22 +1111,22 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 select * from v2;
 i	j
 explain select * from v2;
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 ... as olga
 select * from v2;
 i	j
 explain select * from v2;
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 ... as pjotr
 select * from v2;
 i	j
 explain select * from v2;
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 ... as quintessa
 select * from v1;
 i
 explain select * from v1;
-ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
+ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 ... as root again at last: clean-up time!
 drop user alice at localhost;
 drop user bob at localhost;
diff --git a/mysql-test/t/analyze_stmt.test b/mysql-test/t/analyze_stmt.test
index b64dfe6..87b1616 100644
--- a/mysql-test/t/analyze_stmt.test
+++ b/mysql-test/t/analyze_stmt.test
@@ -259,3 +259,49 @@ analyze select * from t1 straight_join t2 force index(a) where t2.a=t1.a;
 
 drop table t1,t2;
 
+# Analyze <statement> privilege checks testcase.
+--echo #
+--echo # MDEV-7025 and MDEV-7027 ANALYZE SELECT/INSERT/UPDATE/DELETE from a
+--echo # view does not check access permissions on the underlying table
+--echo #
+--enable_connect_log
+create database db;
+use db;
+create table t1 (i int, c varchar(8));
+insert into t1 values (1,'foo'),(2,'bar'),(3,'baz'),(4,'qux');
+create view v1 as select * from t1 where i > 1;
+grant ALL on db.v1 to u1 at localhost;
+
+--connect (con1,localhost,u1,,)
+
+--error ER_TABLEACCESS_DENIED_ERROR
+select * from db.t1;
+--error ER_TABLEACCESS_DENIED_ERROR
+explain select * from db.t1;
+--error ER_TABLEACCESS_DENIED_ERROR
+analyze select * from db.t1;
+
+select * from db.v1;
+--error ER_VIEW_NO_EXPLAIN
+explain select * from db.v1;
+--error ER_VIEW_NO_EXPLAIN
+analyze select * from db.v1;
+
+UPDATE db.v1 SET i = 5;
+--error ER_VIEW_NO_EXPLAIN
+explain UPDATE db.v1 SET i = 5;
+--error ER_VIEW_NO_EXPLAIN
+analyze UPDATE db.v1 SET i = 5;
+
+DELETE FROM db.v1 WHERE i = 5;
+--error ER_VIEW_NO_EXPLAIN
+explain DELETE FROM db.v1 WHERE i = 5;
+--error ER_VIEW_NO_EXPLAIN
+analyze DELETE FROM db.v1 WHERE i = 5;
+
+
+--disconnect con1
+--connection default
+
+drop user u1 at localhost;
+drop database db;
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index 381370e..6b09b4c 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -5194,10 +5194,10 @@ ER_FPARSER_EOF_IN_UNKNOWN_PARAMETER
         rus "Неожиданный конец файла при пропуске неизвестного параметра '%-.192s'"
         ukr "Несподіванний кінець файлу у спробі проминути невідомий параметр '%-.192s'"
 ER_VIEW_NO_EXPLAIN  
-        eng "EXPLAIN/SHOW can not be issued; lacking privileges for underlying table"
-        ger "EXPLAIN/SHOW kann nicht verlangt werden. Rechte für zugrunde liegende Tabelle fehlen"
-        rus "EXPLAIN/SHOW не может быть выполнено; недостаточно прав на таблицы запроса"
-        ukr "EXPLAIN/SHOW не може бути виконано; немає прав на таблиці запиту"
+        eng "ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table"
+        ger "ANALYZE/EXPLAIN/SHOW kann nicht verlangt werden. Rechte für zugrunde liegende Tabelle fehlen"
+        rus "ANALYZE/EXPLAIN/SHOW не может быть выполнено; недостаточно прав на таблицы запроса"
+        ukr "ANALYZE/EXPLAIN/SHOW не може бути виконано; немає прав на таблиці запиту"
 ER_FRM_UNKNOWN_TYPE  
         eng "File '%-.192s' has unknown type '%-.64s' in its header"
         ger "Datei '%-.192s' hat unbekannten Typ '%-.64s' im Header"
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index a3d2b1b..d5f1967 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -1292,11 +1292,12 @@ bool mysql_make_view(THD *thd, File_parser *parser, TABLE_LIST *table,
     Security_context *security_ctx= 0;
 
     /*
-      Check rights to run commands (EXPLAIN SELECT & SHOW CREATE) which show
-      underlying tables.
+      Check rights to run commands (ANALYZE SELECT, EXPLAIN SELECT &
+      SHOW CREATE) which show underlying tables.
       Skip this step if we are opening view for prelocking only.
     */
-    if (!table->prelocking_placeholder && (old_lex->describe))
+    if (!table->prelocking_placeholder && (old_lex->describe ||
+                                           old_lex->analyze_stmt))
     {
       /*
         The user we run EXPLAIN as (either the connected user who issued


More information about the commits mailing list