Revoking privileges in MySQL - Deep Dive

Overview - Revoking privileges

What is it?

Revoking privileges means taking away permissions that a user has on a database. These permissions control what actions the user can perform, like reading data or changing tables. When you revoke privileges, you limit or remove a user's ability to do certain tasks. This helps keep the database safe and organized.

Why it matters

Without the ability to revoke privileges, users might keep access they no longer need or should not have. This can lead to accidental or malicious changes, data leaks, or security problems. Revoking privileges helps protect sensitive information and ensures only the right people can do certain actions.

Where it fits

Before learning about revoking privileges, you should understand what privileges are and how to grant them. After this, you can learn about managing users, roles, and best security practices in databases.

Mental Model

Core Idea

Revoking privileges is like taking away keys from someone who no longer should enter certain rooms in a building.

Think of it like...

Imagine a library where people have keys to different rooms: some can enter the reading room, others the archive. If someone no longer needs access, the librarian takes back their key to keep the library secure.

┌───────────────┐
│   User Access  │
├───────────────┤
│  Read Data     │
│  Write Data    │
│  Modify Schema │
└──────┬────────┘
       │ Revoke removes specific keys
       ▼
┌───────────────┐
│  Limited User  │
│  (Fewer Keys)  │
└───────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding database privileges

Concept: Privileges are permissions that allow users to perform specific actions in a database.

In MySQL, privileges include actions like SELECT (read data), INSERT (add data), UPDATE (change data), DELETE (remove data), and more. These control what a user can do. Without privileges, users cannot access or change data.

Result

Users have specific rights to interact with the database based on granted privileges.

Knowing what privileges exist is essential before you can control or revoke them.

2

Foundation

Granting privileges basics

Concept: Privileges are given to users using the GRANT command to allow actions on databases or tables.

For example, to let a user read data from a table, you use: GRANT SELECT ON database.table TO 'user'@'host'; This gives the user permission to read data.

Result

The user can now perform the granted actions on the specified database objects.

Understanding granting helps you see what revoking will undo.

3

Intermediate

Syntax of REVOKE command

🤔Before reading on: do you think REVOKE removes all privileges or only specified ones? Commit to your answer.

Concept: REVOKE removes specific privileges from a user, not necessarily all at once.

The basic syntax is: REVOKE privilege_type ON database.table FROM 'user'@'host'; For example, REVOKE SELECT ON mydb.mytable FROM 'alice'@'localhost'; removes only the SELECT privilege from Alice on that table.

Result

The user loses the specified privilege but keeps others.

Knowing that REVOKE targets specific privileges prevents accidental removal of all access.

4

Intermediate

Revoking multiple privileges

🤔Before reading on: can you revoke multiple privileges in one command or must you do it one by one? Commit to your answer.

Concept: You can revoke several privileges at once by listing them separated by commas.

Example: REVOKE SELECT, INSERT ON mydb.* FROM 'bob'@'%'; removes both SELECT and INSERT privileges from Bob on all tables in mydb.

Result

Bob loses both privileges in a single command.

This saves time and reduces errors when managing many privileges.

5

Intermediate

Revoking global vs specific privileges

🤔Before reading on: does revoking a global privilege affect all databases or just one? Commit to your answer.

Concept: Privileges can be global (all databases) or specific (one database or table). Revoking global privileges removes access everywhere.

For example, REVOKE ALL PRIVILEGES ON *.* FROM 'carol'@'localhost'; removes all privileges from Carol on all databases. But REVOKE UPDATE ON mydb.orders FROM 'carol'@'localhost'; removes only UPDATE on one table.

Result

Global revokes have wider impact than specific ones.

Understanding scope helps avoid accidentally locking out users from needed access.

6

Advanced

Revoking privileges and flushing privileges

🤔Before reading on: do you think revoked privileges take effect immediately or require extra steps? Commit to your answer.

Concept: After revoking privileges, MySQL may require flushing the privilege cache to apply changes immediately.

The command FLUSH PRIVILEGES; reloads the privilege tables so changes take effect without restarting the server. Without this, some changes might not apply right away.

Result

Revoked privileges become active immediately after flushing.

Knowing when to flush prevents confusion about why revoked privileges still seem active.

7

Expert

Revoking privileges and role management

🤔Before reading on: do you think revoking privileges from a role affects users assigned that role? Commit to your answer.

Concept: In MySQL, roles group privileges. Revoking privileges from a role affects all users with that role.

If you revoke a privilege from a role, all users assigned that role lose that privilege. For example, REVOKE INSERT ON mydb.* FROM 'manager_role'; removes INSERT from everyone with 'manager_role'.

Result

Role-based privilege revokes simplify managing many users at once.

Understanding roles and revokes together helps manage large teams efficiently and securely.

Under the Hood

