Assigning MySQL privileges via the command line

The final task once you have created your MySQL database and created your MySQL user account is to assign privileges. With MySQL 5 this can be done on a global, database, table, column and routine level. Typically though once you have your root account created then other accounts are assigned privileges at the database level, and that is what will be covered in this guide.

• Start the MySQL command line client by selecting the MySQL Command Line Client item from your MySQL program group in your start menu.
• The MySQL command line client will prompt you for the root password. Enter it and push enter.
• To assign privileges to a user simply type in;

GRANT ALL PRIVILEGES ON `testdb\_%` . * TO 'test'@'localhost';

• This will grant all privileges a user called test on localhost for all databases beginning withtestdb_. So if you created a database called testdb_mydb this user will have full privileges to it even if created with the root account, but other standard user accounts with no global permissions will be able to access this database. This is very handy for multi user environments as you can set each user to have their own prefix so that they can create and administer their own databases without them being accessible by other users.
• Finally, lets verify that the privileges have been assigned properly. Type in;

SHOW GRANTS FOR 'test'@'localhost';

• This will list all the privileges that the test account has on the server, and you should see something similar to what is displayed below in figure 1.

 Figure 1That's it. You should be able to now create a database, user and assign permissions from the command line interface for MySQL.

Source:

http://www.iis-aid.com/articles/how_to_guides/assigning_mysql_privileges_via_the_command_line