How to Grant User Access to a MariaDB Database

by
How to Grant User Access to a MariaDB Database

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;
MariaDB Grant User Access to Database
MariaDB Grant User Access to Database

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';
MariaDB Grant User Access to All Database
MariaDB Grant User Access to All Database

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;
MariaDB Grant Remote Access To Database
MariaDB Grant User Remote Access to Database

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;
MariaDB Grant Read Access To Database
MariaDB Grant User Read-Only Access to Database

This is how to grant user read access to a database in MariaDB.

Read MariaDB Vs SQL Server

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;
MariaDB Create User And Grant Access To Database
MariaDB Create User And Grant Access To Database

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;
Create User and Grant Access to Database MariaDB
MariaDB Grant Access to Database

This is how to create a user and grant user access to a database in MariaDB.

Read related MariaDB tutorials.

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