In this MariaDB tutorial, we are going to learn about how to grant users access to a database. Additionally, we will cover the following topics.
- MariaDB Grant User Access To Database
- MariaDB Grant User Access To All Databases
- MariaDB Grant User Remote Access To Database
- MariaDB Grant User Read Access To Database
- MariaDB Create User And Grant Access To Database
Before granting access to a user to a MariaDB database, first, open the MariaDB server with the root password.
If you do not have any database created, then you should type the following command to create a MariaDB database. You can also follow an article on How to Create Database in MariaDB.
CREATE DATABASE 'USA_cityzen';
MariaDB Grant User Access To Database
Let us see, how to grant a user access to a MariaDB database.
The freshly created users don’t have permission to the MariaDB shell or the ability to manage databases.
Step 1 – To grant privileges to the user, type the following command.
GRANT ALL PRIVILEGES ON USA_cityzen.* TO 'James'@localhost IDENTIFIED BY 'James123';
Here, the USA_cityzen.* statement refers to the database or table for which the user has been given privileges. This command can also refer to all the databases located on the server by using *.* in the place of USA_cityzen.*.
Step 2 – Don’t forget to refresh the privileges once new ones have been awarded the command.
FLUSH PRIVILEGES;

This is how to grant user access to a database in MariaDB.
Also, read: How to create a user in MariaDB
MariaDB Grant User Access To All Databases
Let us see, how to grant user access to all MariaDB databases.
The freshly created user does not have permission to the MariaDB shell or the ability to manage databases.
Step 1 – To grant privileges to a user, type the following command.
GRANT ALL PRIVILEGES ON *.* TO 'Chris'@localhost IDENTIFIED BY 'chris123';

Here, the *.* statement refers to the database or table for which the user has been given privileges. This specific command refers to all the databases located on the server.
Note – To avoid misuse, you need to replace * with the name of the database for which you want to give privileges.
Step 2 – Don’t forget to refresh the privileges once new ones have been awarded the command.
FLUSH PRIVILEGES;
This is how to grant a user access to all the databases in MariaDB.
Also, check How To Check MariaDB Version
MariaDB Grant Remote Access To Database
If you want to create a user who can access your database from remote machines in MariaDB, then you can simply remove ‘localhost’ and add ‘%’. Now the user can access the specified database from remote machines too.
For this, you need to type the following steps.
Step 1 – Firstly, you need to create a user to whom you want to grant remote access to a database by typing the following command.
CREATE USER 'Katherine'@'%' IDENTIFIED BY 'kat123';
Step 2 – Now, you need to give remote access to the user by using the following command.
GRANT ALL ON AUS_library.* TO 'Katherine'@'%';
Step 3 – Don’t forget to refresh the privileges.
FLUSH PRIVILEGES;

This is how to grant a user remote access to a database in MariaDB.
Read How to Create Trigger in MariaDB
MariaDB Grant Read Access To Database
Let us see, how to grant user read access to a MariaDB database.
To grant a user with read-only access, you need to follow the steps.
Step 1 – Firstly, create a user with read-only access. To do this, type the following command.
CREATE USER 'alita_readonly'@'localhost' IDENTIFIED BY 'alita123';
Step 2 – Now, grant privileges to a specific database.
GRANT SELECT ON NZ_projects.* TO 'alita_readonly'@'localhost';
Step 3 – Refresh the privileges.
FLUSH PRIVILEGES;

This is how to grant user read access to a database in MariaDB.
MariaDB Create User And Grant Access To Database
Let us see, how to create a user and grant user read access to a MariaDB database.
In this, you need to create a MariaDB user and grant access to MariaDB databases.
Step 1 – To create a MariaDB user, you need to type the following command.
CREATE USER 'Caroline'@localhost IDENTIFIED BY 'Carol123';
Step 2 – Once you create user2, check its status by typing the below command.
SELECT User FROM mysql.user;

The output will be the list of all users and this is how to create a user in MariaDB.
Step 3 – Grant Caroline with all the privileges associated with the NZ_companies database by typing the following command.
GRANT ALL PRIVILEGES ON NZ_companies.* TO 'Caroline'@localhost;
Here, NZ_companies is the specified database that can be accessed by Caroline.
Step 4 – Refresh the privileges.
FLUSH PRIVILEGES;

This is how to create a user and grant user access to a database in MariaDB.
Read related MariaDB tutorials.
- How to Create Table in MariaDB
- How to import CSV files in MariaDB
- How to Grant All Privileges in MariaDB
- How to Remove User in MariaDB
- MariaDB varchar
- MariaDB Date Function
In this MariaDB Tutorial, we have learned about How to grant users access to a database. Additionally, we have covered the following topics.
- MariaDB Grant User Access To Database
- MariaDB Grant User Access To All Databases
- MariaDB Grant Remote Access To Database
- MariaDB Grant Read Access To Database
- MariaDB Create User And Grant Access To Database
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.