0

I am migrating to a docker MariaDB setup and all my users were created w/ "Localhost". From what I read and tested ironically '%' excludes localhost. I want to copy my existing users adding a second entry utilizing '%' as the hostname. Any suggestions?

FreeSoftwareServers
  • 228
  • 1
  • 4
  • 14

1 Answers1

0

You have to call SHOW CREATE USER 'username'@'localhost' and SHOW GRANTS FOR 'username'@'localhost' for all the users fetched by 

SELECT user, host 
  FROM mysql.user
 WHERE host = 'localhost'

and to replace all 'localhost' entries by '%' in the returned statements.

Kondybas
  • 4,323
  • 15
  • 13
  • I know how to replace with UPDATE but I'd like to copy the user and then change hostname. – FreeSoftwareServers May 05 '20 at 08:06
  • @FreeSoftwareServers It's not a problem to update an user already having grants. But if you want to copy some user you have to copy his grants too. The easiest way to do that is to fetch SHOW CREATE USER and SHOW GRANTS FOR then substitute hostname by desired value and then run that statements. There is no native tool to do that at once. – Kondybas May 05 '20 at 08:51
  • Yes I was hoping for something more descriptive than, run SQL against each user and then copy the output. If nobody makes a solution I'll likely script something myself but my SQL foo is limited. – FreeSoftwareServers May 05 '20 at 09:52
  • You might find my SQL User Backer tool of use. It will extract all of the users and grants and all you'd have to do is change localhost to % and load the modified files. – Dave May 05 '20 at 10:56