How to grant privileges in MySQL
When we create a new mysql table, usually we should assign a special user for the table for security instead of using the root. Here are some example of creating user and grant permissions in mysql. Of course these examples should be excuted in the the mysql command prompt. For all the users, setting a strong password in mysql is necessary.
1. Create and grant privileges for a user who named Tommy on table tbx
GRANT ALL ON tbx.* TO 'Tommy' IDENTIFIED BY 'mypass';
This statement will create the user account and grant privileges for him automatically(If NO_AUTO_CREATE_USER is closed in SQL mode. usually it is.). This statement is equal to the next two command.
a. Create a user who named tommy, password is mypass.
CREATE USER 'Tommy'@'localhost' IDENTIFIED BY 'mypass';
b. Grant all privileges for tommy on dbx
GRANT ALL ON dbx.* TO 'Tommy'@'localhost';
2.Limit Tommy usage(max connection = 100)
GRANT USAGE ON *.* TO 'Tommy'@'localhost' WITH MAX_USER_CONNECTIONS 100;
More GRANT_OPTIONS: http://dev.mysql.com/doc/refman/5.7/en/grant.html
3. Flush and reload privileges, and quit
mysql>FLUSH PRIVILEGES; mysql>QUIT;