Creating users in MySQL - Mechanics & Internals

Overview - Creating users

What is it?

Creating users in MySQL means making new accounts that can connect to the database. Each user has a name and a password, and can be given specific permissions to do certain tasks. This helps control who can see or change data in the database. It is like giving someone a key to a locked room with rules about what they can do inside.

Why it matters

Without creating users, anyone could access the database and change or see sensitive information. This would be unsafe and chaotic, like leaving your house unlocked for strangers. Creating users lets you protect your data and share access safely with different people or programs. It also helps track who did what in the database.

Where it fits

Before learning to create users, you should understand basic MySQL commands and how databases work. After this, you can learn about granting permissions, roles, and security best practices. Creating users is an early step in managing database security and access control.

Mental Model

Core Idea

Creating users in MySQL is like making individual keys with specific permissions to safely control who can enter and do things in the database.

Think of it like...

Imagine a building with many rooms. Each user is like a person given a key that opens only certain doors. Some keys open just one room, others open many. This keeps the building secure and organized.

┌───────────────┐
│ MySQL Server  │
├───────────────┤
│  Users Table  │
│ ┌───────────┐ │
│ │ User1     │ │
│ │ Password  │ │
│ │ Permissions│ │
│ └───────────┘ │
│ ┌───────────┐ │
│ │ User2     │ │
│ │ Password  │ │
│ │ Permissions│ │
│ └───────────┘ │
└───────────────┘

Build-Up - 7 Steps

1

Foundation

What is a MySQL user account

Concept: Understanding that a MySQL user is an identity with a name and password used to connect to the database.

A MySQL user account is like a profile that allows someone or something to log into the database. It has a username and a password. Without a user account, no one can access the database. Users can be created with simple commands.

Result

You know that users are needed to access MySQL and that each user has a name and password.

Understanding that users are the gatekeepers to the database is the first step to controlling access and security.

2

Foundation

Basic syntax to create a user

Concept: Learning the SQL command to create a user with a username and password.

The command to create a user looks like this: CREATE USER 'username'@'host' IDENTIFIED BY 'password'; - 'username' is the name of the user. - 'host' is where the user can connect from (like 'localhost' or '%'). - 'password' is the secret to log in. Example: CREATE USER 'alice'@'localhost' IDENTIFIED BY 'mypassword';

Result

A new user named 'alice' is created who can connect from the local computer with the password 'mypassword'.

Knowing the exact syntax lets you create users safely and specify where they can connect from, which is important for security.

3

Intermediate

Understanding user host restrictions

🤔Before reading on: do you think a user created with 'localhost' can connect from any computer? Commit to yes or no.

Concept: Learning that the 'host' part limits where a user can connect from, adding a layer of security.

When creating a user, the 'host' part defines the allowed connection source. - 'localhost' means the user can only connect from the same machine as the database. - '%' means the user can connect from any IP address. - You can specify an IP or domain for more control. Example: CREATE USER 'bob'@'192.168.1.100' IDENTIFIED BY 'secret'; This user can only connect from that IP.

Result

Users have connection limits, preventing unauthorized access from unknown locations.

Understanding host restrictions helps prevent unauthorized access and is a key part of database security.

4

Intermediate

Creating users with no password (not recommended)

🤔Before reading on: do you think creating a user without a password is safe? Commit to yes or no.

Concept: Exploring the option to create users without passwords and why it is risky.

You can create a user without a password: CREATE USER 'guest'@'localhost'; This user has no password and can connect without one. This is usually unsafe because anyone can log in as 'guest'. It might be used for testing or very limited cases.

Result

A user without a password can connect easily but poses a security risk.

Knowing the risks of passwordless users helps avoid common security mistakes.

5

Intermediate

Creating users with authentication plugins

🤔Before reading on: do you think MySQL always uses passwords to authenticate users? Commit to yes or no.

Concept: Introducing authentication plugins that can change how users log in, like using system accounts or external services.

MySQL supports different ways to check user identity called authentication plugins. Example: CREATE USER 'sam'@'localhost' IDENTIFIED WITH auth_socket; This means 'sam' logs in using the system's user account, not a password. Plugins can improve security or integrate with other systems.

Result

Users can authenticate in different ways beyond passwords.

Understanding authentication plugins opens advanced security options and integration possibilities.

6

Advanced

User creation and privileges separation

🤔Before reading on: do you think creating a user automatically gives them permission to do everything? Commit to yes or no.

Concept: Learning that creating a user does not grant any permissions by default; privileges must be assigned separately.

When you create a user, they have no rights to see or change data. You must grant privileges explicitly: GRANT SELECT ON database.* TO 'alice'@'localhost'; This lets 'alice' read data but not change it. Separating user creation and permission assignment improves security.

Result

Users exist but cannot do anything until given permissions.

Knowing that user creation and permission granting are separate helps prevent accidental over-permissioning.

7

Expert

Internal storage of user accounts

🤔Before reading on: do you think MySQL stores user passwords in plain text? Commit to yes or no.

Concept: Understanding how MySQL stores user information securely inside system tables with hashed passwords.

MySQL stores user accounts in the 'mysql.user' table. Passwords are stored as hashes, not plain text, for security. When a user logs in, MySQL hashes the entered password and compares it to the stored hash. This prevents password theft even if the table is accessed. Admins can query this table to see users and their hosts.

