Table of Contents
Introduction
To grant all privileges to a MySQL user use the GRANT command.
In this post we will see through a simple case how to grant all privileges or only a part to a user.
Let’s not waste any more time and let’s get to the point!
How to grant privileges to a MySQL user
To grant all permissions to a MySQL user you need to use the GRANT command.
In this section we will see how to set up your local environment and how to best use the GRANT command.
Prerequisites
Before seeing the syntax, however, you need to have MySQL installed on your local environment.
If you haven’t already done so, you can download it from here and follow the instructions.
Alternatively, if you’re using Ubuntu or other Linux distros, you can follow this tutorial on how to install MySQL.
Once this is done, you will need to have at least one user to play with, don’t worry, we have a tutorial for this too in case you don’t have one!
Everything is ready? then continue reading!
MySQL GRANT command
Before granting some or all permissions to a MySQL user it is necessary to open a terminal and log into the MySQL console.
We will need to connect with a user who has full permissions, usually the root user.
To do this you can use this command:
sudo mysqlThe GRANT command allows us to grant permissions to a user.
The syntax of the command is as follows:
GRANT <permission> ON <database>.<table> TO '<username>'@'<localhost>';As you can see from the code above it is quite simple, just remember to substitute the placeholders.
There are several permissions that can be given, here is the complete list:
- All privileges: User has full access
- Insert: User can insert rows into tables
- Delete: User can remove rows from tables
- Update: User can update tables and databases
- Create: User can create new tables and databases
- Drop: User can drop entire tables and databases
- Select: User can read information in databases
- Refresh: User can refresh the rows of the table
- Grant Option: User can change the privileges of other user accounts
In the next sections we will see a couple of examples to better understand how to use the GRANT command.
How to show grants for a specific user
Before we begin, I want to show you a command that I think can come in handy when you start editing user permissions.
In fact, after several changes it can be useful to check which permissions are present and which are not.
To do this you can be the SHOW GRANTS command.
The command is used like this:
SHOW GRANTS FOR <username>;Grant specific privileges MySQL
Let’s start step by step and first of all let’s see how it is possible to grant a single privilege to a MySQL user for a specific table.
In the previous section we saw what the syntax of the GRANT command looks like, so now let’s turn it into a real example.
GRANT UPDATE ON my_database.my_table TO 'user1'@'localhost';Simply replace the placeholders to give to your user the ability to update.
If we want to give permission to update all tables and databases to user1 in the local environment you will have to run a command similar to this:
GRANT UPDATE ON *.* TO 'user1'@'localhost';Grant all privileges MySQL on specific tables
It is good practice not to grant all permissions to a user unless there is really a need.
For this reason I think that in most cases this chapter is the one that interests the most people.
In fact, let’s see now how it is possible to grant all permissions to a MySQL user for a given table.
The GRANT command is similar to the previous one but in this case we will use ALL PRIVILEGES to grant all privileges.
Check this example:
GRANT ALL PRIVILEGES ON <database>.<table> TO '<username>'@'<localhost>';Replace placeholders with your database and table without forgetting the user.
Once this is done, run the command to grant all privileges to your user on a table.
Grant all privileges MySQL across all databases
Well, in the previous chapters we saw what privileges it is possible to give to a user and how.
But if we wanted to give all privileges for all databases and tables to a user in one shot, how do we do it?
For this specific case we will always use the GRANT command, but instead of specifying the database and the table we will use asterisk symbol (*) in order to say “all”.
The command will then look like this:
GRANT ALL PRIVILEGES ON *.* TO '<username>'@'<localhost>';I remind you that you can always check which privileges you have given to a user with this command:
SHOW GRANTS FOR <username>;Apply modifications
Once you have finished modifying the permissions for the users you will have to launch one last command to apply the changes made.
This command is FLUSH and allows you to immediately make the changes made available.
Here is an example of how to run the command:
FLUSH PRIVILEGES;Conclusion
I hope this post helped you understand how to manage user privileges and in particular how to give all privileges to a user using MySQL.
As always feel free to contact me or leave me a comment below.
I invite you not to stop at this article and to continue learning many new things!