What are the best practices to separate data from users

Question

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.