Result

User data is stored securely and used internally to verify logins.

Knowing the internal storage and hashing mechanism explains why passwords are safe and how authentication works.

Under the Hood

When you create a user, MySQL adds a record to its internal 'mysql.user' table with the username, host, and hashed password or authentication plugin info. This table is checked every time someone tries to connect. MySQL compares the login credentials against this data to allow or deny access. The host part restricts where the connection can come from. Passwords are stored as hashes to protect them. Privileges are stored separately and checked after login to control what the user can do.

Why designed this way?

MySQL separates user identity from permissions to allow fine-grained control and security. Storing passwords as hashes protects against leaks. The host restriction helps prevent unauthorized remote access. This design evolved from early database security needs to balance flexibility, security, and performance. Alternatives like storing plain passwords or combining identity and permissions were rejected due to security risks and inflexibility.

┌───────────────┐
│ CREATE USER   │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ mysql.user    │
│ ┌───────────┐ │
│ │ username  │ │
│ │ host      │ │
│ │ password  │ │
│ │ plugin    │ │
│ └───────────┘ │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Authentication│
│ on connection │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does creating a user automatically give them permission to read or write data? Commit to yes or no.

Common Belief: Creating a user automatically allows them to access and modify data.

Reality: Creating a user only creates the account; no permissions are granted until explicitly assigned.

Why it matters: Assuming users have permissions by default can lead to security holes or confusion when users cannot perform expected actions.

Quick: Can a user created with 'localhost' connect from any remote computer? Commit to yes or no.

Common Belief: The 'localhost' host means the user can connect from anywhere.

Reality: 'localhost' restricts the user to connect only from the local machine where MySQL runs.

Why it matters: Misunderstanding host restrictions can cause failed connections or unintended access if not configured properly.

Quick: Are MySQL passwords stored in plain text inside the database? Commit to yes or no.

Common Belief: MySQL stores user passwords as plain text in its system tables.

Reality: Passwords are stored as secure hashes, not plain text, to protect user credentials.

Why it matters: Believing passwords are stored in plain text can cause unnecessary fear or misuse of security practices.

Quick: Is it safe to create users without passwords if the network is trusted? Commit to yes or no.

Common Belief: Creating users without passwords is safe if the network is secure.

Reality: Users without passwords pose a security risk even on trusted networks because credentials can be intercepted or misused.

Why it matters: Ignoring password requirements can lead to unauthorized access and data breaches.

Expert Zone

1

User accounts are identified by both username and host, so 'alice'@'localhost' and 'alice'@'%' are different users with separate permissions.

2

Authentication plugins can be customized or created to integrate MySQL with external identity providers, adding flexibility beyond passwords.

3

The order of user matching during login is specific: MySQL picks the most specific host match first, which can cause unexpected access if not understood.

When NOT to use

Creating users with broad host '%' or no passwords is not recommended for production. Instead, use specific hosts and strong authentication methods. For very large systems, consider centralized authentication like LDAP or Kerberos instead of managing many MySQL users.

Production Patterns

In production, users are created with minimal necessary privileges following the principle of least privilege. Hosts are restricted to known IPs or domains. Authentication plugins like caching_sha2_password are used for security. User creation is automated with scripts or infrastructure as code tools to maintain consistency.

Connections

Access Control Lists (ACLs)

Creating users in MySQL builds the foundation for ACLs which define what each user can do.

Understanding user creation helps grasp how ACLs enforce security by linking identities to permissions.

Operating System User Accounts

MySQL users can be linked to OS users via authentication plugins like auth_socket.

Knowing OS user accounts helps understand how MySQL can delegate authentication to the system for tighter integration.

Physical Key Security

Creating users with passwords and host restrictions is like issuing physical keys with limited access.

This cross-domain connection shows how digital security mimics physical security principles to protect valuable assets.

Common Pitfalls

#1 Creating a user without specifying the host, assuming it defaults to all hosts.

Wrong approach:

CREATE USER 'john' IDENTIFIED BY 'pass123';

Correct approach:

CREATE USER 'john'@'%' IDENTIFIED BY 'pass123';

Root cause:

MySQL requires the host part; omitting it causes syntax errors or unexpected defaults.

#2 Granting privileges before creating the user, causing errors.

Wrong approach:

GRANT SELECT ON *.* TO 'mary'@'localhost';

Correct approach:

CREATE USER 'mary'@'localhost' IDENTIFIED BY 'pwd';
GRANT SELECT ON *.* TO 'mary'@'localhost';

Root cause:

Privileges can only be granted to existing users; forgetting to create the user first causes failure.

#3 Using weak or no passwords for users in production.

Wrong approach:

CREATE USER 'guest'@'%' IDENTIFIED BY '';

Correct approach:

CREATE USER 'guest'@'%' IDENTIFIED BY 'StrongP@ssw0rd!';

Root cause:

Underestimating the importance of strong passwords leads to security vulnerabilities.

Key Takeaways

Creating users in MySQL is the first step to controlling who can access your database.

Each user is defined by a username and a host, which limits where they can connect from.

Users start with no permissions; you must grant rights explicitly to control their actions.

Passwords are stored securely as hashes, and authentication can use plugins beyond passwords.

Proper user creation and management are essential for database security and safe multi-user environments.