Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
#1 2010-10-31 17:26:32
- net-carver
- Archived Plugin Author
- Registered: 2006-03-08
- Posts: 1,648
[howto] How to use a different MySQL user for public side site access.
This idea came up in a IRC conversation I had with Mr. 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 …
--- 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);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 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 follwing is also needed if logging hits using DNS resolution…
GRANT SELECT(`host`, `ip`) ON `your_txp_db`.`txp_log` TO 'public_user'@'%';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'@'%';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.
— Steve
Offline
Re: [howto] How to use a different MySQL user for public side site access.
Thank’s to Steve for this excellent tutorial, which has also now been published on TXP Tips.
Offline
Re: [howto] How to use a different MySQL user for public side site access.
Nice work Steve :-).
If someone wants to use Steve’s tip and is using some Rah-family plugins, you may need to grant some extra permissions. Let’s see what I can remember off the top of my head (simple list should be enought, I think):
- rah_autogrowing_textarea
- rah_autogrowing_textarea: SELECT
- rah_external_output
- rah_external_output: SELECT
- rah_plugin_download
- rah_plugin_download_count: SELECT, INSERT, UPDATE
- rah_plugin_download_prefs: SELECT
- If rah_plugin_dev integration is used, rah_plugin_dev table needs SELECT permissions.
- rah_plugin_dev
- If plugin dev is used at public side txp_plugin table requires SELECT, INSERT, UPDATE permissions and rah_plugin_dev table requires SELECT permission.
- rah_sitemap
- rah_sitemap: SELECT
- rah_sitemap_prefs: SELECT
- rah_textile_bar
- rah_textile_bar: SELECT
I hope that helps someone :-)
Last edited by Gocom (2010-11-01 04:23:16)
Offline
Re: [howto] How to use a different MySQL user for public side site access.
Jukka, may I ask that you also post some info about this in the comments for the TXP Tips article?
Offline
Re: [howto] How to use a different MySQL user for public side site access.
Why edit the file txplib_db.php ??
Simle write in config.php
$txpcfg['user'] = 'user_frontend';
$txpcfg['pass'] = 'pass_frontend';
if( @txpinterface === 'admin' ){ $txpcfg['user'] = 'user_admin'; $txpcfg['pass'] = 'pass_admin'; }
It’s all.
aks_cron : Cron inside Textpattern | aks_article : extended article_custom tag
aks_cache : cache for TxP | aks_dragdrop : Drag&Drop categories (article, link, image, file)
Offline