Grant All Privileges MariaDB

In this MariaDB Tutorial, we are going to learn about Grant All Privileges MariaDB.

Grant All Privileges MariaDB

Before proceeding, you need to make sure, you have already created a database in MariaDB and created a user in the MariaDB database.

Here, I have created a MariaDB database by using the following command:

CREATE DATABASE 'NZ_data';

And also I have created a user in the MariaDB database by using the following command.

CREATE USER 'Natasha'@localhost IDENTIFIED BY 'nat123';

Syntax

Let us see, the syntax to grant all privileges 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 DBname.* TO 'User'@localhost IDENTIFIED BY 'Password';

Here, DBname is the name of the database for which you want to grant privileges to a user. User is the name of the user who will have privileges to access the particular database and Password is the password corresponding to the user.

The DBname.* 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 DBname.*.

Step 2 – Don’t forget to refresh the privileges once new ones have been awarded the command.

FLUSH PRIVILEGES;

This is the syntax to grant all privileges to a user on a database in MariaDB.

Read How to Grant User Access to a MariaDB Database

Example

Let us see, how to grant all privileges 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 All Privileges To Remote User

If you want to grant all privileges to a remote user who can access your database from remote machines in MariaDB, you can remove ‘localhost’ and add ‘%’. Now the user can access the specified database from remote machines too.

To do 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 grant all privileges 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;

Read How to create a user in MariaDB

MariaDB Grant All Privileges On All Databases

Let us see, how to grant all privileges to a user with 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 all privileges to a user with access to all the databases in MariaDB.

Read How to Create Table in MariaDB

MariaDB Grant All Privileges ERROR 1064

The ERROR CODE 1064 (42000) shows up mainly when the syntax isn’t right i.e. error in misplacing or missing a backtick symbol or during the creation of a database without the use of backstick symbols can also create an error if you will use a hyphen (“-“) in the name, for example, USA-cityzen will display ERROR CODE 1064 (42000).

It can only be resolved by several ways.

• Correct Mistyped Commands
• Replace Obsolete Commands (Removed features or commands)
• Add Missing Data (Example – If it can’t find the database, then you need to specify the database)

MariaDB Grant All Privileges To User Example

Here, we will demonstrate an example of how to grant all privileges to a user.

For this, we will create a new user, verify whether it has been created successfully, and then we will grant it all the privileges on a database.

Step 1 – To create A MariaDB user, you need to type the following command.

CREATE USER 'Tony'@localhost IDENTIFIED BY 'tony123';

Step 2 – Once you create Tony, check its status by typing the below command.

SELECT User FROM mysql.user;

The output will be the list of all users.

Then, you should grant this user all the privileges as the freshly created users don’t have permission to the MariaDB shell or the ability to manage databases.

Step 3 – To grant all privileges to the user on a database, type the following command.

GRANT ALL PRIVILEGES ON USA_cityzen.* TO 'Tony'@localhost IDENTIFIED BY 'tony123';

Here, the USA_cityzen.* statement refers to the database or table for which the user has been given privileges.

Step 4 – Don’t forget to refresh the privileges once new ones have been awarded the command.

FLUSH PRIVILEGES;

Read How to Add Column in MariaDB

MariaDB Grant All Privileges To A User On A Specific Database

Let us see, how to grant all privileges to a user on a specific MariaDB database.

The freshly created users don’t have permission to the MariaDB shell or the ability to manage databases.

Step 1 – To grant all privileges to the user on a specific database, 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;

Read How to Drop Column from MariaDB Table

MariaDB Create User And Grant All Privileges

Here, you need to create a MariaDB user and grant all privileges to a database.

Step 1 – To create A MariaDB user, you need to type the following command.

CREATE USER 'Natalie'@localhost IDENTIFIED BY 'nat123';

Step 2 – Once you create Natalie, check its status by typing the below command.

SELECT User FROM mysql.user;

The output will be the list of all users.

Then, you should grant this user with all the privileges as the freshly created users don’t have permission to the MariaDB shell or the ability to manage databases.

Step 3 – To grant all privileges to the user on a specific database, type the following command.

GRANT ALL PRIVILEGES ON USA_cityzen.* TO 'Natasha'@localhost IDENTIFIED BY 'nat123';

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 4 – Don’t forget to refresh the privileges once new ones have been awarded the command.

FLUSH PRIVILEGES;

This is how to create a user and grant all privilege on a database in MariaDB.

Read How to Create Trigger in MariaDB

MariaDB Grant All Privileges To Existing User

Here, you need to grant all privileges to an existing user that has already been created in MariaDB.

Step 1 – For this, you need to see the list of all user using the following command.

SELECT User FROM mysql.user;

Step 2 – Now, you need to choose one of the user from the list of all users. Suppose, you took James for this purpose and grant all privileges to that user i.e. James and granting the user to access to USA_cityzen using the following command.

GRANT ALL PRIVILEGES ON USA_cityzen.* TO 'James';

Now, you can see that it has been granted the privileges for USA_cityzen.

Step 3 – Don’t forget to refresh the privileges once new ones have been awarded the command.

FLUSH PRIVILEGES;

This is how to grant all privileges to an already existing user on a database in MariaDB.

You may like the following MariaDB tutorials:

• How to Create Function in MariaDB
• MariaDB ERROR 1064
• MariaDB Timestamp
• How to import CSV files in MariaDB
• How to Create View in MariaDB
• How to Remove User in MariaDB
• MariaDB LIMIT

In this MariaDB Tutorial, we have learned about MariaDB Grant All Privileges.