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

Practice

1. What does the GRANT SELECT ON database.* TO 'user'@'host'; command do in MySQL?

easy

A. Allows the user to read data from all tables in the specified database.

B. Allows the user to modify data in all tables in the specified database.

C. Creates a new user with read access to the database.

D. Deletes all data from the specified database.

Solution

1. Step 1: Understand the GRANT command

   The GRANT command is used to give specific permissions to a user on a database or table.

2. Step 2: Analyze the SELECT privilege

   SELECT privilege allows reading data but not modifying it.

3. Final Answer:

   Allows the user to read data from all tables in the specified database. -> Option A

4. Quick Check:

   GRANT SELECT = read permission [OK]

Hint: SELECT privilege means read-only access [OK]

Common Mistakes:

• Confusing SELECT with UPDATE or DELETE privileges
• Thinking GRANT creates a user
• Assuming it deletes data

2. Which of the following is the correct syntax to grant all privileges on the shop database to user 'alice' connecting from any host?

easy

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

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

C. GRANT ALL ON shop.* TO 'alice';

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

Solution

1. Step 1: Check the database and user format

   The database must be followed by .* to specify all tables, and user must include host.

2. Step 2: Verify the correct order of clauses

   The syntax is: GRANT privileges ON database.* TO 'user'@'host';

3. Final Answer:

   GRANT ALL PRIVILEGES ON shop.* TO 'alice'@'%'; -> Option A

4. Quick Check:

   Correct syntax = GRANT ALL PRIVILEGES ON shop.* TO 'alice'@'%'; [OK]

Hint: Use database.* to grant on all tables [OK]

Common Mistakes:

• Omitting .* after database name
• Placing TO clause incorrectly
• Not specifying host with user

3. Given the commands:

GRANT SELECT, INSERT ON sales.* TO 'bob'@'localhost';
SHOW GRANTS FOR 'bob'@'localhost';

What will the SHOW GRANTS output include?

medium

A. GRANT ALL PRIVILEGES ON sales.* TO 'bob'@'localhost'

B. GRANT SELECT ON sales.* TO 'bob'@'localhost'

C. GRANT SELECT, INSERT ON sales.* TO 'bob'@'localhost'

D. No privileges granted

Solution

1. Step 1: Understand the GRANT command given

   The user 'bob' is granted SELECT and INSERT privileges on all tables in the sales database.

2. Step 2: Interpret SHOW GRANTS output

   SHOW GRANTS displays the exact privileges granted, so it will list SELECT and INSERT on sales.* for bob.

3. Final Answer:

   GRANT SELECT, INSERT ON sales.* TO 'bob'@'localhost' -> Option C

4. Quick Check:

   SHOW GRANTS matches granted privileges [OK]

Hint: SHOW GRANTS shows exact privileges granted [OK]

Common Mistakes:

• Assuming ALL PRIVILEGES are granted
• Ignoring INSERT privilege
• Thinking SHOW GRANTS returns no rows

4. Identify the error in the following command:

GRANT UPDATE, DELETE ON * TO 'carol'@'localhost';

medium

A. Host '%' must be used instead of 'localhost'

B. User name must be in double quotes

C. Privileges UPDATE and DELETE cannot be granted together

D. Missing database or table name after ON

Solution

1. Step 1: Check the ON clause syntax

   The ON clause requires a database or table name, e.g., database.* or database.table.

2. Step 2: Identify the missing part

   The command uses * alone, which is invalid syntax in MySQL for ON.

3. Final Answer:

   Missing database or table name after ON -> Option D

4. Quick Check:

   ON must specify database or table [OK]

Hint: ON must specify database or table, not just * [OK]

Common Mistakes:

• Using * alone without database name
• Using wrong quotes for user
• Assuming localhost is invalid host

5. You want to allow user 'dave' to create new tables only in the inventory database but not modify data. Which GRANT command is correct?

hard

A. GRANT ALL PRIVILEGES ON inventory.* TO 'dave'@'localhost';

B. GRANT CREATE ON inventory.* TO 'dave'@'localhost';

C. GRANT CREATE, INSERT ON inventory.* TO 'dave'@'localhost';

D. GRANT CREATE ON *.* TO 'dave'@'localhost';

Solution

1. Step 1: Identify the needed privilege

   To create tables, the CREATE privilege is required; INSERT or ALL PRIVILEGES would allow data changes.

2. Step 2: Scope the privilege to the inventory database

   Granting on inventory.* limits the permission to that database only.

3. Step 3: Check user and host format

   User 'dave'@'localhost' is correctly specified.

4. Final Answer:

   GRANT CREATE ON inventory.* TO 'dave'@'localhost'; -> Option B

5. Quick Check:

   CREATE only on inventory = GRANT CREATE ON inventory.* TO 'dave'@'localhost'; [OK]

Hint: Grant only CREATE on specific database for table creation [OK]

Common Mistakes:

• Granting ALL PRIVILEGES instead of just CREATE
• Including INSERT which allows data changes
• Granting on *.* instead of specific database