Multisite with separate users table

[fale]

Hi,
I'm using WP with the network option since 3.0 has been released. I think that is very cool, but I think that the user management is not at a good level.
At the moment, every user must register to the first site and the admin should 'bring' them to the wanted site. Some plugins (I use Multisite User Management) have been created to try to resolve this problem.
My experience in wp-networks teach me that there are two ways to intend a network:

• something like wordpress.com
• a bounch of sites that are together only for administration purpose.

The actual implementation does not respond to any of the previous cases. In fact to be able to use wp-network for the first case, you have to install a plugin that brings your new users across any site of your network, and for the second purpose the actual implementation of wp-network has huge problems.

I think that the best way to resolve this, is to ask the user what he wants. During the creation of the network the system does ask the user if he prefers a sub-domain install rather than a sub-folder install. I think it should also ask the user if he prefers an installation with a single user db or a user db for each site.

I was thinking which was the best way to manage the two things: for the first case, I think, the actual way is very good (it only needs to create the new user on all sites instead of delegating this to the admin/plugin). For the second case, I think, the best way is to have an user db for each site, each one with different values (yes, a person could register himself on site 5 and appear only in the wp_5_users table). As soon as a user is made super-admin he is 'replicated' on each user table. When a new site is created, the user table of that website will be populated only by the super-admins.

I think this would make WP the best CMS/blogging platform ever seen :)

[nacin]

I covered this in IRC today. I mentioned that if you wanted to set up a network like this, then you would be better off switching table_prefixes in wp-config. That's pretty much exactly what you're looking for here.

If you want network plugins, then use mu-plugins. As long as you're using the same codebase, then the themes and plugins will be shared, with per-site settings.

[fale]

Probably was another guy, the one in IRC, since I don't join IRC since august ;).

I know is possible to create both kinds of networks (I have them up and running), but I'm saying that implement them directly in WP would make WP awesome for a lot more users ;)

[scribu]

If we were to do this, the only common tables left would be wp_sites and wp_blogs.

I don't think the benefit of those tables justifies the amount of work needed to allow separate users tables.

[fale]

I think users are different from plugins and themes. Plugins and themes have sense to be installed only by a super-admin because there is new code, so possible bugs coming... but users are not likke this, imho

[tmoorewp]

In my experience from running MultiSite, there isn't any benefit to having separate tables for users. Blog admins of subsites can, with the right settings, add users to their blogs -- super admins aren't the only ones able to add users.

Having separate users tables would introduce too many issues (non-unique usernames and non-unique email addresses, for starters), which would make it much more difficult for a super admin to manage a network, I think.

[fale]

the problem is that, even if you delegate this to admins, there still is a work for admins/superadmins. In the other way, would be possible to leave all the work to wp/the user...

About the non-unique users... I don't see the problem...

Btw: imho, it can also be solved givin the user the impression that is registering on the site he wants, and not on another site

[lightningspirit]

I think the problem is the user level.

In my experience the idea of having separate user tables is good for WP hosting in a single install, but not for a network like wordpress.com.

I think it could be an checkbox option while installing the multisite: "Separate user table for each blog?"

If the admin turns it on it should:

• create a separate wp_$blogID_users and wp_$blogID_usermeta tables for each blog to hold all the users that belongs to that blog.
• create a central wp_users and wp_usermeta tables to hold network admins.

In this case, if the admin promote a blog user to manage the network it will replicate the user to wp_users.

The same thing happens if someone is trying to create a new user in the wp_$blogID_users but that user exists in wp_users, it will only replicate to wp_$blogID_users.

Also this is very good to host blogs that want themes like the ones in the AppThemes...

[scribu]

I think it could be an checkbox option while installing the multisite: "Separate user table for each blog?"

Hi lightningspirit, you might not know this but we're not particularly fond of checkboxes (​http://wordpress.org/about/philosophy/#decisions), especially when they imply a fundamental architecture change.

[scribu]

Also, you didn't actually mention any tangible benefit that having separate user tables would bring.

[lightningspirit]

Hi scribu,

