Granting privileges in MySQL - Deep Dive

Overview - Granting privileges

What is it?

Granting privileges means giving users permission to do certain actions on a database. These actions can include reading data, adding new data, changing existing data, or managing the database structure. It controls who can do what, keeping the database safe and organized. Without privileges, users cannot access or change the database.

Why it matters

Granting privileges exists to protect data and keep it secure. Without it, anyone could change or delete important information, causing errors or data loss. It also helps teams work together by giving each person only the access they need. Without this control, databases would be chaotic and unsafe.

Where it fits

Before learning about granting privileges, you should understand basic database concepts like tables, users, and SQL commands. After mastering privileges, you can learn about database security, roles, and advanced access control methods.

Mental Model

Core Idea

Granting privileges is like giving keys to specific rooms in a house, deciding who can enter and what they can do inside.

Think of it like...

Imagine a library where each visitor has a card that lets them borrow certain books or use certain rooms. Granting privileges is like issuing these cards with specific permissions.

┌───────────────┐
│   Database    │
│  ┌─────────┐  │
│  │ Tables  │  │
│  └─────────┘  │
│               │
│  Users & Roles│
│  ┌─────────┐  │
│  │ Privileges│
│  └─────────┘  │
└──────┬────────┘
       │
       ▼
  Grant Privileges
       │
       ▼
  User can SELECT, INSERT, UPDATE, DELETE, etc.

Build-Up - 7 Steps

1

Foundation

Understanding database users

Concept: Databases have users who connect and perform actions; each user needs an identity.

A database user is like a person with a name who wants to use the database. Each user has a username and password to log in. Without a user account, no one can access the database.

Result

You can create users who can connect to the database but cannot do anything until given permissions.

Knowing that users are separate from permissions helps you see why access control is needed.

2

Foundation

What are privileges in databases

Concept: Privileges are permissions that allow users to perform specific actions on database objects.

Privileges include actions like SELECT (read data), INSERT (add data), UPDATE (change data), DELETE (remove data), and others like creating tables or managing users. Without privileges, users cannot do anything even if they can log in.

Result

Privileges define what a user can or cannot do inside the database.

Understanding privileges as action permissions clarifies how databases stay secure and organized.

3

Intermediate

Grant command syntax basics

🤔Before reading on: do you think granting privileges requires specifying the user first or the privileges first? Commit to your answer.

Concept: The GRANT command is used to give privileges to users, specifying which privileges and on what objects.

The basic syntax is: GRANT privilege_list ON object TO 'user'@'host'; For example, GRANT SELECT ON mydb.mytable TO 'alice'@'localhost'; This gives user 'alice' permission to read data from 'mytable' in 'mydb'.

Result

The user gains the specified privileges and can perform those actions on the database objects.

Knowing the order and structure of the GRANT command helps avoid syntax errors and grants precise permissions.

4

Intermediate

Granting multiple privileges and global scope

🤔Before reading on: do you think you can grant all privileges at once or must you grant each one separately? Commit to your answer.

Concept: You can grant multiple privileges at once and apply them globally or to specific databases or tables.

Use commas to list privileges: GRANT SELECT, INSERT ON mydb.* TO 'bob'@'%'; The '*' means all tables in 'mydb'. Using ON *.* grants privileges on all databases and tables, which is powerful and risky.

Result

Users can have multiple permissions at once, and scope controls where these apply.

Understanding scope prevents accidentally giving too much access and helps organize permissions efficiently.

5

Intermediate

Revoking privileges and privilege hierarchy

🤔Before reading on: do you think revoking privileges removes all user access or only specific permissions? Commit to your answer.

Concept: Privileges can be taken away with REVOKE, and some privileges imply others in a hierarchy.

Use REVOKE privilege_list ON object FROM 'user'@'host'; to remove permissions. For example, REVOKE INSERT ON mydb.mytable FROM 'alice'@'localhost'; removes only INSERT privilege. Some privileges depend on others, like GRANT OPTION allows giving privileges to others.

Result

You can fine-tune user access by adding or removing specific privileges.

Knowing how to revoke and the hierarchy helps maintain security and avoid privilege creep.

6

Advanced

Granting privileges with GRANT OPTION

🤔Before reading on: do you think a user with GRANT OPTION can give any privilege to others or only those they have? Commit to your answer.

Concept: GRANT OPTION lets a user grant their privileges to other users, creating a chain of permissions.

When you grant privileges with GRANT OPTION, the user can pass those privileges to others. For example, GRANT SELECT ON mydb.* TO 'carol'@'%' WITH GRANT OPTION; means 'carol' can let others read data too. This can be powerful but risky if misused.

Result

Users with GRANT OPTION can spread privileges, affecting database security.

Understanding GRANT OPTION is key to controlling privilege delegation and avoiding unintended access.

7

Expert

Privilege tables and internal storage

🤔Before reading on: do you think privileges are stored as simple flags or in complex tables inside the database? Commit to your answer.

Concept: MySQL stores privileges in special system tables that the server reads to enforce access control.

Privileges are stored in tables like mysql.user, mysql.db, mysql.tables_priv, and mysql.columns_priv. Each table holds different levels of privileges (global, database, table, column). When you run GRANT or REVOKE, these tables update. The server checks these tables on each user action.

