How to use CREATE USER in MariaDB

With CREATE USER, you can create new accounts in MariaDB. To define user rights, use GRANT. If you want to add a new user, you need admin rights.

Step-by-step instructions for using CREATE USER in MariaDB

The CREATE USER statement is used in MariaDB to create a new user. The individual steps you need to perform are shown here.

Step 1: Connect with MariaDB

Since new users are created directly within MariaDB, you need to first connect to the database. Open the MariaDB command line and log in using your username and password. Make sure that your account has administrative rights.

Step 2: Use CREATE USER

Now use CREATE USER to create a new user in MariaDB. The syntax is as follows:

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

Instead of the placeholder “username”, enter the desired username. This must be unique within the server environment. Replace “hostname” with the corresponding hostname or the IP address of your server, followed by a secure password for this account.

Step 3: Configure user permissions

The next step is to provide the new user with user rights. These define what a user can do in the system. A more detailed explanation of user rights can be found below. Use the GRANT statement to assign rights. Here’s the syntax:

GRANT ALL PRIVILEGES ON database.table TO 'username'@'hostname';

After the GRANT statement, list the rights that the account should receive. These are separated from each other by commas. For example, “database.table” can refer to a specific database or individual tables or apply to the entire system with *.*. Instead of the placeholder “username@hostname”, use the user and hostname of the new user.

Step 4: Establish user rights

After you have assigned user rights, use the FLUSH PRIVILEGES instruction to apply them directly. The command looks like this:

FLUSH PRIVILEGES;

Step 5: Log in

The new user can now log in to the MariaDB server. To do this, the new user only needs the username and the corresponding password for the account. To delete an account, you can use the DROP USER command. Just like with CREATE USER, you need admin rights to do this.

How to assign user rights

Privileges determine which actions users can perform in MariaDB. While the admin user is granted all rights, it’s important to limit user rights for other accounts, otherwise security problems may arise. The most common user rights are:

• ALL: Equips an account with all rights except GRANT OPTION
• GRANT OPTION: Grants an account the same permissions as your account
• SELECT: Grants access to databases or tables
• INSERT: Allows an account to add new rows to a table
• UPDATE: Allows an account to update rows
• DELETE: Allows an account to delete rows
• CREATE: Allows an account to create new tables or databases
• ALTER: Allows an account to change the structure of a table
• DROP: Allows an account to delete tables or databases

If you want to change user rights that you have already assigned, you can use the REVOKE command. To get an overview of the rights you have granted to an account, you can use the command SHOW GRANTS FOR.

How to use OR REPLACE and IF NOT EXISTS with CREATE USER

If you want to create a new account and a user with the same name already exists, you’ll receive an error message. To avoid this problem, MariaDB offers two clauses for CREATE USER: OR REPLACE and IF NOT EXISTS.

The syntax of OR REPLACE is:

CREATE OR REPLACE USER 'username'@'hostname' IDENTIFIED BY 'password';

This is a short form of this:

DROP USER IF EXISTS 'username'@'hostname'; 
CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';

The system checks whether there is already a user with the name that you want to use for the new user account. If this is the case, the old account is replaced by the new one. If there is no corresponding user, a new account is created.

This is the syntax for IF NOT EXISTS:

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

Here too, the system checks whether an account with the corresponding name already exists. If this is the case, you’ll receive a warning message and the account will be overwritten. If no such user exists, a new user account will be created.