Learn how to setup users in MySQL to be accessible from various hosts, remote or local, as well as ranges of IP addresses. We'll cover giving users specific grants to determine what they can do to the databases they are assigned.Users in MySQL perform a few duties:

• Define what databases & tables the user can use
• Define what operations the user can do on those databases/tables
• Define from where a user can connect

Create Database

Let's start with a database and table we can use.

CREATE DATABASE my_app 
    DEFAULT CHARACTER SET utf8mb4 
    COLLATE utf8mb4_unicode_ci;

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
  `first_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `role` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `email_nonce` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `subscribed` int(11) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL,
  `updated_at` timestamp NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Create an Open User

We don't want the root user to be used, it has too much power. Let's create one and lock it down a bit.

First, we can make one that's very generic and just about as open as user root, except it can only use our specific database.

CREATE USER 'my_user'@'%' IDENTIFIED BY 'some_secure_password';
GRANT ALL PRIVILEGES on my_app.* TO 'my_user'@'%';

• User can connect from any host (wildcard %)
• We've granted all privileges for that user on that database (and all tables)

Host Restrictions

Let's do more about where this user can connect from.

'my_user'@'%' is not 'my_user'@'192.168.16.16' - the host pairing makes them different

If we have an application server with a specific IP or hostname, or if we want to just open up our office IP address, we can define a user to connect only from a specific IP or hostname:

# Hostname
CREATE USER 'my_user'@'serversforhackers.com' IDENTIFIED BY 'some_secure_password';

# Hostname with wildcard
CREATE USER 'my_user'@'%.serversforhackers.com' IDENTIFIED BY 'some_secure_password';

# Or by IP address
CREATE USER 'my_user'@'12.124.345.67' IDENTIFIED BY 'some_secure_password';

Most interesting is setting users who can connect from a subnet, so we can restrict connetions from a local network (perhaps the private network used for servers within your VPC or other cloud hosting).

# Subnet 192.168.1.1 through 192.168.1.254
CREATE USER 'my_user'@'192.168.1.%' IDENTIFIED BY 'some_secure_password';

Grants

We don't need to grant all privileges. You can limit a user to only what they need.

# A read-only user
GRANT CREATE VIEW, SELECT, SHOW VIEW on my_app.* TO 'my_user'@'-WHATEVER-';

# A "90% user case" user
GRANT ALTER, CREATE, DELETE, DROP, INDEX, INSERT, LOCK TABLES, SELECT, UPDATE on my_app.* TO 'my_user'@'-WHATEVER-';

There are plenty more, this is not exhaustive.

A mysqldump user often needs quite a few privileges to cover all use cases (depending on the complexity of your database), so I don't have an example here.

Delete User

DROP USER 'my_user'@'-WHATEVER-';
FLUSH PRIVILEGES;

Resources

• Connection access docs
• Available grants

Side note, If you're interested in a service to help you manage MySQL-optimized, backup and (eventually) replication-enabled database servers, sign up here to let me know! The idea is to allow you to better manage your MySQL servers, taking advantage of many of MySQL's more advanced options, especially around backup and recovery.