How to secure MySQL using command line

zsero

I'd like to write a command line script for Debian, what secures a MySQL installation, just like mysql_secure_installation.

Do you know any way to either: emulate user inputs to mysql_secure_installation, thus make it run inside a script or to replicate the functionality by SQL statements?

Here is how far I came:

Remove anonymous users: (users with no password) idea:

DROP USER ''@'localhost';

DROP USER ''@'host_name';

problem: I don't know host name in a script file, would be better with where password = '', but I don't know how to combine it with drop user.

DELETE FROM user WHERE user = '';

does it, but AFAIK, it doesn't remove privileges. I'd better use DROP USER for this.

Disallow root login remotely: I think it is to remove the root user, what's host isn't "localhost", "127.0.0.1" or "::1" Any idea how to do this? A stronger alternative is to have skip-networking in the config file:

[mysqld]

skip-networking

Remove test database and access to it:

DROP DATABASE test;

What do I need to do to remove privileges on the test database? Isn't this enought?

Finally, flusing the priviliges:

FLUSH PRIVILEGES;

References

http://dev.mysql.com/doc/refman/5.6/en/mysql-secure-installation.html

http://dev.mysql.com/doc/refman/5.6/en/default-privileges.html

eastonch

I know that minstall guy allows you to just press Y/N on running the secure Installation, don't think you can emulate that process, but surely you can make a script that does the same thing with user defined variables.

djvdorp

Tuxlite does this for you too

zsero

Both scripts just call "mysql_secure_installation", this is what I want to replicate for minstall 2.0 / unattended mode.

At the moment there is no script what does this automatically.

zhuanyi

/usr/local/mysql/bin/mysqladmin -u root password $mysqlrootpwd

cat > /tmp/mysql_sec_script<<EOF
use mysql;
update user set password=password('$mysqlrootpwd') where user='root';
delete from user where not (user='root') ;
delete from user where user='root' and password='';
drop database test;
DROP USER ''@'%';
flush privileges;
EOF

/usr/local/mysql/bin/mysql -u root -p$mysqlrootpwd -h localhost < /tmp/mysql_sec_script

rm -f /tmp/mysql_sec_script

zsero

@zhuany, Nice one! I'm writing this in Python at the moment, but it's going to be much more complex. I think it's quite hard with simple SQL to remove the users permissions from mysql.db too.

zsero

@zhuanyi said: delete from user where not (user='root') ;

And this deletes debian sys maintenance too, plus any user you might have there. I think it's a script only for fresh installations.

zhuanyi

@zsero said: And this deletes debian sys maintenance too, plus any user you might have there. I think it's a script only for fresh installations.

Sorry, I thought this is for new installations, didn't read the post carefully enough

zsero

I think it's next to impossible to do this with a simple shell command, here is how it my Python tool looks at the moment:
http://pastebin.com/M107Hiqt