Implementing the WordPress Shared Users Table Trick

Or how to create a blog network with out WordPress Multi-User.

Almost 2 years ago, I was charged with the duty of building an integrated Blog network with WordPress, for the Parents Everywhere Network. WordPress MU wasn’t quite ready for prime time, as most plugins hadn’t been ported over, and documentation was sparse, and only the bravest of souls wanted to tangle with it. Remember, this was back before WP and MU development were in sync. So I had to come up with another way.

That’s when I ran across these little known wp-config.php keys: CUSTOM_USER_TABLE and CUSTOM_USER_META_TABLE. By setting these keys to the same values in all of your wp-config.php you can have multiple WordPress installs share the same user tables and essentially create a nearly-perfect single sign-on system for your blog network.

There are limitations, of course, but by and large the shared users table trick works like a charm. I’ll show you how to set it up yourself for some newly installed blogs. You can convert existing blogs over, but that takes more DB fiddling then I’ll go into in this article. The biggest limitation is that all of the blogs have to be installed in the same Database. However, they can be on different servers, as long as they can all talk to the same Database.

  1. First, install your primary blog as normal, and set your CUSTOM_USER* keys in your wp-config:define('CUSTOM_USER_TABLE', 'wp_users');
    define('CUSTOM_USER_META_TABLE', 'wp_usermeta');
  2. Second, create the wp-config.php for your second blog, using the same DB Connection information. You also need to set AUTH_KEY, SECURE_AUTH_KEY, LOGGED_IN_KEY, NONCE_KEY, and the CUSTOM_USER* keys to the same value as the first. You will also need to set $table_prefix to a unique value.
  3. Install the second blog and instead of getting a random password for the admin user, it should tell you that it found the user and is using that password.
  4. Rinse and Repeat steps 2 and 3 until all blogs are successfully installed.

Now that all of the blogs are sharing the same user tables, your admin login will work on all of them, and remain the same, even when changing the password and other meta data (i.e. name, etc), but all other settings will remain distinct for all of the other blogs. However, if you allow (or force) users to sign up to comment or do other things on your blog, then you will run into the second limitation of this trick – users only get a Role assigned on the blog that they signed up on. Their account is in table and accessible to the other blogs, they just don’t have any privledges. In other words, they are orphans. This can be fixed manually in the User management screens, or automatically via a plugin.

The final limitation, and this isn’t the fault of WordPress but how the internet works, is that because your blogs are (probably) on separate domains, cookies can’t be shared between them easily and hence users will have to log-in to each blog they visit. A small price to pay for one of the cooler hidden features of WordPress.

