How to create a user in MariaDB – Different ways

In this MariaDB tutorial, we are going to learn about MariaDB create user. We will see, how to create a user in MariaDB, and also, we will see how to create users with passwords in MariaDB.

Additionally, we will cover the following topics.

  • MariaDB Create User
  • MariaDB Create User with All Privilages
  • MariaDB Create User with Remote-Access
  • MariaDB Create User with Root Privileges
  • MariaDB Create User with Read Only Access
  • MariaDB Create User for Specific database
  • MariaDB Create User without Password
  • MariaDB Create User Failed

Before Creating MariaDB User, you need to do several things such as:

  • Accessing MariaDB server by providing Root Password
  • Creation of MariaDB Databases

Accessing MariaDB Server by providing Root Password

Enter the password you created during the installation of MariaDB on your computer.

How to Create User in MariaDB

After entering the correct root password, you can enter commands to execute various queries.

MariaDB Create User
Create a user in MariaDB

Creation of MariaDB Databases

If you do not have any database created, then you should type the following command to create a MariaDB database.

CREATE DATABASE 'yourDB';

To access the list of all databases, you should type the following command.

SHOW DATABASES;
create a user in MariaDB

How to Create a User in MariaDB

Let us see how to create a user in MariaDB.

After the creation of MariaDB databases, you need to create a MariaDB user. To create A MariaDB user, you need to type the following command.

CREATE USER 'user2'@localhost IDENTIFIED BY 'password2';

Once you create user2, check its status by typing the below command.

SELECT User FROM mysql.user;

The output will be the list of all users.

MariaDB User Created
MariaDB User Created

This is how to create a user in MariaDB.

Also, take a look at How to install MariaDB + Uninstallation steps.

MariaDB Create User with All Privileges

The freshly created user does not have permission to the MariaDB shell or the ability to manage databases.

To grant privileges to user2, type the following command.

GRANT ALL PRIVILEGES ON *.* TO 'user2'@localhost IDENTIFIED BY 'password2';

Here, the *.* statement refers to the database or table for which the user has been given privileges. This specific command refers to all the databases located on the server.

Granting permission to MariaDB User
Granting permission to MariaDB User

To avoid misuse, you need to replace * with the name of the database for which you want to give privileges. For granting privileges for the db1 database to user2, type the following command.

GRANT ALL PRIVILEGES ON db1.* TO 'user2'@localhost;
Granting permission for specific database to MariaDB User
Granting permission for specific database to MariaDB User

Don’t forget to refresh the privileges once new ones have been awarded with the command.

FLUSH PRIVILEGES;
Refreshing permission to MariaDB User
Refreshing permission to MariaDB User

This is how to create a user in MariaDB with all the privileges.

Read: MariaDB Create Database

MariaDB Create User with Remote-Access

If you want to create a user who can access your database from remote machines in MariaDB, then you can simply remove ‘localhost’ and add ‘%’. Now the user can access the specified database from remote machines too.

For this, you need to type the following commands.

CREATE USER 'user3'@'%' IDENTIFIED BY 'password3';
GRANT ALL ON db1.* TO 'user3'@'%';

Don’t forget to refresh the privileges.

FLUSH PRIVILEGES;
Creating MariaDB User with Remote Access
Creating MariaDB User with Remote Access

This is how to create a user with remote access in MariaDB.

Also, check: MariaDB Grant User Access To Database

MariaDB Create User with Root Privilege

User with Root privileges means that user is authorized to execute any command and can access any database present on the MariaDB server.

To create a user with root privileges, you need to follow the steps provided below:

Step 1 – Simply create a MariaDB User by typing the following command.

CREATE USER 'user3'@localhost IDENTIFIED BY 'password3';

Step 2 – Grant user3 with all the privileges using the *.* statement.

GRANT ALL PRIVILEGES ON *.* TO 'user3'@localhost IDENTIFIED BY 'password3';
Create user with root privileges
Create user with root privileges

This is how to create user with root privilege in MariaDB.

MariaDB Create User with Read-Only Access

In this, you need to create a MariaDB user with read-only granted permission on a specific database or all the databases present on the server.

To create a user with read-only access, you need to follow the steps.

Step 1 – Firstly, create a user with read-only access. To do this, type the following command.

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

Step 2 – Now, grant privileges to a specific database.

GRANT SELECT ON db1.* TO 'alex_readonly'@'localhost';

Step 3 – Refresh the privileges.

FLUSH PRIVILEGES;
Creating MariaDB User with Read Only Access
Create User with Read-Only Access in MariaDB

This is how to create a user with read-only access in MariaDB.

MariaDB Create User for Specific database

To create a user for a specific database in MariaDB, follow the given steps:

Step 1 – Firstly, create a MariaDB user by typing the following command.

CREATE USER 'user5'@localhost IDENTIFIED BY 'password5';

Step 2 – Grant user2 with all the privileges associated with the db1 database by typing the following command.

GRANT ALL PRIVILEGES ON db1.* TO 'user5'@localhost;

Here, db1 is the specified database that can be accessed by user2.

This is how to create a user for a specific database in MariaDB.

MariaDB Create User without Password

To create a user without a password in MariaDB, you just need to remove the IDENTIFIED BY part.

For example,

CREATE USER user6;
Create MariaDB User without Password

This is how to create a user without a password in MariaDB.

MariaDB Create User Failed

Failure in the creation of user in MariaDB, can only be possible in two ways:

  • By naming the user as root, whenever you will try to do it, it will result in ERROR1396.
  • By naming the user as the name which is already present in the user list.

Naming the user as root – In this, you need to rename the user other than root. As whenever you will name your user as root, it will give an error.

Naming the user who is already present in the user list – To resolve this issue, you can three things which are as follows:

  • By renaming the user, as the user is already present inside the user list.
  • By using OR REPLACE clause to replace the already existing user.
  • By using IF NOT EXISTS clause, it will only create the user if and only if the name of the user is not present inside the list.

OR REPLACE Clause

It replaces the already present name of the user.

For example,

CREATE USER 'user5'@localhost IDENTIFIED BY 'password5';
MariaDB User failed
CREATE OR REPLACE USER 'user5'@localhost IDENTIFIED BY 'password5';
MariaDB User Failed Solution

IF NOT EXISTS

It will only create the user only if it is not present in the user list. Instead of giving an error, it gives a warning.

For example,

CREATE USER 'user5'@localhost IDENTIFIED BY 'password5';
MariaDB User failure
CREATE USER IF NOT EXISTS 'user5'@localhost IDENTIFIED BY 'password5';
MariaDB User Failure Solution

In this tutorial, we learned how to create users and create users with passwords in MariaDB. Additionally, we covered the following topics.

  • MariaDB Create User
  • MariaDB Create User with All Privilages
  • MariaDB Create User with Remote-Access
  • MariaDB Create User with Root Privileges
  • MariaDB Create User with Read Only Access
  • MariaDB Create User for Specific database
  • MariaDB Create User without Password
  • MariaDB Create User Failed