Authenticating users via one database to set the usable database for that user

[kscrow]

I am trying to write a small web app that consists of about 20 pages. These pages are the same for all users but they are populated with data from a database. Each database MUST be for that single customer, I cannot hold multiple customers data in the same database.My question is this...

Can I authenticate each user via a login screen where they will enter their username and password for comparison to my master database of users (say mydatabase.3423.mysqlhost.net) and if that username and password is a match, set a session variable for the link to their database?

In other words, the user logs in as 'bob' with password 'duke' and the database looks up that login and password and provides the browser with the name of the database they are alowed to use along with their credentials (say bd13242.db.374523.mysqlhost.net with username 'bsmith' and password 'earl') and from then on, all the pages the user can see are populated with data from the database residing at bd13242.db.374523.mysqlhost.net? I am trying to build a scenario where each customer gets to see all the same pages but the information is all different depending on what database they are allowed to use. Is setting a $_SESSION variable the way to do this, say by setting $_SESSION['database'] = bd13242.db.374523.mysqlhost.net or is there a better way at going about this?

 

Thanks ahead of time,

K

[Psycho]

Sounds reasonable. Although I would not store the actual database name (or the user's password) in the session data. Instead, I would save an identifier for the correct database for the user. Then on each page load do a lookup in the master database for the correct user database to use. Then switch to that database to get all the specific page data.

[taquitosensei]

my question would be...why can't you store multiple users in the same database? That's a problem with the design right there.

[Andy-H]

Why not have one database for the user logins, store the user credentials in this database and have the user authenticate with those login details, then once they login with the correct details you can simply store a unique identifier for the user in the session and use that session data to pull the required database credentials from the login database upon each request.

 

Of course this means having two connections establised to different databases on each request, however is more secure than storing their database user/pass combo in a session.

[kscrow]

Your solutions sound interesting. Forgive me if I sound a bit nieve but would this unique identifier be a field in my master database? Say like so..?

 

USERNAME | PASSWORD | DATABASE NAME | DBPASSWORD | DBUSERNAME | IDENTIFIER

bob    |  mypassword  |  database1324.2342.mysqlhost.net | vacuum1 | bsmith | 12

 

Is that what you guys are suggesting or am I missing the point completely? The user will of course have to have session variables already set with their username and password for access to the pages themselves but once they have access to the pages, i will need a seperate connection to another database (which one depends on their credentials) from which to pull the data. What I am trying to accomplish here is something along the lines of facebook where all users access the same twenty or so pages but they all get different data populated into page sections depending on who they are (this is th eonly similarity with facebook that I have). This application is being written for the healthcare industry and many companies will be using the same pages but will be pulling their own data (which they have imported or manually entered) and I cannot allow access to any database to anyone but the representative from that company.

 

Thanks for your help,

K