Change Views DEFINER without ALTER VIEW : how to fix thousands of views!
Tags:
Oracle,
Technical Track
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 <omitted> 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
