Revoking privileges in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the effect of this REVOKE command?

Consider the following command executed on a MySQL server:

REVOKE SELECT ON employees FROM 'user1'@'localhost';

What is the result of this command?

MySQL

REVOKE SELECT ON employees FROM 'user1'@'localhost';

A User 'user1' gains SELECT privilege on the employees table.

B User 'user1' loses all privileges on the employees database.

C User 'user1' loses the ability to connect to the server.

D User 'user1' loses the SELECT privilege on the employees table.

💡 Hint

REVOKE removes specific privileges previously granted.

The REVOKE command removes the specified privilege (SELECT) on the specified object (employees table) from the user. It does not affect other privileges or the ability to connect.

📝 Syntax

intermediate

Identify the syntax error in this REVOKE statement

Which option contains a syntax error when trying to revoke privileges in MySQL?

A REVOKE INSERT, UPDATE ON sales FROM 'user2'@'%';

B REVOKE SELECT ON employees FROM 'user5'@'localhost';

C REVOKE DELETE ON database.table TO 'user4'@'localhost';

D REVOKE ALL PRIVILEGES ON *.* FROM 'user3'@'localhost';

💡 Hint

Check the keyword used to specify the user in REVOKE.

The REVOKE statement uses FROM to specify the user, not TO. Option C incorrectly uses TO, causing a syntax error.

🧠 Conceptual

advanced

What happens if you revoke a privilege not granted?

If you execute the command:

REVOKE UPDATE ON employees FROM 'user1'@'localhost';

but 'user1' never had the UPDATE privilege on employees, what will happen?

A MySQL will throw an error indicating the privilege was not granted.

B MySQL will silently succeed without error.

C MySQL will revoke all privileges from the user.

D MySQL will grant the UPDATE privilege instead.

💡 Hint

Think about how MySQL handles revoking privileges that do not exist.

MySQL silently ignores revoking privileges that were never granted. No error is thrown.

❓ query_result

advanced

What privileges remain after this REVOKE?

Given a user 'user1'@'localhost' with privileges: SELECT, INSERT, UPDATE on database 'shop'.

After running:

REVOKE INSERT ON shop.* FROM 'user1'@'localhost';

Which privileges does 'user1' still have on 'shop'?

MySQL

REVOKE INSERT ON shop.* FROM 'user1'@'localhost';

A SELECT and UPDATE only

B INSERT only

C No privileges remain

D SELECT, INSERT, and UPDATE remain

💡 Hint

REVOKE removes only the specified privilege.

Only the INSERT privilege is revoked. SELECT and UPDATE remain intact.

🔧 Debug

expert

Why does this REVOKE command fail?

A DBA runs this command:

REVOKE ALL PRIVILEGES ON *.* FROM 'user1'@'localhost';

But it returns an error:

ERROR 1410 (42000): You are not allowed to revoke all privileges from yourself

What is the reason for this error?

MySQL

REVOKE ALL PRIVILEGES ON *.* FROM 'user1'@'localhost';

A The DBA is trying to revoke all privileges from the current user, which MySQL forbids.

B The user 'user1'@'localhost' does not exist.

C The syntax for revoking all privileges is incorrect; ON *.* is not allowed.

D MySQL does not support revoking ALL PRIVILEGES at once.

💡 Hint

Think about restrictions on revoking privileges from yourself.

MySQL prevents a user from revoking all their own privileges to avoid locking themselves out.