Database design for Role/User based access to the application..

[Shridhar. Blr.]

Hi All,

We want to implement Role/User based access to the application.

Can anyone tell me whats the optimized way of storing the data {User, Role, Access_Type etc} in the database.. The Roles might get added in the future so i dont want to maintain a single table to map User-Access_Type..



Access_Type -->
AT_1 | AT_2 |AT_N |
-------|-------|--------|------|
User_1 | | | |
-------|-------|--------|------|
User_2 | | | |
-------|-------|--------|------|


I want to maintain a table which will map user with the Access_Type, which should be mainatained in a different table..

Any help would be highly appreciated..

Thanks in Advacnce,

Shridhar..

[Stan James]

I think it's common to see user many-many to role, and role many-many to protected resource. The mapping from role to resource can contain an access control bitmap or fields (eg read write delete execute etc) though I haven't had to bother with that myself.

[Rajesh Agarwal]

Sridhar,

I could suggest you the following schema.

Master Tables
=============
1) User
2) Role
3) Access
4) User_Role_Map
5) Role_Access_Map

The 5th table will contain all data regarding which role has which access.