Thanks about the link, I almost forgot it! :)

What do you mean by "tangible benefit"?

As a developer and deployer of WordPress Mulsites network I found sometimes necessary to have two different user_tables because sometimes the blogs that run the same network are very specific. As an example:

• A network of websites that run inside the same WordPress install for different clients and they have different users. The idea is to cut off the "sensation" that those websites are running in the same architecture.

My concern is about giving the admin this possibility, to separate exclusively those websites.

Maybe this is a matter for a plugin and not a functionality provided by the core, but maybe we can hack the core to provide those hooks for plugin to extend themselves.

I know there are some constants like CUSTOM_USER_TABLE and CUSTOM_USER_META_TABLE but my experience is that it is not enough...

May I propose some new code for the code to extend this possibility, if the community agrees?

Thank you!

[nacin]

Last year, I briefly took a crack at a plugin that tried to implement this. I never got much further than the design stage, but I was confident that enough hooks existed in core to do this. I suggest we again close this as wontfix.

[scribu]

The idea is to cut off the "sensation" that those websites are running in the same architecture.

You can do that pretty easily through a plugin:

• remove the My Sites menu from the admin bar
• use custom registration form, instead of the one on the main blog

and that's about all you need to do.

[lightningspirit]

@nacin I will try to create a plugin for this functionality, I will informe you if I can get some news on this...

@scribu I understand your point, but deeply if a logged in user of the site A.com visits the website B.com (A and B in the same network but from two different clients) the user will be logged in, but withour permissions to visit the admin of that site. The problem is, if the user wants to register for site B.com, what happens? B.com responds that the user cannot register because they are already registered... don't you think this is confusing? :S

Okay, maybe I should use two complete different instalations for A and B, and maybe the purpose of the multisite is not in that direction. If this is answer I think we should write somewhere in the Codex to advise users about what "Multisite is not". But again, just for fun, I will try to plugin it and if I get success I announce it here. :)

Thank you both!

[scribu]

Indeed, multisite isn't meant for sites that are supposed to be completely independent. Feel free to modify the Codex to make it clearer.

I think I finally get why the ManageWP service sprang up.

[Ipstenu]

Two thoughts down Scribu's path

1) Make a check on your custom registration page 'if user && email already exists, just add them to site B. Else create new'

2) Make a 'hide admin bar' check in a mu-plugins. If the user isn't a member of THAT site, remove the admin bar. That would hide it so they wouldn't know they were a member.

[SergeyBiryukov]

Version field indicates when the feature request was initially suggested.

[lightningspirit]

@scribu Indeed, but I will try it just for test (and fun!)

@Ipstenu thank you, I think that's sufficient to do what I want!

[lightningspirit]

Between yesterday and today I made a draft of what could be a plugin to achieve this functionality.

I think I did it! :)

NOTE: Use this method in test environments only! It is not stable enough (and I think it will never be...)

Description:
Those two files ( sunrise.php and blog-user-table.php ) can create separated user tables for each blog in a multisite instalation.
It lacks the possibility of creating users inside a blog, even if the netwok option is enabled, but I think it might be an issue related to capabilities.

It actually performs what it is expected: separate users for each blog!

Issues:

• one have to create the users manually in MySQL tables due to a capability issue that I wasn't able to seek.
• Network panel just shows users from the global table (don't know if this is really an issue)

Those files could be the first steps to "recreate" this functionality with a plugin in WordPress I think...

Instalation:

0. Install a WordPress 3.4 from the trunk - make sure it is used just as a test instance
1. Create at least a blog in your multisite test install
2. sunrise.php goes to wp-content/ directory
3. blog-user-tables.php goes to wp-content/mu-plugins/ directory
4. Put define('SUNRISE', true); in wp-config.php file
5. Using SQL in a MySQL browser (like phpmyadmin) copy an existing or create a new user inside wp_2_users and wp_2_usermeta (change the generated SQL as necessary)
6. Try to login with the new account in the new blog

Tested in a local instalation of a Multisite WordPress 3.4 (trunk).

No security tests made! Please, avoid to use it in production sites.

Have fun! :)

