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.

Practice

1. What does the REVOKE statement do in MySQL?

easy

A. It removes specific privileges from a user.

B. It grants new privileges to a user.

C. It creates a new user in the database.

D. It deletes a database table.

Solution

1. Step 1: Understand the purpose of REVOKE

   The REVOKE statement is used to remove privileges that were previously granted to a user.

2. Step 2: Compare with other statements

   GRANT adds privileges, CREATE USER creates users, and DROP TABLE deletes tables, so they do not remove privileges.

3. Final Answer:

   It removes specific privileges from a user. -> Option A

4. Quick Check:

   REVOKE = Remove privileges [OK]

Hint: REVOKE always removes privileges, not adds [OK]

Common Mistakes:

• Confusing REVOKE with GRANT
• Thinking REVOKE creates users
• Assuming REVOKE deletes tables

2. Which of the following is the correct syntax to revoke SELECT privilege on the employees table from user 'john'@'localhost'?

easy

A. REVOKE SELECT ON database.employees FROM 'john'@'localhost';

B. REVOKE SELECT ON database.employees.* FROM 'john'@'localhost';

C. REVOKE SELECT ON employees.* FROM 'john'@'localhost';

D. REVOKE SELECT ON employees FROM 'john'@'localhost';

Solution

1. Step 1: Understand the syntax for REVOKE on a table

   The correct syntax requires specifying the database and table as database.table.

2. Step 2: Analyze each option

   REVOKE SELECT ON database.employees FROM 'john'@'localhost'; correctly uses database.employees format. Options A and C miss the database name, and B incorrectly adds .* after table name.

3. Final Answer:

   REVOKE SELECT ON database.employees FROM 'john'@'localhost'; -> Option A

4. Quick Check:

   REVOKE syntax needs database.table [OK]

Hint: Always specify database.table in REVOKE [OK]

Common Mistakes:

• Omitting database name
• Using .* after table name incorrectly
• Wrong user specification format

3. Given the following commands executed in MySQL:

GRANT SELECT, INSERT ON sales.orders TO 'anna'@'localhost';
REVOKE INSERT ON sales.orders FROM 'anna'@'localhost';
SHOW GRANTS FOR 'anna'@'localhost';

What privileges will 'anna'@'localhost' have on sales.orders after these commands?

medium

A. No privileges

B. SELECT and INSERT privileges

C. Only SELECT privilege

D. Only INSERT privilege

Solution

1. Step 1: Analyze the GRANT statement

   The user 'anna' is granted SELECT and INSERT privileges on sales.orders.

2. Step 2: Analyze the REVOKE statement

   The INSERT privilege is revoked from 'anna' on sales.orders, leaving only SELECT.

3. Final Answer:

   Only SELECT privilege -> Option C

4. Quick Check:

   REVOKE removes INSERT, SELECT remains [OK]

Hint: REVOKE removes specified privileges only [OK]

Common Mistakes:

• Assuming REVOKE removes all privileges
• Confusing which privilege was revoked
• Ignoring SHOW GRANTS output

4. Identify the error in this REVOKE statement:

REVOKE UPDATE ON mydb.* FROM 'mike'@'%';

medium

A. The user specification is incorrect.

B. The privilege name UPDATE is invalid.

C. The ON clause should specify a table, not a database.

D. The syntax is correct; no error.

Solution

1. Step 1: Check user specification

   'mike'@'%' is a valid user format allowing any host.

2. Step 2: Validate privilege and ON clause

   UPDATE is a valid privilege, and ON mydb.* means all tables in database mydb, which is allowed.

3. Final Answer:

   The syntax is correct; no error. -> Option D

4. Quick Check:

   REVOKE syntax with database.* is valid [OK]

Hint: ON database.* is valid for all tables [OK]

Common Mistakes:

• Thinking ON must specify a single table
• Confusing user host wildcard syntax
• Assuming UPDATE is invalid privilege

5. You want to revoke all privileges from user 'lisa'@'localhost' on the finance database but keep her SELECT privilege on the finance.reports table. Which sequence of commands achieves this?

hard

A. REVOKE SELECT ON finance.reports FROM 'lisa'@'localhost';REVOKE ALL PRIVILEGES ON finance.* FROM 'lisa'@'localhost';

B. REVOKE ALL PRIVILEGES ON finance.* FROM 'lisa'@'localhost';GRANT SELECT ON finance.reports TO 'lisa'@'localhost';

C. REVOKE ALL PRIVILEGES ON finance.reports FROM 'lisa'@'localhost';GRANT SELECT ON finance.* TO 'lisa'@'localhost';

D. REVOKE ALL PRIVILEGES ON finance.reports FROM 'lisa'@'localhost';REVOKE ALL PRIVILEGES ON finance.* FROM 'lisa'@'localhost';

Solution

1. Step 1: Revoke all privileges on the entire finance database

   Use REVOKE ALL PRIVILEGES ON finance.* to remove all rights on all tables in finance.

2. Step 2: Grant back SELECT privilege on finance.reports table

   Use GRANT SELECT ON finance.reports to restore only SELECT on that specific table.

3. Final Answer:

   REVOKE ALL PRIVILEGES ON finance.* FROM 'lisa'@'localhost'; GRANT SELECT ON finance.reports TO 'lisa'@'localhost'; -> Option B

4. Quick Check:

   Revoke all then grant SELECT on reports [OK]

Hint: Revoke all then grant needed privilege back [OK]

Common Mistakes:

• Revoking SELECT first removes needed privilege
• Granting SELECT on whole database instead of table
• Revoking privileges in wrong order