Record Based Security

Hi,

We are currently developing a crm that has the following main tables: client, sales, actions, contacts and employees with up to 4 million records. The db contains several other tables with extra information but these aren't relevant for this problem.
Clients have sales, contacts and actions. sales and actions can have seperate contacts as well, and contacts can be related to employees in a way.

Offcourse a security model was implemented but this only provides the option to hide or show all the records of these main tables. Now people noticed that it is a must to let people see for example only companies they created or do sales with. In other words access must be given at a record level.

The first thing that pops in my head is to create a client_security, sales_security, ... table that contains the employee_id and client_id or sales_id, or ... And add a join with one of these security tables for each select that is executed.

I wonder (because of the huge amount of data) if there isn't a better way to get this done?

Another possible issue that came to mind was, that when some data doesn't need protection or some people have the rigth to see everything, we would have to create the records in the security table anyways, resulting in probably hundreds of thousands of records.

Feedback on our solution (if we can call it that allready), or even keywords to find more about this issue via google (record based security really isn't the best description I guess) are most appreciated.

thx in advance.