Share users in a Drupal subdomain but have different content

This article from Lullabot provides some clues on setting up a subdomain Drupal site that allows for a common logins between the main and the subordiniate sites.

https://www.lullabot.com/articles/single-signon-across-subdomains-in-dru…

Note that the instructions for setting up the dastabase array to do this are in the settings.php file:

 * You can also use a reference to a schema/database as a prefix. This may be
 * useful if your Drupal installation exists in a schema that is not the default
 * or you want to access several databases from the same code base at the same
 * time.
 * Example:
 * @code
 *   'prefix' => array(
 *     'default'   => 'main.',
 *     'users'     => 'shared.',
 *     'sessions'  => 'shared.',
 *     'role'      => 'shared.',
 *     'authmap'   => 'shared.',
 *   );

But you would probably need some guidance to understand what to do with this. That’s where the article helps.

Install Drupal 7 to the subdomain database as normal. The prefix value should be empty at first. Otherwise, you won’t get able to install Drupal because the installer with think the database is already populated.

$databases['default']['default'] = array(
  'driver' => 'mysql',
  'database' => 'gcc_sub-prod',
  'username' => 'gcc_sub-prod',
  'password' => 'changeMe!',
  'host' => 'localhost',
  'prefix' => '',
#  'prefix' => array(
#       'default'   => 'gcc_sub-prod.',
#       'users'     => 'gcc_main-new.',
#       'sessions'  => 'gcc_main-new.',
#       'role'      => 'gcc_main-new.',
#       'authmap'   => 'gcc_main-new.',
#  ),
);

Once installed, make the cookie change as outlined in the article. You’ll make this change in the main settings.php file as well as in the subbordinate site’s settings.php file.

$cookie_domain = '.example.com';

Then swap the prefix setting in the database array so authentication is handled by the main database:

$databases['default']['default'] = array(
  'driver' => 'mysql',
  'database' => 'gcc_sub-prod',
  'username' => 'gcc_sub-prod',
  'password' => 'changeMe!',
  'host' => 'localhost',
#  'prefix' => '',
  'prefix' => array(
       'default'   => 'gcc_sub-prod.',
       'users'     => 'gcc_main-new.',
       'sessions'  => 'gcc_main-new.',
       'role'      => 'gcc_main-new.',
       'authmap'   => 'gcc_main-new.',
  ),
);

This will fail. You will see an error like:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘-prod.semaphore WHERE name = ‘variable_init” at line 1

This is because the database name contains a dash. Surround the database name with back ticks to fix this:

$databases['default']['default'] = array(
  'driver' => 'mysql',
  'database' => 'gcc_sub-prod',
  'username' => 'gcc_sub-prod',
  'password' => 'YG3jtYLKSdBbZCJN',
  'host' => 'localhost',
#  'prefix' => '',
  'prefix' => array(
       'default'   => '`gcc_sub-prod`.',
       'users'     => '`gcc_main-new`.',
       'sessions'  => '`gcc_main-new`.',
       'role'      => '`gcc_main-new`.',
       'authmap'   => '`gcc_main-new`.',
  ),
);

Now the site should come up normally.

Log in using an account in the main database. It will likely fail. You will see an error like:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to user ‘gcc_sub-prod’@’localhost’ for table ‘users’: SELECT users.name AS name FROM {users} users WHERE (name LIKE :db_condition_placeholder_0 ESCAPE ‘\’) AND (status = :db_condition_placeholder_1) ; Array ( [:db_condition_placeholder_0] => david.mcmeans [:db_condition_placeholder_1] => 0 ) in user_is_blocked() (line 846 of /var/www/drupal/modules/user/user.module).

This means the gcc_sub-prod user does not have permission to access the main database. Depending on how you set up your main and sub databases you may not have this issue. If you use separate users for each database then you probably will.

Fix it by granting the sub-database user access to the main database .

grant all privileges on `gcc_main-new`.* to `gcc_sub-prod`@localhost identified by 'changeMe!';

Roles

The users_roles table is not included in the prefix list. This is by design so you can have separate role assignments in the sub domain site. Roles, however, come from the main site. So don’t be surprised when you log in to the sub site that your profiles don’t have the same set of roles.