Methods for ensuring security between users in multi-user applications

Question

I'm writing a multiuser application (.NET - C#) in which each user's data is separated from the others and there is no data that's common between users. It's critical to ensure that no user has access to another user's data.

What are some approaches for implementing security at the database level and/or in the application architecture to to accomplish this? For example (and this is totally made up - I'm not suggesting it's a good or bad approach) including a userID column in all data tables might be an approach.

I'm developing the app in C# (asp.net) and SQL Server 2008. I'm looking for options that are are either native in the tools I'm using or general patterns.

Answer 1

I believe associating data with a user via a user id is the most common approach.

Another approach is encryption. Each user could have some secret key, an actual digital key or maybe just a password, and all their data could be encrypted with their secret key so that other users wouldn't be able to access it. You would still need to associate data with user ids for querying etc.

Answer 2

You could do this 1. Create a dbo.users table and have following columns, note this is not complete

table users
 -pid [uniqueidentifier]
 -userfname
 -userlname
 -useremail
 -userpwd




table userdata
  - datapid 
  - pid
  - [other columns to hoold data]

once your user authenticates against this table then you just use the ppid to return and enter and update any data related to that user

Answer 3

why not using any kind of access methods (who can access which file, and has the rights of read, write and delete) that fits your problem if it works with your problem (i have no idea)? For example:

• Manadatory Access Control known as (MAC)
• Discretionary Access Control (DAC)
• Role Based Access Control (RBAC)
• Rule Based Access Control (RBAC)

you can read and select one of them if it fits your problem.