The current system I am working on has the following database security setup:

Security is based on roles and components in the system.  In both cases as the system grows the complexity of the security tables increase.  For each specific role that is out there, a new role will need to be created.  For each new component (Menu, Button, DataGrid, Page) a new component needs to be created.  I was discussing with what could be called our business analyst yesterday about the current security model.  Not only is it not scalable, but it's very hard to configure through the application. To put further salt into the wounds, they used the PrimaryKey's for RoleName and ComponentNames in the system.  This means when securing something you need to use 300 instead of "Administrator".  Imagine the fun I have when I'm going across applications where the databases are different.  On development the ID is 298, Alpha is 302, Beta is 301, and Production is 300.  Yeah, lots of fun.

Anyway, it got me to thinking.  I've ranted about using a Rights system in the past, but I want something that's easier and even more configurable and simple to maintain.  I was working on something else and then the diagram below flashed into my head.  I had to get it out:

This has essentally taken my existing simple User, Role, and Right tables and added two specific new tables, System and User_Right.  The new System table is in response to what happened to the ex-developers here at work.  Instead of a single system, they needed 10 on the same application.  They came up with the "Component" table above.  I can't tell you what a nightmare that setup is.  In this setup, you would simply tell the app which system had which roles.  If you needed to restrict users to certain systems you would only give them the ability to through the "User_System" table.

The "User_Right" table was also added so that instead of just having Roles for a particular User you could allow/deny certain Rights.  While our current system does not require this, we've had to make changes so that specific User have certain Roles.  The problem is, they don't need ALL of the "Rights" (even though in the current system this doesn't exist) that they are granted.

Caveat: I created this in 5 minutes of spare time while programming something totally different and have never put this into a working app.  I probably will, but there will probably be future tweaks.  If you tweak or use, please let me know.  Gratsie.