This is an old post pulled from another blog for preservation
Recently whilst moving a legacy application, I had to make it more secure. In trying to make the database more secure I ran into one particular error in some of our Java code java.sql.BatchUpdateException: The user specified as a definer ('root'@'%') does not exist
The definer ('root'@'%') is the root of my problem (pardon the pun). Our application was accessing MySql as root user (so a compromised application could take control of all the databases on the server), even worse having root@% as a user in the database means that root access is not just restricted to local access.
Having given the application it's own user and remove root access from anywhere everything was running fine until we hit a page activated a trigger. Definers appear in a number of places in MySql including triggers, stored procedures and views (not sure what if any problems occur with a view if the defined no longer exists but will make a point to find out). My problem was an easy fix there was just one trigger that needed altering.
Although I have not had to yet, next time I run into this problem I would be looking to take an approach more similar to this one somewhere in that post or the comments there should be a working solution there to remove the definers from a MySql dump. I assume (would check) that the default definer if set by MySql would be the user credentials used to load the database again.
Fortunately the problem was spotted on a test server first.
No comments:
Post a Comment