MySQL Users & Security

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