[lightningspirit]

sunrise.php goes to wp-content

[lightningspirit]

blog-user-tables.php goes to wp-content/mu-plugins

[scribu]

Sorry, but that's not a patch.

[lightningspirit]

Sorry, I mistook the tag! Yes, defenitely is not a patch.

[nacin]

#25167 was marked as a duplicate.

[Solinx]

"Also, you didn't actually mention any tangible benefit that having separate user tables would bring."
@scribu

As a small dev team we're looking to use a multi-site install to drastically simplify our maintenance efforts for several customers who are running on the exact same codebase. Our customers rightfully demand access to the DB in which their data is stored. Obviously we only want to give them access to their own data. This is particularly true for their user data. Aside from the ethical aspect there are privacy regulations to take into account.

We can use ​Multi DB to store most data in separate DBs, but this is not the case for User data.

@lightningspirit
Thanks for taking the initial steps.

Cheers,
Wouter van Dam

[lukasdan]

Hi,
do anybody test it with WP 4.5?

Best regards,

[anonymized_6443559]

I'm actually quite disappointed in seeing that this didn't gain any traction. Here I am now 9 years later trying to do the same thing, for very much the same reasons managing multiple client sites in one "installation".

@nacin brings up one could simply define a different db prefix for each site to do this without even implementing multisite, and for the most part the claim that this is effectively the same is mostly true. There is one key difference I see, in the code by @lightningspirit there is one key feature that would not exist in simply switching db prefixes, that is a set of core "network admin" users that would have global access (ie for me and any staff I have) without having to add user accounts specific to each client site, IMO that would be quite useful and the tangible benefit that @scribu said is missing from this. Granted it may be a rarely useful benefit but for those of us who would use it that would be very helpful. I would have liked to see this be an option even if by a define statement in wp-config like "define("PERSITE_USERS", true);" or something. Or at least if a stable production ready plugin were available that would suffice too, but that's beyond my capabilities sadly.

Anyway thats my 2 cents on this but I think in the end its probably something best left to a plugin because its very much an edge-case, I just wish someone who has a better deeper knowledge of core and how to make this work in a secure manner would take this up for an official plugin that gets actively maintained.

[maidot]

This is actually a very needed functionality, which is not that hard to apply nowadays!

[metric99]

Hello,

I'm surprised this has been left as closed/wontfix for such a long time, considering the rise of SaaS and specifically for this topic, WaaS business models.

Referring to @scribu comment ¨Also, you didn't actually mention any tangible benefit that having separate user tables would bring.¨ Thinking of an agency, or anyone producing a WaaS, users should be able to create their websites without needing to share wp_user & wp_user_meta, hence the suggestion of @lightningspirit by using CUSTOM_USER_TABLE and CUSTOM_USER_META_TABLE would be perfect, more than hiding my-sites or similar.

I'm not familiar with how wordpress.com handles this, but I'm quite certain they don't share wp_user & wp_user_meta across so many blogs/subsites, as it's quite probable users will unknowingly try to register to more than 1 wordpress.com site (different domains, differnt categories) and don't get the ¨User already exists¨ message on a website they visit for first time. How and why should they know it's all a huge network of websites? Not even mentioning the privacy concerns for a shared user table.

Hopefully, this will be addressed, I guess I'm not the first one who has been looking for days now on how to properly fix this issue.

Cheers

[kodeexii]

WordPress as a Service.
WooCommerce as a Service.
LMS as a Service.
Real Estate sites as a service.

Among the few types of services that can be created with a single install of WordPress - if and only if Multisite no longer shares the user table.

Please do have a look at enabling this in the core.

[joselawrence]

I believe that the idea of separate user and user_meta table is very possible and would take wordpress to a whole new level, most especially for those that want to build WAAS using wordpress multisite (like me), if only the code team can take a deep look into it.

I tested the php scripts provided by @lightningspirit and they are amazing. A user who uses the same username and password on multiple site will be able to automatically switch between those sites just like the single user and user_meta tables which offcourse will make maintainance easy for superadmins once they are using the same user name and password on all sites.

