Multisite: Share users and user roles for existing set up

Question

I have made a Drupal 7 multi-site with 3 sites. Each site is running of the same Drupal install and has a separate database. The database names are myhost_site1, myhost_site2 and myhost_site 3. (My hosting provider puts in the myhost_ automatically).

I need the 3 sites to share the users and user roles. I have read various tutorials and these are the steps I have followed:

1. Created a new database called myhost_users.

2. Copied the following tables from myhost_site1 TO myhost_users: users, user_roles, authmap and sessions

3. Given the database users for each of 3 main databases access to myhost_users.

4. Open the settings.php for each site and insert the following code (replacing the existing $databse definition):

   $databases['default']['default'] = array(
     'driver' => 'mysql',      
     'database' => 'NAME OF DATABASE',
     'username' => 'DATABASE USER',
     'password' => 'DATABASE PASSWORD',
     'host' => 'localhost',
     'prefix' => array(
       'default'   => 'NAME OF DATABASE',
       'users'     => 'myhost_users',
       'sessions'  => 'myhost_users',
       'role'      => 'myhost_users',
       'authmap'   => 'myhost_users',
     ),
     'collation' => 'utf8_general_ci',
   

   );

5. When I go to my site, I get the following error message:

   Uncaught exception thrown in session handler. PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table

6. For the code posted in (4) I have tried using an underscore suffix for the database names (e.g, myhost_users_), but I still get an error message.

What am I doing wrong? Do I need any settings in sites/sites.php?

I would be open to using a module to do this.

Bakery is no good as it doesn't share user roles.

Account Sync shares roles, but it doesn't seem to work.

CAS requires phpCAS which I do not have access to.

Answer 1

You don't need additional modules to achieve what you want.

First: Fix you configuration:

'prefix' => array(
'default'   => 'default_db.',
'users'     => 'myhost_users.', //
'sessions'  => 'myhost_users.',
'role'      => 'myhost_users.',
'authmap'   => 'myhost_users.',
),

The dots on the end of 'users' => 'myhost_users.' tells Drupal to query the users table in myhost_users data base (SQL syntax); without dots you'll get an error like "Base table or view not found myhost_usersusers/myhost_users_users or similar.

Now,

'database' => 'db',
'username' => 'db_user',
'password' => 'db_user_pass',

are credentials for the default db where Drupal is installed. To query users db you need to add this user with his password (db_user, db_user_pass) to the myhost_users database. in other words, if you have a site with 2 subsites:

• myhost_site1 - user of this db
• myhost_site2 - user of this db
• myhost_users - (assuming this db stores Drupal users, that you want to share across ites) user of this sb + user of myhost_site1 and user of myhost_site2

To stay logged in on each site it is necessary to share cookies between sites: edit settings.php file of each site, uncomment the line with # $cookie_domain = '.example.com'; and replace '.example.com' with your domain. Don't forget the dot at the beginning ('.mysite.com') -- without it both mysite.com and notmysite.com could be treated as the same domain.