Sharing Drupal tables between databases using MySQL5 Views

Problem:

You have a "master" database that contains data from multiple Drupal sites and you want to share it among them. Normally, you could use table prefixing to allow each of your sites to point to a single table. But what if you do not want content from one site to "bleed" across to the other sites? Let's say you have a network of Drupal sites sharing a user database. You want to share that user's information across your entire network of sites, but only make the information visible from those sites to which the user has subscribed. Or maybe you want to populate baz.com with users who meet some arbitrary criteria. There are lots of possibilities here, but nobody's paying me to write this, so let's get on with it.

Solution:

Use MySQL5 Views. For our example, we'll use the users table. Our "master" users table (the table that contains all users for all of our sites) resides in a database called "master". The database of the our example site that will have restricted access to our masters users table is called "banana". Assuming you're starting with a fresh instance of the Drupal schema in your database "banana", do this:

use banana;
drop table users;
CREATE VIEW users AS
SELECT *
FROM master.users
WHERE uid IN (
SELECT uid
FROM somedatabase.sometable
WHERE uid = 0
OR label = 'foo'
)

Drupal will use banana.users just as it would a normal users table. No other modifications are necessary. Now only "foo" users will be included in the users table for your banana website. Note: Drupal has a dependency that is not really documented. Every users table must have an entry that contains uid=0. It's a "stub" entry that Drupal needs to function properly when a user is anonymous. A workaround for this dependency is to include "user 0" in the results set that defines your view. See, wasn't that easy? Please post questions here, and I'll update these instructions as needed.