Creating & Managing MySQL Users0%

Creating & Managing MySQL Users

Intermediate12 min read•Updated: 2026-09-12

Creating & Managing MySQL Users

In a production MySQL deployment, allowing applications, developers, or microservices to connect as the root superuser is an immense security hazard. Secure database administration mandates creating dedicated user accounts adhering to the Principle of Least Privilege.

In MySQL, a user identity consists of two distinct components: the Username and the Host:

1'username'@'host'

Understanding the Host Component

The host portion dictates from which physical network address or domain the user is allowed to connect:

'app_user'@'localhost': Allowed to connect only from the local machine hosting MySQL (via local UNIX socket or 127.0.0.1).
'app_user'@'192.168.1.50': Allowed to connect only from this specific IP address.
'app_user'@'10.0.%.%': Allowed to connect from any IP within the 10.0.0.0/16 subnet.
'app_user'@'%': Allowed to connect from any host across the network/internet.

Crucial Concept: 'admin'@'localhost' and 'admin'@'%' are treated by MySQL as two entirely separate accounts with independent passwords and privileges!

Creating MySQL Users (CREATE USER)

1CREATE USER [IF NOT EXISTS] 'username'@'hostname' 
2IDENTIFIED BY 'strong_password';

Real-World Examples:

1-- 1. Create a local web application user
2CREATE USER 'webapp'@'localhost' 
3IDENTIFIED BY 'SecureAppPass!2026';
4 
5-- 2. Create a reporting analyst user allowed from the office VPN
6CREATE USER 'bi_analyst'@'192.168.10.%' 
7IDENTIFIED BY 'Analyst#Pass987';
8 
9-- 3. Create a microservice user connecting from any container in AWS ECS
10CREATE USER 'orders_service'@'%' 
11IDENTIFIED BY 'MicroServiceKey$2026';

Authentication Plugins: caching_sha2_password vs mysql_native_password

Starting in MySQL 8.0, the default authentication plugin is caching_sha2_password, providing SHA-256 password hashing and secure RSA key-pair exchanges.

If an older legacy application driver (e.g., an outdated PHP 5.6 or Python 2 client) fails to connect, you can specify the legacy plugin:

1CREATE USER 'legacy_app'@'%' 
2IDENTIFIED WITH mysql_native_password BY 'OldPass123';

Altering User Passwords and Renaming Users

1-- Change an existing user's password
2ALTER USER 'webapp'@'localhost' 
3IDENTIFIED BY 'NewUpdatedPassword$2026';
4 
5-- Rename a user account
6RENAME USER 'webapp'@'localhost' TO 'ecommerce_app'@'localhost';

Listing and Deleting Users

All user accounts are stored in the user table of the system mysql database:

1-- View all configured accounts and their authentication plugins
2SELECT user, host, plugin, account_locked 
3FROM mysql.user;
4 
5-- Safely delete an obsolete user
6DROP USER IF EXISTS 'old_service'@'%';

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

In MySQL, how is a complete user account uniquely identified?

Question 2

What does the wildcard '%' mean in 'developer'@'%'?

Question 3

What is the default authentication plugin in MySQL 8.0?

Question 4

Which SQL command permanently deletes a MySQL user account?

Question 5

In which system table does MySQL store user account definitions and authentication credentials?

Next Lesson

Granting Privileges (GRANT Statement)

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Transaction Isolation Levels in MySQL	Granting Privileges (GRANT Statement)

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Granting Privileges (GRANT Statement)

Creating & Managing MySQL Users

Intermediate12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Creating & Managing MySQL Users

In MySQL, a user identity consists of two distinct components: the Username and the Host:

1'username'@'host'

Understanding the Host Component

The host portion dictates from which physical network address or domain the user is allowed to connect:

'app_user'@'localhost': Allowed to connect only from the local machine hosting MySQL (via local UNIX socket or 127.0.0.1).
'app_user'@'192.168.1.50': Allowed to connect only from this specific IP address.
'app_user'@'10.0.%.%': Allowed to connect from any IP within the 10.0.0.0/16 subnet.
'app_user'@'%': Allowed to connect from any host across the network/internet.

Crucial Concept: 'admin'@'localhost' and 'admin'@'%' are treated by MySQL as two entirely separate accounts with independent passwords and privileges!

Creating MySQL Users (CREATE USER)

1CREATE USER [IF NOT EXISTS] 'username'@'hostname' 
2IDENTIFIED BY 'strong_password';

Real-World Examples:

1-- 1. Create a local web application user
2CREATE USER 'webapp'@'localhost' 
3IDENTIFIED BY 'SecureAppPass!2026';
4 
5-- 2. Create a reporting analyst user allowed from the office VPN
6CREATE USER 'bi_analyst'@'192.168.10.%' 
7IDENTIFIED BY 'Analyst#Pass987';
8 
9-- 3. Create a microservice user connecting from any container in AWS ECS
10CREATE USER 'orders_service'@'%' 
11IDENTIFIED BY 'MicroServiceKey$2026';

Authentication Plugins: caching_sha2_password vs mysql_native_password

Starting in MySQL 8.0, the default authentication plugin is caching_sha2_password, providing SHA-256 password hashing and secure RSA key-pair exchanges.

If an older legacy application driver (e.g., an outdated PHP 5.6 or Python 2 client) fails to connect, you can specify the legacy plugin:

1CREATE USER 'legacy_app'@'%' 
2IDENTIFIED WITH mysql_native_password BY 'OldPass123';

Altering User Passwords and Renaming Users

1-- Change an existing user's password
2ALTER USER 'webapp'@'localhost' 
3IDENTIFIED BY 'NewUpdatedPassword$2026';
4 
5-- Rename a user account
6RENAME USER 'webapp'@'localhost' TO 'ecommerce_app'@'localhost';

Listing and Deleting Users

All user accounts are stored in the user table of the system mysql database:

1-- View all configured accounts and their authentication plugins
2SELECT user, host, plugin, account_locked 
3FROM mysql.user;
4 
5-- Safely delete an obsolete user
6DROP USER IF EXISTS 'old_service'@'%';

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

In MySQL, how is a complete user account uniquely identified?

Question 2

What does the wildcard '%' mean in 'developer'@'%'?

Question 3

What is the default authentication plugin in MySQL 8.0?

Question 4

Which SQL command permanently deletes a MySQL user account?

Question 5

In which system table does MySQL store user account definitions and authentication credentials?

Next Lesson

Granting Privileges (GRANT Statement)

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Transaction Isolation Levels in MySQL	Granting Privileges (GRANT Statement)

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next