How to Create MariaDB User and Grant Privileges

March 18, 2020

Introduction

One of the most common tasks when administering a database is to oversee access and permissions. MariaDB is an open-source, fully compatible, relational database management system (RDBMS). The MariaDB client makes it easy to add new users and grant them different degrees of privileges.

This simple tutorial shows you how to create a MariaDB user, and then how to grant privileges to the newly created user.

tutorial header on creating and assigning privileges in MariaDB

Prerequisites

  • Access to command line/terminal window
  • A user account with sudo privileges
  • A working instance of MariaDB

Access MariaDB Server

Enter the following command in your command-line terminal to access the MariaDB client shell:

sudo mysql -u root

If your root user has a predefined password, modify the command to reflect that fact:

sudo mysql -u root -p

Enter your password and access the MariaDB client.

MariaDB shell successfully accessed

If you do not have any databases created yet, you can easily do so by typing the following command in your MariaDB client shell:

CREATE DATABASE 'yourDB';

Access a list of existing databases by typing this command:

SHOW DATABASES;

The database we just created is on the list.

new mariadb database is now available.

Create New MariaDB User

To create a new MariaDB user, type the following command:

CREATE USER 'user1'@localhost IDENTIFIED BY 'password1';

In this case, we use the ‘localhost’ host-name and not the server’s IP. This practice is commonplace if you plan to SSH in to your server, or when using the local client to connect to a local MySQL server.

Note: Substitute user1 and password1 with the credentials for the user you are creating.

Once you create user1, check its status by entering:

SELECT User FROM mysql.user;

The output lists all existing users.

example of listing existing users in mariadb

Grant Privileges to MariaDB User

The newly created user does not have privileges to manage databases nor to access the MariaDB shell.

To grant all privileges to user1:

GRANT ALL PRIVILEGES ON *.* TO 'user1'@localhost IDENTIFIED BY 'password1';

The *.* in the statement refers to the database or table for which the user is given privileges. This specific command provides access to all databases located on the server. As this might be a major security issue, you should replace the symbol with the name of the database you are providing access to.

To grant privileges only for yourDB, type the following statement:

GRANT ALL PRIVILEGES ON 'yourDB'.* TO 'user1'@localhost;

It’s crucial to refresh the privileges once new ones have been awarded with the command:

FLUSH PRIVILEGES;

The user you have created now has full privileges and access to the specified database and tables.

Once you have completed this step, you can verify the new user1 has the right permissions by using the following statement:

SHOW GRANTS FOR 'user1'@localhost;

The information provided by the system is displayed on the terminal.

The terminal displays the privileges grated to a specific user.

Remove MariaDB User Account

If you need to remove a user, you can employ the DROP statement:

DROP USER 'user1'@localhost;

The output confirms that user1 no longer has access nor privileges.

example of how to remove a mariadb user with drop

Conclusion

You have successfully created a MariaDB user and granted full user privileges. This basic task should quickly become a routine. There are numerous options to customize privileges and tailor them to your requirements.

We encourage you to explore the many available options that allow you to administer your databases securely and effectively.

Was this article helpful?
YesNo
Vladimir Kaplarevic
Vladimir is a resident Tech Writer at phoenixNAP. He has more than 7 years of experience in implementing e-commerce and online payment solutions with various global IT services providers. His articles aim to instill a passion for innovative technologies in others by providing practical advice and using an engaging writing style.
Next you should read
How to Solve MySQL Error: Access denied for user root@localhost
January 13, 2020

When you install MySQL on your system, the root user may not be able to access it. Using the ALTER command...
Read more
How to Fix MySQL "Command Not Found" (Linux, Windows, mac OS)
December 11, 2019

The 'Command Not Found' error is a general error not only found in MySQL. Learn how to deal with it today.
Read more
How to Install MariaDB 10.4 on Ubuntu 18.04
July 19, 2019

MariaDB is an open-source, fully compatible, relational database management system.
Read more
How to Install MariaDB on CentOS 7
July 14, 2019

For CentOS 7 users, MariaDB is the default database system as it replaced MySQL as the default database system...
Read more