Secure access to the database is likely priority number one for any database administrator. If it is not then you need to seriously look into why it is not.
General guidelines via the manual are already available:
One of the primary issues with security in MySQL is of course the permissions that you give users.
These are a few simply guidelines.
First keep "super user" or "root" accounts to a minimum. A user with full access or "GRANT ALL" will still have access when you have reached your max connections. So the last thing you would want is a program to be executing commands with a user with full access.
Keep in mind what types of accounts you are creating. You can limit a user to
MAX QUERIES, MAX UPDATES, MAX CONNECTIONS and MAX USER CONNECTIONS per HOUR.
Keep in mind of the network environment your users are connecting from. If users are going to be using DHCP network addresses within the same subnet you would just be creating more work for yourself if you limited them to a single IP. You can still limit them to the subnet though with a wildcard. For example '192.168.0.2' versus '192.168.0.%'
Stay away from entire wildcard access for host and users.
> CREATE USER ''@'192.168.0.56' ;
Query OK, 0 rows affected (0.02 sec)
> show grants for ''@'192.168.0.56';
+-----------------------------------------+
| Grants for @192.168.0.56 |
+-----------------------------------------+
| GRANT USAGE ON *.* TO ''@'192.168.0.56' |
+-----------------------------------------+
This would leave you wide open for anyone from 192.168.0.56 and is not a smart secure thing to do.
It could also violate other accounts from 192.168.0.56 because MySQL checks host first and username second.
> GRANT SELECT ON test.* TO 'exampleuser'@'192.168.0.%' IDENTIFIED BY 'somepassword';
> show grants for 'exampleuser'@'192.168.0.%';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for exampleuser@192.168.0.% |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'exampleuser'@'192.168.0.%' IDENTIFIED BY PASSWORD '*DAABDB4081CCE333168409A6DB119E18D8EAA073' |
| GRANT SELECT ON `test`.* TO 'exampleuser'@'192.168.0.%' |
+----------------------------------------------------------------------------------------------------------------------+
This will allow selects only for exampleuser from '192.168.0.%'. You must also keep in mind that if exampleuser is connecting from LOCAL HOST that the system will likely user localhost first before the 192.168.0.% subnet address unless the user used the subnet address the host to connect to.
This means that you can create one user and password with different privileges per host.
> SHOW GRANTS FOR 'exampleuser'@'localhost';
+--------------------------------------------------------------------------------------------------------------------+
| Grants for exampleuser@localhost |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'exampleuser'@'localhost' IDENTIFIED BY PASSWORD '*DAABDB4081CCE333168409A6DB119E18D8EAA073' |
| GRANT SELECT, UPDATE, DELETE ON `test`.* TO 'exampleuser'@'localhost' |
+--------------------------------------------------------------------------------------------------------------------+
Try your best to not use the --password=<password> option via the mysql client. You can use -p to prompt for a password.
You also have the option with MySQL 5.6 to use the
MySQL Configuration Utility.
# mysql_config_editor set --login-path=local --host=localhost --user=root --password
Enter password:
# mysql_config_editor print --all
[local]
user = root
password = *****
host = localhost
# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
# mysql --login-path=local
Welcome to the MySQL monitor.
# mysql --login-path=local -e 'SELECT NOW()';
You do have options to name different paths like local or remote and etc as well. So you can encrypt more than one access user account in your ~/.mylogin.cnf file that is created once you use the set command.
If you have shell scripts that use the mysql client and likely then have passwords in the scripts updating them to use the "
--login-path=" is a more secure way to go.
Of course when you no longer need a user... Drop the user.
> DROP USER 'exampleuser'@'localhost';
