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.