How to create a new user in MySQL

In this article you will learn how to create a user in both MySQL and MariaDB databases. You may want to perform such a task when working directly with the server.

It is worth mentioning that you need all the required access privileges. The instructions that are provided here require root access to the database and the operating system.

Please connect to the server via command line, in most cases you will use the SSH protocol. Then, the next step is to run the following command:

sudo mysql -u root -p

Important note: depending on the system and configuration, you may be asked to enter both the root user password and the MySQL database password.

After that, once you are inside of the MySQL shell, you can start entering the commands. Now, it's time to create a new user for the system.

CREATE USER 'username'@localhost IDENTIFIED BY 'password';

In the above sample, the username keyword is the name that you'd like to assign to the user. You can choose any value, but it must adhere to the MySQL naming convention.

The password value must be provided as well. However, it's recommended to use secure passwords with complicated patterns. As a result, please choose an appropriate value. You can use some random password generators for that purpose.

Please pay attention that the above command will produce an error if the user is already in the system. To avoid that, you may use the "IF NOT EXISTS" clause in the query. If it's specified, there will be a warning if the same user exists in the database. See the example below.

CREATE USER IF NOT EXISTS 'username'@localhost IDENTIFIED BY 'password';

Now, it's time to grant permission to a specific database. You may start with allowing access to the single database only, see our example below.

GRANT ALL PRIVILEGES ON your_database.* TO 'username'@localhost;

You have to replace the your_database string with the name of the desired database. Also, the username value needs to be changed, you should provide there the name of the user that was just created.

Optionally, you can grant permissions to all databases, like it's done in the command below. However, please be careful with such a command, as you may give access to unwanted data.

GRANT ALL PRIVILEGES ON *.* TO 'username'@localhost;

The final step that may be required is to flush the privileges. Please type the following command in the MySQL shell and press the enter button.

FLUSH PRIVILEGES;

Now, you should be able to connect to the database with the new user credentials.

Check if user exists in the database

To ensure that the user was added to the system, you may run the following command in the MySQL or MariaDB shell:

SELECT User FROM mysql.user WHERE User='username';

Please replace the username string with the name of the user that was created earlier.

If the user does exist, you should see output like the following:

+----------+
| User     |
+----------+
| username |
+----------+
1 row in set (0,00 sec)

Privileges

The following list contains the most widely used privileges which you may grant for the specific user.

• ALL PRIVILEGES - This option will allow all the privileges that are available at specified access level, but not including the GRANT OPTION and PROXY permissions.
• ALTER - This will allow use of the ALTER TABLE command.
• INSERT - This will allow use of the INSERT command.
• CREATE - If specified, this gives permission to create databases and tables.
• DELETE - This will allow use of the DELETE command.
• SELECT - This will allow use of the SELECT command.
• UPDATE - This will allow use of the UPDATE command.
• DROP - This option can be used to allow dropping databases, tables and views.
• EVENT - If set, then the events can be used for the event scheduler.
• EXECUTE - If this is set, then the user can execute stored routines.
• FILE - This privilege allows reading or writing the files on a server.
• INDEX - If set, then it's possible to create or drop indexes.
• GRANT OPTION - When this permissions is set, then it's allowed to grant or remove privileges.
• LOCK TABLES - If this is set, then the LOCK TABLES command can be used on tables with the enabled SELECT privilege.
• PROCESS - When this option is enabled, the user can see processes with help of SHOW PROCESSLIST command.
• REFERENCES - The foreign key can be created if this permission is allowed.
• PROXY - Allow a user to be proxied.
• RELOAD - If set, then the FLUSH operations are allowed.
• REPLICATION CLIENT - Allow a user to run SHOW MASTER STATUS, SHOW REPLICA STATUS or SHOW BINARY LOGS commands.
• REPLICATION SLAVE - Allow replications to get updates from the source.
• SHOW DATABASES - If set, then the SHOW DATABASES command is allowed to be executed.
• SHOW VIEW - This permission allows use of the SHOW CREATE VIEW command.
• SHUTDOWN - This has to be set to allow use of mysqladmin shutdown operation.
• SUPER - This privilege allows the use of various advanced administrative operations.
• TRIGGER - If this option is set, then the trigger operations will be allowed.
• USAGE - It means that the user does not have any privileges.

For the full list of acceptable permissions, please refer to the official MySQL and MariaDB specification.

Deleting the user

If the user is no longer needed, then you may easily remove it from the system. The "DROP" command has to be used for that purpose. It should be executed within the MySQL shell.

Please see our example below:

DROP USER IF EXISTS 'username'@localhost;

The username identifier has to be replaced with the actual name of the user that you'd like to remove.

Conclusion

As you see in our article, both MySQL and MariaDB database services provide simple commands that you can use to manage the users. There are various helpful privileges that you can configure in the system.

Although, this guide just covers the topic of adding the users. But the SQL language is very powerful and there are more options and tools available.