What are the best practices to separate data from users

Question

Hi.

For a customer we where developing a big application that where open to all users if you will, meaning, all users could see each others data.

Now suddenly the customer is saying that they want only users belonging to the same organization to be able to view each others data.

So we came up with this data model:

So now the question is: How is it best to separate the data?
This is the only alternative I see:

• SQL JOIN on ALL relevant tables (All tables that have data should no always join on Organization) -- All queries should now add an extra join to Organization, and if the join doesn't exists, we need to create a new foreign key.

But I feel an extra join (We have around 20 tables that needs extra join) is quite costly.
I hope there are some other best practices or solutions we can consider.

PS: This is a Web application developed using Java/JSF/Seam (but I don't know if that is relevant)

UPDATE

I want to clarify something. My consurn is not security but performance. We have added the foreign key to organization to all relevant tables that has shared data, and we are using user's logged in organization to filter the data.

All I want to know is if this is a good architectural solution (inner join) or if we should do something else (ie: Load all shared data, and filter in memory instead of sql join).

Answer 1

You really have to understand the difference between the persistency layer and the application layer.

It doesn't matter how you define your database tables, as anyone with database access will have access to all the users data. What does matter is how you define the behavior in your application.

Changing the database design should only be done for performance reasons, not for security - which should be handled in the application.

Answer 2

I would reckon that the best pattern would be to only expose the user details through the web application, so at that point its a case of restricting the data exposed to each user. This will allow you to build in the required security inside the application.

Alternatively if you are allowing direct database access then you will need to create a login/user (depends on database used) for each organization or user and then restrict the access of these login/user entities to parameterized stored procedures rather than the base tables. This will push security back onto the database, which is riskier but still do-able.

As to meta changes to support the organization column, parameterizing the stored procedures will be fairly trivial:

select @organizationId = organizationId from User where User.id = @currentUserId

select * from User where organizationId = @organizationId

(depending on the sql flavour you will need to enclose some entities eg `User `, [User] etc)