Creating users in MySQL - Quick Revision & Summary

Recall & Review

beginner

What is the basic SQL command to create a new user in MySQL?

The basic command is CREATE USER 'username'@'host' IDENTIFIED BY 'password'; where you specify the username, host, and password.

beginner

Why do you need to specify the host when creating a MySQL user?

The host defines where the user can connect from. For example, 'localhost' means the user can only connect from the local machine.

intermediate

How do you grant all privileges on a database to a user after creating them?

Use GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host'; to give full access to that database.

intermediate

What command do you use to apply changes after creating or modifying users in MySQL?

Run FLUSH PRIVILEGES; to reload the grant tables and apply changes immediately.

beginner

Can you create a user without a password in MySQL? What is the risk?

Yes, but it is not recommended because it allows anyone to connect without authentication, which is a security risk.

Which command creates a new MySQL user with password 'mypassword' for localhost?

A CREATE USER 'user1'@'localhost' IDENTIFIED BY 'mypassword';

B ADD USER 'user1' IDENTIFIED BY 'mypassword';

C NEW USER 'user1'@'localhost' SET PASSWORD 'mypassword';

D CREATE LOGIN 'user1' WITH PASSWORD 'mypassword';

The correct syntax is CREATE USER with username, host, and IDENTIFIED BY password.

What does the host part in 'username'@'host' specify?

A The database the user can access

B The user's role

C The user's password

D The IP or machine from which the user can connect

The host defines where the user is allowed to connect from.

After creating a user, what command applies the changes immediately?

A APPLY PRIVILEGES;

B UPDATE USERS;

C FLUSH PRIVILEGES;

D RELOAD USERS;

FLUSH PRIVILEGES reloads the grant tables to apply changes.

Which command grants all privileges on the database 'shop' to user 'alice'?

A GRANT ALL PRIVILEGES ON shop TO 'alice';

B GRANT ALL PRIVILEGES ON shop.* TO 'alice'@'localhost';

C GRANT ALL ON shop TO 'alice';

D GRANT ALL PRIVILEGES ON shop.* TO 'alice'@'%';

You must specify the database with .* and the user with host.

Is it safe to create a MySQL user without a password?

A No, it is a security risk

B Yes, it is safe and recommended

C Only if the user connects from localhost

D Only if the user has limited privileges

Users without passwords can connect without authentication, which is unsafe.

Explain the steps to create a new MySQL user and give them full access to a specific database.

Think about creating the user, granting rights, then applying changes.

Why is specifying the host important when creating a MySQL user?

Consider how MySQL controls user connections.