Many applications start with a simple database setup: create one MySQL user, give it access to the application schema, put the credentials in the app config, and move on.

That may work at first, but it is not a good long-term security model.

A better approach is to use separate MySQL users for separate application behaviors, especially separating read-only access from write access. This gives you stronger security, clearer auditing, safer operations, and better protection when something goes wrong.

MySQL’s privilege system is designed for this kind of control. MySQL accounts can be created, granted specific permissions, revoked, locked, assigned roles, and restricted by host or authentication policy. The official MySQL documentation describes account management through statements such as CREATE USER, GRANT, REVOKE, SHOW GRANTS, and DROP USER.

The Problem With Using Only One Application User

A common database management anti-pattern (a common recurring design or coding practice that initially appears to be a good solution but ultimately leads to significant problems) looks like this:

CREATE USER 'app_user'@'%' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app_user'@'%';

The application uses this one account for everything:

  • Login page
  • Search page
  • Admin dashboard
  • Background jobs
  • Reporting
  • API reads
  • API writes
  • Data exports

At first, this seems convenient. There is only one credential to manage and one database user to configure.

But convenience comes at a cost.

If that credential is leaked, abused, logged accidentally, exposed through a vulnerable service, or used by a compromised part of the application, the attacker now has both read and write access. A feature that only needed to read product data may now be able to update users, delete records, or modify business-critical tables.

That is an unnecessary risk.

Separate Read and Write Users

A safer pattern is to split database access by responsibility.

For example:

CREATE USER 'app_read'@'10.%' IDENTIFIED BY 'strong_read_password';
CREATE USER 'app_write'@'10.%' IDENTIFIED BY 'strong_write_password';
GRANT SELECT ON appdb.* TO 'app_read'@'10.%';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app_write'@'10.%';

Now the application can use:

  • app_read   -> pages, APIs, reports, dashboards, search, validation lookups
  • app_write  -> create, update, delete, checkout, workflow transitions

This implements the principle of least privilege: each account only has the access needed to perform its job. MySQL supports fine-grained privileges including database-level, table-level, and even column-level permissions in some cases.

Why This Matters

1. A compromised read path cannot become a write path

Most applications have far more read operations than write operations. Product pages, dashboards, search endpoints, profile views, reporting queries, and autocomplete APIs often only need SELECT.

If one of those paths is vulnerable, a read-only database account limits the damage. The attacker may still be able to view data that account can access, which is serious, but they cannot directly use that credential to modify or delete records.

Without separation, every read-only feature effectively carries write capability.

2. Bugs become less destructive

Not every database incident is caused by an attacker. Many are caused by application bugs, bad deployments, incorrect query logic, or unexpected code paths.

A reporting job should not be able to update customer records. A search endpoint should not be able to delete rows. A background analytics task should not be able to modify transactional data.

Separate users turn some classes of bugs into permission errors instead of data corruption incidents.

3. Auditing becomes clearer

When all database activity uses one username, logs are harder to interpret.

Was that UPDATE caused by the API? A migration? A batch job? A support tool? A compromised host?

With separate users, logs become more meaningful:

  • app_read
  • app_write
  • app_migration
  • app_report
  • app_admin_tool
  • app_batch_worker

Even if you do not have perfect auditing, distinct accounts make investigation easier. You can quickly identify which application component or workflow performed a type of operation.

4. Credentials can be rotated independently

If a read-only credential is exposed, you can rotate that credential without touching write paths.

If a batch worker credential is compromised, you can disable that specific user without taking down the entire application.

This is much harder when every service, job, and application component shares the same MySQL account.

5. Production access becomes safer

Applications often need supporting tools: dashboards, customer support utilities, internal admin panels, scripts, data exports, and ETL jobs.

These should not all use the same database account.

A BI dashboard may need read access to selected tables. A migration tool may need schema privileges during deployment. A customer support tool may need limited updates to a narrow set of fields. A background job may only need access to queue tables.

Separate accounts let you match privileges to actual responsibilities.

A Better MySQL User Model

A mature application might use accounts like this:

  • app_read             SELECT only for normal application reads
  • app_write            SELECT, INSERT, UPDATE, DELETE for normal writes
  • app_migration        DDL privileges used only during deployments
  • app_report           SELECT on reporting views or replica only
  • app_batch            Limited privileges for background jobs
  • app_admin_tool       Carefully scoped permissions for internal tools
  • app_healthcheck      Minimal permissions for connectivity checks

You can also use MySQL roles to group common permissions and assign them to accounts. MySQL roles can be created, granted to users, set as default roles, and inspected with SHOW GRANTS.

For example:

CREATE ROLE 'role_app_read';
CREATE ROLE 'role_app_write';

GRANT SELECT ON appdb.* TO 'role_app_read';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'role_app_write';

GRANT 'role_app_read' TO 'app_read'@'10.%';
GRANT 'role_app_write' TO 'app_write'@'10.%';