The idea is for the subsite be completely independent of eachother and for the subsite admins not to know, but superadmins can use the same authentication to make management of their network easy for them. Also if subsite users use the same username and password accross multiple sites they will also be authenticated automatically when switching sites, which is super cool, but most amazingly they can use different password if they want, which is the new feature that is being added and when switching to a site that they have not logged into or that session is expire, they can put the separate password and get logged into both sites with the same email and user name but different sites.

The major problem I see with the code provided by @lightningspirit is that if you using it, only the main site you can create user account or users can register for from the frontend. but for the subsites, you have to do it from the database or phpmyadmin. What if those users could register from the frontend or account be created from the frontend and the data be saved into the subsite user and user_meta table, FRONTEND REGISTRATION PLUGINS DEVELOPERS or MEMBERSHIP PLUGINS DEVELOPERS will definitely begin to support this new feature.

Again, if user account for subsites could be created from the frontend and registration for subsites be possible from the front end it will really be amazing.
Super Administrations get their superadmin privileges accross all subsites once using the same username and password on all subsites, which can be done by replicating all superadmins to a newly created site.

[invokers]

Replying to joselawrence:

I believe that the idea of separate user and user_meta table is very possible and would take wordpress to a whole new level, most especially for those that want to build WAAS using wordpress multisite (like me), if only the code team can take a deep look into it.

I tested the php scripts provided by @lightningspirit and they are amazing. A user who uses the same username and password on multiple site will be able to automatically switch between those sites just like the single user and user_meta tables which offcourse will make maintainance easy for superadmins once they are using the same user name and password on all sites.

The idea is for the subsite be completely independent of eachother and for the subsite admins not to know, but superadmins can use the same authentication to make management of their network easy for them. Also if subsite users use the same username and password accross multiple sites they will also be authenticated automatically when switching sites, which is super cool, but most amazingly they can use different password if they want, which is the new feature that is being added and when switching to a site that they have not logged into or that session is expire, they can put the separate password and get logged into both sites with the same email and user name but different sites.

The major problem I see with the code provided by @lightningspirit is that if you using it, only the main site you can create user account or users can register for from the frontend. but for the subsites, you have to do it from the database or phpmyadmin. What if those users could register from the frontend or account be created from the frontend and the data be saved into the subsite user and user_meta table, FRONTEND REGISTRATION PLUGINS DEVELOPERS or MEMBERSHIP PLUGINS DEVELOPERS will definitely begin to support this new feature.

Again, if user account for subsites could be created from the frontend and registration for subsites be possible from the front end it will really be amazing.
Super Administrations get their superadmin privileges accross all subsites once using the same username and password on all subsites, which can be done by replicating all superadmins to a newly created site.

Wordpress core supports Wordpress Multi Tenancy and its is there for this same reason.

[jaschaio]

I would really like to see this land in core as well.

This is such a basic but quite common sense requirement. It would make transforming individual sites into a multisite or the other way around as well a lot easier.

The 9 year old code still works. Mainly because WordPress actually allows defining CUSTOM_USER_TABLE and CUSTOM_USER_META_TABLE. If there were some more filter and actions hooks within the $wpdb class there would potentially be a less "ugly" hack then what was proposed by @lightningspirit – but so be it.

But I am having the same issue that others have raised that it's impossible to create new User Accounts from within the Sub Sites. Only on the main site (or via the database directly). I will see if I can find a way around that and will keep this issue updated if so.

[metric99]

@invokers Why are you closing this? We're not talking about multi tenancy, we're trying to get sub-site own user database, meaning, having users created on/for a sub-site ONLY, not network-wide. Maybe I'm misunderstanding something here, but WordPress Multi Tenancy is a different ball-game here.

Hope you can elaborate.

[marco_b]

Any news on this topic? I also would really like to see this as plugin or in the core.

[CodeBard]

Separate user tables per site is a necessity in the face of the multiplying privacy regulations in every major economic zone and the legal burden and significant fines they bring.

