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.