by Nate Haug on March 1, 2010 // Short URL

Single Sign-on across Sub-Domains in Drupal with No Extra Modules

With the multitude of single sign-on modules out there for Drupal, it's easy to miss the fact that Drupal has a built-in single sign on mechanism already. No modules, no configuration, just 20 easy lines of PHP in your site's settings.php file. This solution works for a lot of clients, but the set of requirements is pretty specific as to when you can use this approach. This includes:

  • The sites sharing a single log-in must be on the same domain. For example:
    • www.example.com
    • forums.example.com
    • subsite.example.com
  • You must be using MySQL.
  • Your sites must be on the same hardware cluster to be able to query each other's databases.

If your site fits within those requirements, you're on your way to simple, efficient, and easy Single Sign-on!

The concept for this single sign-on approach is based around Drupal's ability to prefix database tables. As you may know, you can run multiple Drupal sites on the same MySQL database. However, most sites are not configured this way, each site is given it's own dedicated database. Drupal's table prefixing can be combined with MySQL's ability to query across databases to make a simple "shared table" across multiple sites. Then you just need to set a cookie domain so that the two sites share session information and you're done!

If that sounds a little heady, let's just look at the code. Open the settings.php file (usually located in sites/default/settings.php) for your two sites. These sites can be in entirely different Drupal installs, or they can be under the same Drupal installation if you're using multisite capabilities.

Master Site Configuration

In your "Master" site (the one that the user information will be stored in), you don't need to make hardly any changes. There should be a line similar to this in your settings.php file:

$db_url = 'mysql://user:pass@localhost/master_database';
$db_prefix = '';

You don't need to change this at all. The master site stores all the user names, passwords, and sessions. However there is another line further down in settings.php that let's you specify a cookie domain. This needs to be un-commented (remove the leading # sign) and set to the name of your domain. Make sure you include the leading period before the domain.

$cookie_domain = '.example.com';

Slave Site Configuration

The slave site will connect to the Master site's database for certain tables, specifically the ones that include user information. This makes it so that user's simply "log in" using the information from the master site's database.

Here's where we use MySQL's database name prefixing, where all queries to the "slave" database are simply prefixed with the name of the slave database. Same goes for the master.

$db_url = 'mysql://user:pass@localhost/slave_database';
$db_prefix = array(
   'default'   => 'name_of_slave_database.',
   'users'     => 'name_of_master_database.',
   'sessions'  => 'name_of_master_database.',
   'role'      => 'name_of_master_database.',
   'authmap'   => 'name_of_master_database.',
);

Then we configure the site to use the same "shared" cookie domain as the master.

$cookie_domain = '.example.com';

The method for a user logging in does not change, the user can just use the exact same user name and password on both sites, logging into one will immediately log you into all of them. Users can even change their passwords and have it work across sites, and you can still use Views to build listings of users without any changes at all. Hurray for shared cookies.

Taking it further

Just by adding a few lines of code to your settings.php file on your different sites can make shared login a piece of cake. Note that this makes it so that other user-related information may need to also be shared. Specifically if you're using Profile module, you may also want to share the "profile_fields" and the "profile_values" tables.

One caveat you might encounter is that your user picture URLs are all relative to the Master site's files directory. To solve this problem, you can create a symlink from "files/pictures" on your slave sites to point to the master site's "files/pictures" directory.

ln -s /usr/home/example.com/sites/default/files/pictures /usr/home/subsite.example.com/sites/default/files/pictures

Again, this approach will only work across subdomains of the same domain and if the sites are hosted on the same set of servers. The security built into browsers prevents domains from reading the cookies of completely different domains, which is when you'll need to look into other solutions such as OpenID Provider, Single Sign-on, or Bakery.

This article was sponsored by our friends at Dooce! You can see this in action between their shared sites:

Nate Haug

Senior Drupal Architect

Want Nate Haug to speak at your event? Contact us with the details and we’ll be in touch soon.

Comments

Damien McKenna

skinet.com

We use this trick at SkiNet.com without any hassles, it works great! The only concern I'd mention is that if you're using modules that interact with the user records, specifically all of the serialized mush in the 'data' column, you really need to have the same modules set up otherwise depending on which site the user updates their profile it could potentially lead to confusion and/or errors.

Reply

marcvangend

"not recommended"?

A similar setup is described on Share tables across instances (not recommended), although it doesn't make very clear why it is not recommended. One risk I can imagine, is that a setup like this would cause an update query run twice on the same shared table.
Nate, what are your thoughts about this?

Reply

nate

Sequences table probably

That article was targeted at Drupal 5, and included the "sequences" table in its example code. My guess is that's the only "dangerous" table because it would do funny things like give sites unique ids for everything. So creating node "100" on site A, then site B would create node "101", etc. Not that I think that would be bad but it definitely could cause some unexpected behavior. This isn't a problem in Drupal 6 because we're now using real auto-increment columns, so the sharing of IDs isn't necessary any more.

That example also includes the "authmap" table, which is used for access control modules. I'm not sure how well this approach would work if you've got an access control module on your site, my guess is probably not very well.

Reply

Custom Drupal

Re: Bakery

This post basically describes the method that Bakery module implements. Bakery module simply builds a user interface to set up master and slave installations. However, you also need to edit your settings.php file manually :)

