Multiple WordPress installation sharing one user table

Recently my project required me to setup multiple WordPress installation and each WordPress installation does different thing. The only problem that i faced was to have a synchronize login between these three WordPress installation. Short to say, once a user has logged into the system, they will not have to do it again if they decide to proceed to the next WordPress installation.  There are a few great explanation on how this can be done even on WordPress forum. However, it seems like everyone faced a little problem of having to touch the core of WordPress in order for their user to access another WordPress installation without getting the message 'You do not have sufficient permissions to access this page.'.

Requirement

Before i began, you will need to know certain things in order for WordPress to share user table across multiple WordPress installation.

  • All your WordPress installation must be within the same database
  • Each WordPress installation must have different prefix ( or else you are performing overwriting)
  • URL must be within the domain. Multiple domain will have problem authenticating an already logged in user although sharing user table is still possible.
  • WordPress installation version must be similar across the media you wished to share.

Enough of my crap. Let's start with something simple.

Sharing WordPress user table

Our objective here is to share WordPress table across sub domain. Although different domain will also work but your user will have to log in manually on each different domain WordPress installation. (i will explain later) The main thing you will need for each sub domain to use a single user table is to point all your WordPress installation towards one user table. In order to do that, you will have to edit your wp-config.php file on all your sub domain WordPress installation and place the following code into it.

define('CUSTOM_USER_TABLE', 'wp_users');
define('CUSTOM_USER_META_TABLE', 'wp_usermeta');

where wp_ is your WordPress installation prefix, change it to your own prefix. The above constant variable (or code) is telling your WordPress installation that we won't be needing any users and usermeta table. We will be using the one defined above. Hence, any user who login from different installation will all see the same table. (sharing user has accomplished)

Login once only

Pointing everyone to one table is pretty simple. Next we want our user only login once and every time when they visit a new WordPress installation blog. In this case, we will have to further add in some code into ALL wp-config.php file. (although you don't need all but let's play safe)

Dump the following code into all of your wp-config.php file (including the main and sub installation).

$baseurl = 'http://www.domain.com'; // replace with the actual domain name for your root site
$cookiehash = md5($baseurl);
define('COOKIEHASH', $cookiehash);
define ('AUTH_COOKIE', 'wordpress_'.COOKIEHASH);
define ('SECURE_AUTH_COOKIE', 'wordpress_sec_'.COOKIEHASH);
define ('LOGGED_IN_COOKIE','wordpress_logged_in_'.COOKIEHASH);
define ('TEST_COOKIE', 'wordpress_test_cookie');

define('COOKIE_DOMAIN', '.domain.com'); // change domain with your root site
define('COOKIEPATH', '/');

 define('AUTH_KEY',        'to be generated at https://api.wordpress.org/secret-key/1.1/');
 define('SECURE_AUTH_KEY','to be generated at https://api.wordpress.org/secret-key/1.1/');
 define('LOGGED_IN_KEY',  'to be generated at https://api.wordpress.org/secret-key/1.1/');
 define('NONCE_KEY',       'to be generated at https://api.wordpress.org/secret-key/1.1/');

 define('AUTH_SALT',   'can use one of the key on https://api.wordpress.org/secret-key/1.1/');
 define('LOGGED_IN_SALT',       'can use one of the key on  https://api.wordpress.org/secret-key/1.1/');

The above will be adviced to be placed on all of your WordPress installation that you wished to have an one time login feature. Please take note if there is a constant variable or code similar on your wp-config.php file, please remove them. The objective of having the above code similar across each WordPress installation is so that each installation can authenticate your user login detail correctly across different installation.

Now, take note of the cookie constant variable. WordPress uses that to authenticate your user login and cookie authentication cannot do cross domain validation. And this is the reason why different domain couldn't achieve one time login feature.

Explanation on what's going on

