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 <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:

http://dev.mysql.com/doc/refman/5.5/en/create-view.html

http://dev.mysql.com/doc/refman/5.5/en/stored-programs-security.html

http://dev.mysql.com/doc/refman/5.5/en/alter-view.html

http://forge.mysql.com/wiki/MySQL_Internals_File_Formats

Comments

Thanks, I thought it'd be defined in a Mysql table as for procedures.That will make things faster! 

Wed, 08/28/2013 - 03:47

Awesome tip! Thanks!

Tue, 08/06/2013 - 09:02

Thanks for sharing - saved me a bunch of time after our 'definer' took a job at another company.

If you're worried about screwing things up, your backup procedures need to be revisited.

Angeber
Tue, 06/18/2013 - 01:39

There are lots of .frm files in my sql system, but only one view that is not a system view. All the system views work fine, but the view used by my original database does not work in the database that I dumoed into the latest MySQL (Xamp version) , hence the error 1449 problem.What has changed in 5.5.27 from version 5.1 that causes this error?As a windows interface user, I can see what the code above is trying to do - i.e. change the old user to the new user. But my old and new user are the same, so what do I do in this case?

Thu, 02/14/2013 - 17:55

<cite>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.</cite>Your solution is devilishly clever, however I would strongly advise the average user <strong>NEVER mess with any of the files in the MySQL data directory, ever, unless you really know what you're doing and you already made a full backup</strong>!At any rate, if it were me, I would instead re-issue the CREATE VIEW statements again and change the DEFINER to 'root' or a specific MySQL admin user you plan on never deleting or changing, and simultaneously change the views permission to SQL SECURITY INVOKER. That way you will never run into this situation again!

Thu, 12/27/2012 - 14:38

Thank you for this article! I was getting caught up when attempting to use mysqldump because a user that did not exist owned some views.

kaplanb1
Thu, 12/08/2011 - 18:40

Interesting.  I take notes.  Dirty or not.  It might not be dirty if we agree this is the format of view and that it is the source of truth.  Else, we should try to do it as much as possible via the interface mysql provide.  Unless it take age, then your way might be a solution for doing it fast (I cannot pronounce myself if the "clean" way would be slow).Some comments to try to help improve your script:

  • I think your strategy should look that the .frm is of TYPE=VIEW (there are other component than view that have a definer, and I just don't know if they store it in .frm; you might have to take care of them via .frm or other means too).  But maybe you should not if you would want to change any .frm, but some look to be in binary format.  So maybe better to create a white-filter.
  • It would be nice if your script was also handling other case with definer (like TRIGGERS, FUNCTIONS, PROCEDURES, EVENTS, etc.) as usually we will want to do it all around.
  • You are handling case with user123 and user1234 (proof: echo definer_user=user1234 | egrep '^definer_user=user123$' won't yield any line).  So I don't know if doing it with regular expression can create potential problem, but so far it looks OK.  But if there is a kind of library/fuction that handle key=value files, then maybe we should try to find this.  Else regex are good enough.
  • Question: Have you verified that it updated information_schema.views correctly?

Even thought good critic is useful, sometime it is not useful if we don't suggest a better way, so let's try.So how to do it a "cleaner" way?

  • How to get a list of view with their definer?With that query we can:<code>SELECT v.table_schema, v.table_name, v.definerFROM information_schema.views AS v;</code>Unfortunately, this will not works (at least now):<code>UPDATE information_schema.views SET definer = 'user123@test.com' WHERE definer = "user123@example.com";</code>Nice try!
  • We can use mysqldump to dump lot of thing (trigger, events, tables, views).  This will dump definer among others thing.
  • If we dump all view (with drop view), we will have to use regex (or a parser, more time) to modify the definer information just like you did, then we will have to recreate all view by running the dump.
  • We can also only dump the view that were identified with a query to identified the views for a given definer (use the first query I gave and put a WHERE v.definer='YOURDEFINER').  Then with that list, we can do a mysqldump only for the targeted views (optimisation), then filter the mysqldump definer, then just execute the dump to redefine de view with the right definer.

I cannot garantee if it will be as fast for 26k tables, but you just don't go on and play behind the mysql interface that way (unless mysql support the direct .frm edition).  If you ever want to try that technique with your 26k views, let me know about performance, I can even help you create the exact script, if it is gonna be used.Finally, how to filter your dump definer?Me I personally had to use this to do a backup WITHOUT definers (because I want to be able to reload them in development environment with my own definers), so what I did to remove the definer was to use a regex in perl like this (I had trouble doing exactly this with sed as I wanted to use ?!):<code>mysqldump ... | perl -p -e 's/\/\*(((?!\*\/).)*)DEFINER=(((?!\*\/).)*)\*\///g' \</code>Of course you might have to fix that perl filter depending on how you create your dump and/or if you want to modify the definer on the fly (me I simply erase it).  I wish MySQL had a ignore definer option or something that achieve that.  But that is not there AFAIK.If we were mad enough, we could probably create a kind-of mysql tools that handle such administration needs (there are a couple other).  Or if we were even more mad, go on and try to improve mysql/mysqldump itself to allow us to do this easily. ;-)

Sun, 10/16/2011 - 15:51

Handy and will save some time in the future I'm sure! Thanks

Wed, 10/05/2011 - 15:03

Reply

The content of this field is kept private and will not be shown publicly.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

More information about formatting options

By submitting this form, you accept the Mollom privacy policy.
Website by Digital Loom