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

Part 1 of 3. This part covers the actual SQL commands 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 2 will cover using phpMyAdmin. 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 SQL:

First, you have to have GRANT privileges yourself to do this. If so, use the CREATE USER statement, like so:

CREATE USER 'username'@'hostname';

The above creates a user without a password. (Not a good idea.)

CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';

This creates the user and the password. The main issue with this syntax is that the password is right there in plain text. There is a MySQL function which creates an encoded password. That is the PASSWORD() function. Like so:

SELECT PASSWORD('badpwd');

returns ‘*AAB3E285149C0135D51A520E1940DD3263DC008C’. You can then use that encoded string in the CREATE USER statement:

CREATE USER 'username'@'hostname' IDENTIFIED BY 
PASSWORD '*AAB3E285149C0135D51A520E1940DD3263DC008C';

Make sure to include that PASSWORD after IDENTIFIED BY.  You can also use the ‘%’ wildcard for the hostname so that the given user can log on from any host:

CREATE USER 'username'@'%' IDENTIFIED BY 
PASSWORD '*AAB3E285149C0135D51A520E1940DD3263DC008C';

Specifying privileges is done using a GRANT statement:

GRANT SELECT ON database_name.table_name TO 'username'@'%';

This allows the user access only to the specified database and table and only for use of SELECT statements.

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

allows usage on all tables in the named database.

So, two statements will do it:

CREATE USER 'username'@'%' IDENTIFIED BY 
PASSWORD '*AAB3E285149C0135D51A520E1940DD3263DC008C';
GRANT SELECT ON database_name.* TO 'username'@'%';