Tutorialsplane

MySQL Grant all privileges on database


MySQL Grant all privileges on database : GRANT ALL PRIVILEGES is used to grant the privileges on database in MySQL. We sometimes need to give the full privileges to the user on database. Here in this tutorial we are going to explain how you can grant all privileges on database to the users.


MySQL Grant all privileges on database

There are many ways to Grant all privileges to the user on any database. Here are some useful ways to grant the privileges on database in MySql.

MySQL Grant all privileges WITH GRANT OPTION

If you want to grant all privileges with grant option in mysql use the following Query –

mysql grant all privileges on database to user:

GRANT ALL PRIVILEGES ON my_database.* TO 'my_user'@'%' WITH GRANT OPTION;

This will give all privileges to the user my_user with grant option.

Note : Make sure while giving the give all privileges with grant option. Because you are giving superuser privileges which may be a security risk.

Here is another way to grant all privileges to the user.

MySQL Grant all privileges without GRANT OPTION

MySQL Grant all privileges to user identified by:

GRANT ALL PRIVILEGES ON my_database.* to 'my_user@localhost' INDENTIFIED by 'my_passwd';

The above example will give the full PRIVILEGES to user identified by the password without grant option.

More About MySQL Grant Privileges

Let us learn more about the MySQL Grant Privileges. Let us give the selected Privileges to the user.

MySQL Grant SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,INDEX, ALTER privileges to user

You can Grant SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,INDEX, ALTER privileges simply using the below query on database.

MySQL Grant SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,INDEX, ALTER :

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,INDEX, ALTER 
ON my_database.* TO 'my_user@localhost' IDENTIFIED BY 'my_password';

The above example will give the SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,INDEX and ALTER PRIVILEGES to the user on the database.