While this isn’t strictly part of the migration process from S9y to WordPress, I decided to take some advice I read on the net and change the prefix for WordPress tables. Most use this feature for when they have to share a database, but the suggestions I read said to do it to help prevent from SQL injection hacks. Basically the majority of these injections presume a default of ‘wp_’ prefix.
I made this after install very simple by going in MySQL and running
RENAME TABLE `wp_posts` TO `prefix_posts` against each table (where “prefix_” was the new prefix). After changing it in MySQL, I changed the wp-config.php line of $table_prefix = ‘wp_’; to read as my new prefix. I popped up to the web and the blog worked fine. Except… I couldn’t access the administrative interface.
You do not have sufficient permissions to access this page.
LIAR!!! What the hell? Yes I do. I did 10 minutes ago and I should now. I tried my other (administrative) users, same problem. This was quite displeasing to me. After a little googling about, I found out that the answer is that the `meta_key` prefix of ‘wp_’ (in `wp_usermeta`) and certain values in `wp_options` are also dependent of this prefix value. Fortunately it is really easy to fix:
UPDATE `<strong>PREFIX</strong>_usermeta` SET `meta_key` = REPLACE( `meta_key` , 'wp_', '<strong>PREFIX</strong>_' ); -- Change <strong>PREFIX</strong>_ to your new prefix.UPDATE `<strong>PREFIX</strong>_options` SET `option_name` = '<strong>PREFIX</strong>_user_roles' WHERE `option_name` = 'wp_user_roles' AND `blog_id` = 0; -- Change <strong>PREFIX</strong>_ to your new prefix.
Execute those two lines of SQL (after you’ve replaced it with the proper prefix), and you’ll be ready to rock and roll again. This is one of those odd little bits that they don’t explain anywhere in the manual — probably because most people don’t use anything other than the defaults… which brings us right back to the security concern (i.e. easy to guess/default password = bad).
Having keys in tables change because the table prefix is different is one of the stranger things done in a web application that I’ve worked with. I can’t really think of a reason to do this, but I’m sure the developers had a good reason (as strange as it might be). Fortunately this problem is easy enough to fix, but it certainly is frustrating when your admin user gets “You do not have sufficient permissions to access this page.” and you know better.