Attachment #138868 for bug #201669




GRANT DROP, CREATE VIEW ON db26813.v3 TO u26813@localhost;
GRANT SELECT ON db26813.t1 TO u26813@localhost;
ALTER VIEW v1 AS SELECT f2 FROM t1;
ERROR 42000: Access denied; you need the SUPER privilege for this operation
ALTER VIEW v2 AS SELECT f2 FROM t1;
ERROR 42000: Access denied; you need the SUPER privilege for this operation
ALTER VIEW v3 AS SELECT f2 FROM t1;
ERROR 42000: Access denied; you need the SUPER privilege for this operation
SHOW CREATE VIEW v3;
View	Create View
v3	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`f1` AS `f1` from `t1`
DROP USER u26813@localhost;
DROP DATABASE db26813;
#
# Bug#29908: A user can gain additional access through the ALTER VIEW.
#
CREATE DATABASE mysqltest_29908;
USE mysqltest_29908;
CREATE TABLE t1(f1 INT, f2 INT);
CREATE USER u29908_1@localhost;
CREATE DEFINER = u29908_1@localhost VIEW v1 AS SELECT f1 FROM t1;
CREATE DEFINER = u29908_1@localhost SQL SECURITY INVOKER VIEW v2 AS
SELECT f1 FROM t1;
GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v1 TO u29908_1@localhost;
GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_1@localhost;
GRANT SELECT ON mysqltest_29908.t1 TO u29908_1@localhost;
CREATE USER u29908_2@localhost;
GRANT DROP, CREATE VIEW ON mysqltest_29908.v1 TO u29908_2@localhost;
GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_2@localhost;
GRANT SELECT ON mysqltest_29908.t1 TO u29908_2@localhost;
ALTER VIEW v1 AS SELECT f2 FROM t1;
ERROR 42000: Access denied; you need the SUPER privilege for this operation
ALTER VIEW v2 AS SELECT f2 FROM t1;
SHOW CREATE VIEW v2;
View	Create View
v2	CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY INVOKER VIEW `v2` AS select `t1`.`f2` AS `f2` from `t1`
ALTER VIEW v1 AS SELECT f2 FROM t1;
SHOW CREATE VIEW v1;
View	Create View
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f2` AS `f2` from `t1`
ALTER VIEW v2 AS SELECT f1 FROM t1;
SHOW CREATE VIEW v2;
View	Create View
v2	CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY INVOKER VIEW `v2` AS select `t1`.`f1` AS `f1` from `t1`
ALTER VIEW v1 AS SELECT f1 FROM t1;
SHOW CREATE VIEW v1;
View	Create View
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1`
ALTER VIEW v2 AS SELECT f2 FROM t1;
SHOW CREATE VIEW v2;
View	Create View
v2	CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY INVOKER VIEW `v2` AS select `t1`.`f2` AS `f2` from `t1`
DROP USER u29908_1@localhost;
DROP USER u29908_2@localhost;
DROP DATABASE mysqltest_29908;
#######################################################################
DROP DATABASE IF EXISTS mysqltest1;
DROP DATABASE IF EXISTS mysqltest2;
CREATE DATABASE mysqltest1;





connect (u1,localhost,u26813,,db26813);
connection u1;
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
ALTER VIEW v1 AS SELECT f2 FROM t1;
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
ALTER VIEW v2 AS SELECT f2 FROM t1;
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
ALTER VIEW v3 AS SELECT f2 FROM t1;

connection root;

DROP DATABASE db26813;
disconnect u1;

--echo #
--echo # Bug#29908: A user can gain additional access through the ALTER VIEW.
--echo #
connection root;
CREATE DATABASE mysqltest_29908;
USE mysqltest_29908;
CREATE TABLE t1(f1 INT, f2 INT);
CREATE USER u29908_1@localhost;
CREATE DEFINER = u29908_1@localhost VIEW v1 AS SELECT f1 FROM t1;
CREATE DEFINER = u29908_1@localhost SQL SECURITY INVOKER VIEW v2 AS
  SELECT f1 FROM t1;
GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v1 TO u29908_1@localhost;
GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_1@localhost;
GRANT SELECT ON mysqltest_29908.t1 TO u29908_1@localhost;
CREATE USER u29908_2@localhost;
GRANT DROP, CREATE VIEW ON mysqltest_29908.v1 TO u29908_2@localhost;
GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_2@localhost;
GRANT SELECT ON mysqltest_29908.t1 TO u29908_2@localhost;

connect (u2,localhost,u29908_2,,mysqltest_29908);
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
ALTER VIEW v1 AS SELECT f2 FROM t1;
ALTER VIEW v2 AS SELECT f2 FROM t1;
SHOW CREATE VIEW v2;

connect (u1,localhost,u29908_1,,mysqltest_29908);
ALTER VIEW v1 AS SELECT f2 FROM t1;
SHOW CREATE VIEW v1;
ALTER VIEW v2 AS SELECT f1 FROM t1;
SHOW CREATE VIEW v2;

connection root;
ALTER VIEW v1 AS SELECT f1 FROM t1;
SHOW CREATE VIEW v1;
ALTER VIEW v2 AS SELECT f2 FROM t1;
SHOW CREATE VIEW v2;

DROP USER u29908_1@localhost;
DROP USER u29908_2@localhost;
DROP DATABASE mysqltest_29908;
disconnect u1;
disconnect u2;
--echo #######################################################################

#
# BUG#24040: Create View don't succed with "all privileges" on a database.
#




