Create VIEW with Security Invoker

ds00424 posted 2 years ago in General

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 posted 1 year ago

Same with me!

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

Thnx, Flingo

ds00424 posted 1 year ago

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).


ansgar posted 1 year ago

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

flingo posted 1 year ago

Works as expected! Thnx, Flingo

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