SET DEFAULT ROLE 'role_app_read' TO 'app_read'@'10.%';
SET DEFAULT ROLE 'role_app_write' TO 'app_write'@'10.%';

Roles make privilege management easier, especially when you have many users or environments.

Keep Permissions Narrow

Avoid this unless absolutely necessary:

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

That gives the application far more power than it should normally have.

Instead, scope permissions by:

  • Host
  • Database
  • Table
  • Column
  • Operation
  • Environment
  • Application component

For example:

GRANT SELECT ON appdb.products TO 'catalog_read'@'10.%';
GRANT SELECT ON appdb.categories TO 'catalog_read'@'10.%';
GRANT SELECT, UPDATE(status, updated_at) ON appdb.orders TO 'order_worker'@'10.%';

Column-level privileges are not always practical for every application, but they are useful for sensitive workflows where only specific fields should be writable.

Restrict Where Users Can Connect From

This is another common mistake:

'app_user'@'%'

The % host wildcard means the user can connect from any host that can reach the MySQL server and pass authentication.

Prefer narrower host patterns:

'app_read'@'10.20.%'
'app_write'@'app-server-01.example.com'
'app_batch'@'10.30.40.%'

MySQL account identity includes both user and host, and connection access checks match the client host and username against account rows.

Network controls still matter, but database-level host restrictions add another useful layer.

Use Strong Password and Account Policies

Good MySQL user management also includes password and account controls.

MySQL password management supports password expiration, password reuse policies, failed-login tracking, and temporary locking when configured for accounts. Failed-login tracking and temporary locking require both FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME to be set to nonzero values.

Example:

CREATE USER 'app_read'@'10.%'
IDENTIFIED BY 'strong_password_here'
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LOCK_TIME 2;

You can also lock accounts that are not currently needed:

ALTER USER 'old_app_user'@'10.%' ACCOUNT LOCK;

MySQL supports explicit account locking and unlocking with ALTER USER.

Separate Application Users from Human Users

Application users and human users should not be the same.

A developer, DBA, support engineer, or analyst should have their own named account, not share the application’s production credential.

Bad:

  • Everyone uses app_user

Better:

  • app_read
  • app_write
  • alice.dba
  • bob.support
  • etl_service
  • reporting_service

This improves accountability. When a person leaves the team, you disable that person’s account. You do not have to rotate the production application credential because it was shared informally.

Do Not Use Root for Applications

The MySQL root account should never be used by application code.

Root-level access is for administrative tasks, not normal application behavior. If an application can connect as root, then any SQL injection vulnerability, leaked config file, or compromised application server can become a full database takeover.

Application accounts should be purpose-built and limited.

Use Read Replicas Carefully

Many teams direct read-only traffic to replicas and write traffic to the primary database. This pairs naturally with separate users:

  • app_read   -> read replica
  • app_write  -> primary

This improves scalability and reduces load on the primary.

However, remember that replicas can lag. Do not send read-after-write critical paths to a replica unless the application can tolerate delay or has logic to read from the primary after writes.

Security-wise, read replicas should still use restricted users. A replica is not automatically safe just because it is read-oriented.

Avoid Schema Changes from the Main Application User

Schema migrations often require table privileges such as ALTER, CREATE, DROP, or INDEX.

The main runtime application user usually should not have those privileges.

Instead:

  • app_write -> normal runtime writes
  • app_migration -> schema changes during deployment only

This prevents an application bug or injection flaw from turning into a schema-destruction event.

Review Grants Regularly

Permissions tend to grow over time.

A user gets extra access during an incident. A script needs temporary permissions. A migration account is created and never removed. A retired service still has access.

Use SHOW GRANTS to inspect what an account can do. MySQL documents SHOW GRANTS as the way to display privilege and role assignments for accounts.

Example:

SHOW GRANTS FOR 'app_write'@'10.%';
SHOW GRANTS FOR 'app_read'@'10.%';

Then remove what is no longer needed:

REVOKE DELETE ON appdb.* FROM 'app_write'@'10.%';
DROP USER 'old_service'@'10.%';

Practical Checklist

A secure MySQL application setup should usually include:

  • Separate users for reads and writes
  • No application use of root
  • No broad GRANT ALL unless truly required
  • Host restrictions instead of unrestricted ‘%’ access
  • Separate users for migrations, reports, batch jobs, and admin tools
  • Strong password and account-locking policies
  • Regular SHOW GRANTS reviews
  • Fast credential rotation process
  • Named human accounts instead of shared credentials
  • Minimal permissions for each service
  • Locked or dropped unused accounts

Example: A Safer Baseline

Here is a simple starting point:

CREATE USER 'myapp_read'@'10.%'
IDENTIFIED BY 'strong_read_password'
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LOCK_TIME 2;

CREATE USER 'myapp_write'@'10.%'
IDENTIFIED BY 'strong_write_password'
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LOCK_TIME 2;