[johnbillion]

I think privacy and data protection regulation is an argument against implementing this feature. If you're managing multiple separate client sites on one multisite installation of WordPress, with one database that shares some tables (eg. wp_blogs) but not others, then you're setting yourself up for data protection concerns and maintenance overhead that will not be solved by using separate users tables. It may even introduce a false sense of security or privacy that is not achieved via separate database tables.

Privacy and data protection regulations that apply to a site where users are contained within one table will still apply to a site where users are contained within multiple tables in the same database.

The WordPress and hosting ecosystem is quite different to how it was back in 2010 when multisite was merged into WordPress and when this ticket was opened, as are privacy and data protection regulations. Storage space is cheap and tools such as WP-CLI and services such as ManageWP allow multiple sites to be managed with greater ease. Data protection and security concerns are strong arguments against using shared architecture for sites administered by separate organisations. Allowing a multisite network to use separate users tables doesn't address those concerns.

I'll close this ticket off again. As usual, conversation can continue on the closed ticket but I can't see this feature ever being considered.

[CodeBard]

I think privacy and data protection regulation is an argument against implementing this feature. If you're managing multiple separate client sites on one multisite installation of WordPress, with one database that shares some tables (eg. wp_blogs) but not others, then you're setting yourself up for data protection concerns and maintenance overhead that will not be solved by using separate users tables

Thats incorrect. The law requires separate sites do not share the same info. It does not say anything about using the same database. If it did, almost entire world of website SaaS would be in upheaval. Such requirement exists only in certain legislative zones, only for certain applications, and even at that, for compliance - not as a requirement. (ie, Brazil privacy las require legal sites not share a database for compliance)

Privacy and data protection regulations that apply to a site where users are contained within one table will still apply to a site where users are contained within multiple tables in the same database.

This is a null argument. The legal requirement is that the sites not be able to access each other's data. It doesnt have any problem with using the same database.

The WordPress and hosting ecosystem is quite different to how it was back in 2010 when multisite was merged into WordPress and when this ticket was opened, as are privacy and data protection regulations. Storage space is cheap and tools such as WP-CLI and services such as ManageWP allow multiple sites to be managed with greater ease.

Well, this feels out of touch with the realities of the world that the users live in. Not only a lot of users host multiple sites in multisite installations instead of having to manage multiple hosting accounts and sites, but also a lot of agencies have gone the way of using multisite to easily host, maintain and update the websites of their clients. These users span from small businesses to universities to nonprofits as multisite still remains the best and easiest way to maintain innumerable similar sites than entering a scripting and command line hell. WP, like other open source projects that cater to the majority instead of technical minorities, must make things easier, not harder. And no, wp cli and command line are not 'easy', nor economical for the design houses that server individuals and small to medium businesses, leave aside the average user. Proposing this feels like you are looking at this from a programmer's window, not the users' window.

I would like to remind you what was the recent reaction of the community when someone proposed deprecating multisite to put the perspective of the importance and the prolific-ness of multisite into perspective:

​https://wptavern.com/wordpress-multisite-is-still-a-valuable-and-often-necessary-tool

​https://wpowls.co/articles/its-time-to-give-wordpress-multisite-some-love/

...

In addition to these, awareness of the recent Eu cyber resilience act seems to be quite lacking in the community: The new law considers those who 'deploy software for profit' responsible with the security issues of those software with fines ranging from 10% of annual revenue to 10 million Euros. The vague wording of this law looks like it will put everyone from small agencies and web hosts to actual users who use wp for their business as the end user as legally responsible for the software that they use, which means that deploying less things and offloading the responsibility to other services by not entering any of the software & management angle of hosting. This means that using multisite for multiple sites with everything else offloaded to host would likely be safest - much better to deploy multisite for a dozen or hundred sites and be responsible with its security vulnerabilities than take on the management of hosting accounts, leaving aside hosting software or command line and increase the surface area for responsibility and risk.

I will reopen this as this is important to a lot of us in the community. Maybe someone who looks at things from a users' perspective can take a look at this.