How to Create MariaDB User and Grant Privileges

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.

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.

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.

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.

Once you create user1, check its status by entering:

SELECT User FROM mysql.user;

The output lists all existing users.

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.

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.

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.