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

MySQL Users & Security