Revoking privileges in MySQL - Step-by-Step Execution

Concept Flow - Revoking privileges

Start

↓

Identify user and privileges

↓

Execute REVOKE command

↓

Check if privileges exist

Yes No↓

Remove privileges

↓

Confirm success

↓

End

The flow shows starting with identifying the user and privileges, executing the REVOKE command, checking if privileges exist, removing them if yes, and confirming success.

Execution Sample

MySQL

REVOKE SELECT, INSERT ON mydb.* FROM 'user1'@'localhost';

This command removes SELECT and INSERT privileges on all tables in 'mydb' from user1 connecting from localhost.

Execution Table

Step	Action	Privileges Before	Privileges Revoked	Privileges After	Result
1	Identify user 'user1'@'localhost'	SELECT, INSERT, UPDATE	N/A	SELECT, INSERT, UPDATE	User found with privileges
2	Execute REVOKE SELECT, INSERT ON mydb.* FROM 'user1'@'localhost'	SELECT, INSERT, UPDATE	SELECT, INSERT	UPDATE	Privileges revoked successfully
3	Check remaining privileges	UPDATE	N/A	UPDATE	User retains UPDATE privilege
4	End	UPDATE	N/A	UPDATE	Revoking process complete

💡 Privileges SELECT and INSERT revoked; user retains UPDATE privilege; process ends.

Variable Tracker

Variable	Start	After Step 2	Final
Privileges	SELECT, INSERT, UPDATE	UPDATE	UPDATE

Key Moments - 2 Insights

Why do some privileges remain after the REVOKE command?

Only the specified privileges (SELECT, INSERT) are revoked; others like UPDATE remain unchanged as shown in execution_table step 2 and 3.

What happens if you try to revoke privileges the user does not have?

No change occurs; the command completes without error but no privileges are removed, similar to the 'No change' branch in the concept_flow.

Visual Quiz - 3 Questions

Look at the execution_table, what privileges does the user have after step 2?

A SELECT, INSERT, UPDATE

B UPDATE

C SELECT, INSERT

D No privileges

💡 Hint

Refer to the 'Privileges After' column in row for step 2.

At which step does the REVOKE command actually remove privileges?

A Step 2

B Step 1

C Step 3

D Step 4

💡 Hint

Look at the 'Privileges Revoked' column in the execution_table.

If the user had no INSERT privilege initially, what would happen at step 2?

A INSERT privilege would be revoked anyway

B An error would occur

C No change for INSERT privilege

D All privileges would be revoked

💡 Hint

See key_moments explanation about revoking privileges the user does not have.

Concept Snapshot

REVOKE privileges syntax:
REVOKE privilege_list ON database.table FROM 'user'@'host';
Only specified privileges are removed.
Privileges not listed remain unchanged.
No error if revoking non-existent privileges.
Use to restrict user access rights.

Full Transcript

This visual execution trace shows how the REVOKE command works in MySQL. First, the user and their current privileges are identified. Then the REVOKE command removes the specified privileges. The system checks which privileges remain after revocation. Only the privileges listed in the REVOKE command are removed; others stay. If you try to revoke privileges the user does not have, nothing changes and no error occurs. This process helps manage user access by removing specific rights.

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