Change the procedure or function loses the permission

Question

I am using DBForge and MariaDB10, what is happening is that I am using roles to control the acess to the database.

When I do some change on the procedure or function code the role loses the permission that I granted before, so I need to grant acess again.

This is bad because every time that I alter the code the user lost his acess and I need to grant again.

Anyone can help me or give me some light?

Answer 1

Neither MySQL nor MariaDB (up to the most recent versions 10.7+) support alteration of Stored Procedures or Functions. This is why they have to be dropped and re-created, leading to a total loss of all granted privileges to the procedure/function.

However, MariaDB 10.1.3+ supports the CREATE OR REPLACE PROCEDURE ... clause which preserves all granted privileges:

https://mariadb.com/kb/en/stored-routine-privileges/#dropping-stored-routines

Some commercial UI vendors also support the automatic recreation of privileges. dbForge for MySQL however has a bug in that when you use "Apply Changes" the privileges will be lost (as of version 9.0.791). Instead click on "Script Changes". The generated code will contain the regeneration of the privileges.