Result

Privilege changes take effect immediately after reloading or flushing privileges.

Knowing the internal storage helps understand how privilege changes propagate and how to troubleshoot permission issues.

Under the Hood

When a user connects, MySQL checks the mysql.user table for global privileges, then mysql.db for database-level, then mysql.tables_priv and mysql.columns_priv for finer control. It matches the user and host, then combines all applicable privileges to decide if an action is allowed. This layered check ensures precise control.

Why designed this way?

This design allows flexible, fine-grained access control at multiple levels, balancing security and usability. Early databases had simpler models, but as needs grew, this layered privilege system was created to handle complex scenarios.

┌───────────────┐
│ User connects │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Check mysql.user │
│ (global privileges)│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Check mysql.db │
│ (database level)│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Check mysql.tables_priv │
│ (table level)           │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Check mysql.columns_priv│
│ (column level)          │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Combine privileges │
│ Decide allow/deny  │
└───────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does granting ALL PRIVILEGES to a user mean they can manage other users by default? Commit to yes or no.

Common Belief: Granting ALL PRIVILEGES gives a user full control including managing other users.

Reality: ALL PRIVILEGES covers most database actions but does NOT include the ability to manage users unless GRANT OPTION is also given.

Why it matters: Assuming ALL PRIVILEGES includes user management can lead to security holes or unexpected permission errors.

Quick: If you grant privileges to 'user'@'localhost', does that apply when the user connects from another computer? Commit to yes or no.

Common Belief: Privileges granted to a user apply regardless of where they connect from.

Reality: Privileges are tied to both username and host. 'user'@'localhost' is different from 'user'@'%'. The host part controls where the user can connect from.

Why it matters: Ignoring host specificity can cause users to be unable to connect or have wrong permissions.

Quick: Does revoking a privilege always remove it completely from a user? Commit to yes or no.

Common Belief: Revoking a privilege removes it entirely from the user immediately.

Reality: If a user has the privilege granted from multiple levels (global, database, table), revoking at one level may not remove it completely.

Why it matters: Misunderstanding this can cause confusion when users still have access after revoking.

Quick: Can a user with GRANT OPTION grant privileges they do not have themselves? Commit to yes or no.

Common Belief: Users with GRANT OPTION can grant any privilege, even those they don't have.

Reality: Users can only grant privileges they themselves possess.

Why it matters: This limits privilege spread and helps maintain security boundaries.

Expert Zone

1

Privileges can be granted at different levels (global, database, table, column), and the most specific privilege applies, which can cause unexpected access if not carefully managed.

2

The order of host matching in MySQL privilege tables affects which privileges apply when multiple entries exist for the same user with different hosts.

3

Flushing privileges (using FLUSH PRIVILEGES) is sometimes necessary after manual changes to privilege tables, but GRANT and REVOKE commands do this automatically.

When NOT to use

Granting privileges directly to many individual users can become hard to manage; instead, use roles (available in newer MySQL versions) to group privileges and assign roles to users for easier administration.

Production Patterns

In production, privileges are often granted minimally following the principle of least privilege. Roles are used to group permissions by job function. GRANT OPTION is given sparingly to trusted DBAs. Auditing and regular privilege reviews are common to maintain security.

Connections

Role-based access control (RBAC)

Builds-on

Understanding granting privileges is foundational before learning RBAC, which groups privileges into roles for easier management.

Operating system file permissions

Similar pattern

Both systems control access by granting permissions to users or groups, showing a common security principle across computing.

Legal contracts and permissions

Analogous concept

Just like contracts specify what actions parties can perform, granting privileges defines allowed actions in databases, highlighting the importance of clear permission boundaries.

Common Pitfalls

#1 Granting ALL PRIVILEGES globally to a user without restricting host access.

Wrong approach:

GRANT ALL PRIVILEGES ON *.* TO 'user'@'%';

Correct approach:

GRANT ALL PRIVILEGES ON *.* TO 'user'@'specific_host';

Root cause:

Not understanding that '%' allows connections from any host, which can expose the database to unauthorized access.

#2 Assuming REVOKE removes privileges granted at all levels.

Wrong approach:

REVOKE SELECT ON mydb.mytable FROM 'user'@'localhost'; -- expecting all SELECT revoked

Correct approach:

REVOKE SELECT ON *.* FROM 'user'@'localhost'; -- revoke global SELECT if needed

Root cause:

Not realizing privileges can be granted at multiple levels and revoking one does not remove others.

#3 Granting privileges without specifying the host part of the user.

Wrong approach:

GRANT SELECT ON mydb.* TO 'user';

Correct approach:

GRANT SELECT ON mydb.* TO 'user'@'localhost';

Root cause:

MySQL requires user and host to identify accounts; omitting host causes syntax errors or unexpected behavior.

Key Takeaways

Granting privileges controls what users can do in a database, protecting data and ensuring proper access.

Privileges are given using the GRANT command, specifying actions, objects, and users with hosts.

Privileges can be granted at different levels: global, database, table, and column, allowing fine control.

Users with GRANT OPTION can pass privileges to others, so this permission must be given carefully.

Understanding how privileges are stored and checked internally helps manage and troubleshoot access issues effectively.