How to share table between 2 or more databases?

Question

I have a Joomla! site and now I want to make some subdomains. But, I want that the users must be the same in both. Like, if a user logs on to the main site and then goes to the subdomain at the same time, he won't need to log in again.

I found that, if I will share these tables, it can be ok:

tables #__user% and #__sessions

Questions:

1. How can I do it?
2. Can I share users between 2 or more Joomla! databases?

Answer 1

Yes, you can share the users from one Joomla! site to multiple other Joomla! sites (as long as you keep them on the same J! version). This is how you do it in Joomla! 2.5 - 3.6 (current version):

1. Choose the "master" site from which the users will be shared (this is the database which will actually store the data).
2. Copy the $secret value from the master site's configuration.php file to the same file on all the "sister" sites.
3. Ensure access to the master site's database is given to the users of each of the sister sites' databases.

4. Run the following SQL script on each of the sister sites' databases:

   RENAME TABLE `josPrefix2_users` TO `josPrefix2_bak_users`;
   RENAME TABLE `josPrefix2_session` TO `josPrefix2_bak_session`;
   RENAME TABLE `josPrefix2_usergroups` TO `josPrefix2_bak_usergroups`;
   RENAME TABLE `josPrefix2_user_notes` TO `josPrefix2_bak_user_notes`;
   RENAME TABLE `josPrefix2_user_profiles` TO `josPrefix2_bak_user_profiles`;
   RENAME TABLE `josPrefix2_user_usergroup_map` TO `josPrefix2_bak_user_usergroup_map`;
   RENAME TABLE `josPrefix2_viewlevels` TO `josPrefix2_bak_viewlevels`;
   
   CREATE VIEW josPrefix2_users AS SELECT * FROM dbSite1.josPrefix1_users;
   CREATE VIEW josPrefix2_session AS SELECT * FROM dbSite1.josPrefix1_session;
   CREATE VIEW josPrefix2_usergroups AS SELECT * FROM dbSite1.josPrefix1_usergroups;
   CREATE VIEW josPrefix2_user_notes AS SELECT * FROM dbSite1.josPrefix1_user_notes;
   CREATE VIEW josPrefix2_user_profiles AS SELECT * FROM dbSite1.josPrefix1_user_profiles;
   CREATE VIEW josPrefix2_user_usergroup_map AS SELECT * FROM dbSite1.josPrefix1_user_usergroup_map;
   CREATE VIEW josPrefix2_viewlevels AS SELECT * FROM dbSite1.josPrefix1_viewlevels;
   

---

Note 1: For the script above, you will need to:

1. Replace dbSite1 with the name of your master site's database.
2. Replace josPrefix1 with the prefix of your master site's database tables.
3. Replace josPrefix2 with the prefix of your sister site's database tables.

---

Note 2: If you already have data stored in one of the sister sites, you may need to update the user ID of many entries in the database as the user IDs will have changed. Examples: article author and modified user, LogMan access logs, etc.

---

References:

http://forum.joomla.org/viewtopic.php?f=470&t=473666#p2015114 http://forum.joomla.org/viewtopic.php?f=470&t=473666&p=3424061#p2850629

Answer 2

http://extensions.joomla.org/extension/mightysites

With the help of this component, you will be able to share the "users" table between two or more Joomla! websites.

It will give you content, users, and other db table-sharing capabilities between two sites in a variety of configurations.