Managing privileges and record level access

[Inflight]

Hi

I'm designing a database where I'd like to hide records. A user logging in will be externaly authenticated against the company AD which works fine. In this way I'm able to control record level access that the authenticated user only has access to his own records. So far so good.

But how can I manage the access rights that all emloyees of a department - they all have individual logins - can see and edit the records of their collegues? There are also supervisors and managers and they should have of course access to the records of more than one department to have an overview.

I thougth about a related table where an admin can enter the departments an employee is allowed to have access.

Like that:

Users I Departments

[color:green]User1 I O

User1 I OT

User1 I OTE

[color:blue]User2 I OT

[color:purple]User3 I OTE

[color:orange]User4 I O

etc.

What would be the best approach to get that fixed? How can I manage the RLA when as an example User1 is allowed to see the records of the departments O, OT and OTE?

Hope my description is clear enough to understand.

Thanks in advance for your help.

[hfinger]

One brute force technique I've used is to have 3 views, each one filtered by Department privileges. Then give each department a different user set and let your startup script define what they can view. If you also provide a "show all" button on each view that actually retains the restrictions, you could each the sense that they are seeing everything when they are really seeing everything they're aloud to see.

It isn't the most elegant solution, but from a user standpoint it would appear simple.

[Vaughan]

Do this with privilege sets that represent the roles of the users.

Say there are staff in Sales, Support, and they all need to edit their colleague's records, then one privilege set needs to be made for Staff and another for Support. As records are created, a "Group" field is entered with the privilege set that created it. The privilege set then has RLA set to only allow view/edit/delete for Group="Staff" or Group="Support" respectively.

Once you get the complexity sorted out for determining the roles the users have, the rest is easy. Try very, very hard to minimise the complexity of the user roles. Less is indeed more here. You don't want to end up with a karma sutra of privilege sets where (as Oscar Wilde observed) number 234 is the same as 65 except she has her fingers crossed in one and uncrossed in the other.