Reply

nate

Last sentence

I mention Bakery along with OpenID Provider and SSO in the last sentence. The whole point of this article is single sign-on without any modules. :-)

Reply

Dave Reid

/me slaps forehead

D'oh! Not sure why I missed that you did mention it. Maybe I saw an early revision somehow and I'm not completely crazy? :)

Reply

szb100

What does this mean?

"Note that this makes it so that other user-related information may need to also be shared. Specifically if you're using Profile module, you may also want to share the "profile_fields" and the "profile_values" tables."

To elaborate on the question... what additional setup tasks would there be in order to "share" other user information across domains in the example above?

Reply

nate

Just add the tables

$db_prefix = array(
   'default'   => 'name_of_slave_database.',
   'users'     => 'name_of_master_database.',
   'sessions'  => 'name_of_master_database.',
   'role'      => 'name_of_master_database.',
   'authmap'   => 'name_of_master_database.',
   'profile_fields'   => 'name_of_master_database.',
   'profile_values'   => 'name_of_master_database.',
);
Reply

Brian Morris

Very Timely and a ?

This is a timely post. I am setting up a multisite and I want to do exactly what you are describing here. I had everything but the dot.

One question. In a lot of documentation for this kind of sso and for multi-site it says to share the 'role' table. If I want to have different roles for my users on different sites, should I not share this table? Will there be any adverse effects that you can see?

Reply

nate

No immediate problems...

I can't think of any reason off-hand why you would absolutely need to share the role table. Even in this tutorial we didn't share the "users_roles" table, which is where users are delegated their roles. So that means that a user could be an "editor" on one site but not be one on another. So no, I can't think of a reason why you would need to share the "role" table.

Reply

Upasaka

Will this work for subsites

Will this method work for example.com/event, example.com/store etc or just for subdomains? If yes, shud the cookies be set any different?
About to start on a 'big' site, and would love to have ubercart and civicrm on different subsites and yet share user tables. Would appreciate any response! Thanks

Reply

nate

Yep, subsites on the same domain

Yep, subsites on the same domain will work just fine with this approach. You don't even need to change the $cookie_domain if you're using sub-directories instead.

Reply

Upasaka

Got it working!

Thank you for your timely response. Love the book 'Using Drupal'!

Reply

Bram

What if the main domain should not be included?

I have example.com (should not share), master.example.com (should share) and slave.example.com (should share). I'm using .example.com as the cookies table (nobody but me can log in to example.com, so none of the other user ids will match).

