Securing Your MySQL Server within your team?

Question

Recently I have been having an issue with our internal team members changing the password of the root user. I noticed the following things:

OLD_PASSWORD = 1

(So all passwords on our development server were encrypted with a 16 bit hash, I think, which is really bad)

The GENERAL_LOG is off. This is important as some team members access MySQL via a GUI (MySQL Workbench) and are then able to change the root password by:

SELECT * FROM mysql.user;

And then manually changing the password, and clicking apply.

In my opinion the general_log should be on so we can place the blame on team members. Also could backups of the entire database be made nightly? How would that be done? How does this affect performance?

I know that when accessing via command line, the server stores all MySQL commands in a hidden file in the home directory of a user in .mysql_history. Is this the best option or is there another way of logging MySQL cli commands? This is also a security threat if someone has access.

What are the best practices in ensuring that other people, cannot change any of the root password or permissions/grants. Would this mean not allowing any users to change permissions entirely. Would they still be able to manually change the mysql.user table.

The team members access MySQL using the root password so I have contemplated creating a new user, that does not have the ability to change permissions and password and even drop tables. Then changing the root password and telling them to use that account.

Also in terms of logging all MySQL command line and the MySQL general log how can I implement log rotation on these files.

Also do you have any other best practise suggestions for a development and production server?

Answer 1

Some best practices:

• Create a DBA user for yourself. This should be the only user with "WITH GRANT OPTION" in their permissions. This should also be the only user with select privileges on *.* because that includes mysql.user.
• Every user should have their own username and password, no shared accounts. They should each have permissions to specific databases; again, not *.*.
• And then delete that shared root user.
• Don't let non-DBAs have root access on your db server.
• Symlink your ~/.mysql_history file to /dev/null and it won't record anything.

There are others; this will get you to a better place.

You can also write a little script that outputs the grants (e.g., using pt-show-grants) and diffs to the previous output, and then emails you when there's a difference.

btw, it's hard to have enough space to store a general log on an active production system. It can get very big very quickly.

Answer 2

You definitely should disable old_passowrd. You'd need to regenerate them all to start using the new hash. (You probably could setup a script to brute force crack them all for you ;)

Is you team the DBA team or developers.

If DBA then yeah you are the guys that should have root access and any root password changes should be coordinated by the team and have the password stored in a shared password manager (we use PassPack).

In general you want to minimize the amount of privileges each account has to what is necessary (with in reason). We only have one super "all privileges on ." account and it is configured to only allow connections from "localhost" meaning you need to have a systems level account to begin with.

Consider if general users need RW access. Especially with phpmyadmin, that account credentials should be given database.* specific access enumerated. This way you don't have to worry about someone updating the mysql.users table via phpmyadmin (or other client).

To prevent passwords from ending up in the .mysql_history file we have a user management script that prompts the administrator for username, host and password (while not echoing to the terminal). This does an initial generic "grant usage on . to user@host identified by 'assdf'".

After this is done other grant management can be accomplished without specifying the password since the user already exists. As an aside make user to run w/

sql_mode = NO_AUTO_CREATE_USER

This will prevent the accidental creation of a nopassword user.

You had mentioned keeping general_log off to prevent storing set password commands there. Another thing to keep in mind is if you have any kind of replication running that statement will get written to the binary logs to be pushed out to the slaves.

Depending on what your needs are you could prevent the inital grant from getting written to the binlogs by running

set sql_log_bin=0

Prior to the grant statement.

I don't recommend doing this because slaves are generally around in the event of an emergency fail over. The risk of recovery time problems because grants weren't consistently applied out weighs any risk of the password making it into the binlogs.

Think about it, if some unsavory user gets to your binlogs, they have access to all the data that was streaming through at the time anyway. Just be sure the file system permissions aren't world readable and keep any actual system level accounts to actual sysadmins to true "need to have" basis. Try to limit system level accounts on your db servers to developers for example.

If you find some reason your stack setup necessitates many people have system level accounts, eliminate or minimize those reasons as much as possible, have a dedicated host (could be a VM) that's only job is running mysqld and related tools. (Apps should be on their own segment)

This goes for development and production servers. Sure devs will need access to the dev environment to dev, but you can get them individual mysql level user accounts that their apps can connect to (restricted to your local network segment; don't use % for the host).