Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Shridhar. Blr.
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
(instanceof Sidekick)
Ranch Hand
Posts: 8791
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic