Granting privileges in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of the GRANT statement in MySQL?

The GRANT statement is used to give users specific permissions to perform actions on databases, tables, or other objects in MySQL.

beginner

How do you grant SELECT privilege on a database named 'shop' to a user 'alice'?

Use: <br>GRANT SELECT ON shop.* TO 'alice'@'localhost';

intermediate

What does the ALL PRIVILEGES keyword mean in a GRANT statement?

ALL PRIVILEGES means the user gets all available permissions on the specified database or table, like SELECT, INSERT, UPDATE, DELETE, etc.

intermediate

Why is it important to specify the host when granting privileges in MySQL?

Because MySQL users are identified by both username and host, specifying the host controls where the user can connect from, improving security.

beginner

What command should you run after granting privileges to make sure they take effect immediately?

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

Which command grants a user 'bob' permission to insert data into the 'employees' table?

A GRANT INSERT ON employees TO 'bob'@'localhost';

B GRANT INSERT ON database.* TO 'bob'@'localhost';

C GRANT INSERT ON employees.* TO 'bob'@'localhost';

D GRANT INSERT ON database.employees TO 'bob'@'localhost';

Privileges are granted on databases or tables using the format database.table. To grant INSERT on the employees table, use database.employees. Option D correctly grants INSERT on the employees table.

What does this command do? GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY 'pass';

A Revokes all privileges from 'admin'.

B Gives 'admin' all permissions on all databases from any host.

C Creates a new database named 'admin'.

D Gives 'admin' only SELECT permission on all databases.

The command grants all privileges on all databases and tables (*.*) to user 'admin' connecting from any host ('%').

Why might you use 'GRANT SELECT ON mydb.* TO 'user'@'localhost';' instead of 'GRANT SELECT ON mydb.* TO 'user'@'%';'?

A To allow the user to connect only from the local machine for security.

B To allow the user to connect from any machine.

C To grant the user all privileges.

D To revoke privileges from the user.

Specifying 'localhost' restricts the user to connect only from the local machine, which is more secure than allowing connections from any host ('%').

What is the effect of running 'FLUSH PRIVILEGES;' after a GRANT statement?

A It backs up the privilege tables.

B It deletes all user privileges.

C It reloads the privilege tables so changes take effect immediately.

D It restarts the MySQL server.

FLUSH PRIVILEGES reloads the grant tables in memory so that privilege changes apply without restarting the server.

Which of these is NOT a valid privilege you can grant in MySQL?

A FLY

B INSERT

C SELECT

D UPDATE

'FLY' is not a valid MySQL privilege. SELECT, INSERT, and UPDATE are valid privileges.

Explain how to grant a user permission to read data from a specific database in MySQL.

Think about the syntax: GRANT SELECT ON database.* TO 'user'@'host';

Describe why specifying the host is important when granting privileges in MySQL.

MySQL treats 'user'@'localhost' and 'user'@'%' as different users.