Mysql Cheat Sheet for Grant Permissions

Submitted by sklav on Fri, 02/04/2011 - 11:11

An example of how to add SELECT privileges to a user using GRANT

GRANT SELECT ON projectdb.* TO user@'localhost' IDENTIFIED BY 'passwd';

If SELECT is not enough for our user we can add more privileges using a query similar to the one below:

An example of how to add a selection of privileges to a user using GRANT

GRANT SELECT, INSERT, DELETE ON projectdb.* TO user@'localhost' IDENTIFIED BY 'passwd';

If you want to GRANT ALL the privileges to a user then use this query:

An example of how to Grant Privileges in MySQL

GRANT ALL PRIVILEGES ON projectdb.* TO user @'%' IDENTIFIED BY 'passwd';

As you see in the latest example we use ‘%’ instead of localhost, which means that our user can use all the privileges from every host.

Sometimes you need to grant privileges to a user for a specific table. To specify the table, replace ‘*’ in ‘db_base.*’ with your table’s name.

An example of how to Grant Privileges in MySQL

GRANT ALL PRIVILEGES ON projectdb.phonebook TO user @'%' IDENTIFIED BY 'passwd';

Once you have given the desired privileges for your user, you will need to FLUSH privileges in order to complete the setup and to make the new settings work. To do so, run this command within the SQL command prompt:

An example of how to FLUSH privileges

FLUSH PRIVILEGES;