Monday, April 16, 2012

can I delete stale entries from mysql.user table?

I did a bunch of GRANTS in trying to gain connectivity from a remote host. So I did :



GRANT ALL PRIVILEGES ON *.* TO 'prog'@'foobar-dev.foobar.com' WITH GRANT OPTION;


however, 'foobar-dev' no longer exists. and there are a bunch of other entries for other non existent hosts..



I did the following REVOKE, hoping the entry in mysql.user would go away :



REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'prog'@'foobar-dev.foobar.com'.


This sets all the the columns in mysql.user to "N" for 'prog'@'foobar-dev.foobar.com', but wouldn't it be better (and cleaner) to delete the row? I have not deleted the row, as this could 'break something' and violate referential integrity of system tables I am not that familiar with. But, it sort of is distracting to have bogus entries I have to see it every time I select on mysql.user to check privileges. I always thought system tables should reflect reality, not be a litany of the past.



So, does mysql.user "stand by itself" such that deleting rows in it has no side affects to other tables? what is the correct way to maintain this table?



Would this break anything?



delete from mysql.user where Host = 'foobar-dev.foobar.com' and User = 'prog';


TIA!





No comments:

Post a Comment