SQL commands for a fresh install
As a rule I always execute the following commands on a fresh database installation. Then for each user that is granted privileges, of course they are given a password and the host permissions are locked down as much as possible for their needs, alternately table and column privs as well. I’m not going to get into the parts the manual covers, but rather mention a couple of things on my mind.
First the initial commands:
mysql> use mysql
mysql> delete from user where User='';
mysql> delete from db where User='';
mysql> update user set Password=password('password_here'
mysql> flush privileges;
However, one thing I’ve noticed is that when you hand over a server to someone that doesn’t necessarily follow your same understanding or regard to user privilege security, bad things can happen. Such as users created without a password. It is possible to grant a user privileges, even global level privileges without a password. Why this would ever be considered a “feature” from the code point of view – or the default state of the SQL mode (which handles the GRANT command as well as other things) is beyond me but there is a good way to make sure your users cannot be created without a password: NO_AUTO_CREATE_USER SQL MODE. So just add this to the cnf:
[mysqld]
sql_mode=NO_AUTO_CREATE_USER
Or issue the global command at runtime:
mysql> set global sql_mode=NO_AUTO_CREATE_USER;
To further lock down the server it’s best to just begin with sql_mode=TRADITIONAL but that’s a whole new discussion and chances are some applications will not enjoy using this mode and cease to function because of improper design. So remember, set a password 
arjen Said,
February 19, 2008 @ 8:04 pm
Have you looked at the mysql_secure_installation script?
It does the removing of the anon user, setting up root pwd, etc…
Easy!
flupps Said,
February 20, 2008 @ 2:35 am
The first steps will be covered by “mysql_secure_installation” on Unix systems, and on Windows the installer will take care of this for you…
flupps@flupps:~$ mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we’ll need the current
password for the root user. If you’ve just installed MySQL, and
you haven’t set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on…
Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
Set root password? [Y/n]
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
… Success!
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n]
… Success!
Normally, root should only be allowed to connect from ‘localhost’. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n]
… Success!
By default, MySQL comes with a database named ‘test’ that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n]
– Dropping test database…
… Success!
– Removing privileges on test database…
… Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n]
… Success!
Cleaning up…
All done! If you’ve completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!
flupps@flupps:~$
sql command for changing password Said,
May 15, 2008 @ 2:35 pm
[...] database installation. … cannot be created without a password: NO_AUTO_CREATE_USER sql MODE. …http://themattreid.com/wordpress/?p=44SQL Command and Function Reference – DBMaker Online / ManualsFor security reasons, users other than [...]