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.
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.