1

This question already has an answer here:

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

  1. Applications - Basically a list of our applications so we have an Id to link the permissions to
  2. GlobalSettings - Settings used for all users, mostly password requirments
  3. 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)
  4. PreviousPasswords - Lists the last x hashed passwords for each user (the ones they can't use again)
  5. UnusablePasswords - Lists passwords we won't let our end-users use (such as password1)
  6. User_Group_Link - Links a User to a Group
  7. Users - Lists all end-users and their needed data (birthday, name, etc)

marked as duplicate by Joel Brown, mustaccio, Erik Darling, Mr.Brownstone, McNets Nov 13 '17 at 8:00

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Have you looked at the schema that SQL Server uses for modeling permissions within a database? You might try using that as a starting point, and update your question with any pros/cons you see in that permission model. In particular, I highly recommend a role-based approach to user-permission assignment. – AMtwo Nov 12 '17 at 12:54
0

Some Ideas (Anyone: please feel free to Edit/Add)

ApplicationPermissions (Application Level Permissions)

  • ApplicationID
  • PermissionID
  • Allow (Y/N/1/0)
  • Value (As in a Max Number of "X" allowed)
  • CreatedDate
  • CreatedByUserID
  • LastUpdatedDate
  • LastUpdatedByUserID

User_Application_Permissions (User Level Permissions)

  • UserID
  • ApplicationID
  • Allow (Y/N/1/0)
  • CreatedDate
  • CreatedByUserID
  • LastUpdatedDate
  • LastUpdatedByUserID

UserPermissions (Descriptive/Attribute Table)

  • PermissionID
  • PermissionDescription
  • CreatedDate
  • CreatedByUserID
  • LastUpdatedDate
  • LastUpdatedByUserID

Users (Descriptive/Attribute Table)

  • UserID
  • UserName
  • CreatedDate
  • CreatedByUserID
  • LastUpdatedDate
  • LastUpdatedByUserID

Not the answer you're looking for? Browse other questions tagged or ask your own question.