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.
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.
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.
Next you should also 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,…
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. By learning how to deal with it,…
How to Install MariaDB 10.4 on Ubuntu 18.04
July 19, 2019
MariaDB is an open source, fully compatible, relational database management system. It is commonly used as a…
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…
Author
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.