Creating users in MySQL - Practice Exercises

Challenge - 5 Problems

❓ query_result

intermediate

What is the result of this user creation query?

Consider the following MySQL command:

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

What will be the result after running this command?

MySQL

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

A User 'alice' is created and can connect from any host.

B The user 'alice' is created but without any password set.

C A new user named 'alice' is created with password 'mypassword' and can connect only from localhost.

D Syntax error because password must be hashed before use.

💡 Hint

Think about what the '@' symbol means in MySQL user creation.

The command creates a user 'alice' who can connect only from 'localhost' with the specified password. The password is stored securely by MySQL internally.

📝 Syntax

intermediate

Which option has the correct syntax to create a user with host wildcard?

You want to create a user 'bob' who can connect from any host. Which command is syntactically correct?

A CREATE USER 'bob' IDENTIFIED BY 'secret'@'%';

B CREATE USER 'bob'@'%' IDENTIFIED BY 'secret';

C CREATE USER 'bob'@'*' IDENTIFIED BY 'secret';

D CREATE USER 'bob'@'all' IDENTIFIED BY 'secret';

💡 Hint

The '%' symbol is used as a wildcard for hosts in MySQL.

Option B uses the correct syntax with '%' as a wildcard for any host. Other options have invalid host specifications or misplaced parts.

🔧 Debug

advanced

Why does this user creation command fail?

Given the command:

CREATE USER 'carol'@'localhost' IDENTIFIED WITH mysql_native_password;

Why does this command fail?

MySQL

CREATE USER 'carol'@'localhost' IDENTIFIED WITH mysql_native_password;

A Because the IDENTIFIED WITH clause requires a password after the plugin name.

B Because 'mysql_native_password' is not a valid authentication plugin.

C Because the user 'carol' already exists.

D Because the host 'localhost' is invalid.

💡 Hint

Check the syntax for IDENTIFIED WITH in MySQL user creation.

The IDENTIFIED WITH clause requires specifying the authentication plugin and the password or authentication string. Omitting the password causes a syntax error.

🧠 Conceptual

advanced

What does the host part in a MySQL user account specify?

In MySQL, user accounts are defined as 'username'@'host'. What does the 'host' part control?

A It specifies from which machines the user is allowed to connect.

B It specifies the user's default database.

C It specifies the user's password encryption method.

D It specifies the user's privileges on tables.

💡 Hint

Think about network connections and security.

The 'host' part restricts the machines or IP addresses from which the user can connect to the MySQL server.

❓ optimization

expert

Which command efficiently creates multiple users with the same password?

You want to create three users: 'dave', 'eve', and 'frank', all connecting from any host, with the password 'pass123'. Which command is the most efficient and valid?

A CREATE USER 'dave'@'%' IDENTIFIED BY 'pass123'; CREATE USER 'eve'@'%' IDENTIFIED BY 'pass123'; CREATE USER 'frank'@'%' IDENTIFIED BY 'pass123';

B CREATE USER 'dave', 'eve', 'frank'@'%' IDENTIFIED BY 'pass123';

C CREATE USER 'dave'@'%', 'eve'@'%', 'frank'@'%' IDENTIFIED BY PASSWORD 'pass123';

D CREATE USER 'dave'@'%', 'eve'@'%', 'frank'@'%' IDENTIFIED BY 'pass123';

💡 Hint

MySQL allows creating multiple users in one command by listing them with hosts.

Option D correctly creates multiple users with hosts and a shared password in one command. Option D is valid but less efficient. Option D has syntax errors. Option D uses IDENTIFIED BY PASSWORD incorrectly with a plain password.

Practice

1. What is the main purpose of the CREATE USER statement in MySQL?

easy

A. To add a new user who can access the database

B. To delete an existing user

C. To change the password of a user

D. To list all users in the database

Solution

1. Step 1: Understand the purpose of CREATE USER

   The CREATE USER command is used to add a new user account to the MySQL server.

2. Step 2: Differentiate from other user commands

   Deleting users uses DROP USER, changing passwords uses ALTER USER, and listing users requires querying system tables.

3. Final Answer:

   To add a new user who can access the database -> Option A

4. Quick Check:

   CREATE USER adds users [OK]

Hint: Remember: CREATE USER adds, DROP USER removes [OK]

Common Mistakes:

• Confusing CREATE USER with DROP USER
• Thinking CREATE USER changes passwords
• Assuming CREATE USER lists users

2. Which of the following is the correct syntax to create a user named 'alice' who can connect from any host with password 'mypassword'?

easy

A. CREATE USER 'alice'@'localhost' PASSWORD 'mypassword';

B. CREATE USER alice IDENTIFIED BY 'mypassword';

C. CREATE USER 'alice'@'%' IDENTIFIED BY 'mypassword';

D. CREATE USER 'alice' IDENTIFIED WITH 'mypassword';

Solution

