Creating a user with SELECT privileges only on a specific database, Part 2

Part 2 of 3. This part covers using phpMyAdmin for creating a user with SELECT privileges only for use while displaying data on a webpage. See Protecting Your Data from an SQL Injection Attack for more. Part 1 covered the actual SQL commands. Part 3 will cover using Dreamhost‘s web panel. I picked Dreamhost because they host conlang.org and most of the subdomains of conlang.org. Also, they disallow using the methods in part 1 and part 2.

Using phpMyAdmin:

From the homepage, go to the Privileges tab.

phpMyAdmin homepage

There you will find an “add a new user” link.

add a new user link screenshot

Click that for the Add a New User form.

add a new user form screenshot

Fill this out. Make sure None is the option under Database for user, and do not check anything under Global privileges. Click GO.

success screenshot

This display the green success checkmark and gives the SQL for what has been done:

CREATE USER 'username'@'%' IDENTIFIED BY '***';
GRANT USAGE ON *.* TO  'username'@'%' IDENTIFIED BY '***' ...;

GRANT USAGE does not grant anything at all.

Underneath is the Edit User form (also reachable through the list of users).

Edit user form screenshot

Note that the first part is labelled Global privileges. We don’t want those, we want privileges for a specific database (‘test’). That is under the Global Privileges part. We select the database from the select box and get to the database’s privileges page:

database specific edit form screenshot

Here we check the box marked SELECT and click GO.

final success screenshot

And Success!. The SQL here reads:

GRANT SELECT ON test.* TO 'username'@'%';