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?