User Specific DAL

[kxcntry99]

Everyone,

I am trying to turn a single user web app into a multi-tennant system. Currently I have all data access calls going to a DAL with a set datbase, username and password.

My problem is this...I need to create a separate db for each tennant and theoretically each db would need its own username and pass to keep from having one username for all tennants with privildges to all db's.

Initially I was thinking that when the user authenticates to the userstore (one userstore for all tennants) that I could slip a db name and username back to the client in the auth ticket. But this got me to thinking about the effects on the DAL for other users.

Since there is only one DAL and it is only called once the app needs or edits data I believe that the DAL would then be considered "server side". In other words if I pass a db and username to it for one user...all users logged on will end up using this combination until it is changed again. I.e. :

User 1 logs in and sends a db and username to the DAL of database1 and user 1. Lets say a second user loggs on for another tennant. Now they send a db and username to the DAL of database2 and user2. Now if user 1 makes any changes the DAL is still set to the db and username of the last logged on client of database2 user2. User1 is no effectivly editing and reading from the wrong Database.

How can I work around this? I know that I could pass a username and db to the DAL on every data call but this seems horriably inefficient. Is there a way to set a "secession DAL" for each logged on user? Meaning I set the db and username one time when they log on, which is kept until they log off???

Thanks for your thoughts

[Kasracer]

First off, why are you choosing to create separate databases? Typically you'd have a table that specifies who's a tenet then when they login, you'd only display their information. Additional databases is a bit more inefficient and makes maintenance much more difficult. So I think the most important question is: why?

Secondly, even if you decided to separate them and send a username and password back to the user to send to the DAL (not a good idea security wise) I don't understand why it would replace the credentials constantly like you suggest. If you sent them to user 1, user 2 logs in and sends to the DAL, why would that affect user 1 at all? This is the web so everything is created and destroyed with each page load so I'd imagine if you went this route you'd just send the appropriate DAL credentials each time a database update needed to occur.

Now if you d decide to use one database, do not use any type of method where the username and password is passed around like that. Instead, define all of them in the web config and just do a switch in your DAL to the correct one depending on who's logged in. Much easier.

[kxcntry99]

Kasracer thanks for the response....

There are a couple reasons I am using separate databases. First, if one of the tennants databases becomes corrupt it will be much easier to do a restore of one tennants database rather than the database for all tennants.

Second, although logging in is more difficult it will eliminate the chance that a user can view or edit records belonging to another tennant. In other words if I use one table the first 5 records may belong to tennant 1 the next 3 belong to tennant 2...so on and so on. I would then need to build into the logic that tennant 1 only pulls the rows that they own. I guess this wouldn't be too big of a deal but it makes me nervios when it comes to editing records or if the site has a problem and starts showing all tennants all records in the table. I am thinking security would be easier with separate db's.

Typically you'd have a table that specifies who's a tenet then when they login

I am not sure what you mean by this comment. Are you referring to the userstore that is used to authenticate? If so that is a separate database from the data that the clients will store and edit.

As you stated passing usernames and passwords acorss the web is deffinately not secure at all and I would like to find a way around that. I like your idea of putting the data in the web config.

Just to confim I would put each tennants db name and dbuser id to edit with in the web config then anytime a command is passed to the DAL it would reference the webconfig to retrieve this information and edit the table. I guess at that point I would need to pass some kind of tennant identifier to the DAL each time data is needed. But is there a way around having to pass this identifier each time a data call is required? I.e. A user logs on once passes this identifier to the DAL once for the session?

[Kasracer]

There are a couple reasons I am using separate databases. First, if one of the tennants databases becomes corrupt it will be much easier to do a restore of one tennants database rather than the database for all tennants.

Not a good excuse. The company I work for has over a hundred active databases all being heavily used throughout the day. I have yet to see any become corrupted. It's not very common and if you have a good backup solution you shouldn't run into any difficulties unless you're doing something very odd (which I don't think you are).

Second, although logging in is more difficult it will eliminate the chance that a user can view or edit records belonging to another tennant. In other words if I use one table the first 5 records may belong to tennant 1 the next 3 belong to tennant 2...so on and so on. I would then need to build into the logic that tennant 1 only pulls the rows that they own. I guess this wouldn't be too big of a deal but it makes me nervios when it comes to editing records or if the site has a problem and starts showing all tennants all records in the table. I am thinking security would be easier with separate db's.

They won't be able to view anything about the other tenants unless you want them to.

For instance, say I have a table called tenants. This table looks like this:

