Why Revoking privileges in MySQL? - Purpose & Use Cases

The Big Idea

What if you could instantly stop someone from accessing your data with just one command?

The Scenario

Imagine you run a busy library where many people borrow books. You give some friends permission to lend books to others. But later, you realize some friends should no longer have that power. Without a clear way to take back permissions, you have to watch everyone closely or manually check each transaction.

The Problem

Manually tracking who can do what is slow and confusing. Mistakes happen easily, like forgetting to stop someone from lending books or accidentally letting someone keep too much control. This can cause chaos and security risks.

The Solution

Revoking privileges lets you quickly and safely take back permissions from users. It is like having a simple switch to turn off access, so you keep control without hassle or errors.

Before vs After

✗ Before

Keep a list on paper and check it every time someone borrows a book.

✓ After

REVOKE SELECT ON database.* FROM 'user'@'host';

What It Enables

It enables you to manage who can do what in your database easily and securely, protecting your data and keeping control.

Real Life Example

A company hires a contractor and gives them access to some data. When the project ends, the company revokes the contractor's privileges to keep the data safe.

Key Takeaways

Manual permission tracking is slow and error-prone.

Revoking privileges quickly removes unwanted access.

This keeps your database secure and under control.

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