CREATE USER 'myapp_migration'@'10.%'
IDENTIFIED BY 'strong_migration_password'
ACCOUNT LOCK;

GRANT SELECT
ON myapp.*
TO 'myapp_read'@'10.%';

GRANT SELECT, INSERT, UPDATE, DELETE
ON myapp.*
TO 'myapp_write'@'10.%';

GRANT CREATE, ALTER, DROP, INDEX
ON myapp.*
TO 'myapp_migration'@'10.%';

The migration account is locked by default and only unlocked during controlled deployment windows:

ALTER USER 'myapp_migration'@'10.%' ACCOUNT UNLOCK;

— run migration

ALTER USER 'myapp_migration'@'10.%' ACCOUNT LOCK;

Store Database Credentials in Configuration, Not Code

Another important part of MySQL user management is where the application stores the database credentials.

Avoid hardcoding database usernames, passwords, hostnames, and ports directly in application code.

Bad:

connection = mysql.connector.connect(
    host="prod-db-01.example.com",
    user="app_write",
    password="SuperSecretPassword",
    database="appdb"
)

This creates several problems:

  • Changing a password requires a code change.
  • Changing a database host requires a code change.
  • The same credential may be copied into many files.
  • Secrets can accidentally be committed to source control.
  • Developers may unknowingly share or expose production credentials.
  • Credential rotation becomes slower and riskier.

A better approach is to store database connection settings in a configuration file, environment-specific secret store, or deployment-managed configuration source, and have the application read those values at startup.

For example:

[database_read]
host=prod-read-replica.example.com
port=3306
database=appdb
user=app_read
password=strong_read_password

[database_write]
host=prod-primary.example.com
port=3306
database=appdb
user=app_write
password=strong_write_password

The application can then load the configuration:

import configparser
import mysql.connector

config = configparser.ConfigParser()
config.read("/etc/myapp/database.ini")

read_db = mysql.connector.connect(
    host=config["database_read"]["host"],
    port=config["database_read"]["port"],
    user=config["database_read"]["user"],
    password=config["database_read"]["password"],
    database=config["database_read"]["database"]
)

write_db = mysql.connector.connect(
    host=config["database_write"]["host"],
    port=config["database_write"]["port"],
    user=config["database_write"]["user"],
    password=config["database_write"]["password"],
    database=config["database_write"]["database"]
)

Now, if the database host changes, a password is rotated, or the application needs to switch from one MySQL user to another, you update the configuration file instead of editing application code.

This is especially useful when separating read and write users. The application code can reference logical connection names such as database_read and database_write, while the actual MySQL usernames, passwords, and hostnames remain outside the codebase.

However, configuration files that contain database credentials must be protected carefully.

A credentials file should usually be:

  • Owned by the application runtime user
  • Readable only by the application runtime user
  • Excluded from source control
  • Excluded from application logs
  • Different for development, test, staging, and production
  • Managed through deployment automation or a secrets management process
  • Rotated regularly
  • Backed by strong file-system permissions

For example, on Linux:

chown myapp:myapp /etc/myapp/database.ini
chmod 600 /etc/myapp/database.ini

This means only the myapp user can read or write the file.

It is also a good idea to keep a template version in source control without real secrets:

[database_read]
host=
port=3306
database=
user=
password=

[database_write]
host=
port=3306
database=
user=
password=

The real production file should be created by deployment tooling, configuration management, or a secrets-management system rather than manually copied around.

For higher-security environments, consider using a dedicated secrets manager instead of a plain text file. Examples include cloud secrets services, Oracle Key Vault, HashiCorp Vault, Kubernetes Secrets, Docker secrets, or an enterprise credential vault. The key idea is the same: application code should not contain the actual database credentials.

Separating credentials from code gives you operational flexibility. You can rotate passwords, rename MySQL users, move from a primary database to a replica, change hostnames, or adjust environments without rewriting and redeploying the application. Combined with separate read and write users, this makes the application easier to operate and safer to secure.

Final Thought

Database credentials are not just connection details. They are security boundaries.

Using one MySQL user for every application operation makes the system easier to configure, but it also makes every bug, leaked secret, SQL injection flaw, and compromised component more dangerous.

Separating read and write users is one of the simplest and most effective improvements you can make. From there, continue separating responsibilities: migrations, reports, background jobs, admin tools, and human access.

It is also important to keep database credentials out of the application code itself. Store usernames, passwords, hostnames, ports, and database names in a protected configuration file or secrets-management system that the application reads at runtime. That way, when you need to rotate a password, change a MySQL username, move to a new database host, or update read/write connection settings, you can change the configuration instead of editing code throughout the application.

Those credential files must be treated as sensitive assets: restrict file permissions, keep them out of source control, avoid logging their contents, and manage separate versions for development, test, staging, and production.

Good MySQL user management is not about making life harder. It is about making sure that when something goes wrong, the blast radius is small, the logs are useful, credentials can be rotated quickly, and recovery is manageable.