Mysql – Securing Your MySQL Server within your team

MySQLSecurity

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?

Best Answer

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.

Related Question