CREATE TABLE [MySchema].[Tenants]
(
      TenantID INT NOT NULL IDENTITY PRIMARY KEY,
      Name VARCHAR(128) NOT NULL
)

Now let's say I have 3 users in this table

TenantID             Name
1                     Ralph
2                     Kevin
3                     Sam

For any table that contains Tenant specific records, I will make sure to use the TenantID across these tables. So if I want to have, say, a Payments table it might look like this

CREATE TABLE [MySchema].[Payments]
(
      PaymentID INT NOT NULL IDENTITY PRIMARY KEY,
      TenantID INT NOT NULL CONSTRAINT TenantUserID FOREIGN KEY REFERENCES [MySchema].Tenants(TenantID),
      Payment MONEY NOT NULL
)

So now my Payments table is tied to my Tenants table (this ensures data integrity so you can't delete something and have payments floating around). So if I want to grab all payments for, let's say, Kevin, I'd just do this

SELECT * FROM [MySchema].[Payments] WHERE TenantID = 3

This will make it impossible for other Tenants to see anyone else's information. You just need to specify what records you're looking for.

If you break them up across multiple databases then your database server will become larger much faster due to the additional databases, tables, indexes, etc. Not only that but it'll be difficult to maintain as if you need to add 1 field you now need to add it across 5+ databases instead of just one. To make things better and even more secure / less likely to make a mistake I'd recommend using stored procedures. So you could turn our previous query into

CREATE PROCEDURE [MySchema].[GetPaymentsByTenant]
(
      @TenantID INT
)AS
      BEGIN
            SELECT * FROM [MySchema].[Payments] WHERE TenantID = @TenantID
      END

Then you could simply call your code via

EXECUTE [MySchema].[GetPaymentsByTenant] 3

I am not sure what you mean by this comment. Are you referring to the userstore that is used to authenticate? If so that is a separate database from the data that the clients will store and edit.

I don't think I quite understand. Why is this in yet another database? Keeping this in a separate database prevents the usage of constraints making it more difficult to keep your data integrity.

How do the clients store and edit this data?

Just to confim I would put each tennants db name and dbuser id to edit with in the web config then anytime a command is passed to the DAL it would reference the webconfig to retrieve this information and edit the table. I guess at that point I would need to pass some kind of tennant identifier to the DAL each time data is needed. But is there a way around having to pass this identifier each time a data call is required? I.e. A user logs on once passes this identifier to the DAL once for the session?

No. You could store this identifier in the user's session but you'd still need to pass it to the DAL every time.

Remember, with the Web everything is stateless. This means each time someone comes to your web site / application the site creates the page for the user then destroyed everything allocated in memory (besides session and cache, of course). So you'd need to pass this to the DAL one way or another.

[kxcntry99]

Thanks for the insight Kasracer! I appreciate the reasoning why I should be looking at one database. I had read this article awhile ago and determined at that time that multi db's would work best for my solution. (at least until such time as I need to scale up)
http://msdn.microsoft.com/en-us/library/aa479086.aspx

The question I have about going with one db is how to handle the fact that one tenant may store some different data than the next. I.e. tenant1 wants to track demographic information and birthdate vs. tenant2 doesn't need birthdate but rather a field for soc#'s. I was going to handle this with a metadata table with information about the custom fields (the article above also speaks to this).

Another reason why I was thinking separate db's is that for now (with only 5 tenants in mind) this seemed to be a cleaner approach. In the future I knew that I would have to consider the one db approach but I was thinking more in the short term. Perhaps I should just put the framework in place now and be done with it.

Thoughts?

[Kasracer]

Thanks for the insight Kasracer! I appreciate the reasoning why I should be looking at one database. I had read this article awhile ago and determined at that time that multi db's would work best for my solution. (at least until such time as I need to scale up)
http://msdn.microsoft.com/en-us/library/aa479086.aspx

I haven't read this article so it may have additional insights. From my brief look at it; it looks to suggest that the additional databases may be a faster / easier approach in the beginning. I'm not sure I'd entirely agree with that but then again I've been doing a ton of database work lately so I find it easy to write it the scalable way first .

The question I have about going with one db is how to handle the fact that one tenant may store some different data than the next. I.e. tenant1 wants to track demographic information and birthdate vs. tenant2 doesn't need birthdate but rather a field for soc#'s. I was going to handle this with a metadata table with information about the custom fields (the article above also speaks to this).

Hmm, I hadn't thought of this. Is it possible that, in the future other tenants may want this data? My first thought would be adding all of the fields they each want to track into the same database.

So, if one wants to track birthdays and the other wants to track social security numbers I'd put them both in whatever table(s) you use to describe each person.

Not sure if you necessarily need additional meta data with each field; I don't see a problem just adding the specific fields. Then again I don't know everything that they want to check.

Another reason why I was thinking separate db's is that for now (with only 5 tenants in mind) this seemed to be a cleaner approach. In the future I knew that I would have to consider the one db approach but I was thinking more in the short term. Perhaps I should just put the framework in place now and be done with it.

My advice is to always go the best route for the future as possible. Sometimes this isn't doable due to tight deadlines but I've been in many situations where someone did something one way and said they'd make it scalable later but due to time constraints it never happens, things snowball and before you know it you have 347 different databases tracking the same type of data.

So I would just go for the one database approach in the beginning if possible.

[mendhak]

It may be on MSDN but its fundamental basis is flawed. When you have a single DAL talking to multiple instances of the same database, then you're creating a problem for yourself. What if you find a bug in some table structure somewhere, are you going to update all the data or all the schemas in all the tables in all the databases? It's not a scalable solution and that article is wrong.

I had a quick read through that article and although I won't use the words that come to mind, I will say that it appears to be some sort of a solution that they probably use in their company for some specific application and which they thought was a really great idea. That's the problem with architects. They often feel the need to create something pointlessly complicated and sell it as the be-all and end-all solution. And to think that's what I aspire to become some day

There are a couple reasons I am using separate databases. First, if one of the tennants databases becomes corrupt it will be much easier to do a restore of one tennants database rather than the database for all tennants.

Second, although logging in is more difficult it will eliminate the chance that a user can view or edit records belonging to another tennant. In other words if I use one table the first 5 records may belong to tennant 1 the next 3 belong to tennant 2...so on and so on. I would then need to build into the logic that tennant 1 only pulls the rows that they own. I guess this wouldn't be too big of a deal but it makes me nervios when it comes to editing records or if the site has a problem and starts showing all tennants all records in the table. I am thinking security would be easier with separate db's.

It all sounds like you're trying to cover up for your lack of experience and/or nervousness. If you have trouble doing something, then ask about it. All this trouble just to avoid a few "WHERE" clauses? Really, come on... And yes, I know I'm sounding rude but I have to convey this to you somehow.

While you may have different types of data stored for each user (DOB, SSN, Hair Color), the data is not, I'm sure, defined on the fly - it's you or the DBA who's creating tables for this. That simply means that for each set of data you want stored, you create a new table and associate it with the user by a foreign key.

[kxcntry99]

Thanks kasracer and mendhak --very valid points all around.

As for your comment about lack of experience and/or nervousness, I am not ashame to admit it you are absolutely correct about my lack of experience. I have worked for a long time with windows apps for a single tenant. This is the first time I am trying to make a multi-tenant web app. (heck of a project to start asp.net with right????) Anyway, after reading the MSDN article I took it as truth that I might have an error in my web app that would allow a tenant to see all records!

Fortunately, I am still early in the design of this project so it will not be hard for me to move to aone db setup.

At this point I am thinking that all general demographic information that all tenants use can be in the main table and any custom columns can be in extension tables with the tenantID and recID to tie it all together.

The only question remaining is how to pass the tenantID to the client after the logon. Is it appropriate to pass it back with the auth ticket or is there antoher method?

[Kasracer]

The only question remaining is how to pass the tenantID to the client after the logon. Is it appropriate to pass it back with the auth ticket or is there antoher method?

I would store it in the session. This way the client cannot see their ID and they cannot change it in a cookie but your web application can access it at any time.

[kxcntry99]

I would store it in the session. This way the client cannot see their ID and they cannot change it in a cookie but your web application can access it at any time.

Nice a little built in security feature!

[Blakk_Majik]

Be careful with sessions, and use them VERY sparingly.

Remember that each and every session object you create for each and every user is stored on the server. Use them wisely and clean them up appropriately.

[mendhak]

OK, it's nice of you to take that criticism with good grace. Now, although you may admit to whatever shortcomings, I think it's good that you're (at least from your perception?) taking the plunge.

Start by designing your tables first with your relationships between them. If you're using SQL Server, you should be able to produce a diagram of the relationships between them. Once you're satisfied with the design (you need to get the DB right!) then proceed. If at any point you have any nagging doubts, don't hesitate to post again and we'll take our sweet time in replying to you as your deadline looms

[kxcntry99]

Thanks for the assistance on this one. I am pretty sure I have the table layout I need and the relationships are not bad at all. So now its just onward and upward

If at any point you have any nagging doubts, don't hesitate to post again and we'll take our sweet time in replying to you as your deadline looms

Strange: that is the reason why I joined the forums