Database design for Multi level Rolebased authorization

Question

I'm designing an application in ASP.NET Web-From which require Multilevel role-based authorization and permission (CRUD Operation ) .

It required to do authorization users at accessing Web-Forms and CRUD Operations On Forms And tables of database .

Administrator of the application would be able to determine which roles has access to specific page and which operation authorized to do .

//More Info :

I'm using ASP.NET Web-Form 4.0 and Entity Framework 4.1 Database First approach.

I'm familiar with ASP.NET 2.0 Membership, Roles, Forms Authentication .

I would appreciate any recommendation or help in designing Database .

Answer 1

If I understand this correctly, you will then have a Set of Users (possibly subdivided in SubSets: each Subset being a Group).

Also:

1. A User is always part of at least one Group
2. A Group may be part of another Group
3. Actual ACLs are set at Group Level and defined for each Form or Table.

So:

    Item     Type  GroupId     C R U D
   Form001    F    ALL_USERS   N Y N N
   Form001    F    Sales       N R U N
   Form002    F    Admin       N Y Y Y
   All_FORMS  T:F  Admin       Y Y Y Y
   Tab-045A   D    Sales       Y Y Y Y

Which says: Form001 is a (F)orm and Everyone can Read it (but not modify its structure). Users from the SALES group can use Form 1 to update, too. Administrators (a Group) can modify, delete or use Form Form002 (but not create it...) Administrators can Create new Forms. Tab-045A is a table, whose records can be created/used/modified/deleted by users from the Sales group.

A few caveats:

• Please do everybody a favour and don't allow privileges to be set at the SINGLE USER level but always at group level only. A new user will automatically be part of ALL_USERS, and may be later added to (or removed from) other groups.
• It's probably best to have not only Tables and Forms, but "groups of forms" too (assuming forms can be created by end-users). If this is not the case, then the "C" field i the CRUD flags set becomes useless for forms. (Are forms something that a user group can design? or are they part of the application, as I suppose would be the case with Tables?).
• In general you have to define the appropriate semantics for Create/Read/Update/Delete. For tables I suppose this means CREATING A SINGLE RECORD (not a table), but for forms it's a bit confused at the moment.
• The table above is just an example and is not properly normalized. Depending on specifics you may have to split it into at least two tables, possibly more.
• In order to find out if User X can do Operation Y on Object Z you basically have to lookup the permission set on the Item/Group table, and see which groups User X is part of.
• You have to properly manage cases when a User is part of two distinct groups, and always choose the one with most permissions, or the union of all permissions.
• You have to manage the cases where a User is part of a Group which is a Subgroup of another one, and the privileges have been defined at the higher-level group.

Having to manage groups and subgroups you better look what kinds of facilities exist in your target DB to manage Tree structures. And you better brush up on the subject by looking at a few questions about this. Here is one to give you a start, but it's not the only one:

How to store a tree in SQL database