How to manually grant privileges in MySQL

There are plenty of reasons why you might want to grant various privileges to a user in MySQL manually rather than with a control panel or phpMyAdmin. But regardless of the reason, the process is quick and easy, once you learn how to do it. For this you will need SSH access, which you will have with your own server or with special permission from your web hosting provider.

In this example, you will grant a user the “SELECT” privilege.

1. First login to your server via SSH and enter the following:

mysql -uadmin -p`cat /etc/psa/.psa.shadow`

(you will be prompted for your password)

2. At the “mysql>” prompt, enter:

GRANT SELECT ON database.* TO username@'localhost' IDENTIFIED BY 'password';

After you have assigned privileges, enter:

FLUSH PRIVILEGES;

You will need to replace “database”, “username”, and “password” with the appropriate information. That is all it takes to enter a basic privilege. For more extensive examples, see mediatemple.net

Photo Source: Flickr