How to create a user in MySQL or MariaDB

Creating a user in MySQL or MariaDB is an essential task in managing database environments. By assigning unique user accounts, you can control access to different parts of the database, ensuring each user has the appropriate privileges. This process enhances security and helps organize access across different applications or users. In both systems, you can manage user accounts with the CREATE USER statement and set privileges using the GRANT statement.

Both MySQL and MariaDB store user information in their system tables, such as the mysql.user table for MySQL. To create a new user, administrative access is required, typically through the root account. Strong, unique passwords should be used for each account to minimize security risks. User-specific access helps restrict permissions and maintain a controlled environment for data operations.

When managing users, it’s critical to correctly assign privileges based on their role. Privileges determine what actions users can perform, like querying data or modifying databases. Creating a separate account for each task ensures a clear structure in your database. With administrative access, you can create, modify, and remove user accounts as necessary.

1. Log in to your MySQL or MariaDB server using an account with administrative privileges.

   $ mysql -u root -p
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 10
   Server version: 8.0.30 Source distribution
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
   mysql>

2. Create a new user account.

   mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'securepassword';
   Query OK, 0 ROWS affected (0.01 sec)

   Make sure your password meets the password validation policy requirement or you'll get the following error:

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

3. Assign the necessary privileges to the user for specific databases or tables.

   mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'newuser'@'localhost';
   Query OK, 0 ROWS affected (0.00 sec)

   You can assign more specific privileges instead of all privileges, depending on the user's needs. For example:

   mysql> GRANT SELECT, INSERT ON database_name.* TO 'newuser'@'localhost';

   Related: How to grant user access and permissions in MariaDB and MySQL

4. Apply the changes by reloading the privileges.

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

5. Exit the server session.

   mysql> exit
   Bye

6. Test the new user account by logging in with the new credentials.

   $ mysql -u newuser -p
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 11
   Server version: 8.0.30 Source distribution
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
   mysql>