Recommened SQL Priviledges for MySQL users for CMS'es

Question

I have been experimenting with a lot of web development apps like Drupal, Moodle, Efront and Elgg lately. For better security, I run test servers on a XAMPP setup on a virtual XP machine inside Mac OS X 10.4, using Parallels. I think this should be pretty secure, right?

When installing software like Elgg, it asks me to create a user in the MySQL server for it to access the database. I want to know whether my method of creating a new user is correct, and what permissions I should grant this user.

Currently, I create new users by going into PHPMyAdmin, the MySQL database, user table, and inserting a new row. Is this correct? For some reason, its not always working.

PS - Should I bother with all of this, or just let each software use my root account?

Answer 1

if you insert rows directly in to the user table, then you have to click on the FLUSH PRIVILEGES link on the privileges page to force mysql to look at the users table again.

the better way is to use the privileges page on phpmyadmin to set up new users. to get there, click on the house logo at the top of the left column. then on the right side of the screen, click the link for privileges.

and you should definitely NOT let your software just use the root account. you should be creating a user for each application with the MOST RESTRICTIVE permissions possible.

Answer 2

The MySQL user privileges that you use is very important and can prevent you from getting hacked. You should follow a system of least privilege access.

To add a user in PHPMyAdmin: 1) login as root 2)go to the Privileges tab 3)click on "Add a new user."

PHPMyAdmin groups the MySQL privileges as Data, Structure, and Administration.

The "Administration" privileges should be disabled for a web application, only root should have this access. None of the "Administration" privileges can be used in a SQL Injection exploit, unless the application allows for query stacking. (Most do not allow this!)

During the installation of a web application the "Structure" privileges are required to build the database, however these are almost never needed during the web application's operation, so they can be turned off.

The "Data" section is tricky. The "FILE" privileges is the most dangerous privilege you could give a web application. The reason why is because it allows a hacker to use a SQL Injection vulnerability to read and write files on your server. "FILE" access is the only privilege which could give an attacker Remote Code Execution (RCE), and must ALWAYS be disabled.

Last but not least, Apache Friends XAMPP is terrible software. Known vulnerabilities exist and have gone unpatched for nearly a year. I recommenced using Ubuntu's LAMP install, it is both easy to use and secure.