How to grant permissions in MySQL

For a MySQL user to be usable, it needs to have permission to alter a database. By default, new users have no permissions at all.

Here is a list of the different privileges that are available in MySQL:

• ALL PRIVILEGES – Full access to a database or table
• CREATE – create new tables or databases
• DROP – delete tables or databases
• DELETE – delete rows in tables
• INSERT – insert rows into tables
• SELECT – allows the user to use the SELECT command to read data from databases
• UPDATE – alter table rows
• GRANT OPTION – grant or remove other users’ privileges in the selected database

To give a user a specific privilege, construct the command like this:

mysql> GRANT permission ON database.table TO 'username'@'%';

Replace ‘permission’ with one of the types of priveleges above. If you want the user to have that permission on the whole database, replace the table name with a wildstar (*), and if you want the user to have it across all databases, also replace the database name with a wildstar.

Each time you update a user’s privileges, use the Flush privileges command:

mysql> FLUSH PRIVILEGES;

To revoke a privilege from a user, the command is almost the same:

mysql> REVOKE permission ON database.table FROM 'username'@'%';

Change GRANT to REVOKE and TO to FROM.

To show a user’s current permissions, run the following command:

mysql> SHOW GRANTS username;