How to grant user access and permissions in MariaDB and MySQL

Granting least-privilege access in MariaDB and MySQL controls who can read or change data, reduces the blast radius of leaked credentials, and keeps application accounts from behaving like accidental DBAs.

Both servers model an account as 'username'@'hostname', so 'app'@'localhost' and 'app'@'%' are separate identities with separate privilege sets. Permissions are assigned with GRANT at multiple scopes (global, database, table, column, routine), and SHOW GRANTS reveals the exact rules applied to a given account.

Modern deployments typically require explicit CREATE USER before applying privileges with GRANT, and password updates should use ALTER USER rather than legacy PASSWORD() hashing. Broad host wildcards ('%'), global privileges (*.*), and WITH GRANT OPTION expand the attack surface, so scope privileges tightly and delegate only when required. Privilege changes apply immediately when using CREATE USER and GRANT, while FLUSH PRIVILEGES is mainly relevant after manual edits to the mysql privilege tables.

1. Connect to the server using an administrative account.

   $ mysql -u root -p
   Enter password: ********
   mysql>

   Some installations use socket authentication for administrative access, so sudo mysql may replace mysql -u root -p.

2. Create the account with the correct host component.

   mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'REPLACE_WITH_STRONG_PASSWORD';
   Query OK, 0 rows affected (0.00 sec)

   Replace newuser and localhost with the exact identity required, such as 'newuser'@'192.0.2.10' or 'newuser'@'192.0.2.%'.

3. Grant the minimal privileges required on the target database.

   mysql> GRANT SELECT, INSERT, UPDATE ON `mydatabase`.* TO 'newuser'@'localhost';
   Query OK, 0 rows affected (0.00 sec)

   Replace mydatabase with the schema name and keep the 'user'@'host' portion identical to CREATE USER.

   Parameter	Meaning
   privilege1, privilege2, …	Comma-separated privileges such as SELECT, INSERT, UPDATE, DELETE, CREATE, DROP.
   `database`.*	Object scope, where `db`.* applies to all tables in a schema.
   `database`.`table`	Table scope, where privileges apply only to one table.
   'username'@'hostname'	Account identity, where hostname can be a literal host, a subnet pattern, or '%'.
   WITH GRANT OPTION	Allows the account to grant its privileges to other accounts.

4. Grant privileges on a single table when database-wide access is unnecessary.

   mysql> GRANT SELECT, INSERT ON `mydatabase`.`mytable` TO 'newuser'@'localhost';
   Query OK, 0 rows affected (0.00 sec)

   Use backticks around identifiers that contain dashes or reserved words.

5. Create a separate remote account entry when logins must originate from another host.

   mysql> CREATE USER 'newuser'@'192.0.2.%' IDENTIFIED BY 'REPLACE_WITH_STRONG_PASSWORD';
   Query OK, 0 rows affected (0.00 sec)

   Using '%' allows logins from any host, so prefer a specific host or subnet pattern.

6. Grant the required privileges to the remote account entry.

   mysql> GRANT SELECT, INSERT, UPDATE ON `mydatabase`.* TO 'newuser'@'192.0.2.%';
   Query OK, 0 rows affected (0.00 sec)

7. Change the account password using ALTER USER.

   mysql> ALTER USER 'newuser'@'localhost' IDENTIFIED BY 'REPLACE_WITH_NEW_STRONG_PASSWORD';
   Query OK, 0 rows affected (0.00 sec)

   Avoid PASSWORD() hashing in application SQL, since password handling is managed by account statements.

8. Review the effective grants for the account.

   mysql> SHOW GRANTS FOR 'newuser'@'localhost';
   +------------------------------------------------------------------------+
   | Grants for newuser@localhost                                           |
   +------------------------------------------------------------------------+
   | GRANT USAGE ON *.* TO 'newuser'@'localhost'                            |
   | GRANT SELECT, INSERT, UPDATE ON `mydatabase`.* TO 'newuser'@'localhost'|
   +------------------------------------------------------------------------+
   2 rows in set (0.00 sec)

9. Run FLUSH PRIVILEGES only when privilege tables were modified directly.

   mysql> FLUSH PRIVILEGES;
   Query OK, 0 rows affected (0.00 sec)

   GRANT and CREATE USER apply immediately, so this step is typically unnecessary.

10. Verify access by running a simple query as the new account.

    $ mysql -u newuser -p -D mydatabase
    Enter password: ********
    mysql> SELECT CURRENT_USER();
    +-------------------+
    | CURRENT_USER()    |
    +-------------------+
    | newuser@localhost |
    +-------------------+
    1 row in set (0.00 sec)

11. Use these common templates for typical privilege sets.

    mysql> GRANT SELECT ON `mydatabase`.* TO 'report'@'localhost';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON `mydatabase`.* TO 'app'@'localhost';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> GRANT ALL PRIVILEGES ON `mydatabase`.* TO 'dbadmin'@'localhost';
    Query OK, 0 rows affected (0.00 sec)

    Global admin grants such as GRANT ALL PRIVILEGES ON *.* significantly increase risk, so scope privileges to a single schema whenever possible.