You do not have sufficient permissions to access this page

Wordpress logo

I have extremely conflicting feeling about WordPress. On the one hand it’s clearly the least sucky option when it comes to open source blogging software. On the other hand; when something goes wrong and you have to lift the bonnet and peer inside, what you find is just scary. Twice this week I’ve had the dubious pleasure of diagnosing WordPress problems.

The fun began when I moved this blog from my personal VPS to our (SYSE) new web server platform. After copying the files, dumping and restoring the database I was rewarded with the dreaded and incredibly misleading “Error establishing a database connection” message. What this really means is “Something, at least peripherally related to your database, isn’t exactly as it should be, but we refuse to tell you what it is”. I have created a patch for the latest version of WordPress, and submitted a ticket:

https://core.trac.wordpress.org/ticket/34186

The real problem in this case was an incomplete MySQL dump, but the incredibly inept error message caused me to chase wild geese for the better part of an hour. Fun times!

Next up was changing the table prefix. I have several WordPress databases in the same MySQL database, and to keep them separated I use a table name prefix. Since the dump I imported came from a dedicated database it had the default WordPress table name prefix of wp_. To change this I renamed all tables to use the prefix bbj_wp_ with the following command:

RENAME TABLE wp_commentmeta        TO bbj_wp_commentmeta,
             wp_comments           TO bbj_wp_comments,
             wp_links              TO bbj_wp_links,
             wp_nxs_log            TO bbj_wp_nxs_log,
             wp_options            TO bbj_wp_options,
             wp_postmeta           TO bbj_wp_postmeta,
             wp_posts              TO bbj_wp_posts,
             wp_term_relationships TO bbj_wp_term_relationships,
             wp_term_taxonomy      TO bbj_wp_term_taxonomy,
             wp_terms              TO bbj_wp_terms,
             wp_usermeta           TO bbj_wp_usermeta,
             wp_users              TO bbj_wp_users;

Now being used to relational databases I thought this would be enough, and visiting the blog seemed to work as expected. That was until I tried to log in to add another post. I was promptly rewarded with the ridiculously vague error “You do not have sufficient permissions to access this page”. So, back to chasing wild wild geese. After quite a bit of digging and another fair bit of searching I found some hints that WordPress actually have columns holding the names of other tables in them. Whoever came up with that idea should have their RDBMS privileges revoked for life and be forced to forever work with key-value store databases. So; in addition to renaming the tables as above, I had to execute the following before being able to log back into WordPress:

UPDATE bbj_wp_options
SET option_name = 'bbj_wp_user_roles'
WHERE option_name = 'wp_user_roles';
UPDATE bbj_wp_usermeta
SET meta_key = 'bbj_wp_user_level'
WHERE meta_key = 'wp_user_level';
UPDATE bbj_wp_usermeta
SET meta_key = 'bbj_wp_capabilities'
WHERE meta_key = 'wp_capabilities';

There are probably more, but at least now I’m once again able to log in to WordPress. I’ve submitted the following ticket (and patch) to WordPress:

https://core.trac.wordpress.org/ticket/34189

Makes me think of my old friend Ted Codd:

Edgar F. Codd

“The key, the whole key, and nothing but the key, so help me Codd.”