At work, I am tasked with creating a user database. It needs to account for (end)user permissions for 8 different installed programs. The database will be hosted by each client containing their specific information behind a firewall. I have the majority of it built out (tables below), but I don't know how to incorporate permissions.
I could put all available permissions in a table, and then link them to users/groups and applications in another table. Or I could put all available permissions in a table, then link them to users/groups in another table, and then applications in yet another table. Problem is that both of these options don't play well with the applications because I cannot think of a way to actually search for a specific permission (ex: call to database to find if user can do 'x') without the programmer hard-coding the permission Ids into the program.
I could have a table per application with each of that application's permissions as a column, but there are 70+ permissions for each application and the tables would get huge (also not very expandable if we need to add more permissions later).
So the end questions is; How do I set up permissions for end-user accounts in a way that the programmer does not have to know and hard-code the Ids into the applications?
Table - Description
- Applications - Basically a list of our applications so we have an Id to link the permissions to
- GlobalSettings - Settings used for all users, mostly password requirments
- Groups - Lists all end-user administrator defined groups of users (this way a permission can be tied to a group of users at one time)
- PreviousPasswords - Lists the last x hashed passwords for each user (the ones they can't use again)
- UnusablePasswords - Lists passwords we won't let our end-users use (such as password1)
- User_Group_Link - Links a User to a Group
- Users - Lists all end-users and their needed data (birthday, name, etc)