Resources
See “Securing Your MySQL Installation” by Paul DuBois at http://www.kitebird.com/articles/ins-sec.html.
From now on, when you log in, command:
mysqladmin -u root -p
MySQL will then prompt you for root’s password.
You could also log in like this:
mysqladmin -u root -psomething_more_secure
Note that there is no space between the “-p” and the actual password. Also note that this is a terrible idea, because your password will be clearly visible.
More specifically, users can and should use .my.cnf files in their home directories to store their passwords if they’re using good, complex passwords. However, see Secure All Option Files below.
Command:
useradd mysqluser #assuming you want to name this user mysqluser
Now make sure that mysqld is running under that user’s permissions by configuring the global configuration file, /etc/my.cnf:
[mysqld]
user=mysqluser
RPM installation will put MySQL in /var/lib/mysql, and add startup scripts to /etc/rc.d/ .
Installation from a .tar.gz package can be done to /usr/local, /usr/bin, or the location of your choice.
Know the location of the mysql executables on your system.
Assume for this example that the executables are in /usr/local/mysql/ , and the data directory is /usr/local/mysql/data/ .
Set the OS permissions so that the mysql user, and that user’s group, own the appropriate directories. You may have to modify the path, depending on your installation:
chown -R root /usr/local/mysql/. #This changes user ownership
#of the mysql directory, and everything inside it.chown -R mysql /usr/local/mysql/data #This changes user ownership
#of the data directory, and everything inside it.chgrp -R mysql /usr/local/mysql/. #This changes group ownership
#of the mysql directory and everything inside it (-R).
Now remove the permissions of other users on these directories:
chmod -R 770 /usr/local/mysql/
This denies all access to other users, even browse permissions.
You’re stuck running mysqld as the System user, which is roughly the equivalent of the Unix root user. While nobody can log in as System (which is good), all users can request resources through System (which is not so good). Configure all other users’ access tightly.
There’s a file named /usr/local/mysql/mysql.sock (or /var/lib/mysql/mysql.sock) that has a special function: it allows a connection (socket) on the local computer.
What we’re talking about here is user access to the mysql databases when they’re logged directly into the server computer. In other words, connections from other computers (hosts) won’t be affected by the state of this file. However, if you’ve locked down the mysql directory as I’ve specified above, local users aren’t going to be able to access the socket file, so they won’t be able to access the mysql databases locally.
There are two basic solutions if you need to allow this kind of access. The first is to open the mysql directory back up:
chmod 755 /usr/local/mysql
This is not so great, because it allows local users to see files and directories in the mysql directory. The better solution is to compile the mysqld package with the socket option set to place the socket file outside the /usr/local/mysql directory. At the configure stage, command:
./configure –with-unix-socket-path /usr/local #or a location of your choosing
Follow this procedure. First, start the mysql client:
mysql -u root -p
Eliminate anonymous accounts (accounts with no user name) In the client, command:
USE mysql;
DELETE FROM user WHERE User=”;
#That’s a pair of single quotes, enclosing nothing
FLUSH PRIVILEGES;Check for accounts with no password:
SELECT * FROM user WHERE Password=”;
Any accounts listed have no password. Correct this:
UPDATE user SET Password=PASSWORD(‘new_pass’) WHERE User=’user_name’ AND Host=’host_name’;
Or, more succinctly:
SET PASSWORD FOR ‘user_name’@’host_name’=PASSWORD(‘new_pass’);
Then run a FLUSH PRIVILEGES command.
Use hostname wildcards ( % ) sparingly, if at all:
Specifying a host from which a user must connect is a critical element of MySQL security. Use this specification. This, for instance, is a “quick and dirty” (or easy) way to let the user peter connect from any host:
GRANT SELECT ON albums.* TO peter@’%’ IDENTIFIED BY ‘comes_alive’;
It’s also a great way to get cracked, giving crackers lots of potential points of entry to break into (any host that can reach your database server). If you have dozens or hundreds of hosts, you may be forced into it:
GRANT SELECT ON albums.* TO peter@’%.library.local’ IDENTIFIED BY ‘comes_alive’;
But note that in this example the wildcard restricts connections to a local domain, unlike the potentially-disastrous first example. This means you need to audit your accounts for hostname wildcards. But how do you search for a wildcard character that occurs anywhere in a hostname? It’s a little trickier than it might seem, but this will work:
SELECT * FROM user WHERE Host LIKE ‘%\%%’;
Think about the LIKE clause here. Now, examine any results of this query. You’ll need to evaluate on a case-by-case basis whether the wildcard is warranted or not. Perform an UPDATE query on any accounts you can configure more tightly.
But this won’t be enough in itself. You’ll need to perform a similar search on your grant tables as well, since those contain similar user/hostname strings, and UPDATE them as well.
As always, once you’re done, FLUSH PRIVILEGES.
Don’t Grant Global Privileges
Global privileges (to every database and every table) are granted like this:
GRANT privileges ON *.* TO user@host;
The most dangerous grant you can perform is:
GRANT ALL ON *.* TO user@host;
Don’t do this.
For one thing, granting ALL allows this user FILE privileges, so he can write from files to any database, or to files, thus stealing the database. For another, ALL includes SHUTDOWN, which lets this user do exactly that to the database.
Use REVOKE to remove privileges:
REVOKE FILE ON *.* FROM user@host;
REVOKE ALL ON *.* FROM user@host;REVOKE ALL has one hole: it does not revoke GRANT privileges (which a user could use to re-grant themselves privileges):
REVOKE GRANT OPTION ON *.* FROM user@host;
There are several of these. The global option file is /etc/my.cnf. It must be owned by root so root can call the server startup:
chown root /etc/my.cnf
It must also, however, be readable by client programs. Use:
chmod 644 /etc/my.cnf
Every user can have their own /home/username/.my.cnf file as well. Just because it’s a “dot” (hidden) file doesn’t mean it’s protected; likewise its location in the user’s home is no real assurance of protection either. First, as the admin (root or other admin account), look at the file’s ownership:
ls -la /home/username/.my.cnf
Make sure the file is owned by the user, and the user’s group. Modify as necessary. Next, make the file inaccessible to everyone else:
chmod 600 /home/username/.my.cnf
Better yet, clean up configuration files that your users have left world-writable:
find /home -name .my.cnf ! -type l -ls | awk ‘{print $11}’ | xargs chmod 600
Or go one better and correct all files that have been left world-writable, which is a good exercise to perform from time to time in any multi-client environment:
find /home -perm -2 ! -type l -ls | awk ‘{print $11}’ | xargs chmod 600
How do you run a command like this on a timed basis? Glad you asked.
Use a file browser or a terminal window, whichever you’re most comfortable using. Go to the directory /etc/cron.weekly. (Notice that there are cron.daily, cron.hourly and cron.monthly directories, too.) If you put a script here, it will run once a week.
The smart thing to do is start from an existing script. You should have a yum.cron script, for instance; open this one or whatever you have.
You’ll see the all-important “she-bang” at the top, in most cases:
#!/bin/bash
or
#!/bin/sh
Save this script with a new name: fixfile.cron, for instance. Now all you have to do is erase the current contents (after the shebang) and add the command we used above. Save again and exit. You’re done. This script will run every week.
Would you like to be notified that the script has run? Just add this:
mail -s “File correction script” root@localhost <<EOF
“The file correction script ran at “
dateEOF
(This does require your server to have mail service available.)
Note that this modification should also be performed on any SQL utilities as well, for instance phpMyAdmin; these utilities must be owned by the mysql user and group.
Now, set the GUID and SUID bits (see this lecture if you’re unfamiliar with this concept) so that anything, anything, created in these directories is owned by the mysql user and the mysql group:
chmod g+s /usr/local/mysql/
chmod g+s /usr/local/mysql/data/
chmod u+s /usr/local/mysql/
chmod u+s /usr/local/mysql/data/
See the PHP section for an example of using an included, protected database connection file.
See the PHP Security section about using Magic Quotes, and the addslashes() and mysql_escape_string() functions.