Best user role permissions database design practice? [closed]
As krokodilko wrote in his comment, it depends on the level of flexibility you need.
I have implemented role based permissions for one of my clients as follows:
- User (user id (PK), user name (unique), password (salted and hashed!), first name, last name, phone etc')
- Role (role id (PK), role name (unique), role description)
- Permission (permission id (PK), permission name (unique)) - the tabs / screens / actions goes here
- User To Role (user id, role id) - PK is both columns combined
- Role to Permission (role id, permission id) - PK is both columns combined
But my requirement was to be as flexible as possible, and it is a system that is still growing (6 years and counting).
I guess a lot of applications can have the user to role as a one to many relationship, instead of a many to many like in my case, but I wouldn't go hard coding permissions or role to permissions in any application.
Further explanation: Role based security database design on What the # do I know?