1. Step 1: Check the correct syntax for user creation

   The correct syntax includes the username in quotes, the host in quotes after @, and the password set with IDENTIFIED BY.

2. Step 2: Validate each option

   CREATE USER 'alice'@'%' IDENTIFIED BY 'mypassword'; uses 'alice'@'%' which means any host, and IDENTIFIED BY 'mypassword' is correct. CREATE USER alice IDENTIFIED BY 'mypassword'; misses the host part. CREATE USER 'alice' IDENTIFIED WITH 'mypassword'; uses IDENTIFIED WITH which is incorrect here. CREATE USER 'alice'@'localhost' PASSWORD 'mypassword'; uses PASSWORD keyword incorrectly.

3. Final Answer:

   CREATE USER 'alice'@'%' IDENTIFIED BY 'mypassword'; -> Option C

4. Quick Check:

   Correct syntax includes user@host and IDENTIFIED BY [OK]

Hint: User must have 'user'@'host' and IDENTIFIED BY password [OK]

Common Mistakes:

• Omitting the host part after username
• Using PASSWORD instead of IDENTIFIED BY
• Using IDENTIFIED WITH incorrectly

3. What will happen if you run this command?

CREATE USER 'bob'@'localhost' IDENTIFIED BY 'secret';

medium

A. A new user 'bob' is created who can connect only from localhost

B. An error occurs because password is missing

C. User 'bob' is created but can connect from any host

D. The command deletes user 'bob' if exists

Solution

1. Step 1: Analyze the command

   The command creates a user named 'bob' who can connect only from 'localhost' with password 'secret'.

2. Step 2: Understand host specification

   The '@localhost' means the user can connect only from the local machine, not from other hosts.

3. Final Answer:

   A new user 'bob' is created who can connect only from localhost -> Option A

4. Quick Check:

   User@localhost limits connection to local machine [OK]

Hint: Host part limits where user can connect from [OK]

Common Mistakes:

• Assuming user can connect from any host without '%' wildcard
• Thinking password is missing causes error
• Confusing CREATE USER with DROP USER

4. Identify the error in this command:

CREATE USER 'carol' IDENTIFIED BY 'pass123';

medium

A. Usernames cannot be in quotes

B. Missing host part after username

C. Password must be numeric only

D. IDENTIFIED BY is not valid syntax

Solution

1. Step 1: Check syntax requirements for CREATE USER

   MySQL requires specifying the host part after the username, like 'user'@'host'.

2. Step 2: Identify the missing part

   The command misses '@host' after 'carol', which causes a syntax error.

3. Final Answer:

   Missing host part after username -> Option B

4. Quick Check:

   User must be 'name'@'host' [OK]

Hint: Always include '@host' after username [OK]

Common Mistakes:

• Omitting the host part
• Thinking quotes are not allowed for usernames
• Believing password must be numeric

5. You want to create a user 'dave' who can connect only from IP 192.168.1.100 with password 'safePass!'. Which command correctly achieves this and ensures the user has permission to select data from the 'employees' database?

hard

A. CREATE USER 'dave'@'192.168.1.100' IDENTIFIED BY 'safePass!'; GRANT SELECT ON employees TO 'dave'@'localhost';

B. CREATE USER 'dave' IDENTIFIED BY 'safePass!'; GRANT SELECT ON employees.* TO 'dave';

C. CREATE USER 'dave'@'%' IDENTIFIED BY 'safePass!'; GRANT ALL PRIVILEGES ON employees TO 'dave'@'%';

D. CREATE USER 'dave'@'192.168.1.100' IDENTIFIED BY 'safePass!'; GRANT SELECT ON employees.* TO 'dave'@'192.168.1.100';

Solution

1. Step 1: Create user with correct host and password

   The user 'dave' must be created with host '192.168.1.100' and password 'safePass!'. CREATE USER 'dave'@'192.168.1.100' IDENTIFIED BY 'safePass!';

2. Step 2: Grant correct permissions to the right user and host

   CREATE USER 'dave'@'192.168.1.100' IDENTIFIED BY 'safePass!'; GRANT SELECT ON employees.* TO 'dave'@'192.168.1.100'; grants SELECT on employees.* to 'dave'@'192.168.1.100', matching the user host. CREATE USER 'dave'@'192.168.1.100' IDENTIFIED BY 'safePass!'; GRANT SELECT ON employees TO 'dave'@'localhost'; grants to 'dave'@'localhost', which is incorrect. Options B and C have wrong host or privileges.

3. Final Answer:

   CREATE USER 'dave'@'192.168.1.100' IDENTIFIED BY 'safePass!'; GRANT SELECT ON employees.* TO 'dave'@'192.168.1.100'; -> Option D

4. Quick Check:

   User host and grant host must match [OK]

Hint: Match user host in CREATE USER and GRANT [OK]

Common Mistakes:

• Granting permissions to wrong host
• Using '%' wildcard when specific IP is required
• Granting wrong privileges or missing database.*