3

I'd like to make a very simple thing, replicate the functionality of mysql's interactive mysql_secure_installation script. My question is that is there a simple, built-in way in MySQL to combine the output of a SELECT query with the input of a DROP user or DROP database script?

For example, if I'd like to drop all users with empty passwords. How could I do that with DROP USER statement? I know an obvious solution would be to run everything for example from a Python script,

  1. run a query with mysql -Bse "select..."
  2. parse the output with some program
  3. construct the drop query
  4. run it.

Is there an easy way to do it in a simple SQL query? I've seen some example here, but I wouldn't call it simple: https://stackoverflow.com/a/12097567/518169

Would you recommend making a combined query, or just to parse the output using for example Python or bash scripts/sed?

Community
  • 1
  • 1
hyperknot
  • 13,454
  • 24
  • 98
  • 153

2 Answers2

4

You can build SQL dynamically, for example:

SELECT CONCAT('DROP USER ', GROUP_CONCAT(QUOTE(User), '@', QUOTE(Host)))
INTO   @sql
FROM   mysql.user
WHERE  PASSWORD = ''

Then prepare a statement from it:

PREPARE stmt FROM @sql

Then execute that statement:

EXECUTE stmt

Finally, deallocate the prepared statement:

DEALLOCATE PREPARE stmt
eggyal
  • 122,705
  • 18
  • 212
  • 237
0

My solution is using --skip-column-names flag and piping the output of SELECT CONCAT(..) query back to another mysql session:

echo 'select concat("DROP USER ",user,"@\"",host,"\";") from mysql.user where User = "alex"' \
| mysql -uroot --skip-column-names \
| mysql -uroot

source https://stackoverflow.com/a/47902078/410939

Alex
  • 1,313
  • 14
  • 28