I recently launched my first word press web site and had a friend tell me that a important security precaution was to change the table prefix of your word press database. The default prefix for the tables is “wp_” which can make it extremely easy for a attacker to run sql injection commands through flaws the they may find in word press. If we can change the name of a table a attacker will have to figure out that information as well thereby adding one more layer of security to our site. I was getting ready to change all the wp_ prefix’s manually when I discovered a plug in called WP Security Scan which claimed to automate the process.
The home page for the plugin is here. just download it and add it to your plug in directory like any other word press plug in. Once its installed and activated you can scroll down to the section that says security. You can actually run the scan and it will check various things in you wordpress install that can be changed.
Some of the security risks in wordpress are:
- WordPress version
- Your table prefix
- Display of WordPress version
- WordPress DB Errors showing
- WP ID META tag in core
- User admin is active
- No .htaccess exists in wp-admin
- File permissions
In the beginning you can run the security scan to see what things you can change to make your wordpress install more secure. I found the directions and documentation quite good so there is no need to rehash them, however, I did run into one issue after doing the database wp_ prefix change. I was unable to access my database after running the plug in due to a small bug in the plug in that missed a few changes. As you can imagine I was kicking my self for not doing it manually and trusting an automated plug in. Well if this has happened to you don’t worry its a pretty simple fix.
Ok so lets get started.
Open up the tab that says Database on the security plugin:
You are presented with a screen titled wp database security. The first thing it says it make a backup. I highly suggest always making database back ups before messing with them.
Here is the proper command assuming your user is wp_admin and your database is called wordpress:
- mysqldump -h localhost -u wp_admin -p wordpress > wp_backup_db.sql
The next thing is to make sure your wordpress database user has “alter” privileges on the database:
Here is the proper command assuming your user is wp_admin and your database is called wordpress.
- GRANT ALTER ON wordpress.* TO 'wp-admin'@'localhost' IDENTIFIED BY 'some_password';
After you have done that there is a section where you fill in the new prefix you want to use:
I recommend keeping the underscore and naming the prefix something that is short and unique to you. For example you could use “mysite_” or something along those lines.
Here is a example screenshot of the database screen:
Now you can hit the button to start the renaming process. At this point you may become locked out of your admin web page. For me the command completed and assured me it was successful however as soon as I navigated to a new screen I was presented with a 404 which complained I was unable to proceed.
Fixing the problem:
Seems there are a few things that the plug in does not change properly so I wrote a couple of quick SQL statements to solve the issue. Log into your mysql database and execute the following commands.
Just replace ‘mysite_’ with your new prefix. The statement goes to ‘meta_key’ and does a string replace from ‘wp_’ to ‘mysite_’.
- UPDATE `mysite_usermeta` SET `meta_key` = REPLACE( `meta_key` , 'wp_', 'mysite_' );
In the options table, there is ‘wp_user_roles’, make sure you get that changed into ‘prefix_user_roles’.
- UPDATE `mysite_options` SET `option_name` = 'mysite_user_roles' WHERE `option_name` ='wp_user_roles' AND `blog_id` =0;
After executing these commands I was able to log back into my admin page. I re-ran the WP Security Scan and was presented with a page detailing my success with renaming the database and the other security measures the plug in suggested.