Tutorial – MySQL administration using phpMyAdmin

We have walked you through XAMPP before and we have shown you how to easily bring up an Apache web server bundled with PHP and MySQL with a simple Joomla example. Now we will go a little deeper into administering the MySQL database.

I used the term “administering” because we will do some “admin” stuff like creating a new user and granting priveleges like allowing a user to create tables or insert and delete data. The reason is simple why this is needed, you don’t want any of your database users to freely modify the database.

The steps below assume that you have XAMPP installed in your PC. If you have a remote host, just ignore the steps including XAMPP.

First make sure that the Apache and MySQL services are running using the XAMPP control panel:

Browse the local phpMyAdmin webpage: http://localhost/phpmyadmin

TIP: Most PHP and MySQL hosting services have phpMyAdmin installed by default. Usually you can access it via the console main page under the database group of tools.

Click the Privileges tab to access the list of users:

Click the edit button next to a user that you want to edit. In the example below, I clicked the edit button next to user joomla to edit joomla’s priveleges:

You should see all of the user’s allowed priveleges that has a check next to it.

Here is a realistic admin scenario: as an admin, I wouldn’t want the user joomla to have “administration” priveleges since only the root or admin should have this capability. Uncheck all boxes on “administration” to remove these priveleges and then click the “Go” button to enable the changes.

The changes are posted realtime so you don’t have to restart the MySQL process to have the changes take in effect. You should see the updated priveleges when you click the “priveleges” tab again:

Now instead of “all priveleges” assigned to user “joomla”, it only has a more restricted set of privileges.

That’s it for basic privilege administration. Now if you want to completely remove a user from the list of MySQL users, check the box next to it then click the “Go” button under the “Remove selected users” group:

If you use a lot of MySQL accounts, it is a good habit to clean those unused user accounts. For security reasons it is best to audit their privileges too to avoid unwanted database modifications that could rise from user misuse.