Changing the WordPress table prefix
This may be of use to people experiencing the dreaded “You do not have sufficient permissions to access this page.” message when trying to reach WordPress’ admin page, even when logging in as a proper administrator. WordPress embeds the table prefix in 4 different locations:
- The wp-config.php file
- The name of the tables
- The name of user metadata keys
- The name of blog options
Thus if you want to change the prefix, you have to:
- Edit wp-config.php to change the prefix
- Rename your tables
- Rename your user metadata
- Rename your site options
Missing steps 1 or 2 will cause WordPress to not find the tables, and it will go through the initial install process again.
Missing step 3 will cause the account to lose its roles, and thus not be authorized to do much besides read public posts.
Missing step 4 is more insidious, as it destroys the option wp_user_roles, the link between roles and capabilities, and thus even if your account is an administrator, it is no longer authorized for anything.
It feels quite clunky to embed the database prefix in column values, not just tables, just like WordPress’ insistence on converting relative links to absolute links. The former makes moving tables around (e.g. when consolidating multiple blogs on a single MySQL database) harder than necessary. The latter makes moving a blog around in a site’s URL hierarchy break internal links. I suppose there are security reasons underlying Automattic’s design choice, but security by obscurity of the WordPress table prefix is hardly a foolproof measure.
If you are renaming the tables, say, from the default prefix wp_ to foo_, the MySQL commands necessary for steps 2–4 would be the following:
UPDATE wp_usermeta SET meta_key=REPLACE(meta_key, 'wp_', 'foo_')
WHERE meta_key LIKE 'wp_%';
UPDATE wp_options SET option_name=REPLACE(option_name, 'wp_', 'foo_')
WHERE option_name LIKE 'wp_%';
ALTER TABLE wp_commentmeta RENAME TO foo_commentmeta;
ALTER TABLE wp_comments RENAME TO foo_comments;
ALTER TABLE wp_links RENAME TO foo_links;
ALTER TABLE wp_options RENAME TO foo_options;
ALTER TABLE wp_postmeta RENAME TO foo_postmeta;
ALTER TABLE wp_posts RENAME TO foo_posts;
ALTER TABLE wp_redirection_groups RENAME TO foo_redirection_groups;
ALTER TABLE wp_redirection_items RENAME TO foo_redirection_items;
ALTER TABLE wp_redirection_logs RENAME TO foo_redirection_logs;
ALTER TABLE wp_redirection_modules RENAME TO foo_redirection_modules;
ALTER TABLE wp_term_relationships RENAME TO foo_term_relationships;
ALTER TABLE wp_term_taxonomy RENAME TO foo_term_taxonomy;
ALTER TABLE wp_terms RENAME TO foo_terms;
ALTER TABLE wp_usermeta RENAME TO foo_usermeta;
ALTER TABLE wp_users RENAME TO foo_users;