I am using MariaDB10 with DBForge and creating a Delphi Project, I created a role to manage the permissions but it's not working good.
1 - Create a role on DbForge panel with a permission for a procedure myprocedure
2 - Add a Tmyquery in my Delphi project with the code set default role myrole
3 - Add a procedure on afterConnect calling the query created
4 - Set disconnectedMode = true on MyConnection options
Sometimes this process works, but most times I receive an Error: Acess denied to user XX for routine myprocedure
This happens with routines, functions and tables that I try to control the permissions
Besides that every time that I give a permission on a role and after edit the procedure the role loses the permission that I gave before
Obs: The roles work okey on DbForge panel is just when I tried to put this on Delphi
If the user doesn't already have access to that role then them running set default role myrole doesn't give it to them.
SET DEFAULT ROLE requires these privileges:
Setting the default roles for another user requires the global CREATE USER privilege, or the UPDATE privilege for the mysql.default_roles system table.
Setting the default roles for yourself requires no special privileges, as long as the roles you want as the default have been granted to you.