CREATE USER

Creating a user is like giving each person their own key card to the building, rather than everyone sharing one master key.

In this page:

How to Create a New User
Specifying Host Constraints
Creating Users with Authentication Plugins
Listing All Database Users
Creating Users with Password Expiry

Syntax

sql

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

How to Create a New User

Creating a dedicated account for each person or application that connects to your database, rather than sharing one login, is a core security practice — CREATE USER takes a username, a host, and a password to set one up.

Example: How to Create a New User

sql

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongPass123!';

Specifying Host Constraints

Specifying a host constraint restricts exactly where a user is allowed to connect from, whether that's a single IP address, a specific domain, or a wildcard — locking this down closes off a common attack surface.

Example: Specifying Host Constraints

sql

CREATE USER 'app_user'@'192.168.1.10' IDENTIFIED BY 'StrongPass123!';

Creating Users with Authentication Plugins

MySQL supports different authentication plugins for managing how passwords are verified, and you can choose a specific plugin at creation time to match whatever security policy your organization requires.

Example: Creating Users with Authentication Plugins

sql

CREATE USER 'app_user'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'StrongPass123!';

Listing All Database Users

Regularly reviewing the full list of accounts that exist in your database — by querying the mysql system schema — helps catch stale or forgotten accounts before they become a security liability.

Example: Listing All Database Users

sql

SELECT user, host FROM mysql.user;

Creating Users with Password Expiry

Forcing a newly created account's password to expire immediately means the user must set their own password the first time they log in, which avoids a shared or default password ever being used long-term.

Example: Creating Users with Password Expiry

sql

CREATE USER 'temp_user'@'localhost' IDENTIFIED BY 'TempPass123!' PASSWORD EXPIRE;

Chapter Quiz — Complete all 6 topics to unlock

0/6 topics done

Complete these topics first:

← Previous Lesson Transaction Isolation Levels Next Lesson → GRANT Privileges