Grant Different User Permissions On a MYSQL database.

Here is a short list of other common possible permissions that users can enjoy.

• ALL PRIVILEGES- as we saw previously, this would allow a MySQL user all access to a designated database (or if no database is selected, across the system)
• CREATE- allows them to create new tables or databases
• DROP- allows them to them to delete tables or databases
• DELETE- allows them to delete rows from tables
• INSERT- allows them to insert rows into tables
• SELECT- allows them to use the Select command to read through databases
• UPDATE- allow them to update table rows
• GRANT OPTION- allows them to grant or remove other users' privileges

To provide a specific user with a permission, you can use this framework:

 GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’;

e.g. GRANT ALL PRIVILEGES ON test_db .* TO 'test-db-user'@'localhost';

If you want to give them access to any database or to any table, make sure to put an asterisk (*) in the place of the database name or table name.

Each time you update or change a permission be sure to use the Flush Privileges command.

FLUSH PRIVILEGES;

If you need to revoke a permission, the structure is almost identical to granting it:

 REVOKE [type of permission] ON [database name].[table name] FROM ‘[username]’@‘localhost’;

Just as you can delete databases with DROP, you can use DROP to delete a user altogether:

 DROP USER ‘demo’@‘localhost’;

To test out your new user, log out by typing

 quit 

and log back in with this command in terminal:

mysql -u [username]-p