The current client database has dbo.Users Table and dbo.UserRoles table. dbo.Roles is a master table holding various role description. The image below captures what they have. They are looking to expand by giving specific users with certain roles additional permission/responsibility.
The FK on UserRoles table is
CONSTRAINT (PK_dbo.UserRoles) PRIMARY KEY CLUSTERED ( (UserId) ASC, (RoleId) ASC )
The expansion of role – an example would be
- Add a new column to dbo.UserRoles which I could use to store value based on bitwise operation. Refer to for example
- Business layer would determine and drive the UI based on what permission is enabled. This would be on user management screen where they would see additional options per user/role combo.
If a lead user requires approval from manager for purchase approval – then the above schema does not support it and I will have to add a new table to support this.
and use bitwise operation to store different permission per user. This only gets populated based on roles that have additional permission. This would be managed by Role-Permission table. A bit of approach taken from here.
Is there a cleaner approach to this without redoing the entire db?