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.