Creating a User in MariaDB: A Step-by-Step Guide

Introduction

To create a new user in MariaDB, you need to have access to the MariaDB server with an account that has sufficient privileges to create new users. If you don’t have such an account, you will need to contact your database administrator to create a user for you.

Assuming you have the necessary privileges, you can create a new user in MariaDB using the following steps:

Connect to MariaDB

You can connect to the MariaDB server using the mysql command-line client, or graphically using Beekeeper Studio. You will need to provide the appropriate username and password for an account that has the necessary privileges.

Execute CREATE USER

Once you are connected to the MariaDB server, use the CREATE USER statement to create a new user. This statement takes the following form:

CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';

Replace username with the desired username for the new user, hostname with the hostname or IP address of the server where the user will be able to connect from, and password with the desired password for the user.

Need a secure password for your database users? Try our free Database Password Generator tool.

Here’s an example which allows user ‘matthew’ to connect from any host.

CREATE USER 'matthew'@'%' IDENTIFIED BY 'supersecretpassword';

Grant Permissions

After creating the user, you will need to grant the necessary privileges to the user. This is done using the GRANT statement, which has the following form:

GRANT priv_type ON priv_level TO 'username'@'hostname';

Replace priv_type with the type of privilege you want to grant (such as SELECT, INSERT, UPDATE, etc.), priv_level with the level at which the privilege should apply (such as a specific database or table), and username and hostname with the values you used in the CREATE USER statement.

You can view all the privileges that the database supports by calling SHOW PRIVILEGES. See the official documentation for more information.

Here is an example of granting ALL permissions on all databases to our user, Matthew:

GRANT ALL PRIVILEGES ON * . * TO 'matthew'@'%';

Flush privileges

After granting the necessary privileges to the user, you can use the FLUSH PRIVILEGES statement to make the changes take effect. This statement has the following form:

FLUSH PRIVILEGES;

That’s It

Once you have completed these steps, the new user will be able to connect to the MariaDB server and perform the actions allowed by the privileges you have granted. It is important to note that you can always use the REVOKE statement to remove privileges from a user or the DROP USER statement to delete the user entirely.

MariaDB CREATE USER Summary

In summary, creating a new user in MariaDB involves using the CREATE USER and GRANT statements to create the user and grant the necessary privileges, and then using the FLUSH PRIVILEGES statement to make the changes take effect. As always, it is important to carefully manage user accounts and privileges to ensure the security and integrity of your MariaDB server.