Best way to ensure data security

[eluser]phester[/eluser]
I am working on a sales leads applications where groups of people can enter in data about sales leads in a database to help track them.

I have a users table with USER_ID and GROUP_ID fields for each user (and other fields).

I also have a leads table with USER_ID AND LEAD_ID (and other fields).

I am trying to find the best way to provide data security so that each group can only view leads posted by users of their group only. I have a "view" controller and I need to find the best way to stop users from accessing another group's information by entering in a lead id in the url after a controller/method eg: view/lead/20 where 20 is a LEAD_ID posted by a different group.

The one way I was thinking about to fix this is to store the group_id of the user in a cookie when they login, and add a GROUP_ID field in the leads table. And each time they want to view a lead, I would add the GROUP_ID as a "where" clause using active record.

This is a simplified explanation of my problem. I have a lot more than one table, and I'm not sure if adding GROUP_ID to all the tables is a smart idea.

Any other ideas are greatly appreciated! THanks!

[eluser]gtech[/eluser]
storing the group_id in the session is perfectly valid
you could do a table join when retrieving data

e.g. use the user_id to join users and leads .. where users.group_id = whatever

[eluser]phester[/eluser]
Ah yes, of course! I was thinking a little 1-D and forgot about joins. Thanks. Any other suggestions?

[eluser]gtech[/eluser]
[quote author="phester" date="1194391219"]Ah yes, of course! I was thinking a little 1-D and forgot about joins. Thanks. Any other suggestions?[/quote]

yes
1) USE table lookups


if a lead can belong to more than one group, you can create a group lookup table. this is an over head on creation but save when selecting data.

Code:

groups     group_lead_lookup  leads
------     -----------------  -----
groupid    groupid            leadid
           leadid             userid

A user might belong to mutliple groups?

Code:

groups    user_group_lookup    users    leads
------    -------------------  -----    -----
groupid   groupid              userid   leadid
          userid                        userid

A lead might belong to multiple users (create a user_lead_lookup) you get the idea.