# 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:

```sql
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](https://www.ionos.com/digitalguide/server/security/protect-sensitive-data-with-a-strong-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:

```sql
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:

```sql
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:

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

This is a short form of this:

```sql
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`:

```sql
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.

Tip In our Digital Guide, you can find out even more about MariaDB. Among other things, we explain how the commands [MariaDB CREATE DATABASE](https://www.ionos.com/digitalguide/hosting/technical-matters/mariadb-create-database/) and [MariaDB CREATE TABLE](https://www.ionos.com/digitalguide/hosting/technical-matters/mariadb-create-table/) work, what the [differences and similarities between MariaDB and MySQL](https://www.ionos.com/digitalguide/hosting/technical-matters/mariadb-vs-mysql/) are, and how to [install MariaDB](https://www.ionos.com/digitalguide/hosting/technical-matters/install-mysql-mariadb/).


This is a markdown version of: [https://www.ionos.com/digitalguide/hosting/technical-matters/mariadb-create-user/](https://www.ionos.com/digitalguide/hosting/technical-matters/mariadb-create-user/) for AI/LLM consumption.