MySQL : How to grant all privileges to the user on database ?

This tutorial guides you on how to grant all privileges to the user on database. Let’s see how a database administrator will do this step by step with an example.

MySQL – Grant all privileges to the user on database

The database administrators can use GRANT statement of MySQL to give privileges and define roles to the user accounts. Note, you cannot mix both privileges and roles with GRANT statement.

Therefore, a GRANT statement can either use privileges or roles. In this tutorial let’s limit our scope to GRANT privileges. Let’s see example queries below.

First, database admin will create an user as shown below.

mysql > CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password1';

Then, to grant all privileges to user ‘user1’ on database ‘db1’ (.*), run the following MySQL query.

mysql > GRANT ALL PRIVILEGES ON db1.* TO 'user1'@'%' WITH GRANT OPTION;

That’s all. This is how you can create super user privileges for the specified user.

Note, the above command can be used to give privileges on all the tables present in the database ‘db1’.

To give privileges on all databases (*.*) then you need to execute the following statement or query.

mysql > GRANT ALL PRIVILEGES ON *.* TO 'user1'@'localhost' IDENTIFIED BY 'password1';

And to allow connections from anywhere just not from localhost, you need to use ‘%’ in the query instead of localhost as shown below.

mysql > GRANT ALL PRIVILEGES ON *.* TO 'user1'@'%' IDENTIFIED BY 'password1';

GRANT ALL ON Vs GRANT ALL PRIVILEGES ON – MySQL

Both commands are equivalent. Therefore you can also use the following query to grant all privileges to the user on database.

mysql > GRANT ALL ON 'db1'.* TO 'user1'@'localhost' IDENTIFIED BY 'password1';

Then, to give privileges on all databases.

mysql > GRANT ALL ON *.* TO 'user1'@'localhost' IDENTIFIED BY 'password1';

The best practice is to give permissions for users as limited as possible and not to give privileges for all databases unless he is an super user.

The following link Summary of Available Privileges provides detailed information on the privileges provided by MySQL. The table ‘Summary of Available Privileges’ provides static privilege names that are used in GRANT and REVOKE statements.

Also note, the Privilege Name ALL [ PRIVILEGES] is the Synonym for “All Privileges”.

That’s it. You had learnt how to grant all privileges to the user using GRANT ALL ON and GRANT ALL PRIVILEGES statements.

Hope it helped 🙂

MySQL – Grant all privileges to the user on database

GRANT ALL ON Vs GRANT ALL PRIVILEGES ON – MySQL

References