grant create view on test.* to test@localhost; show grants for test@localhost; Grants for test@localhost GRANT USAGE ON *.* TO 'test'@'localhost' GRANT CREATE VIEW ON `test`.* TO 'test'@'localhost' revoke create view on test.* from test@localhost; show grants for test@localhost; Grants for test@localhost GRANT USAGE ON *.* TO 'test'@'localhost' drop user test@localhost; create database mysqltest; create table mysqltest.t1 (a int, b int); create table mysqltest.t2 (a int, b int); grant select on mysqltest.t1 to mysqltest_1@localhost; grant create view,select on test.* to mysqltest_1@localhost; create definer=root@localhost view v1 as select * from mysqltest.t1; ERROR 42000: Access denied; you need the SUPER privilege for this operation create view v1 as select * from mysqltest.t1; alter view v1 as select * from mysqltest.t1; ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table 'v1' create or replace view v1 as select * from mysqltest.t1; ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table 'v1' create view mysqltest.v2 as select * from mysqltest.t1; ERROR 42000: CREATE VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v2' create view v2 as select * from mysqltest.t2; ERROR 42000: ANY command denied to user 'mysqltest_1'@'localhost' for table 't2' show create view v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_1`@`localhost` SQL SECURITY DEFINER VIEW `test`.`v1` AS select `mysqltest`.`t1`.`a` AS `a`,`mysqltest`.`t1`.`b` AS `b` from `mysqltest`.`t1` grant create view,drop,select on test.* to mysqltest_1@localhost; use test; alter view v1 as select * from mysqltest.t1; create or replace view v1 as select * from mysqltest.t1; revoke all privileges on mysqltest.t1 from mysqltest_1@localhost; revoke all privileges on test.* from mysqltest_1@localhost; drop database mysqltest; drop view test.v1; create database mysqltest; create table mysqltest.t1 (a int, b int); create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; grant select (c) on mysqltest.v1 to mysqltest_1@localhost; select c from mysqltest.v1; c select d from mysqltest.v1; ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'd' in table 'v1' revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; delete from mysql.user where user='mysqltest_1'; drop database mysqltest; create database mysqltest; create table mysqltest.t1 (a int, b int); create algorithm=temptable view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; grant select (c) on mysqltest.v1 to mysqltest_1@localhost; select c from mysqltest.v1; c select d from mysqltest.v1; ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'd' in table 'v1' revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; delete from mysql.user where user='mysqltest_1'; drop database mysqltest; create database mysqltest; create table mysqltest.t1 (a int, b int); create table mysqltest.t2 (a int, b int); create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; create algorithm=temptable view mysqltest.v2 (c,d) as select a+1,b+1 from mysqltest.t1; create view mysqltest.v3 (c,d) as select a+1,b+1 from mysqltest.t2; create algorithm=temptable view mysqltest.v4 (c,d) as select a+1,b+1 from mysqltest.t2; grant select on mysqltest.v1 to mysqltest_1@localhost; grant select on mysqltest.v2 to mysqltest_1@localhost; grant select on mysqltest.v3 to mysqltest_1@localhost; grant select on mysqltest.v4 to mysqltest_1@localhost; select c from mysqltest.v1; c select c from mysqltest.v2; c select c from mysqltest.v3; c select c from mysqltest.v4; c show columns from mysqltest.v1; Field Type Null Key Default Extra c bigint(12) YES NULL d bigint(12) YES NULL show columns from mysqltest.v2; 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 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 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 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 show create view mysqltest.v4; ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v4' grant select on mysqltest.t1 to mysqltest_1@localhost; explain select c from mysqltest.v1; 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 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; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found 2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const 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 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 show create view mysqltest.v4; ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v4' grant show view on mysqltest.* to mysqltest_1@localhost; explain select c from mysqltest.v1; 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 show create view mysqltest.v1; View Create View 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` explain select c from mysqltest.v2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found 2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table show create view mysqltest.v2; View Create View 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` explain select c from mysqltest.v3; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 system NULL NULL NULL NULL 0 const row not found show create view mysqltest.v3; View Create View 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` explain select c from mysqltest.v4; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found 2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table show create view mysqltest.v4; View Create View 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` revoke all privileges on mysqltest.* from mysqltest_1@localhost; delete from mysql.user where user='mysqltest_1'; drop database mysqltest; create database mysqltest; create table mysqltest.t1 (a int, b int, primary key(a)); insert into mysqltest.t1 values (10,2), (20,3), (30,4), (40,5), (50,10); create table mysqltest.t2 (x int); insert into mysqltest.t2 values (3), (4), (5), (6); create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1; create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1; create view mysqltest.v3 (a,c) as select a, b+1 from mysqltest.t1; grant update (a) on mysqltest.v2 to mysqltest_1@localhost; grant update on mysqltest.v1 to mysqltest_1@localhost; grant select on mysqltest.* to mysqltest_1@localhost; use mysqltest; update t2,v1 set v1.a=v1.a+v1.c where t2.x=v1.c; select * from t1; a b 13 2 24 3 35 4 46 5 50 10 update v1 set a=a+c; select * from t1; a b 16 2 28 3 40 4 52 5 61 10 update t2,v2 set v2.a=v2.a+v2.c where t2.x=v2.c; select * from t1; a b 16 2 31 3 44 4 57 5 61 10 update v2 set a=a+c; select * from t1; a b 18 2 34 3 48 4 62 5 71 10 update t2,v2 set v2.c=v2.a+v2.c where t2.x=v2.c; ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for column 'c' in table 'v2' update v2 set c=a+c; ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for column 'c' in table 'v2' update t2,v3 set v3.a=v3.a+v3.c where t2.x=v3.c; ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for table 'v3' update v3 set a=a+c; ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for table 'v3' use test; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; drop database mysqltest; create database mysqltest; create table mysqltest.t1 (a int, b int, primary key(a)); insert into mysqltest.t1 values (1,2), (2,3), (3,4), (4,5), (5,10); create table mysqltest.t2 (x int); insert into mysqltest.t2 values (3), (4), (5), (6); create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1; create view mysqltest.v2 (a,c) as select a, b+1 from mysqltest.t1; grant delete on mysqltest.v1 to mysqltest_1@localhost; grant select on mysqltest.* to mysqltest_1@localhost; use mysqltest; delete from v1 where c < 4; select * from t1; a b 2 3 3 4 4 5 5 10 delete v1 from t2,v1 where t2.x=v1.c; select * from t1; a b 5 10 delete v2 from t2,v2 where t2.x=v2.c; ERROR 42000: DELETE command denied to user 'mysqltest_1'@'localhost' for table 'v2' delete from v2 where c < 4; ERROR 42000: DELETE command denied to user 'mysqltest_1'@'localhost' for table 'v2' use test; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; drop database mysqltest; create database mysqltest; create table mysqltest.t1 (a int, b int, primary key(a)); insert into mysqltest.t1 values (1,2), (2,3); create table mysqltest.t2 (x int, y int); insert into mysqltest.t2 values (3,4); create view mysqltest.v1 (a,c) as select a, b from mysqltest.t1; create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1; grant insert on mysqltest.v1 to mysqltest_1@localhost; grant select on mysqltest.* to mysqltest_1@localhost; use mysqltest; insert into v1 values (5,6); select * from t1; a b 1 2 2 3 5 6 insert into v1 select x,y from t2; select * from t1; a b 1 2 2 3 5 6 3 4 insert into v2 values (5,6); ERROR 42000: INSERT command denied to user 'mysqltest_1'@'localhost' for table 'v2' insert into v2 select x,y from t2; ERROR 42000: INSERT command denied to user 'mysqltest_1'@'localhost' for table 'v2' use test; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; drop database mysqltest; create database mysqltest; create table mysqltest.t1 (a int, b int); create table mysqltest.t2 (a int, b int); grant update on mysqltest.t1 to mysqltest_1@localhost; grant update(b) on mysqltest.t2 to mysqltest_1@localhost; grant create view,update on test.* to mysqltest_1@localhost; create view v1 as select * from mysqltest.t1; create view v2 as select b from mysqltest.t2; create view mysqltest.v1 as select * from mysqltest.t1; ERROR 42000: CREATE VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v1' create view v3 as select a from mysqltest.t2; ERROR 42000: ANY command denied to user 'mysqltest_1'@'localhost' for column 'a' in table 't2' create table mysqltest.v3 (b int); grant create view on mysqltest.v3 to mysqltest_1@localhost; drop table mysqltest.v3; create view mysqltest.v3 as select b from mysqltest.t2; grant create view, update on mysqltest.v3 to mysqltest_1@localhost; drop view mysqltest.v3; create view mysqltest.v3 as select b from mysqltest.t2; grant create view, update, insert on mysqltest.v3 to mysqltest_1@localhost; drop view mysqltest.v3; create view mysqltest.v3 as select b from mysqltest.t2; ERROR 42000: create view command denied to user 'mysqltest_1'@'localhost' for column 'b' in table 'v3' create table mysqltest.v3 (b int); grant select(b) on mysqltest.v3 to mysqltest_1@localhost; drop table mysqltest.v3; create view mysqltest.v3 as select b from mysqltest.t2; ERROR 42000: create view command denied to user 'mysqltest_1'@'localhost' for column 'b' in table 'v3' create view v4 as select b+1 from mysqltest.t2; ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'b' in table 't2' grant create view,update,select on test.* to mysqltest_1@localhost; create view v4 as select b+1 from mysqltest.t2; ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'b' in table 't2' grant update,select(b) on mysqltest.t2 to mysqltest_1@localhost; create view v4 as select b+1 from mysqltest.t2; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; drop database mysqltest; drop view v1,v2,v4; create database mysqltest; create table mysqltest.t1 (a int); grant all privileges on mysqltest.* to mysqltest_1@localhost; use mysqltest; create view v1 as select * from t1; use test; revoke all privileges on mysqltest.* from mysqltest_1@localhost; drop database mysqltest; create database mysqltest; create table mysqltest.t1 (a int, b int); grant select on mysqltest.t1 to mysqltest_1@localhost; grant create view,select on test.* to mysqltest_1@localhost; create view v1 as select * from mysqltest.t1; show create view v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_1`@`localhost` SQL SECURITY DEFINER VIEW `test`.`v1` AS select `mysqltest`.`t1`.`a` AS `a`,`mysqltest`.`t1`.`b` AS `b` from `mysqltest`.`t1` revoke select on mysqltest.t1 from mysqltest_1@localhost; select * from v1; ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them grant select on mysqltest.t1 to mysqltest_1@localhost; select * from v1; a b REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; drop view v1; drop database mysqltest; create database mysqltest; use mysqltest; create table t1 (a int); insert into t1 values (1); create table t2 (s1 int); drop function if exists f2; create function f2 () returns int begin declare v int; select s1 from t2 into v; return v; end// create algorithm=TEMPTABLE view v1 as select f2() from t1; create algorithm=MERGE view v2 as select f2() from t1; create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select f2() from t1; create algorithm=MERGE SQL SECURITY INVOKER view v4 as select f2() from t1; create SQL SECURITY INVOKER view v5 as select * from v4; grant select on v1 to mysqltest_1@localhost; grant select on v2 to mysqltest_1@localhost; grant select on v3 to mysqltest_1@localhost; grant select on v4 to mysqltest_1@localhost; grant select on v5 to mysqltest_1@localhost; use mysqltest; select * from v1; f2() NULL Warnings: Warning 1329 No data - zero rows fetched, selected, or processed select * from v2; f2() NULL Warnings: Warning 1329 No data - zero rows fetched, selected, or processed select * from v3; ERROR HY000: View 'mysqltest.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them select * from v4; ERROR HY000: View 'mysqltest.v4' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them select * from v5; ERROR HY000: View 'mysqltest.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them use test; drop view v1, v2, v3, v4, v5; drop function f2; drop table t1, t2; use test; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; drop database mysqltest; create database mysqltest; use mysqltest; create table t1 (a int); insert into t1 values (1); create table t2 (s1 int); drop function if exists f2; create function f2 () returns int begin declare v int; select s1 from t2 into v; return v; end// grant select on t1 to mysqltest_1@localhost; grant execute on function f2 to mysqltest_1@localhost; grant create view on mysqltest.* to mysqltest_1@localhost; use mysqltest; create algorithm=TEMPTABLE view v1 as select f2() from t1; create algorithm=MERGE view v2 as select f2() from t1; create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select f2() from t1; create algorithm=MERGE SQL SECURITY INVOKER view v4 as select f2() from t1; use test; create view v5 as select * from v1; revoke execute on function f2 from mysqltest_1@localhost; select * from v1; ERROR HY000: View 'mysqltest.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them select * from v2; ERROR HY000: View 'mysqltest.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them select * from v3; f2() NULL Warnings: Warning 1329 No data - zero rows fetched, selected, or processed select * from v4; f2() NULL Warnings: Warning 1329 No data - zero rows fetched, selected, or processed select * from v5; ERROR HY000: View 'mysqltest.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them drop view v1, v2, v3, v4, v5; drop function f2; drop table t1, t2; use test; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; drop database mysqltest; create database mysqltest; use mysqltest; create table t1 (a int); create table v1 (a int); insert into t1 values (1); grant select on t1 to mysqltest_1@localhost; grant select on v1 to mysqltest_1@localhost; grant create view on mysqltest.* to mysqltest_1@localhost; drop table v1; use mysqltest; create algorithm=TEMPTABLE view v1 as select *, a as b from t1; create algorithm=MERGE view v2 as select *, a as b from t1; create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select *, a as b from t1; create algorithm=MERGE SQL SECURITY INVOKER view v4 as select *, a as b from t1; create view v5 as select * from v1; use test; revoke select on t1 from mysqltest_1@localhost; select * from v1; ERROR HY000: View 'mysqltest.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them select * from v2; ERROR HY000: View 'mysqltest.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them select * from v3; a b 1 1 select * from v4; a b 1 1 select * from v5; ERROR HY000: View 'mysqltest.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them drop table t1; use test; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; drop database mysqltest; create database mysqltest; use mysqltest; create table t1 (a int); insert into t1 values (1); create algorithm=TEMPTABLE view v1 as select *, a as b from t1; create algorithm=MERGE view v2 as select *, a as b from t1; create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select *, a as b from t1; create algorithm=MERGE SQL SECURITY INVOKER view v4 as select *, a as b from t1; create SQL SECURITY INVOKER view v5 as select * from v4; grant select on v1 to mysqltest_1@localhost; grant select on v2 to mysqltest_1@localhost; grant select on v3 to mysqltest_1@localhost; grant select on v4 to mysqltest_1@localhost; grant select on v5 to mysqltest_1@localhost; use mysqltest; select * from v1; a b 1 1 select * from v2; a b 1 1 select * from v3; ERROR HY000: View 'mysqltest.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them select * from v4; ERROR HY000: View 'mysqltest.v4' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them select * from v5; ERROR HY000: View 'mysqltest.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them use test; drop view v1, v2, v3, v4, v5; drop table t1; use test; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; drop database mysqltest; drop view if exists v1; create table t1 as select * from mysql.user where user=''; delete from mysql.user where user=''; flush privileges; grant all on test.* to 'test14256'@'%'; use test; create view v1 as select 42; show create view v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`test14256`@`%` SQL SECURITY DEFINER VIEW `v1` AS select 42 AS `42` select definer into @v1def1 from information_schema.views where table_schema = 'test' and table_name='v1'; drop view v1; create definer=`test14256`@`%` view v1 as select 42; show create view v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`test14256`@`%` SQL SECURITY DEFINER VIEW `v1` AS select 42 AS `42` select definer into @v1def2 from information_schema.views where table_schema = 'test' and table_name='v1'; drop view v1; select @v1def1, @v1def2, @v1def1=@v1def2; @v1def1 @v1def2 @v1def1=@v1def2 test14256@% test14256@% 1 drop user test14256; insert into mysql.user select * from t1; flush privileges; drop table t1; create database mysqltest; use mysqltest; CREATE TABLE t1 (i INT); CREATE VIEW v1 AS SELECT * FROM t1; SHOW CREATE VIEW v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i` from `t1` GRANT SELECT, LOCK TABLES ON mysqltest.* TO mysqltest_1@localhost; use mysqltest; LOCK TABLES v1 READ; SHOW CREATE TABLE v1; ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v1' UNLOCK TABLES; use test; use test; drop user mysqltest_1@localhost; drop database mysqltest; create definer=some_user@`` sql security invoker view v1 as select 1; Warnings: Note 1449 There is no 'some_user'@'' registered create definer=some_user@localhost sql security invoker view v2 as select 1; Warnings: Note 1449 There is no 'some_user'@'localhost' registered show create view v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`some_user`@`` SQL SECURITY INVOKER VIEW `v1` AS select 1 AS `1` show create view v2; View Create View v2 CREATE ALGORITHM=UNDEFINED DEFINER=`some_user`@`localhost` SQL SECURITY INVOKER VIEW `v2` AS select 1 AS `1` drop view v1; drop view v2; CREATE DATABASE mysqltest1; CREATE USER readonly@localhost; CREATE TABLE mysqltest1.t1 (x INT); INSERT INTO mysqltest1.t1 VALUES (1), (2); CREATE SQL SECURITY INVOKER VIEW mysqltest1.v_t1 AS SELECT * FROM mysqltest1.t1; CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_ts AS SELECT * FROM mysqltest1.t1; CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_ti AS SELECT * FROM mysqltest1.t1; CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tu AS SELECT * FROM mysqltest1.t1; CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tus AS SELECT * FROM mysqltest1.t1; CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_td AS SELECT * FROM mysqltest1.t1; CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tds AS SELECT * FROM mysqltest1.t1; GRANT SELECT, INSERT, UPDATE, DELETE ON mysqltest1.v_t1 TO readonly; GRANT SELECT ON mysqltest1.v_ts TO readonly; GRANT INSERT ON mysqltest1.v_ti TO readonly; GRANT UPDATE ON mysqltest1.v_tu TO readonly; GRANT UPDATE,SELECT ON mysqltest1.v_tus TO readonly; GRANT DELETE ON mysqltest1.v_td TO readonly; GRANT DELETE,SELECT ON mysqltest1.v_tds TO readonly; SELECT * FROM mysqltest1.v_t1; ERROR HY000: View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them INSERT INTO mysqltest1.v_t1 VALUES(4); ERROR HY000: View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them DELETE FROM mysqltest1.v_t1 WHERE x = 1; ERROR HY000: View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them UPDATE mysqltest1.v_t1 SET x = 3 WHERE x = 2; ERROR HY000: View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them UPDATE mysqltest1.v_t1 SET x = 3; ERROR HY000: View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them DELETE FROM mysqltest1.v_t1; ERROR HY000: View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them SELECT 1 FROM mysqltest1.v_t1; ERROR HY000: View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them SELECT * FROM mysqltest1.t1; ERROR 42000: SELECT command denied to user 'readonly'@'localhost' for table 't1' SELECT * FROM mysqltest1.v_ts; x 1 2 SELECT * FROM mysqltest1.v_ts, mysqltest1.t1 WHERE mysqltest1.t1.x = mysqltest1.v_ts.x; ERROR 42000: SELECT command denied to user 'readonly'@'localhost' for table 't1' SELECT * FROM mysqltest1.v_ti; ERROR 42000: SELECT command denied to user 'readonly'@'localhost' for table 'v_ti' INSERT INTO mysqltest1.v_ts VALUES (100); ERROR 42000: INSERT command denied to user 'readonly'@'localhost' for table 'v_ts' INSERT INTO mysqltest1.v_ti VALUES (100); UPDATE mysqltest1.v_ts SET x= 200 WHERE x = 100; ERROR 42000: UPDATE command denied to user 'readonly'@'localhost' for table 'v_ts' UPDATE mysqltest1.v_ts SET x= 200; ERROR 42000: UPDATE command denied to user 'readonly'@'localhost' for table 'v_ts' UPDATE mysqltest1.v_tu SET x= 200 WHERE x = 100; UPDATE mysqltest1.v_tus SET x= 200 WHERE x = 100; UPDATE mysqltest1.v_tu SET x= 200; DELETE FROM mysqltest1.v_ts WHERE x= 200; ERROR 42000: DELETE command denied to user 'readonly'@'localhost' for table 'v_ts' DELETE FROM mysqltest1.v_ts; ERROR 42000: DELETE command denied to user 'readonly'@'localhost' for table 'v_ts' DELETE FROM mysqltest1.v_td WHERE x= 200; ERROR 42000: SELECT command denied to user 'readonly'@'localhost' for column 'x' in table 'v_td' DELETE FROM mysqltest1.v_tds WHERE x= 200; DELETE FROM mysqltest1.v_td; DROP VIEW mysqltest1.v_tds; DROP VIEW mysqltest1.v_td; DROP VIEW mysqltest1.v_tus; DROP VIEW mysqltest1.v_tu; DROP VIEW mysqltest1.v_ti; DROP VIEW mysqltest1.v_ts; DROP VIEW mysqltest1.v_t1; DROP TABLE mysqltest1.t1; DROP USER readonly@localhost; DROP DATABASE mysqltest1; CREATE TABLE t1 (a INT PRIMARY KEY); INSERT INTO t1 VALUES (1), (2), (3); CREATE DEFINER = 'no-such-user'@localhost VIEW v AS SELECT a from t1; Warnings: Note 1449 There is no 'no-such-user'@'localhost' registered SHOW CREATE VIEW v; View Create View v CREATE ALGORITHM=UNDEFINED DEFINER=`no-such-user`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `t1`.`a` AS `a` from `t1` Warnings: Note 1449 There is no 'no-such-user'@'localhost' registered SELECT * FROM v; ERROR HY000: There is no 'no-such-user'@'localhost' registered DROP VIEW v; DROP TABLE t1; USE test; CREATE USER mysqltest_db1@localhost identified by 'PWD'; GRANT ALL ON mysqltest_db1.* TO mysqltest_db1@localhost WITH GRANT OPTION; CREATE SCHEMA mysqltest_db1 ; USE mysqltest_db1 ; CREATE TABLE t1 (f1 INTEGER); CREATE VIEW view1 AS SELECT * FROM t1; SHOW CREATE VIEW view1; View Create View view1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_db1`@`localhost` SQL SECURITY DEFINER VIEW `view1` AS select `t1`.`f1` AS `f1` from `t1` CREATE VIEW view2 AS SELECT * FROM view1; # Here comes a suspicious warning SHOW CREATE VIEW view2; View Create View view2 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_db1`@`localhost` SQL SECURITY DEFINER VIEW `view2` AS select `view1`.`f1` AS `f1` from `view1` # But the view view2 is usable SELECT * FROM view2; f1 CREATE VIEW view3 AS SELECT * FROM view2; SELECT * from view3; f1 DROP VIEW mysqltest_db1.view3; DROP VIEW mysqltest_db1.view2; DROP VIEW mysqltest_db1.view1; DROP TABLE mysqltest_db1.t1; DROP SCHEMA mysqltest_db1; DROP USER mysqltest_db1@localhost; CREATE DATABASE test1; CREATE DATABASE test2; CREATE TABLE test1.t0 (a VARCHAR(20)); CREATE TABLE test2.t1 (a VARCHAR(20)); CREATE VIEW test2.t3 AS SELECT * FROM test1.t0; CREATE OR REPLACE VIEW test.v1 AS SELECT ta.a AS col1, tb.a AS col2 FROM test2.t3 ta, test2.t1 tb; DROP VIEW test.v1; DROP VIEW test2.t3; DROP TABLE test2.t1, test1.t0; DROP DATABASE test2; DROP DATABASE test1; DROP VIEW IF EXISTS v1; DROP VIEW IF EXISTS v2; DROP VIEW IF EXISTS v3; DROP FUNCTION IF EXISTS f1; DROP FUNCTION IF EXISTS f2; DROP PROCEDURE IF EXISTS p1; CREATE SQL SECURITY DEFINER VIEW v1 AS SELECT CURRENT_USER() AS cu; CREATE FUNCTION f1() RETURNS VARCHAR(77) SQL SECURITY INVOKER RETURN CURRENT_USER(); CREATE SQL SECURITY DEFINER VIEW v2 AS SELECT f1() AS cu; CREATE PROCEDURE p1(OUT cu VARCHAR(77)) SQL SECURITY INVOKER SET cu= CURRENT_USER(); CREATE FUNCTION f2() RETURNS VARCHAR(77) SQL SECURITY INVOKER BEGIN DECLARE cu VARCHAR(77); CALL p1(cu); RETURN cu; END| CREATE SQL SECURITY DEFINER VIEW v3 AS SELECT f2() AS cu; CREATE USER mysqltest_u1@localhost; GRANT ALL ON test.* TO mysqltest_u1@localhost; The following tests should all return 1. SELECT CURRENT_USER() = 'mysqltest_u1@localhost'; CURRENT_USER() = 'mysqltest_u1@localhost' 1 SELECT f1() = 'mysqltest_u1@localhost'; f1() = 'mysqltest_u1@localhost' 1 CALL p1(@cu); SELECT @cu = 'mysqltest_u1@localhost'; @cu = 'mysqltest_u1@localhost' 1 SELECT f2() = 'mysqltest_u1@localhost'; f2() = 'mysqltest_u1@localhost' 1 SELECT cu = 'root@localhost' FROM v1; cu = 'root@localhost' 1 SELECT cu = 'root@localhost' FROM v2; cu = 'root@localhost' 1 SELECT cu = 'root@localhost' FROM v3; cu = 'root@localhost' 1 DROP VIEW v3; DROP FUNCTION f2; DROP PROCEDURE p1; DROP FUNCTION f1; DROP VIEW v2; DROP VIEW v1; DROP USER mysqltest_u1@localhost; CREATE DATABASE db17254; USE db17254; CREATE TABLE t1 (f1 INT); INSERT INTO t1 VALUES (10),(20); CREATE USER def_17254@localhost; GRANT SELECT ON db17254.* TO def_17254@localhost; CREATE USER inv_17254@localhost; GRANT SELECT ON db17254.t1 TO inv_17254@localhost; GRANT CREATE VIEW ON db17254.* TO def_17254@localhost; CREATE VIEW v1 AS SELECT * FROM t1; DROP USER def_17254@localhost; for a user SELECT * FROM v1; ERROR 42000: SELECT command denied to user 'inv_17254'@'localhost' for table 'v1' for a superuser SELECT * FROM v1; ERROR HY000: There is no 'def_17254'@'localhost' registered DROP USER inv_17254@localhost; DROP DATABASE db17254;