Create, Drop, Rename MySQL User

Create a user that can access remotely from IP address 192.168.19.5
mysql> CREATE USER ‘my_user’@’192.168.19.5’ IDENTIFIED BY ‘my_password’;

Create a user that can access remotely from host.domain.com
mysql> CREATE USER ‘my_user’@’host.domain.com’ IDENTIFIED BY ‘my_password’;

Create a user that can access remotely from IP address 192.16.19.*
mysql> CREATE USER ‘my_user’@’192.16.0.5/255.255.255.0’ IDENTIFIED BY ‘my_password’;

Create a user that can access from any sub-domain of mydomain
mysql> CREATE USER ‘my_user’@’%.mydomain.com’ IDENTIFIED BY ‘my_password’;

Create a user that can access from any IP address
mysql> CREATE USER ‘my_user’@’%’ IDENTIFIED BY ‘my_password’;

Rename a MySQL user
mysql> RENAME USER old_username TO new_username;

Set MySQL user password
mysql> SET PASSWORD FOR ‘my_user’@’192.16.0.5’ = PASSWORD(‘mypassword’);

Drop a MySQL user
mysql> DROP USER my_user;

mysql> DROP USER ‘my_user’@’192.168.19.5′;
MySQL User Account Hardening

Display all user accounts
mysql> SELECT user, host, password FROM mysql.user;
NOTE:
For users without a password or anonymous user ( user=’ ‘ ):
mysql> SELECT user, host, password FROM mysql.user where password=”;
mysql> SELECT user, host, password FROM mysql.user where user=”;

Drop the user or
Add a password if it is empty

To control the maximum connections allowed for a account
max_user_connections
GRANT ALL ON db1.* TO ‘myuser’@’localhost’
WITH MAX_CONNECTIONS_PER_HOUR 10
MAX_USER_CONNECTIONS 5
MAX_QUERIES_PER_HOUR 20
MAX_UPDATES_PER_HOUR 20

Grant & Revoke MySQL User Privileges

Show MySQL grant privileges
mysql> SHOW GRANTS;
mysql> SHOW GRANTS FOR ‘name’@’host’;
mysql> SHOW GRANTS FOR CURRENT_USER();

Grant Privileges to MySQL User
For most web application user

mysql> GRANT SELECT,INSERT,DELETE,UPDATE,EXECUTE ON my_db.* TO ‘user’@’host’;
mysql> GRANT ALL ON *.* TO ‘user’@’host’;
mysql> GRANT ALL ON my_db.* TO ‘user’@’host’;
mysql> GRANT ALL ON my_db.tbl TO ‘user’@’host’;
mysql> USE my_db
mysql> GRANT ALL ON * TO ‘user’@’host’;
mysql> GRANT ALL ON tbl TO ‘user’@’host’;
mysql> GRANT SELECT, INSERT ON my_db.* TO ‘user’@’host’;
mysql> GRANT SELECT, INSERT ON my_db.tbl TO ‘user’@’host’ WITH GRANT OPTION;

GRANT OPTIONS allow the use to grant the same rights to other user

To flush the privileges
FLUSH PRIVILEGES;

Revoke MySQL User Privileges
mysql> REVOKE ALL ON *.* FROM ‘user’@’host’;
mysql> REVOKE ALL PRIVILEGES,GRANT OPTION FROM ‘user’@’host’;
mysql> REVOKE GRANT OPTION ON *.* FROM ‘user’@’host’;

Most Common Privileges
MySQL Privilege Meaning
DELETE DELETE
SELECT SELECT
INSERT INSERT
UPDATE UPDATE
EXECUTE Execute stored routines
USAGE Empty privilege
MySQL Privilege Meaning
CREATE Table creation
CREATE ROUTINE Create routine
CREATE TEMPORARY TABLES Create tmp tables
ALTER ROUTINE Altered or dropped stored routines
CREATE VIEW Create or alter view
EVENT Events for the Event Scheduler
INDEX Enable Create or drop indexes
SHOW VIEW SHOW CREATE VIEW
TRIGGER Create or drop trigger
MySQL Administrator privileges
MySQL Privilege Meaning
LOCK TABLES LOCK TABLES on tables for which you have the SELECT privilege
SHOW DATABASES SHOW DATABASES
REPLICATION CLIENT Locate master or slave servers
REPLICATION SLAVE Enable replication slaves

Do not grant the following privilege until absolutely needed.

Mis-used of those privileges can cause production issues.

MySQL Privilege Meaning

ALL Grant all privileges except GRANT OPTION
ALTER ALTER TABLE
CREATE USER CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES
FILE Read or write files
GRANT OPTION Grant user’s privileges to others
PROCESS Read information on server threads
RELOAD FLUSH
SUPER Operation task
DROP DROP
SHUTDOWN SHUTDOWN

Other MySQL Security Best Practices

Never use OS level root account to start mysqld
# Forcing UNIX to start mysqld with an OS account mysql
# It is the default during Ubuntu installation
[mysqld]
user=mysql

Data directory and its sub-directory (including all symbolics links) should have no access to group or others
sh> sudo find /var/lib/mysql -follow -print | xargs chown mysql
sh> sudo find /var/lib/mysql -follow -print | xargs chgrp mysql

Only allow “root” to access the “user” table in the DB “mysql”
Do not allow user access from any IP address: ‘user’@’%’
Do not allow anonymous user

All MySQL users must have an encrypted password
Make sure no one can access MySQL without password like this
% mysql -u root
Should not allow symbolic links
–skip-symbolic-links option
Do not grant File, process & super privilege to non-administrator