How to grant privileges to a MySQL or MariaDB user

Granting database access precisely keeps application accounts, reporting logins, and human operators limited to the work they actually need to do. That reduces accidental data changes, limits what leaked credentials can reach, and makes permission troubleshooting much easier later.

Both MySQL and MariaDB treat an account as the full 'user'@'host' identity, so the same user name can have different access from localhost, 127.0.0.1, or a remote IP. GRANT assigns privileges at global, database, table, column, routine, or role scope, and SHOW GRANTS returns the statements that describe the access currently attached to that exact account entry.

Current MySQL releases expect you to create the account first with CREATE USER, and while MariaDB can still allow implicit account creation in some SQL mode combinations, that is not a reliable cross-platform workflow. Use the full 'user'@'host' form every time, avoid broad '%' hosts and *.* grants unless they are truly required, and run FLUSH PRIVILEGES only after direct edits to the mysql grant tables.

1. Connect with an administrative account in the mysql or mariadb client.

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

   Some installations use socket authentication for the local administrator, so sudo mysql or sudo mariadb can be the expected entry point.

2. Create the account with the exact host component it should use.

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

   Use the full 'user'@'host' form every time. In current MySQL and MariaDB releases, omitting the host defaults it to '%', and on Linux 'localhost' usually matches local socket connections rather than 127.0.0.1 TCP.

   Related: How to create a user in MySQL or MariaDB

3. Grant only the database privileges the account actually needs.

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

   Use `database`.* for all tables in one schema, `database`.`table` for one table, and add WITH GRANT OPTION only when the account must delegate the same privileges to other accounts.

4. Grant table-level privileges when database-wide access is unnecessary.

   mysql> GRANT SELECT, INSERT ON `appdb`.`orders` TO 'appuser'@'localhost';
   Query OK, 0 rows affected (0.00 sec)

   Use backticks around identifiers that contain dashes, spaces, or reserved words.

5. Create a separate account entry for a remote client host when the same user name must connect from elsewhere.

   mysql> CREATE USER 'appuser'@'192.0.2.25' IDENTIFIED BY 'GrantPass123!';
   Query OK, 0 rows affected (0.00 sec)

   Prefer an exact IP or a narrow subnet entry over '%'. MariaDB also supports netmask host entries such as '192.0.2.0/255.255.255.0', and MySQL servers that run with skip-name-resolve work more reliably with literal IP-based hosts than DNS names.

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

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

   'appuser'@'localhost' and 'appuser'@'192.0.2.25' are different accounts, so privileges granted to one do not automatically apply to the other.

7. Change the password with ALTER USER when the account needs a new secret.

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

   Keep the 'user'@'host' identity identical to the account you are updating, and do not rely on legacy PASSWORD() hashing in application SQL.

   Related: How to change MySQL or MariaDB user password

8. Review the effective grants for the exact account entry.

   mysql> SHOW GRANTS FOR 'appuser'@'localhost';
   Grants for appuser@localhost
   GRANT USAGE ON *.* TO `appuser`@`localhost`
   GRANT SELECT, INSERT, UPDATE ON `appdb`.* TO `appuser`@`localhost`
   GRANT SELECT, INSERT ON `appdb`.`orders` TO `appuser`@`localhost`

   MariaDB can also show an IDENTIFIED BY PASSWORD clause in this output, while MySQL 8.4 typically shows the privilege lines without the stored password hash.

9. Reload the grant tables only when someone edited the mysql privilege tables directly instead of using account-management statements.

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

   CREATE USER, GRANT, ALTER USER, and REVOKE take effect without this step, so keep it for manual table edits or a server started with --skip-grant-tables.

10. Verify the new account can connect to the intended schema.

    $ mysql -u appuser -p -D appdb -e "SELECT CURRENT_USER(), DATABASE();"
    Enter password:
    +-------------------+------------+
    | CURRENT_USER()    | DATABASE() |
    +-------------------+------------+
    | appuser@localhost | appdb      |
    +-------------------+------------+

    If you changed the password or granted global privileges to an account that is already connected, reconnect before testing so the session picks up the new authentication or privilege state.

11. Use the closest matching grant pattern when translating application requirements into a final privilege set.

    mysql> GRANT SELECT ON `appdb`.* TO 'report'@'localhost';
    
    mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON `appdb`.* TO 'app'@'localhost';
    
    mysql> GRANT ALL PRIVILEGES ON `appdb`.* TO 'dbadmin'@'localhost';

    Prefer schema-level grants over GRANT ALL PRIVILEGES ON *.*, and use WITH GRANT OPTION only for trusted administrative accounts that truly need to delegate access.