I tried very hard to explain in least technical aspect as possible but i still thinks it is quite technical so allow me to summarize a bit here.

  • Firstly we point all WordPress installation to the main WordPress installation user table where all user login details are located.
  • Secondly, we copy the required code for one time login to all WordPress installation including the main WordPress installation in order for all WordPress installation to proceed the same way to identify a user (it's like everyone holding the same key to open one lock where the lock refer to the user)
  • Thirdly, 'You do not have sufficient permissions to access this page.' message bugs you. (it's a good thing)

Alright, this should be it.

Solve the problem of You do not have sufficient permissions to access this page

Soon, you figure when you try to login from other WordPress installation using other WordPress installation user detail, you receive the message  'You do not have sufficient permissions to access this page.'. If you manage to get this message after you have logged in to the main blog and directly access your sub ones. This means that one time login has successfully implemented. The reason why this is happening is because within WordPress core code, it is checking your user capability with the prefix that you have install on that WordPress installation and not the one used on our user table.

Example, main blog A uses prefix wp_ and sub blog B uses prefix wpp_. You tried to login from blog B and you get the error message because on blog B installation, it is checking wpp_ instead of wp_ .

This logic resist within the core code on wp-include/capabilities.php and you will have to dig into the core in order to fix this problem or manually add the record yourself every time a new user register.

If you select the first option to manually insert the record on other sub domain, you will go to your usermeta table and insert the following record.

wp-multiple-installation-one-user

Or you may want to change the logic in your core implementation so that all your sub domain knows the correct usermeta table to look into. If you do, change all sub domain WordPress installation capabilities.php file located at wp-include/capabilities.php line 514 where you will see this.

	function _init_caps() {
		global $wpdb;
		$this->cap_key = $wpdb->prefix . 'capabilities';
		$this->caps = &$this->{$this->cap_key};
		if ( ! is_array( $this->caps ) )
			$this->caps = array();
		$this->get_role_caps();
	}

On the comment above you will notice the trouble maker. Now, hardcode the prefix to the main domain to this instead for all your sub domain. (because everyone is sharing the same user table, we won't have the detail of their unique prefix + user_roles table.)

//$this->cap_key = $wpdb->prefix . 'capabilities'; //this is the trouble maker
$this->cap_key = 'wp_capabilities'; //this is the trouble maker

this will do the trick but you will have to change this every time wordpress update 🙁

I have another personal solution which doesn't need to do both or required to set a default permission type for all user who do not have access across sub domain. However, there is a problem with it that is why i can't release it at the moment (although it's really a small problem) but let me fix it and release for you guys in the near future 🙂

Security

Having the same user to access across different WordPress installation is great but doing this might degrade the strength of each WordPress installation. The security concern lays on the one time login implementation and not how each WordPress installation is using the same user table. Since one time login required every security measurement to be same across different WordPress installation, this means that only one installation is required to be broken in order to access all other sub domain. Short to say, instead of multiple wall of defends for each implementation. Now, we have just one.

**** UPDATE *****

Unfortunately, the above method doesn't work anymore for latest WordPress version (hence, stop your research and keep reading the same appraoch). In order to get auto login in multiple wordpress sub domain sites with multiple installation, another approach has to be done. It is still possible to get all users to login automatically across every website and sharing user login without using CUSTOM_USER_TABLE. It is more reliable and you do not have to combine databases together. However, this approach require a bit of bypassing and modification certain behavior which i will try to write one out if i get the time. Do dig deep into the core code to understand what you guys might think as an 'easy' approach might not be that easy anymore.

13 thoughts on “Multiple WordPress installation sharing one user table

  1. Hi Clay, thanks for this. I'd been looking for a fix everywhere. I'm not sure exactly what you mean for me to change when you say "hardcode the prefix to the main domain to this instead". Does that mean change the line: "$wpdb->prefix . 'user_roles';" to "$wpdb->http://mydomainname.com 'user_roles';"

    Thanks for sharing this. I'll be posting about it when I'm sure I understand it.

  2. no no..it means hardcode to
    "wp_user_roles"
    instead of
    $wpdb->predix . 'user_roles'.

    You will need to change the $wpdb->prefix to the one similar to the main prefix user table that you used for sharing.

  3. Thanks! I think I'm still half asleep this morning. I did that and was still getting the "you do not have permission" error, so I went in and changed the database (thanks for that explanation!) and then installed the wp-orphanage plugin in case any new users are orphaned. I'll poke around in the capabilities file some more later to see if I can figure out what I did wrong.

  4. Sorry Clay, but the permissions problem persists even after following all your steps. I've got two WP 2.9.2 installs, and am trying to point blog #2 to blog #1. Adding custom tables and auth data to wp-config.php goes fine, and I can log in. But even after altering capabilities.php in blog #2, it tells me I don't have access.

    Also tried going into table wp_2_options and changing value 'wp_1_user_roles' to 'wp_2_user_roles'.

  5. @Daniel: My bad, you should change the one on _init_caps() instead of init(), changing prefix.'capability.php' instead of prefix. 'user_role'.

  6. To future readers, before Clay gets a chance to update:

    As mentioned, in wp-includes/capabilities.php, you need to edit _init() as described, replacing $wpdb->prefix . 'user_roles' with 'YOURPREFIX_user_roles'.

    But you ALSO have to edit _init_caps(), replacing $wpdb->prefix . 'capabilities' with 'YOURPREFIX_capabilities'

    Thanks Clay. Please update; it's the only working solution and I saw a lot of threads with people stuck - some citing your current post as 'not working.'

  7. Thanks Daniel. Please bear in mind that altering WordPress core code will means you will have to edit them again in every single WordPress version update or else it will not work.

    P.S: There are other alternatives rather than editing your core code 🙂

  8. Hi I'm abit confused on the instructions on how to get rid of the permission issue? I'm not to sure exactly what to edit and what areas to replace? Will this still work if we decide we don't want to integrate both the logins? i.e. just do the table sharing and edit the capabilities.php ??

  9. sorry forgot to mention the area i'm getting confused on is the part about editing 2 parts in capabilities.php... and also which prefix to place in there and do you also keep the wording capabilities in there with the prefix

  10. actually sorry for all the posts just got this working.... Daniels post confused me you only need to change the _init_caps() as mentioned by Clay not both

  11. The only issue i have now is that I can't seem to access the wp-admin on my second blog. i login to blog 1 which works, go to blog 2 and it shows me logged in, so i click on site admin and it takes me to a wordpress login area with my user name in there and empty password. So i add my password in and it just keeps going back to the same login screen... isn't saying my login details are wrong or anything .... also can you delete my first couple of posts?

  12. @nirok: Is there any plugin you have installed on your second blog that might be causing this? Try deactivate all other plugin to ensure that both your blog have been synchronous before activating the plugin will be advisable 🙂

  13. Hey i have it working now, the only issue is logging out of hte second blog doesn't log otu of the first blog however i don't think this will be an issue
    Thanks

Comments are closed.