{
  LEX *lex= thd->lex;
  bool link_to_local;
#ifndef NO_EMBEDDED_ACCESS_CHECKS
  bool definer_check_is_needed= mode != VIEW_ALTER || lex->definer;
#endif
  /* first table in list is target VIEW name => cut off it */
  TABLE_LIST *view= lex->unlink_first_table(&link_to_local);
  TABLE_LIST *tables= lex->query_tables;

      - same as current user
      - current user has SUPER_ACL
  */
  if (lex->definer &&
      (strcmp(lex->definer->user.str, thd->security_ctx->priv_user) != 0 ||
       my_strcasecmp(system_charset_info,
                     lex->definer->host.str,

Return to bug 201669

Lines 776-790 GRANT CREATE VIEW ON db26813.v2 TO u2681 Link Here

(-)mysql/mysql-test/r/view_grant.result (-3 / +47 lines)
776	GRANT DROP, CREATE VIEW ON db26813.v3 TO u26813@localhost;	776	GRANT DROP, CREATE VIEW ON db26813.v3 TO u26813@localhost;
777	GRANT SELECT ON db26813.t1 TO u26813@localhost;	777	GRANT SELECT ON db26813.t1 TO u26813@localhost;
778	ALTER VIEW v1 AS SELECT f2 FROM t1;	778	ALTER VIEW v1 AS SELECT f2 FROM t1;
779	ERROR 42000: CREATE VIEW command denied to user 'u26813'@'localhost' for table 'v1'	779	ERROR 42000: Access denied; you need the SUPER privilege for this operation
780	ALTER VIEW v2 AS SELECT f2 FROM t1;	780	ALTER VIEW v2 AS SELECT f2 FROM t1;
781	ERROR 42000: DROP command denied to user 'u26813'@'localhost' for table 'v2'	781	ERROR 42000: Access denied; you need the SUPER privilege for this operation
782	ALTER VIEW v3 AS SELECT f2 FROM t1;	782	ALTER VIEW v3 AS SELECT f2 FROM t1;
		783	ERROR 42000: Access denied; you need the SUPER privilege for this operation
783	SHOW CREATE VIEW v3;	784	SHOW CREATE VIEW v3;
784	View Create View	785	View Create View
785	v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`f2` AS `f2` from `t1`	786	v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`f1` AS `f1` from `t1`
786	DROP USER u26813@localhost;	787	DROP USER u26813@localhost;
787	DROP DATABASE db26813;	788	DROP DATABASE db26813;
		789	#
		790	# Bug#29908: A user can gain additional access through the ALTER VIEW.
		791	#
		792	CREATE DATABASE mysqltest_29908;
		793	USE mysqltest_29908;
		794	CREATE TABLE t1(f1 INT, f2 INT);
		795	CREATE USER u29908_1@localhost;
		796	CREATE DEFINER = u29908_1@localhost VIEW v1 AS SELECT f1 FROM t1;
		797	CREATE DEFINER = u29908_1@localhost SQL SECURITY INVOKER VIEW v2 AS
		798	SELECT f1 FROM t1;
		799	GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v1 TO u29908_1@localhost;
		800	GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_1@localhost;
		801	GRANT SELECT ON mysqltest_29908.t1 TO u29908_1@localhost;
		802	CREATE USER u29908_2@localhost;
		803	GRANT DROP, CREATE VIEW ON mysqltest_29908.v1 TO u29908_2@localhost;
		804	GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_2@localhost;
		805	GRANT SELECT ON mysqltest_29908.t1 TO u29908_2@localhost;
		806	ALTER VIEW v1 AS SELECT f2 FROM t1;
		807	ERROR 42000: Access denied; you need the SUPER privilege for this operation
		808	ALTER VIEW v2 AS SELECT f2 FROM t1;
		809	SHOW CREATE VIEW v2;
		810	View Create View
		811	v2 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY INVOKER VIEW `v2` AS select `t1`.`f2` AS `f2` from `t1`
		812	ALTER VIEW v1 AS SELECT f2 FROM t1;
		813	SHOW CREATE VIEW v1;
		814	View Create View
		815	v1 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f2` AS `f2` from `t1`
		816	ALTER VIEW v2 AS SELECT f1 FROM t1;
		817	SHOW CREATE VIEW v2;
		818	View Create View
		819	v2 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY INVOKER VIEW `v2` AS select `t1`.`f1` AS `f1` from `t1`
		820	ALTER VIEW v1 AS SELECT f1 FROM t1;
		821	SHOW CREATE VIEW v1;
		822	View Create View
		823	v1 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1`
		824	ALTER VIEW v2 AS SELECT f2 FROM t1;
		825	SHOW CREATE VIEW v2;
		826	View Create View
		827	v2 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY INVOKER VIEW `v2` AS select `t1`.`f2` AS `f2` from `t1`
		828	DROP USER u29908_1@localhost;
		829	DROP USER u29908_2@localhost;
		830	DROP DATABASE mysqltest_29908;
		831	#######################################################################
788	DROP DATABASE IF EXISTS mysqltest1;	832	DROP DATABASE IF EXISTS mysqltest1;
789	DROP DATABASE IF EXISTS mysqltest2;	833	DROP DATABASE IF EXISTS mysqltest2;
790	CREATE DATABASE mysqltest1;	834	CREATE DATABASE mysqltest1;

Lines 1034-1043 GRANT SELECT ON db26813.t1 TO u26813@loc Link Here

(-)mysql/mysql-test/t/view_grant.test (-2 / +47 lines)
1034		1034
1035	connect (u1,localhost,u26813,,db26813);	1035	connect (u1,localhost,u26813,,db26813);
1036	connection u1;	1036	connection u1;
1037	--error 1142	1037	--error ER_SPECIFIC_ACCESS_DENIED_ERROR
1038	ALTER VIEW v1 AS SELECT f2 FROM t1;	1038	ALTER VIEW v1 AS SELECT f2 FROM t1;
1039	--error 1142	1039	--error ER_SPECIFIC_ACCESS_DENIED_ERROR
1040	ALTER VIEW v2 AS SELECT f2 FROM t1;	1040	ALTER VIEW v2 AS SELECT f2 FROM t1;
		1041	--error ER_SPECIFIC_ACCESS_DENIED_ERROR
1041	ALTER VIEW v3 AS SELECT f2 FROM t1;	1042	ALTER VIEW v3 AS SELECT f2 FROM t1;
1042		1043
1043	connection root;	1044	connection root;
Lines 1047-1052 DROP USER u26813@localhost; Link Here
1047	DROP DATABASE db26813;	1048	DROP DATABASE db26813;
1048	disconnect u1;	1049	disconnect u1;
1049		1050
		1051	--echo #
		1052	--echo # Bug#29908: A user can gain additional access through the ALTER VIEW.
		1053	--echo #
		1054	connection root;
		1055	CREATE DATABASE mysqltest_29908;
		1056	USE mysqltest_29908;
		1057	CREATE TABLE t1(f1 INT, f2 INT);
		1058	CREATE USER u29908_1@localhost;
		1059	CREATE DEFINER = u29908_1@localhost VIEW v1 AS SELECT f1 FROM t1;
		1060	CREATE DEFINER = u29908_1@localhost SQL SECURITY INVOKER VIEW v2 AS
		1061	SELECT f1 FROM t1;
		1062	GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v1 TO u29908_1@localhost;
		1063	GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_1@localhost;
		1064	GRANT SELECT ON mysqltest_29908.t1 TO u29908_1@localhost;
		1065	CREATE USER u29908_2@localhost;
		1066	GRANT DROP, CREATE VIEW ON mysqltest_29908.v1 TO u29908_2@localhost;
		1067	GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_2@localhost;
		1068	GRANT SELECT ON mysqltest_29908.t1 TO u29908_2@localhost;
		1069
		1070	connect (u2,localhost,u29908_2,,mysqltest_29908);
		1071	--error ER_SPECIFIC_ACCESS_DENIED_ERROR
		1072	ALTER VIEW v1 AS SELECT f2 FROM t1;
		1073	ALTER VIEW v2 AS SELECT f2 FROM t1;
		1074	SHOW CREATE VIEW v2;
		1075
		1076	connect (u1,localhost,u29908_1,,mysqltest_29908);
		1077	ALTER VIEW v1 AS SELECT f2 FROM t1;
		1078	SHOW CREATE VIEW v1;
		1079	ALTER VIEW v2 AS SELECT f1 FROM t1;
		1080	SHOW CREATE VIEW v2;
		1081
		1082	connection root;
		1083	ALTER VIEW v1 AS SELECT f1 FROM t1;
		1084	SHOW CREATE VIEW v1;
		1085	ALTER VIEW v2 AS SELECT f2 FROM t1;
		1086	SHOW CREATE VIEW v2;
		1087
		1088	DROP USER u29908_1@localhost;
		1089	DROP USER u29908_2@localhost;
		1090	DROP DATABASE mysqltest_29908;
		1091	disconnect u1;
		1092	disconnect u2;
		1093	--echo #######################################################################
		1094
1050	#	1095	#
1051	# BUG#24040: Create View don't succed with "all privileges" on a database.	1096	# BUG#24040: Create View don't succed with "all privileges" on a database.
1052	#	1097	#

Lines 224-232 bool mysql_create_view(THD *thd, TABLE_L Link Here

(-)mysql/sql/sql_view.cc (-4 / +1 lines)
224	{	224	{
225	LEX *lex= thd->lex;	225	LEX *lex= thd->lex;
226	bool link_to_local;	226	bool link_to_local;
227	#ifndef NO_EMBEDDED_ACCESS_CHECKS
228	bool definer_check_is_needed= mode != VIEW_ALTER \|\| lex->definer;
229	#endif
230	/* first table in list is target VIEW name => cut off it */	227	/* first table in list is target VIEW name => cut off it */
231	TABLE_LIST *view= lex->unlink_first_table(&link_to_local);	228	TABLE_LIST *view= lex->unlink_first_table(&link_to_local);
232	TABLE_LIST *tables= lex->query_tables;	229	TABLE_LIST *tables= lex->query_tables;
Lines 281-287 bool mysql_create_view(THD *thd, TABLE_L Link Here
281	- same as current user	278	- same as current user
282	- current user has SUPER_ACL	279	- current user has SUPER_ACL
283	*/	280	*/
284	if (definer_check_is_needed &&	281	if (lex->definer &&
285	(strcmp(lex->definer->user.str, thd->security_ctx->priv_user) != 0 \|\|	282	(strcmp(lex->definer->user.str, thd->security_ctx->priv_user) != 0 \|\|
286	my_strcasecmp(system_charset_info,	283	my_strcasecmp(system_charset_info,
287	lex->definer->host.str,	284	lex->definer->host.str,