Tell me about yourself . . .
A little while ago MySQL introduced Stored Procedures thereby, in some peoples eyes, making it a ‘proper’ database (clearly a load of rubbish as it was totally fit for purpose anyway but that’s another argument).
For security reasons if you DROP a stored procedure in MySQL any grants to use it are also removed. This makes perfect sense as stored procedures can be set to run internally as a different user that the one that called it. If the GRANT wasn’t removed it would be possible to have a perfectly innocent procedure removed and, a while later a new one added that happened to have the same name (I know that this would require a deal of disorganisation but hey) that could drop the whole DB. The user who had access to the first procedure would then have access to the new one (bad times).
While it makes sense that the GRANT is removed when the stored procedure is removed it doesn’t really make sense that you can’t update stored procedures, you have to drop and recreate them, thereby losing all the grants applicable to them every time – damn annoying
The solution . . .The solution is to make sure you get all of the applicable grants ready to put back into the database before you drop the stored procedure. However, unless you’re very organised (I’m not) or you have a limited number of DB users to manually check (we don’t) this can be a bit of a pain.
MySQL stores all the information about a users GRANTs and PRIVILEGEs in a database called mysql so rather than doing all those lookups manually and possibly missing something, why not just get the server to tell you who it thinks has access to what. To find all of the users (user/host pairs obviously) that have access to execute a procedure called procedureName in the database dbName you can use this query.
select User, Host FROM mysql.procs_priv WHERE Routine_name=’procedureName’ AND Db=’dbName’;
Which is great, but it does mean that you’re still going to have to rewrite all the queries to put those grants back in . . . why not use CONCAT to have the query output the finished queries for you??
select CONCAT(‘GRANT ‘, Proc_priv, ‘ ON PROCEDURE `’, Db, ‘`.`’, Routine_name, ‘` TO `’, User, ‘`@`’, Host, ‘`;’) FROM mysql.procs_priv WHERE Routine_name=’procedureName’ AND Db=’dbName’;
Bingo . . . just run this before you are going to DROP a stored procedure and you can add it’s output onto the end of the stored procedure script. That way it’ll add the relevant grants straight back in 🙂
Comments
Leave a Reply
