How to create a user in MySQL or MariaDB

Creating dedicated user accounts in MySQL or MariaDB keeps applications and operators from sharing the same administrative credentials, reduces the blast radius of leaked passwords, and improves auditing by tying changes to a specific identity.

Accounts are defined by a user name plus a host pattern (for example 'appuser'@'localhost'), and privileges are evaluated from the server’s internal grant tables when a statement is executed. User creation is handled with CREATE USER, while access is granted with GRANT at the global, schema, table, or column level.

Host patterns and privilege scope are the two controls that most affect security: broad hosts (especially '%') and broad grants (GRANT ALL PRIVILEGES) expand access far beyond what most workloads need. Password validation policies can also reject weak secrets with errors like ERROR 1819, so keep a strong password ready and prefer least-privilege grants from the start.

1. Log in to the server using an administrative account.

   $ mysql -u root -p
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 54
   Server version: 8.0.44 MySQL Community Server - GPL
   
   Copyright (c) 2000, 2025, Oracle and/or its affiliates.
   
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
   mysql>

   Some installations authenticate root via unix_socket; use

   $ sudo mysql

   or

   $ sudo mariadb

   when password authentication is disabled for root.

2. Create a new user account with an explicit host value.

   mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'ChangeMe-Use-A-Real-Secret';
   Query OK, 0 ROWS affected (0.00 sec)

   Restrict the host value; '%' allows logins from any host and is rarely appropriate.
   Weak passwords can be rejected by validation policies:

   ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

   'localhost' matches local socket connections, while TCP connections to 127.0.0.1 match a different host entry; create the matching account when the client connects over TCP.

3. Grant the minimum privileges needed on the target schema.

   mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'newuser'@'localhost';
   Query OK, 0 ROWS affected, 1 warning (0.01 sec)

   Read-only example:

   GRANT SELECT ON database_name.* TO 'newuser'@'localhost';

   
   Schema admin example:

   GRANT ALL PRIVILEGES ON database_name.* TO 'newuser'@'localhost';

4. Reload the privilege tables when changes were made directly to the grant tables.

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

   CREATE USER and GRANT apply immediately in normal use; FLUSH PRIVILEGES is typically only needed after manual edits to privilege tables.

5. Confirm the assigned privileges for the account.

   mysql> SHOW GRANTS FOR 'newuser'@'localhost';
   +----------------------------------------------------------------------------+
   | Grants FOR newuser@localhost                                               |
   +----------------------------------------------------------------------------+
   | GRANT USAGE ON *.* TO `newuser`@`localhost`                                |
   | GRANT SELECT, INSERT, UPDATE, DELETE ON `appdb`.* TO `newuser`@`localhost` |
   +----------------------------------------------------------------------------+
   2 ROWS IN SET (0.00 sec)

   Password rotation:

   ALTER USER 'newuser'@'localhost' IDENTIFIED BY 'NewStrongSecret';

   
   Remove access:

   REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'newuser'@'localhost';

6. Exit the server session.

   mysql> exit
   Bye

7. Log in as the new user to verify authentication.

   $ mysql -u newuser -p
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 55
   Server version: 8.0.44 MySQL Community Server - GPL
   
   Copyright (c) 2000, 2025, Oracle and/or its affiliates.
   
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
   mysql>

8. Select the schema that was granted to confirm access.

   mysql> USE appdb;
   DATABASE changed

9. List tables in the selected schema.

   mysql> SHOW TABLES;
   +-----------------+
   | Tables_in_appdb |
   +-----------------+
   | audit_log       |
   | customers       |
   | order_items     |
   | orders          |
   | users           |
   +-----------------+
   5 ROWS IN SET (0.00 sec)

10. Exit the test session.

    mysql> exit
    Bye