MySQL Forums
Forum List  »  Newbie

Re: How to change definer of a mysql view!!!
Posted by: Rick James
Date: August 08, 2011 07:28PM

I'm pretty sure you have to redefine the VIEW with an ALTER statement:

# Set up the problem:
mysql> CREATE DEFINER = "Bogus" VIEW JustCity AS SELECT city FROM us;
mysql> SHOW CREATE VIEW JustCity;
+----------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View     | Create View                                                                   | character_set_client | collation_connection |
+----------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| justcity | CREATE ALGORITHM=UNDEFINED DEFINER=`Bogus`@`%` SQL SECURITY DEFINER VIEW `justcity` AS select `world`.`us`.`city` AS `city` from `us` | latin1          | latin1_swedish_ci    |
+----------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
mysql> SELECT * FROM JustCity LIMIT 4;
ERROR 1449 (HY000): The user specified as a definer ('Bogus'@'%') does not exist

# Now to fix it:
mysql> ALTER DEFINER = 'root'@'localhost' VIEW JustCity AS SELECT city FROM us;
mysql> SELECT * FROM JustCity LIMIT 4;
+-----------+
| city      |
+-----------+
| Anchorage |
| Barrow    |
| Bethel    |
| College   |
+-----------+

Options: ReplyQuote


Subject
Written By
Posted
Re: How to change definer of a mysql view!!!
August 08, 2011 07:28PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.