Granting Database Privileges in MySQL via SSH

This week I experienced some problems installing Magento the Open Source shopping cart system onto a dedicated server hosted by Wedfusion. The installation seemed to go well until just after the database was populated by the installer script. All the tables were created as expected but Magento produced a rather unhelpful general error. After some investigation it turned out that the issue was occurring because by default a database created in the server control panel do not assign the CREATE TEMPORARY TABLES privilege to the database user. To fix the issue all I needed to do was add the privilege to the user and the installer then works flawlessly. Below are some simple instruction on granting privileges in MySQL via SSH.

Step 1: Connecting to your server via SSH.

See my previous article regarding login on to a remote server using SSH in OS X Terminal application here.

Step 2: Login to MySQL.

mysql -u root -p
Enter password:

You should now enter the database root password. This is not the database user password but the root users password that allows access to all databases installed on the machine. I had to request this password from Webfusion as it is not issued unless you ask for it. The password is not shown when entered for security reasons. Once the password is entered, you should see the following

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12345
Server version: 5.0.51a-3ubuntu5.4 (Ubuntu)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>

Step 3: Granting permissions.

We need to change to the mysql database which contains the permissions by entering the following:

mysql> use mysql

You should then see the following message:

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

Now we can assign the permissions as required. You will obviously need to replace your_db and your_user with your own values.

mysql> GRANT CREATE TEMPORARY TABLES on your_db.* to ‘your_user’@’%’;
Query OK, 0 rows affected (0.00 sec)

And then flush the privs to ensure the new permissions are made active.

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Now we can exit MySQL and return to the normal server command line.

mysql> exit
Bye

When you are done logout of the session by typing logout then quit terminal as normal.

This article was posted on 26 March 2010 in Misc, Tutorials

What you have had to say about all this...

That's the end of this article. I hope you found it useful. If you're enjoyed this article why don't you have a look around the archives, where you can find some more tutorials, tips and general ramblings.