The shared tables statement does not seem to be working. If I log in to the master site, I get two cookies locally (one a session cookie under example.com, one a "has_js" cookie under master.example.com.

Slave.example.com still shows me as anonymous. When I log in to slave.example.com, it replaces the session cookie at .example.com, and now I'm logged OUT of master.example.com.

Help?

-Bram

Reply

Michael

LDAP

How does this integrate into using the LDAP module?

Reply

nate

No change

LDAP is merely another way of getting a user logged into a Drupal site. Once they're logged in and given a session, it doesn't matter what approach they used to log in. This approach should work fine with LDAP or any other authentication methods. Once you're logged into one site, you're logged into all of them.

Reply

Tejas Mehta

Nice tutorial. I have

Nice tutorial. I have already setup my Multisite with this concept. Now i have one different type of question.

I have master site at www.master.com with following database details
user name : master_user
password : master_password
database name : master_database

also one shared site with following database details
user name : shared1_user
password : shared1_password
database name : shared1_database

now in shared site i have setup following lines.

$db_url = 'mysql://shared1_user:shared1_password@localhost/shared1_database';
$db_prefix = array(
   'default'   => '',
   'users'     => 'master_database.',
   'sessions'  => 'master_database.',
   'role'      => 'master_database.',
   'authmap'   => 'master_database.',
);

Now my question is how do drupal recognize that master site has different login details ? As in my case it still works. Both site works and shares data for users and other tables.

Reply

nate

Same login/password on both databases

Drupal will only be able to work if both databases have a shared account that works between them. Intentionally or not, I bet that your "shared1_user" already has access to both the slave database and the master database.

Reply

wcsinn

can't get this to work

First, I'm new to this type of modification, so I am sure I'm doing something wrong. We are trying to implement this across two sites, comm.animemavens.com & animemavens.com. We have two sites which are sub-domains, both use MySql and are physically located on the same server (they are separate drupal installs).

We selected comm.animemavens.com to be the master and changed the cookies line to read $cookie_domain = '.comm.animemavens.com';

as I read the article, that should be it for our master file.

On our slave we added:

$db_url = 'mysql://animemav_drpl1:ourpassword@localhost/animemav_drpl1';
$db_prefix = array(
'default' => 'animemav_drpl1',
'users' => 'animemav_drpl5',
'sessions' => 'animemav_drpl5',
'role' => 'animemav_drpl5',
'authmap' => 'animemav_drpl5',
);

animemav_drpl1 being the name of the slave sites db & animemav_drpl5 being the id of the main sites database, we also removed the originals files following line:

$db_url = 'mysql://animemav_drpl1:ourpassword@localhost/animemav_drpl1';
$db_prefix = '';

and changed the cookies domain line to be the same as the master.

As I read the article - this should be all we need to do - but when the modified files are installed we get a series of fatal errors on the slave site.

Any help would be appreciated - we tried bakery, but unfortunately our host runs all of the scripts known to cause issues with bakery ( and they did - lol).

Reply

nate

Prefixes, not full names

In your prefix list you need to include the period after each database name.

$db_prefix = array(
  'default' => 'animemav_drpl1.',
  'users' => 'animemav_drpl5.',
  'sessions' => 'animemav_drpl5.',
  'role' => 'animemav_drpl5.',
  'authmap' => 'animemav_drpl5.',
);

In addition, your cookie domain must be the main domain, on both slave and master sites, not a sub-domain.

$cookie_domain = '.animemavens.com';
Reply

Michael Hofmockel

default should be null

default should be null -

$db_prefix = array(
'default' => '',
'users' => 'name_of_master_database.',
'sessions' => 'name_of_master_database.',
'role' => 'name_of_master_database.',
'authmap' => 'name_of_master_database.',
);

I got error when default was not null.

Drupal database update
user warning: Table 'batch' already exists query: CREATE TABLE talldave_drpl34.batch ( `bid` INT unsigned NOT NULL auto_increment, `token` VARCHAR(64) NOT NULL, `timestamp` INT NOT NULL, `batch` LONGTEXT DEFAULT NULL, PRIMARY KEY (bid), INDEX token (token) ) /*!40100 DEFAULT CHARACTER SET UTF8 */ in .../includes/database.inc on line 529.

Found fix here:
http://drupal.org/node/662860

Reply

reign85

share entities with drupal 7

Hi and thank you for this tip. I have to make 3 site who will sharing nodes and ENTITY (drupal 7) So can we share entities by making : $db_prefix = array( 'default' => '', 'users' => 'master_', 'sessions' => 'master_', 'role' => 'master_', 'authmap' => 'master_', 'entity_table_name' =>''master_', ); Reply

Jason Dodd

D7 specific syntax for settings.php

The 'prefix' array, should be an element within the $databases array within settings.php

For example: $databases = array ( 'default' => array ( 'default' => array ( 'database' => 'dbname', 'username' => 'dbuser', 'password' => 'dbpass', 'host' => 'localhost', 'port' => '', 'driver' => 'mysql', 'prefix' => array( 'default' => 'masterdb.', 'users' => 'slavedb.', 'sessions' => 'slavedb.', 'role' => 'slavedb.', 'authmap' => 'slavedb.', ),

),

),

Reply

indra

drupal 6

how to create a domain name $cookie_domain = '.example.com'; where to create

Reply