How to create a MySQL or MariaDB user and grant permissions

Topic: Databases core

Create a MySQL user with CREATE USER and grant privileges with GRANT on databases, tables, or global. Restrict by host (e.g. 'app'@'10.0.0.%'). Use this when onboarding an application or implementing least privilege access to MySQL or MariaDB.

Intent: How-to

Quick answer

  • CREATE USER 'app'@'localhost' IDENTIFIED BY 'secret'; CREATE USER 'app'@'10.0.0.%' IDENTIFIED BY 'secret'; Use specific host or subnet; avoid 'app'@'%' unless necessary.
  • GRANT ALL PRIVILEGES ON mydb.* TO 'app'@'localhost'; GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'readwrite'@'10.0.0.%'; FLUSH PRIVILEGES; after grant changes.
  • Read-only: GRANT SELECT ON mydb.* TO 'ro'@'%'; Revoke: REVOKE ALL ON mydb.* FROM 'app'@'localhost'; List users: SELECT user, host FROM mysql.user;. Store passwords in a secret manager.

Prerequisites

Steps

  1. Create user with host

    CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'password'; or 'myuser'@'10.0.0.%' for a subnet. Each user@host is a separate account; create one per app and host range needed.

  2. Grant database privileges

    GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost'; or GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'myuser'@'localhost'; FLUSH PRIVILEGES;

  3. Grant specific privileges

    For read-only: GRANT SELECT ON mydb.* TO 'ro'@'%'; For specific table: GRANT SELECT ON mydb.sensitive TO 'reader'@'%'; FLUSH PRIVILEGES;

  4. Verify and audit

    mysql -u myuser -p -h localhost mydb; run expected queries. SHOW GRANTS FOR 'myuser'@'localhost'; to audit. Remove unused users; use strong passwords and store in vault.

Summary

Create users with CREATE USER (user@host); grant privileges with GRANT on database or table; FLUSH PRIVILEGES. Use this to add users and enforce least privilege.

Prerequisites

Steps

Step 1: Create user with host

Create user for a specific host or subnet; avoid broad ’%’ unless required.

Step 2: Grant database privileges

Grant ALL or specific privileges on database.*; FLUSH PRIVILEGES.

Step 3: Grant specific privileges

Use SELECT-only or per-table grants for read-only or restricted access.

Step 4: Verify and audit

Connect as the user; use SHOW GRANTS to audit; remove unused users and store passwords in a vault.

Verification

  • User can connect and perform allowed operations; SHOW GRANTS reflects intent.

Troubleshooting

Access denied — Check user@host matches connection; check password; FLUSH PRIVILEGES. Too many privileges — REVOKE then GRANT only what is needed.

Next steps

Continue to