← Back to Chapters

MySQL CREATE USER

?‍? MySQL CREATE USER

⚡ Quick Overview

The CREATE USER statement in MySQL is used to create a new user account that can connect to the MySQL server.

It is part of DCL (Data Control Language) and is usually followed by GRANT statements to provide permissions.

  • Create a new login identity in MySQL.
  • Specify from where (host) the user can connect.
  • Set an authentication password for the user.

? Secure user management command

? Key Concepts

  • User account: A login identity (like 'john'@'localhost') used to connect to MySQL.
  • Host: Defines from which machine the user can connect, e.g. localhost or % (any host).
  • Password: A secret string used to authenticate the user.
  • DCL: Data Control Language commands manage users and permissions (e.g. CREATE USER, GRANT, REVOKE).

? Syntax

? View Code Example 
-- Syntax for creating a user in MySQL
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
  • 'username' → Name of the user account you want to create.
  • 'host' → Host from which the user can connect (e.g. localhost or % for any host).
  • 'password' → Secure password for the user account.

? Example: Creating a Local User

? View Code Example 
-- Create a user 'john' who can connect only from localhost
CREATE USER 'john'@'localhost' IDENTIFIED BY 'john123';

? Common Use Cases

  • Creating separate users for different applications (e.g. reporting app, web app).
  • Giving developers their own login instead of sharing the root account.
  • Creating read-only users for analytics or dashboards.

? Live Output / Explanation

What happens when you run:

CREATE USER 'john'@'localhost' IDENTIFIED BY 'john123';

  • MySQL adds a new row to its internal user table (mysql.user).
  • The user 'john'@'localhost' can now attempt to log in with password john123.
  • By default, the user has no privileges on any database until you explicitly grant them.

So CREATE USER only creates the login identity. To actually work with databases, you must use GRANT afterwards.

✅ Tips & Best Practices

  • Always use strong, complex passwords to enhance security.
  • If you want the user to connect from any host, use 'username'@'%', but avoid this in production unless really needed.
  • After creating a user, immediately assign privileges with the GRANT command.
  • Give users the minimum required privileges (principle of least privilege).

? Try It Yourself / Practice Tasks

Imagine you have a database called EmployeeDB. Perform the following tasks:

  1. Create a user named employee_user with a secure password.
    ? View Code Example 
    -- Create a new user for EmployeeDB
CREATE USER 'employee_user'@'localhost' IDENTIFIED BY 'StrongP@ssw0rd!';
  2. Grant this user all privileges on EmployeeDB.
    ? View Code Example 
    -- Give full access on EmployeeDB to employee_user
GRANT ALL PRIVILEGES ON EmployeeDB.* TO 'employee_user'@'localhost';
  3. Apply the privilege changes (recommended in some setups).
    ? View Code Example 
    -- Reload privilege tables (older MySQL versions)
FLUSH PRIVILEGES;
  4. Verify that the user exists in the MySQL user table.
    ? View Code Example 
    -- Check all users and their hosts
SELECT User, Host FROM mysql.user;