Change Views DEFINER Without ALTER VIEW: How to Fix Thousands of Views

Recently I faced an interesting challenge: modify over 26k views on a single mysqld instance.
Altering the view one by one was far from an easy task, so a I had to look for an efficient way to do it. Read to find out more.

Preface:

Views have security context and their own access control.
When a view is created, a DEFINER is assigned to it (by default, the user that creates the view), and a SQL SECURITY that specifies the security context (definer by default).
Assume you use ‘user123’@’192.168.1.%’ , and you issue the follows (a very simple view):

CREATE VIEW view1 SELECT * FROM tbl1;

Behind the scene, this becomes:

CREATE ALGORITHM=UNDEFINED DEFINER=`user123`@`192.168.1.%` SQL SECURITY DEFINER VIEW `view1` AS select from `tbl1` ;

Problem:

So far, all good.
What if your application change user to `user456`@`192.168.1.%` ?
The result can be very disappointing (even if expected):

mysql> SELECT * FROM view1;
ERROR 1045 (28000): Access denied for user 'user456'@'192.168.1.%' (using password: YES)

What if you use a highly privileged user like root?

mysql> SELECT * FROM view1;
ERROR 1449 (HY000): The user specified as a definer ('user123'@'192.168.1.%') does not exist

Not much more luck, we are sort of stuck until we change permission.

ALTER VIEW has the same syntax of CREATE VIEW , ( just ‘s/CREATE/VIEW/’ ) , therefore there is no way to change only the definer: all the statement that define the view need to be re-issued.

Even if recreating the view is an easy task, isn’t that easy if you thousands and thousands of views to fix.

Solution:

There is a dirty way to do this, but it does its job!
Each view is a .frm file in the database directory.
Changing the definer is easy as editing the file changing definer_user and/or definer_host .

This is the procedure to update all the views, no matter their number (26k views updates in only few minutes):

shell> cd /var/lib/mysql
shell> for i in `find . -name "*frm"` ; do if [ `cat $i | egrep '^definer_user=user123$' | wc -l` -ne 0 ]; then echo $i ; fi ; done > /tmp/list_views.txt
# don't forget to make a backup of your views!
shell> tar cf /tmp/views_backup.tar /tmp/list_views.txt
shell> for i in `cat /tmp/list_views.txt` ; do tar rf /tmp/views_backup.tar $i ; done
shell> for i in `cat /tmp/list_views.txt` ; do cp -a $i $i.old && sed -e 's/^definer_user=user123$/definer_user=user456/' $i.old > $i && rm $i.old ; done
mysql> FLUSH TABLES;

Describing the above procedure:

• /tmp/list_views.txt lists all the views that need to be modified ;
• /tmp/views_backup.tar is a backup with all the frm listed in above list ;
• for each view (file) : copy the .frm file as .frm.old , applies the change using sed , remove the .frm.old file
• close all opened tables (included view) so the changes take effects.

References:
https://dev.mysql.com/doc/refman/5.5/en/create-view.html
https://dev.mysql.com/doc/refman/5.5/en/stored-programs-security.html
https://dev.mysql.com/doc/refman/5.5/en/alter-view.html
https://forge.mysql.com/wiki/MySQL_Internals_File_Formats

 

---

Originally posted by Rene Cannao at https://www.palominodb.com/blog/2011/10/05/change-views-definer-without-alter-view-how-fix-thousands-views