This tutorial explains how you can grant privileges on a database in MySQL. It will introduce you all the steps beginning from connecting to MySQL and then accessing the MySQL commands to set the desired level of privileges.
So, if you wish to grant or update the privileges in MySQL, first you should connect to the running MySQL instance. You may log in with a root user or as someone with the super user-level access.
While you install MySQL, it prompts you to set the root user password. And you need to keep it secure with you as it will come handy with many such tasks. If you’ve not done it yourself, then you can read our below post on installing MySQL.
How to Grant Privileges in MySQL
Let’s now look at the steps to grant rights on databases in detail.
Use MySQL CLI to connect to database
It is the very first step to launch the MySQL CLI client (MySQL CLI). For this tutorial, we’ll be using the root account to connect to the database.
So, the first command, we issue is the MySQL:
$ mysql mysql>
It will bring you the MySQL query console where you run any of the MySQL statement. However, if the MySQL command fails, then try providing the user directly, as shown below:
$ mysql --user=My_user
Here, you can specify the name of the user along with –user flag.
Grant privileges on tables
Since we’ve already opened the MySQL CLI, so our next step is to issue the GRANT command. And, we also need to understand which options to use while assigning permissions.
Understand the database access rights
By using the GRANT command, we can apply a range of privileges. For example, one might need permission to create tables and schemas or the ability to write/update files or restarting the server instance.
There is also a necessary security measure that you protect the MySQL database by assigning it to a unique user. No other account can access it or perform any operation.
Syntax
Here is the statement to grant permission on a DATABASE for the specified USER:
-- MySQL GRANT Syntax GRANT [SELECT, INSERT, DELETE, ..., GRANT] ON DATABASE_NAME TO USER_NAME;
We can choose a set of access rights from the below list to apply.
- SELECT – To view the result set from a TABLE
- INSERT – To add records to a TABLE
- DELETE – To remove rows from a TABLE
- INDEX – To create indexes on a TABLE
- CREATE – To create tables/schemas
- ALTER – To modify tables/schemas
- DROP – To delete a TABLE
- ALL – To give ALL permissions excluding GRANT
- UPDATE – Can modify a TABLE
- GRANT – Change or Add permissions
Next, we would see some examples of granting privileges in MySQL.
Examples
In examples below, we’ll use EMPL as the database name, and JOHN as the user.
1. Grant SELECT Privilege
GRANT SELECT ON EMPL TO 'JOHN'@'localhost;
2. Grant more than one Privilege
GRANT SELECT, INSERT, DELETE, UPDATE ON EMPL TO 'JOHN'@'localhost;
3. Grant All the Privilege
GRANT ALL ON EMPL TO 'JOHN'@'localhost;
4. Grant a Privilege to all Users
GRANT SELECT ON EMPL TO '*'@'localhost;
In the above example, we used an asterisk to grant SELECT privilege to all the existing users.
Grant privileges on functions/procs
We may have to define functions and stored procedures in MySQL. So, they also need permissions to work. And we can apply the Grant statement on these as well.
However, it is the EXECUTE privilege that we need to specify in the GRANT command for a function or procedure.
Syntax
-- Grant for a function or proc GRANT EXECUTE ON [ PROCEDURE | FUNCTION ] DATABASE_NAME TO USER_NAME;
Now, let’s have some of granting EXECUTE Privileges examples:
Examples
In the cases below, we’ll use GetSalary as the FUNCTION and SetJoiningDate as the PROCEDURE name, and JOHN as the user.
1. Grant EXECUTE rights on a FUNCTION in MySQL
GRANT EXECUTE ON FUNCTION GetSalary TO 'JOHN'@localhost';
2. Grant EXECUTE rights to all Users on a FUNCTION in MySQL
GRANT EXECUTE ON FUNCTION GetSalary TO '*'@localhost';
3. Grant EXECUTE rights to Users on a PROCEDURE in MySQL
GRANT EXECUTE ON PROCEDURE SetJoiningDate TO 'JOHN'@localhost';
4. Grant EXECUTE rights to all Users on a PROCEDURE in MySQL
GRANT EXECUTE ON PROCEDURE SetJoiningDate TO '*'@localhost';
Check privileges
We can also test the PERMISSIONS that we’ve given to a particular user. For this purpose, we can use the SHOW GRANTS statement.
-- Check Privileges Syntax SHOW GRANTS FOR USER_NAME;
Now, to see the privileges assigned to a user named “JOHN” and the localhost, use the following command:
SHOW GRANTS FOR 'JOHN'@localhost';
It will get you the following result:
GRANTS FOR JOHN@localhost GRANT USAGE ON *.* TO SUPER@localhost
Summary – Grant privileges
We hope that after wrapping up this tutorial, you should feel comfortable in using the MySQL Grant privileges commands. However, you may practice more with examples to gain confidence.
Also, to learn SQL from scratch to depth, do read our step by step MySQL tutorial.
