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.
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 <omitted> from `tbl1` ;
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.
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.
Thank you a lot. You are saved me!
Greate idea. Thanks Guys a lot !