Ref: http://drupal.org/node/291373

Multi-site with single codebase, different content databases, shared user database, shared sign-on

Last updated November 3, 2009. Created by greg.harvey on August 5, 2008.
Edited by add1sun, lut4rp. Log in to edit this page.

SECURITY ALERT - The Shared Sign-On module this
tutorial formerly relied on has been withdrawn because of security
risks. It is no longer available at all. There is a replacement module
called Single Sign On.
It should replace the Shared Sign-On module, but currently reports
problems with user registration and recommends registration happens
through the master site. If your solution requires branded registration
pages, you could create a suite of registration pages on the master site
and use the Theme Key module to apply the correct theme to each one, for a seamless user experience (except for the URL, obviously).

WARNING - Following this approach will make updates
potentially trickier. It's not a major issue, but you must be mindful
that you are sharing some tables amongst several Drupal instances, so
you will need to be aware of install and update hooks which want to make
changes to any shared tables. For example, it is possible for the
update or install scripts of a module unique to one instance to break
other instances if it makes changes to shared tables. There are other multi-site solutions, so plan carefully before selecting which one is right for you.

This tutorial makes two assumptions:

1. You are _not_ a total newbie
2. You have downloaded MySQL GUI Tools (to make life easier) - clearly
this is not a pre-requisite - if you know and prefer command line MySQL
then you can replicate the steps I provide easily enough:
http://dev.mysql.com/downloads/gui-tools/5.0.html

Make a copy of /sites/default to /sites/site1 and another to
/sites/site2. You may add modules and themes directories in these new
directories in the usual way for site-specific configurations. A files
directory should be there already but create it if not. Rename
/sites/site1/default.settings.php to settings.php and
/sites/site2/default.settings.php to settings.php (and, for Linux, make
sure both new files directories are writeable by the web user).

Add these lines to your hosts files:

Create three empty MySQL databases:

Go to http://site1 and run through the install process in the normal way, using the database name 'drupal_site1'.

In MySQL Administrator create a new back-up project of the
'drupal_site1' database containing _only_ the tables you want to share,
save it and run it. The result should be a .sql file somewhere on your
computer. For reference, the Drupal 6.x tables for sharing users are as
follows:

The absolute basic essential tables (if you don't want to share any
data except for the users and sessions, no profile data, roles, etc.)
are:

IMPORTANT: if you have Open ID authentication enabled you will also need to share the openid_associations table.

(This is not a definitive list. Nothing stops you from sharing
taxonomy, etc. For example, with this configuration permissions are
_not_ shared - each site has its own permissions matrix - but you might
want to share one across all sites. Ditto with enabled modules.)

Edit the .sql script you created and update the database name within
to 'drupal_shared_tables'. Using the Restore interface, run the .sql
script. You should now have a database called 'drupal_shared_tables'
containing only the list of tables above.

Go to http://site2 and run through the install process in the normal way, using the database name 'drupal_site2'.

Go back to your original 'drupal_site1' database and drop the same
list of tables from the schema. Ditto for the 'drupal_site2' database.

Now our databases are ready.

Browse to your Drupal application and edit /sites/site1/settings.php. Change these lines (usually starting at line 93 in Drupal 6.3):

To:

Note the all important trailing dot on the end of
drupal_shared_tables - this is vital! You're pre-pending table name info
to Drupal's default table name, so you need the dot or Drupal will try
to select:

Instead of the desired:

Do the same for /sites/site2/settings.php, but with $db_url set to drupal_site2.

You should now be able to login as the super user on both sites separately (http://site1/user or http://site2/user),
using the credentials you entered when you installed site1, give them
both different settings, modules, permissions and content but use the
same user credentials. Moreover, if you create a user on site1, you will
see them in users in site2, but with an entirely independent set of
permissions you can decide on a site level.

Now for the shared logins. Download and unzip the Single Sign On module in to /sites/all/modules.

Enable the Single Sign On Controller module on the site you want to
use as the master for logins (the controller) - in our case, site1.
Enable the Single Sign On Client module on all other sites, for this
example just site2.

Then IMMEDIATELY add the following line to settings.php for both sites (see the Single Sign-On README) or you will get PHP errors:

Copy and paste the Controller information on site1 (found at
admin/settings/singlesignon-controller) over to the Client settings on
site2 (go to admin/settings/singlesignon-client). Save settings on site2
and logout of both sites. Clear your cookies to avoid any hang-overs
from pre-single-sign-on days.

Go to http://site1/user and login. Now go to http://site2.
You should stay logged in. Logout again and see you are logged out in
both locations. Reverse it so you login on site2 and visit site1. Neat,
huh? =)

Need a new site? Take a copy of /sites/default to /sites/new-site,
make the necessary vhosts (optional, see below) and DNS/hosts changes,
create a blank database, run the Drupal installer, drop the tables you
don't want from your new database (the shared ones), edit settings.php
adding the $db_prefix array as above, configure Single Sign-On and
you're done!

Optional Apache settings

If you would like to set different Apache settings for your various
sites (e.g. different access and error logs for each) add a vhost entry
to Apache looking something like this:

This is not usually necessary.

IMPORTANT - if any of your client sites have any
URLs that need to be accessed without an authentication check, the site
concerned _must_ have a line in the 'Request paths' box of Single Sign
On Client settings to exclude that URL from being redirected to the
controller site for authentication. These settings can be found under
'Bot recognition' settings (admin/settings/singlesignon-client). For
example, if one of your clients uses the Services module
to expose Drupal-based web services you would add a line like this to
that box, in order to exclude all your endpoints from the authentication
check:

Remember, this only applies to client sites, not the controller, and must be done on all sites