MySQL stores user privileges in special tables inside the mysql database, like user, db, and tables_priv. When you revoke privileges, MySQL updates these tables to remove the specified permissions. The server caches these privileges in memory for fast access. Flushing privileges reloads these tables into memory so changes take effect immediately.

Why designed this way?

Storing privileges in tables allows flexible, fine-grained control and easy updates. Caching improves performance by avoiding disk reads on every access check. Flushing is a manual step to balance performance with immediate consistency.

┌───────────────┐
│ Privilege     │
│ Tables in     │
│ mysql DB      │
└──────┬────────┘
       │ Update on REVOKE
       ▼
┌───────────────┐
│ Privilege     │
│ Cache in      │
│ Server Memory │
└──────┬────────┘
       │ Flushed by FLUSH PRIVILEGES
       ▼
┌───────────────┐
│ Access Checks │
│ Use Cached    │
│ Privileges    │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does REVOKE remove all privileges from a user by default? Commit yes or no.

Common Belief: REVOKE removes all privileges from a user if you don't specify which ones.

Reality: REVOKE only removes the privileges you explicitly list; other privileges remain intact.

Why it matters: Assuming REVOKE removes everything can cause users to lose unexpected access or lead to incomplete privilege removal.

Quick: After revoking, do privileges always update immediately without extra commands? Commit yes or no.

Common Belief: Revoked privileges take effect immediately without any additional steps.

Reality: Sometimes you must run FLUSH PRIVILEGES to apply changes immediately, especially if you modify privilege tables directly.

Why it matters: Not flushing can cause confusion when revoked privileges still seem active, leading to security risks.

Quick: Does revoking a privilege from a role affect users assigned that role? Commit yes or no.

Common Belief: Revoking privileges from a role does not affect users who have that role.

Reality: Revoking privileges from a role immediately affects all users assigned that role.

Why it matters: Misunderstanding this can cause unexpected loss of access for multiple users.

Quick: Can revoking privileges from one database affect others? Commit yes or no.

Common Belief: Revoking privileges on one database automatically revokes them on all databases.

Reality: Privileges are scoped; revoking on one database does not affect others unless global privileges are revoked.

Why it matters: Assuming global effect can lead to over-restricting users or missing needed revokes.

Expert Zone

1

Revoking privileges does not delete the user account; it only changes what the user can do.

2

Some privileges depend on others; revoking a base privilege might implicitly restrict related actions.

3

Directly modifying privilege tables without using REVOKE or GRANT requires careful FLUSH PRIVILEGES to avoid inconsistencies.

When NOT to use

Avoid revoking privileges when you want to temporarily restrict access; consider disabling the user account instead. For complex permission management, use roles or external access control systems.

Production Patterns

In production, revoking privileges is often automated in scripts during employee offboarding or role changes. Role-based access control (RBAC) is used to manage groups of privileges efficiently. Auditing tools track privilege changes for security compliance.

Connections

Role-Based Access Control (RBAC)

Revoking privileges is a key operation within RBAC systems to manage user permissions.

Understanding revoking helps grasp how RBAC dynamically controls access by adding or removing privileges from roles.

Operating System File Permissions

Both involve granting and revoking access rights to resources, like files or databases.

Knowing how OS permissions work clarifies the concept of database privilege revocation as controlling access to resources.

Security Clearance in Organizations

Revoking privileges is like lowering or removing security clearance levels for personnel.

This connection shows how access control principles apply beyond technology, emphasizing the importance of timely revocation.

Common Pitfalls

#1 Revoking privileges without specifying the exact privilege.

Wrong approach:

REVOKE ON mydb.* FROM 'user'@'localhost';

Correct approach:

REVOKE SELECT ON mydb.* FROM 'user'@'localhost';

Root cause:

Misunderstanding that REVOKE requires explicit privilege names; omitting them causes syntax errors.

#2 Not flushing privileges after direct table changes.

Wrong approach:

UPDATE mysql.user SET Select_priv='N' WHERE User='user';

Correct approach:

UPDATE mysql.user SET Select_priv='N' WHERE User='user'; FLUSH PRIVILEGES;

Root cause:

Forgetting that MySQL caches privileges and requires flushing to apply manual changes.

#3 Revoking privileges from a role but expecting no effect on users.

Wrong approach:

REVOKE INSERT ON mydb.* FROM 'manager_role'; -- then assuming users keep INSERT

Correct approach:

REVOKE INSERT ON mydb.* FROM 'manager_role'; -- users lose INSERT immediately

Root cause:

Not understanding that roles propagate privilege changes to assigned users.

Key Takeaways

Revoking privileges removes specific permissions from users to control their database actions.

You must specify which privileges to revoke; otherwise, the command will not work.

Revoked privileges may require flushing the privilege cache to take effect immediately.

Revoking privileges from roles affects all users assigned those roles, simplifying management.

Understanding privilege scope (global, database, table) is crucial to avoid unintended access changes.

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