Skip to content
DBAExperts
MySQL Users & permissions

How to create a user and grant permissions in MySQL

In MySQL 8.0 a user is identified by the user@host pair. First you create the user with CREATE USER, then you grant permissions with GRANT. The guiding principle is to grant only the permissions that are needed, not one more.

  1. 1

    Create the user

    Define the host it connects from. Use localhost for local connections or a range like 10.0.%.% for an internal network. Avoid the global wildcard % unless it is strictly necessary.

    CREATE USER 'app_lectura'@'10.0.%.%' IDENTIFIED BY 'una_clave_fuerte';
  2. 2

    Grant minimal permissions on a database

    Grant only what the application needs. A read-only user does not require INSERT or DELETE.

    GRANT SELECT ON mi_base.* TO 'app_lectura'@'10.0.%.%';
  3. 3

    For an application user with write access

    Grant the usual data operations, but avoid administrative permissions like GRANT OPTION or SUPER.

    GRANT SELECT, INSERT, UPDATE, DELETE ON mi_base.* TO 'app_web'@'10.0.%.%';
  4. 4

    Apply and verify the permissions

    In MySQL 8.0 GRANT takes effect immediately, but FLUSH PRIVILEGES is useful after manual changes to the system tables. Always review what you granted.

    SHOW GRANTS FOR 'app_lectura'@'10.0.%.%';

// common mistake

Granting ALL PRIVILEGES ON *.* turns the user into a near-root account and is a huge security risk. Also, if the GRANT host does not match the CREATE USER host, MySQL creates a different user entirely and you will be left wondering why login does not work.

// when it's worth an expert

When your permission scheme grew unchecked and nobody knows who can do what anymore, it is worth auditing. At dba.mx we review and reorganize access with least privilege and roles, at a fixed price.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.