44 thoughts on “Implementing the WordPress Shared Users Table Trick

  1. Pingback: WP-Orphanage Plugin

  2. Pingback: WP-Orphanage v1.0 Released « xentek

  3. I had multiple installs in different databases and combined them all into my main db using a different table prefix for each. I have done all the necessary editing in the wp-config.php file, and am now not able to log into my other secondary blog. It is saying that I dont have sufficient permissions. I found a code change for the capabilities file, but that has not proven successful either. Do you know of a way to fix this problem? The username/passwords for all of my blogs were setup as the same, but are all different in the user database tables due to the hash system. I have tried copying the pw listed in the original installations’ user table to the secondary user table, but it’s not working either. Suggestions?

    • You will need to start over with your blogs separated. Install a new blog with a unique table prefix, and get it to ‘hook’ into the admin account you have now, as explained in this blog. Then import the content from the 2nd blog into this new instance, and you should be good to go. I’m pretty sure only new blog installs can use the Shared Users Table trick.

    • Most people will run into permissions errors when they start using a single users table. That is because WordPress doesn’t handle the creation of user roles properly (or maybe it does, but it just isn’t the experience most of us would expect).

      Regardless, everyone having issues with permissions are suffering from what is called “orphaned roles.” It’s actually a pretty easy concept, and there is a plugin that’ll fix it for you too.

      I created a video screencast explaining the whole issue, and showing how to use a single users table for multiple WordPress sites. You can find it here: http://mikemclin.net/single-users-table-multiple-wordpress-sites/

  4. Does combining the users tables for other blogs work with multi-site blogs? Anything would get in the way of how 3.0 is organized?

    • These instructions are really for single blogs only. Multisite already has the blogs it hosts integrated together at the user level. You’d be better off importing the other blogs into your multisite install via the WordPress XML Import file.

  5. I am going to try this for MU, because I would be usable if you had two different MU communities with similar needs to provide content, such as university study blogs and student blogs. Domain mapping is a pain; it would be a lot easier just to have:

    dir.unviersityblog.com /blog1 /blog2
    student.universityblog.com /blog1 /blog2

    It seems that the only thing that is needed is to differentiate prefixes, and the user table is universal, so I assume it could work. But I will test it and see if there is a possibility.

  6. Hi I just wanna confirm the do I put

    define(‘CUSTOM_USER_TABLE’, ‘wp_users’);
    define(‘CUSTOM_USER_META_TABLE’, ‘wp_usermeta’);

    in the 2nd blog config or leave it

  7. @biziclop: Thanks for all the follow up links. That really helped me out a lot.

    @Eric: Thanks for putting this tutorial together. That’s exactly what I was looking for for a new site.

  8. Pingback: WP-Orphanage Review Review by Best Plugins for Wordpress

  9. I’m trying to install a set of WP blogs with shared user IDs and passwords.

    The first install works fine.
    If I leave the wp_usermeta and wp_users tables that the first install created, then the second installation generates the following error at the end of installation:
    Catchable fatal error: Object of class WP_Error could not be converted to string in /home/ppvw/public_html/wordpress/wp_02voices/wp-includes/formatting.php on line 2773

    A page refresh gets me past the error to the log-in screen, and the second install accepts the ID and password that I setup in the first install, but logs me on as a subscriber – so I can’t access any of the admin functions in the second install. :-(

    If I delete the wp_usermeta and wp_users tables that the first install created and let the second install re-create them, then I don’t get the error, and the second installation will log on with correct admin privileges – but then the first install demotes the user to subscriber role with no privileges.
    This is true even if I activate the orphanage plug-in in the first install before I create the second install. :-(

    Clearly I’m doing something wrong. Anyone have any idea what?

    Thanks!

  10. I’m making progress – Let me see if I can document what sent me awry;

    First:
    You post the following code example:

    define(‘CUSTOM_USER_TABLE’, ‘wp_users’);
    define(‘CUSTOM_USER_META_TABLE’, ‘wp_usermeta’);

    and you remark:
    “You will also need to set $table_prefix to a unique value.”

    This caused me some confusion because I assumed you were talking about the default $table_prefix setting for the installation.

    It took me a while to realize you were also referring to prefix for the wp_users and wp_usermeta tables.

    The code that actually worked for me is:
    define(‘CUSTOM_USER_TABLE’, usrdata.’my_users’);
    define(‘CUSTOM_USER_META_TABLE’, usrdata.’my_usermeta’);

    Note that I create a new prefix different from any used by the rest of WP.

    Second:
    There seems to be a very specific sequence of steps that must be followed to make this work. I’ll try to list them in the correct order.

    1) Modify the wp-config.php file in the usual way to point to the correct database, add fresh keys and a unique value for $table_prefix. Add the custom user table commands with their own unique prefix.

    2) Upload the Orphanage plug-in to the proper directory.

    3) Run install.php to install WP. Create a dummy user during set-up. It will be deleted later and the real users added.

    4) Log on and activate the orphanage plug-in. Set the default user role in the SETTINGS >> ORPHANAGE dialog to ADMINISTRATOR. Log off the first instance of WP. You should not try to log on to this copy of WP again untill you get to the last step.

    5) Use phpMyAdmin or whatever database utility you are comfortable with to delete (drop) the two custom user tables created by the WP install.

    6) Copy the wp-config.php file from the WP install you just did to the proper directory for the next WP install.
    Change the $table_prefix value to some new unique value for this WP install. DO NOT CHANGE ANYTHING ELSE.

    7) Repeat steps 2, 3, 4, 5, and 6 till you have all but the last of your WP instances installed. DO NOT DO STEPS 5 AND 6 ON YOUR LAST INSTALL. DO NOT DELETE THE CUSTOM USER TABLES.

    8) Log in to your last WP install and create a new user with admin privileges. You can now log on to all your other WP installs with this new user. Delete the dummy users you created for install and create real users.
    Note: I find it sometimes takes a few minutes for the data to populate. You may have to try logging on a few times before it works.

    9) You can (and probably should) now set the default user role in the SETTINGS >> ORPHANAGE dialog to something less than administrator.

    10) Enjoy!

  11. Thank you for this, but how can you add additional user date to be shared? For example extra custom fields such as facebook url, birthday etc?

    • Even in WordPress Multisite, the User and User Meta table are always shared. All of that information will be available in the normal way (get_usermeta, etc), if you stored it in the usermeta table to begin with.

  12. Brilliant!
    I have a group of blogs that I wanted to connect.
    I had to 2 new installs of wordpress as per your instructions. Then I will import the blogs one by one. Adding new installs for each one.
    All working now!

    Thank you
    Tarnya

  13. Pingback: Hotchkissmade!

  14. These instructions worked brilliantly for implementing a shared user table. Unfortunately I now need to reverse the process – ie create two separate user and user meta tables from the one.

    Presumably I remove:

    define(‘CUSTOM_USER_TABLE’, ‘wp_users’);
    define(‘CUSTOM_USER_META_TABLE’, ‘wp_usermeta’);

    from wp-config.php but will I then be locked out of the second blog as it will no longer have a usertable?

    I don’t need to copy all the user data across to the second blog, but I’m concerned about being unable to login.

    Any top tips for reversing the process would be much appreciated!

    Thanks

    Stefhan

    • I’ve never done this, but I would copy the user tables over and once you’ve confirmed the two blogs are separate, just use wp-admin to remove the users you no longer want on the second site (or just set them all as subscriber).

  15. Hey, it works! Thanks! Just added a 2nd blog on a subdomain which shares users with an existing main site. Tricky, but doable.

  16. Hello,I have already a user table at my site.Can I use it for the user management of a word-press blog?It has a different schema .
    How can user login with these credentials?(of the existing table)

  17. Hi,
    Nice info, Thx.
    Did anyone find an easy way to stay logged in for users on all sites (site1.com, site2.com,…) ex: logged in on site1 goes to site2 and don´t need to login again?
    How to share cookies between those sites?

    Thank you,

    Svenson

    • Its possible, but outside of the scope of this plugin/technique. However, you might want to check out WordPress 3.0′s network features. It supports that scenario out of the box more or less.

  18. Eric – great article. Does this “trick” work on subdomains? In addition, here’s a complex question – if you’ve got an existing subdirectory based install, could you synch separate installs on subdomains using this? It would be super grand if that was in the affirmative!

    • I don’t see why not. Subdomains just map to virtual hosts (most likely) on the server. But as long as they can all talk to the same MySQL instance, you’ll be able to pull this off just fine. However, I would recommend taking a look at the network feature in WordPress 3.0+ too, to see if that wouldn’t better solve your needs.

  19. Hi!
    Thanks for the info!
    But I have a question about this solution, or the multisite solution:
    I need the users to have their own themes folder, so they can upload their own custom themes, I can achieve that with multisite?
    Or is better to use this solution, install one blog for each user so they can have their own themes folder.

    Thanks.

  20. Pingback: WP-Orphanage Extended | Best Plugins - wordpress – widgets – plugin 2012

  21. I know no one has commented on this lately but I have a question concerning this. I do not wish to activate MU on my wordpress install as I am only planning to have two separate sites. Here is what I have. I currently have two wordpress sites sharing a similar theme with some graphical only changes. I just made them so there isn’t really any content to lose as of yet so I’m not too worried about that. Along with this I don’t have any users yet other than the same admin username and password. Currently they are using two separate databases but I can easily change the table and database info so that they both are using the same database. Is there any way to do this with two existing sites? I don’t mind if I have to add or delete any users and such since the sites are brand spankin new. Thanks!

    • These days I would just go with Multisite. You can easily use the WordPress import/export to move content/media/users from one installation to another.

  22. Pingback: WP-Orphanage | Best Plugins - wordpress – widgets – plugin 2012

  23. Pingback: Installing more than one templates into a website

  24. I have 4 separate WP 3.5.1 and BP 1.6.4 installations. I have connected them via the CUSTOM_USER_TABLE CUSTOM_USER_META_TABLE’ lines in the wp-config.php file. I have also installed the WP Orphan plugin.

    When a person goes to one installation to register they will be given a user account and a blog site. When they go to one of the other installations they are there as a user but not having a site. In the admin listing of users, they are listed as a user in the ‘/’ (or admin) site, presumably as Subscriber role.

    If I wanted the user to have sites the other installations as well being users what would I have to do then? Would I change the role to ‘Editor’ in the Orphan plugin, or is there another database that I would have to share?

    • Yes, you can change the role if you want to give them a more privileged role on the other site, but be very careful with this. You probably wouldn’t want to set it up to give subscribers admin roles on other sites sharing the the user table, etc.

      You should also take a look at WP Multi-Site (Network Sites) built into core. It’s much nicer to use for this type of thing and is better supported than this trick.

  25. Hi Eric,

    Can you please explain better the point n. 2,
    no very confident with database would be good a little guide step by step

    regards

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>