MySQL – Problems caused by SQL Security definer or invoker

MySQLview

I have a view used to be defined as:

> **CREATE 
>     ALGORITHM = UNDEFINED 
>     DEFINER = `user1`@`localhost` 
>     SQL SECURITY DEFINER VIEW `schema1`.`view1` AS
>     select 
>        ...
>     from
>      ...
>     where
>      ...**

The user1 has all privileges to the schema1's objects. And if I, as user2, 

> select * from view1

, it works fine. But If I do:

   SELECT * FROM schema1.view1 INTO OUTFILE '/tmp/file.txt'  FIELDS TERMINATED BY '|'

I got error saying:

Error Code: 1356. View 'schema1.view1' references invalid table(s) or
column(s) or function(s) or definer/invoker of view lack rights to use
them 0.000 sec

If I changed the definition of view from "SQL SECURITY DEFINER" to "SQL SECURITY INVOKER", and do the above query again (and I also have full privileges to the objects of the schema1), it works.

Can anybody explain why this happens? Thank a lot.

Best Answer

i got same issue on my case, my resolution re-create the view with change this part from

CREATE ALGORITHM=UNDEFINED DEFINER=`sst_user`@`10.148.0.0/255.255.240.0` SQL SECURITY DEFINER

to this

CREATE SQL SECURITY INVOKER VIEW

and test select the view from user.

Related Question