Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Create VIEW with Security Invoker

ds00424's profile image ds00424 posted 3 years ago in General Permalink

I am using HeidiSql (v9.3.0.4984) to connect and manage my remote databases. I like the interface and would like to keep using it.

However, when I create a view it sets the Security to Definer and there is no option to set Security to Invoker. Creating a Procedure does provide an option for Security, but not creating a view.

Am I missing something or is this currently not possible in HeidiSql?

Thanks. ds00424

flingo's profile image flingo posted 3 years ago Permalink

Same with me!

Would be nice to be able to define the sql security invoker for views!

Thnx, Flingo

ds00424's profile image ds00424 posted 3 years ago Permalink

So this is becoming a bigger issue for me.

When I create or change a view on my hosted system, heidi makes the Security DEFINER. Then when my IP address changes (as it does every few months or when the power goes out at my office), I cannot remotely access my hosted databases until I add my new IP address to "Remote MySql" via cPanel. I usually remove my old address from "Remote MySql" as it is no longer needed. But now I realize that the MySql user user@<OldIpAddress> is also removed and then all my views fail until I change them all to be owned by user@<NewIpAddress>. And that is cumbersome as the view owner no longer exists so I have to delete the view and re-create it.

One solution on my part is to leave the OldIpAddress in "Remote MySql" so user@<OldIpAddress> is not deleted from MySql. But I would rather set the view to Security INVOKER and remove the bogus user.

Please, please, please - add an option for a view to have Security INVOKER.

(I am starting to use other MySql tools to change the view Security, but i really like the HeidiSql user interface over others).

Thanks.

Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
Code modification/commit 5312a37 from ansgarbecker, 3 years ago, revision 5142
Support SQL security setting in view editor via drop down menu. See http://www.heidisql.com/forum.php?t=21740
ansgar's profile image ansgar posted 3 years ago Permalink

I have added a drop down menu for setting the SQL security clause on a view, in r5142.

flingo's profile image flingo posted 3 years ago Permalink

Works as expected! Thnx, Flingo

Please login to leave a reply, or register at first.