Granting privileges in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the effect of this GRANT statement?

Consider the following MySQL command:

GRANT SELECT, INSERT ON employees TO 'user1'@'localhost';

What privileges does 'user1' have on the employees database after this command?

MySQL

GRANT SELECT, INSERT ON employees TO 'user1'@'localhost';

A 'user1' can read and add new data to the employees database.

B 'user1' has full control over the employees database.

C 'user1' can read, add, and delete data from the employees database.

D 'user1' can only read data from the employees database.

💡 Hint

Look at the privileges listed after GRANT.

The GRANT statement gives 'user1' the SELECT and INSERT privileges, which means they can read and add data but cannot delete or modify existing data.

📝 Syntax

intermediate

Identify the syntax error in this GRANT statement

Which option correctly fixes the syntax error in the following statement?

GRANT SELECT, UPDATE employees TO 'user2'@'%';

MySQL

GRANT SELECT, UPDATE employees TO 'user2'@'%';

A GRANT SELECT, UPDATE employees FROM 'user2'@'%';

B GRANT SELECT, UPDATE TO 'user2'@'%' ON employees;

C GRANT ON employees SELECT, UPDATE TO 'user2'@'%';

D GRANT SELECT, UPDATE ON employees TO 'user2'@'%';

💡 Hint

Check the placement of the ON keyword.

The correct syntax requires the ON keyword before the database or table name. Option D correctly places ON before employees.

❓ optimization

advanced

Optimizing privilege grants for multiple users

You want to grant SELECT privilege on the database sales to three users: 'alice', 'bob', and 'carol', all connecting from any host. Which option is the most efficient way to do this in MySQL?

A GRANT SELECT ON sales TO 'alice'@'%'; GRANT SELECT ON sales TO 'bob'@'%'; GRANT SELECT ON sales TO 'carol'@'%';

B GRANT SELECT ON sales TO ALL USERS;

C GRANT SELECT ON sales TO 'alice'@'%', 'bob'@'%', 'carol'@'%';

D GRANT SELECT ON sales TO 'alice', 'bob', 'carol';

💡 Hint

MySQL allows multiple users in one GRANT statement separated by commas.

Option C uses a single GRANT statement listing all users separated by commas, which is more efficient than multiple separate statements.

🔧 Debug

advanced

Why does this GRANT statement fail?

A DBA runs this command:

GRANT ALL PRIVILEGES ON *.* TO 'dave'@'localhost' IDENTIFIED BY 'pass123';

But it returns an error. What is the most likely cause?

A The IDENTIFIED BY clause cannot be used in GRANT statements for existing users.

B The user 'dave'@'localhost' does not exist and must be created first.

C The syntax requires the keyword WITH after ALL PRIVILEGES.

D The *.* syntax is invalid for granting privileges.

💡 Hint

IDENTIFIED BY is used when creating users, not when granting privileges to existing users.

In MySQL, IDENTIFIED BY is used in CREATE USER or GRANT for creating new users. Using it in GRANT for existing users causes an error.

🧠 Conceptual

expert

Understanding privilege scope in MySQL

If a user is granted SELECT privilege on the database inventory and separately granted INSERT privilege on the table inventory.products, which of the following statements is true?

A The user can INSERT into all tables in inventory but SELECT only on inventory.products.

B The user can SELECT from all tables in inventory and INSERT only into inventory.products.

C The user can only SELECT and INSERT on the inventory.products table.

D The user can SELECT and INSERT on all tables in inventory.

💡 Hint

Database-level privileges apply to all tables in that database unless overridden by table-level privileges.

Granting SELECT on the database allows reading from all tables. Granting INSERT on one table allows adding data only to that table.

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