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.

Next you should also read