Creating users in MySQL - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a new user named 'alice' with password 'mypassword'.

MySQL

CREATE USER 'alice'@'localhost' IDENTIFIED BY '[1]';

Drag options to blanks, or click blank then click option'

A'admin'

B'password123'

C'mypassword'

D'userpass'

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting to put the password in quotes.

Using a different password than specified.

The password must be specified after IDENTIFIED BY. Here, 'mypassword' is the correct password for user 'alice'.

2fill in blank

medium

Complete the code to create a user 'bob' who can connect from any host.

MySQL

CREATE USER 'bob'@'[1]' IDENTIFIED BY 'secure123';

Drag options to blanks, or click blank then click option'

A127.0.0.1

Blocalhost

C192.168.1.%

D%

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'localhost' limits the user to local connections only.

Using a specific IP restricts connections unnecessarily.

The '%' symbol means the user can connect from any host.

3fill in blank

hard

Fix the error in the code to create user 'carol' with password 'pass123'.

MySQL

CREATE USER 'carol'@'localhost' IDENTIFIED [1] 'pass123';

Drag options to blanks, or click blank then click option'

AIDENTIFIED BY

BBY

CWITH PASSWORD

DPASSWORD

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'WITH PASSWORD' instead of 'BY'.

Omitting the BY keyword.

The correct syntax is IDENTIFIED BY followed by the password.

4fill in blank

hard

Fill both blanks to create a user 'dave' who can connect from '192.168.0.10' with password 'davepass'.

MySQL

CREATE USER 'dave'@'[1]' IDENTIFIED [2] 'davepass';

Drag options to blanks, or click blank then click option'

A192.168.0.10

Blocalhost

CBY

DWITH PASSWORD

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'localhost' instead of the IP address.

Using 'WITH PASSWORD' instead of 'BY'.

The host must be '192.168.0.10' and the keyword after IDENTIFIED is BY.

5fill in blank

hard

Fill all three blanks to create user 'eve' who can connect from any host with password 'eve123', and grant all privileges on database 'shop'.

MySQL

CREATE USER 'eve'@'[1]' IDENTIFIED [2] '[3]';
GRANT ALL PRIVILEGES ON shop.* TO 'eve'@'[1]';

Drag options to blanks, or click blank then click option'

A%

BBY

Ceve123

Dlocalhost

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'localhost' instead of '%'.

Forgetting to match the host in GRANT statement.

Using wrong keyword after IDENTIFIED.

The '%' allows connection from any host, BY is the correct keyword, and 'eve123' is the password. The GRANT statement uses the same host.

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.*