Textpattern tips, tutorials and code snippets

How to use a different MySQL user for public side site access

This idea came up in a IRC conversation I had with Stef Dawson a few days ago so I thought it was worth trying out as a proof of concept.

The basic premise is to adopt ‘deny-by-default’ database access for the public side of your site so that any, as yet undiscoved, SQL injection vulnerability in the core or (more likely) plugins, should not be able to DROP or ALTER tables, INSERT or UPDATE users behind your back or dump sensitive data (like all the user password hashes from the users table) or even IMPORT from the filesystem. Of course, this is no panacea but might perhaps be a useful exercise.

Firstly I needed a way to have the site’s public side use different credentials when connecting to a MySQL server. So I worked up a quick patch to the DB layer that will use two new credentials from config.php if they are present. Here’s the patch file for the change I made to txplib_db.php

The patch file

--- a/textpattern/lib/txplib_db.php
+++ b/textpattern/lib/txplib_db.php
@@ -27,8 +27,18 @@ class DB {

		$this->host = $txpcfg['host'];
		$this->db	= $txpcfg['db'];
-		$this->user = $txpcfg['user'];
-		$this->pass = $txpcfg['pass'];
+
+		if( @txpinterface === 'public' && @$txpcfg['public_user'] && @$txpcfg['public_pass'])
+		{
+  			$this->user = $txpcfg['public_user'];
+	  		$this->pass = $txpcfg['public_pass'];
+		}
+		else
+		{
+  			$this->user = $txpcfg['user'];
+	  		$this->pass = $txpcfg['pass'];
+		}
+
		$this->client_flags = isset($txpcfg['client_flags']) ? $txpcfg['client_flags'] : 0;

		$this->link = @mysql_connect($this->host, $this->user, $this->pass, false, $this->client_flags);

Adding lines to the config.php file

Next, I added these two lines to my config.php file…

$txpcfg['public_user'] = 'public_user';
$txpcfg['public_pass'] = 'public_user_password';

I then created a new MySQL user called ‘public_user’ via phpmyadmin. When I created the user and their password I gave them no global MySQL privs. Instead, I planned to give them read access to most of my Txp installation’s tables. Most, but not all.

I decided against giving the user read access to the `txp_log` table or read/write access to the `txp_user` table. And, except for a few fields from these two tables, was able to get pretty much error/warning/notice free public browsing on the site up in nearly no time. However, it turns out that for both of these tables you do need to allow the core read access to a few fields. In particular, the core needs access to the `name` and `Realname` fields in the user table in order to attribute articles to particular Author’s by name.

To my surprise, it turns out that default installation settings cause the core to need READ access to the txp_log table from the public interface. I was anticipating it only needing WRITE access in order to log visits, but not read access. Upon investigation, this is due to the core using the txp_log table as a kind of rDNS cache that gets used if you are using DNS to resolve the hostnames of the visitors machines from their IP address. In this case, access was needed for only the `host` and `ip` fields so these were added to the public_user’s permitted GRANTs.

Commenting, of course, needs to write to both the `txp_discuss` and the `txp_discuss_nonce` tables so that was added pretty easily. Nonces, it turns out, are also deleted on the public side so I had to add that into the mix too.

One thing that caught me out was that file download counting was messed up. Actually, this is obvious when you think about it, but I forgot that incrementing the download count would need UPDATE permissions on the `downloads` field.

Of course, this doesn’t take into account individual public-side plugin’s requirements at all. So individual investigation would be needed for them. One popular and obvious one that I will mention here though is zem_contact_reborn which will need require write access to `txp_discuss_nonce`.

Summary of GRANTS issued via phpMyAdmin

Here is the basic set of GRANTS for a public-side low-priv MySQL connection…

GRANT USAGE  ON `your_txp_db`.* TO 'public_user'@'%';
GRANT SELECT ON `your_txp_db`.`textpattern` TO 'public_user'@'%';
GRANT SELECT ON `your_txp_db`.`txp_section` TO 'public_user'@'%'; and 
GRANT SELECT ON `your_txp_db`.`txp_prefs` TO 'public_user'@'%';
GRANT SELECT ON `your_txp_db`.`txp_category` TO 'public_user'@'%';
GRANT SELECT ON `your_txp_db`.`txp_discuss` TO 'public_user'@'%';
GRANT SELECT ON `your_txp_db`.`txp_discuss_nonce` TO 'public_user'@'%';
GRANT SELECT ON `your_txp_db`.`txp_discuss_ipban` TO 'public_user'@'%';
GRANT SELECT ON `your_txp_db`.`txp_file` TO 'public_user'@'%';
GRANT SELECT ON `your_txp_db`.`txp_form` TO 'public_user'@'%';
GRANT SELECT ON `your_txp_db`.`txp_css` TO 'public_user'@'%';
GRANT SELECT ON `your_txp_db`.`txp_image` TO 'public_user'@'%';
GRANT SELECT ON `your_txp_db`.`txp_lang` TO 'public_user'@'%';
GRANT SELECT ON `your_txp_db`.`txp_link` TO 'public_user'@'%';
GRANT SELECT ON `your_txp_db`.`txp_page` TO 'public_user'@'%';
GRANT SELECT ON `your_txp_db`.`txp_plugin` TO 'public_user'@'%';
GRANT SELECT(`RealName`, `name`) ON `your_txp_db`.`txp_users` TO 'public_user'@'%';
GRANT UPDATE(`downloads`) ON `your_txp_db`.`txp_file` TO 'public_user'@'%';

The following is also needed if you are logging hits. Don’t enable if you don’t log hits…

GRANT INSERT ON `your_txp_db`.`txp_log` TO 'public_user'@'%';

The following is also needed if logging hits using DNS resolution…

GRANT SELECT(`host`, `ip`) ON `your_txp_db`.`txp_log` TO 'public_user'@'%';

If you need commenting

The following are needed for commenting — don’t enable if your site doesn’t accept comments…

GRANT INSERT ON `your_txp_db`.`txp_discuss` TO 'public_user'@'%';
GRANT INSERT, DELETE ON `your_txp_db`.`txp_discuss_nonce` TO 'public_user'@'%';

If you use zem_contact_reborn

The following is needed for zem_contact_reborn or any plugin that uses TXP nonces…

GRANT INSERT, DELETE ON `your_txp_db`.`txp_discuss_nonce` TO 'public_user'@'%';

Well, I’ve only tried this out for a day so I’m sure there are a few areas I’ve missed; but so far this looks quite promising. Let me know if you can think of anything I’ve missed or how it works out for you.

Use Textile help to style your comments