Sharing Drupal tables between databases using MySQL5 Views
Problem: You'd like to share some data between two different drupal websites without changing any Drupal code or otherwise having to lift much of a finger. But you don't want to share information unless it meets certain requirements (for example, only users labeled "foo" in somedatabase.sometable) For our example, we'll use the users table.
Solution: Use MySQL5 Views (http://dev.mysql.com/doc/refman/5.0/en/views.html)
Our "master" users table resides in a database called "master". The database of the site that will have restricted access to our masters users table is called "banana". Assumming you're starting with a fresh instance of the Drupal schema in your database "banana", do this:
mysql> use banana;
mysql> drop table users;
mysql> CREATE VIEW users AS
mysql> SELECT *
mysql> FROM master.users
mysql> WHERE uid IN (
mysql> SELECT uid
mysql> FROM somedatabase.sometable
mysql> WHERE uid = 0
mysql> OR label = 'foo'
mysql> ) 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. A workaround for this dependency is to include "user 0" in the results set that defines your view.
See, wasn't that easy?
Not necessary
This was news to me until today, but see this comment from Damien Tournoud:
http://drupal.org/node/292367#comment-956572
With that behaviour supported by the core, MySQL Views are not required at all. =)
--
http://www.drupaler.co.uk/
However, what about "sharing" users from a NON-drupal system?
Would a view be the way to handle that? If so, how?
In our case, the user database we'd like to "share" is on the same db server, but has its own structure. We're trying to allow our installed user base to be "logged in" when they come over to our new Drupal site(s). Been trying to force a user_save to accomplish this, but have been unable to get it to work right.
Also, I don't think we want to JUST use the external user table, but to allow those users to be automatically registered and logged in on our Drupal sites.
Could be
A view could certainly do that - you could join your existing, non-Drupal users table on your Drupal users table within a view, as long as you can marry up the fields (e.g. every Drupal user table field has an equivalent in your non-Drupal app, or at least the Drupal field is permitted to be empty).
Or you could look at alternative shared sign-on solutions designed for wider integration, such as LDAP.
Or you could look at writing your own single sign-on around web services for disparate applications. I've seen this done effectively. In principle it's very simple. We used a system like this at Dow Jones, where a central web service was responsible for saying if someone had access to an app or not, and providing it responded accordingly each app handled it's own local sessions, etc.
--
http://www.drupaler.co.uk/