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
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)
Let's do more about where this user can connect from.
'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';
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.
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.
DROP USER 'my_user'@'-WHATEVER-'; FLUSH PRIVILEGES;
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.