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
• Create User with All Privilages in MariaDB
• MariaDB Create User with Remote-Access
• How to Create User with Root Privileges in MariaDB
• MariaDB Create User with Read Only Access
• How to Create User for Specific database in MariaDB
• Create User without Password in MariaDB
• MariaDB Create User Failed
• MariaDB ERROR 1396

If you are new to MariaDB, check out how to install MariaDB.

How to Create a User in MariaDB

Let us see, how to create a user in MariaDB, but before creating any user in MariaDB, first access the MariaDB server by providing the Root password.

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

CREATE DATABASE USA_book;

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

SHOW DATABASES;

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 'Justin'@localhost IDENTIFIED BY 'justin123';

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

SELECT User FROM mysql.user;

The output will be the list of all users.

This is how to create a user in MariaDB.

Read How To Check MariaDB Version

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 Justin, type the following command.

GRANT ALL PRIVILEGES ON *.* TO 'Justin'@localhost IDENTIFIED BY 'justin123';

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.

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 USA_data database to Justin, type the following command.

GRANT ALL PRIVILEGES ON USA_data.* TO 'Justin'@localhost;

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

FLUSH PRIVILEGES;

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

Read How to Create Database in MariaDB

Create User with Remote-Access in MariaDB

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 'Joey'@'%' IDENTIFIED BY 'joey123';

GRANT ALL ON USA_data.* TO 'Joey'@'%';

Don’t forget to refresh the privileges.

FLUSH PRIVILEGES;

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

Read MariaDB query examples

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 'Jessica'@localhost IDENTIFIED BY 'jessica123';

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

GRANT ALL PRIVILEGES ON *.* TO 'Jessica'@localhost IDENTIFIED BY 'jessica123';

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

Read How to Grant User Access to a MariaDB Database

How to Create User with Read-Only Access in MariaDB

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'@'localhost' IDENTIFIED BY 'alex123';

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

GRANT SELECT ON USA_data.* TO 'Alex'@'localhost';

Step 3 – Refresh the privileges.

FLUSH PRIVILEGES;

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

Read How to Remove User 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 'Justin'@localhost IDENTIFIED BY 'justin123';

Step 2 – Grant Justin with all the privileges associated with the USA_data database by typing the following command.

GRANT ALL PRIVILEGES ON USA_data.* TO 'Justin'@localhost;

Here, USA_data is the specified database that can be accessed by Justin.

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

Read How to Create Table in MariaDB

Create User without Password in MariaDB

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

For example,

CREATE USER Harry;

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 'Justin'@localhost IDENTIFIED BY 'justin123';

CREATE OR REPLACE USER 'Justin'@localhost IDENTIFIED BY 'justin123';

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 'Justin'@localhost IDENTIFIED BY 'justin123';

CREATE USER IF NOT EXISTS 'Justin'@localhost IDENTIFIED BY 'justin123';

You may like the following MariaDB tutorials:

• MariaDB Vs SQL Server
• How to Grant All Privileges in MariaDB
• MariaDB query examples
• MariaDB Row_Number

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
• How to Create User with All Privilages in MariaDB
• MariaDB Create User with Remote-Access
• How to Create User with Root Privileges in MariaDB
• MariaDB Create User with Read Only Access
• MariaDB Create User for Specific database
• How to Create User without Password in MariaDB
• MariaDB Create User Failed
• Fix error, MariaDB ERROR 1396