Creating users in MySQL - Visual Walkthrough

Concept Flow - Creating users

Start

↓

Write CREATE USER statement

↓

Execute statement in MySQL

↓

User added to mysql.user table

↓

User can be granted privileges

↓

End

The process starts by writing a CREATE USER command, executing it, which adds the user to the database system, ready for privileges.

Execution Sample

MySQL

CREATE USER 'alice'@'localhost' IDENTIFIED BY 'password123';

This command creates a new user named 'alice' who can connect from 'localhost' with the given password.

Execution Table

Step	Action	Command/Condition	Result	Notes
1	Write command	CREATE USER 'alice'@'localhost' IDENTIFIED BY 'password123';	Command ready	User creation command prepared
2	Execute command	Run CREATE USER statement	User 'alice' added	User added to mysql.user table
3	Verify user	SELECT User, Host FROM mysql.user WHERE User='alice';	Returns 'alice'@'localhost'	User exists in system
4	Exit	No errors	Process complete	User creation successful

💡 User 'alice' successfully created and stored in mysql.user table

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
User 'alice'@'localhost'	Does not exist	Created	Verified	Exists

Key Moments - 2 Insights

Why do we specify '@localhost' when creating a user?

The '@localhost' part defines where the user can connect from. In the execution_table step 1 and 2, it shows the user is created only for connections from the local machine.

What happens if we try to create a user that already exists?

MySQL will return an error and not create a duplicate user. This is why in step 2, the command must be unique or use IF NOT EXISTS to avoid errors.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result after executing the CREATE USER command?

A No change in users

B User 'alice' added to mysql.user table

C User 'alice' deleted

D Error message shown

💡 Hint

Check execution_table row 2 under Result column

At which step do we verify that the user exists in the system?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at execution_table rows and find where SELECT query is run

If we omit '@localhost' in the CREATE USER command, what would happen?

A Syntax error occurs

B User can connect from any host

C User can only connect from localhost

D User creation fails silently

💡 Hint

MySQL requires host part in user creation, see step 1 command format

Concept Snapshot

CREATE USER 'username'@'host' IDENTIFIED BY 'password';
- Creates a new database user
- 'username' is the user name
- 'host' defines where user can connect from
- Password sets authentication
- User added to mysql.user table
- Must execute in MySQL server

Full Transcript

Creating a user in MySQL involves writing a CREATE USER statement specifying the username, host, and password. When executed, this command adds the user to the mysql.user table, making the user recognized by the database system. The host part defines from where the user can connect, commonly 'localhost' for local machine access. After creation, the user can be granted privileges to access databases. Verification can be done by querying the mysql.user table to confirm the user exists. If a user already exists, MySQL will return an error unless IF NOT EXISTS is used. This process ensures secure and controlled access to the database.