Under normally functioning circumstances, you may never need to manually grant privileges in MySQL. When creating databases in cPanel, phpMyAdmin, or any other web-based interface, you can easily accomplish the same task without accessing the command line. In rare instances, however, those frontends may not work or may not be easily accessible to you. Furthermore, knowing how to access MySQL from SSH is important when you need to manage your server remotely and quickly make changes.
To grant the “SELECT” privilege, for example, you would first log on to your server via SSH and then enter the following command:
# mysql -u [username] -p
Replace “[username]” with the administrative mysql user on your server. It will then prompt you for a password. Enter it and press enter.
Next, at the “mysql>” prompt, type:
GRANT SELECT ON [database].* TO [username]@’localhost’ IDENTIFIED BY ‘[password]‘;
This will assign the specific privilege of “SELECT” to the username and password you choose only within the database you have entered. That way, the user will not be given global select permissions.
To complete the task, type:
To grant all privileges, you would type:
GRANT ALL PRIVILEGES ON [database].* TO [username]@’localhost’ IDENTIFIED BY ‘[password]‘;
Again, this will only affect the database you specify. It will not grant all privileges to the user for any other database. Under normal circumstances, it is a huge security risk to grant global privileges to any user other than the administrator. For more information about “grant”, consult the MySQL documentation.