Put a study into permissions system doctrine. This is a rough description of what I've used for awhile. It's overkill most of the time, but it works. but first, lets look at permissions in general.
A permission is an action. As in UNIX, there are essentially two actions against a record - read, write. UNIX also has execute, but that will never be relevant here. We can, probably should split this up more. I use CRUD -- Create, Read, Update or Delete.
Permission can be dependent upon record organization. Again, from Unix we have user, group and world. This is a reasonable model.
In a database system it may become necessary to set permission on a field level. A billing officer might be able to see a patient's billing information, but not their medical history details. A doctor may have reverse access. An office worker can see both. All of them can see the patient's social security number, but only an administrator can edit it once committed. A problem is that sometimes data off the same table might have different permissions.
Writing a system to deal with this is not easy. I've done it partially, but the project was cancelled before completion for lack of funding.
As far as enforcing permissions, I was going to use Role Based Access Control (RBAC). In this approach users are mapped to roles, and roles are mapped to actions. Users can have one or more roles. Users are also mapped to groups, but groups address the question of record ownership, not permission (at least not directly - this will be clearer in a moment).
RBAC is pessimistic - that is it assumes the user does not have permission to perform an action. When an action is attempted the roles of the user are checked. If any of the roles says "yes" to the action and none of the roles says "no" the user can perform the action.
Each role has one of 5 settings for an action
Yes, if a member of an owning group of the resource to be affected.
Yes, if the owner of the resource to be affected
When a role is first created it makes no comment on any action. You then map what the role can do. By mapping a user to multiple roles you can compose quite specific permissions for individuals.
A final piece to this puzzle though is account aliasing. If you have this complicated a permissions system eventually you will need to test the permissions of a user. Many programmers take a cheap route and establish a "skeleton key" password that can log into any account. DON'T DO THIS. The liability can be enormous. And there's a better way.
When developing a system this complex develop a means for super users to masquerade as other users from their own account. This 'account aliasing' should be exactly like being logged in as the aliased user except two critical differences
- You can leave their account and return to your own at any time.
- When you perform an action, any audit logging in place still notes that you performed that action, aliased as another user. For example my system leaves the note "File modified by Michael Morris aliased as John Doe" instead of the normal "File modified by John Doe" note.
Aside from that you will see the system as the user you are aliased as sees it so that the permissions can be tested. Reason - even if you code RBAC correctly, it's very easy to configure it wrong. It often won't be the dev's job to do such testing - rather the admins - but it is important.