*
The moose likes JDBC and the fly likes Database design for Role/User based access to the application.. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Database design for Role/User based access to the application.." Watch "Database design for Role/User based access to the application.." New topic
Author

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

Shridhar. Blr.
Ranch Hand

Joined: Nov 23, 2000
Posts: 45
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

Joined: Jan 29, 2003
Posts: 8791
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.


A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
Rajesh Agarwal
Ranch Hand

Joined: Aug 01, 2005
Posts: 79
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.
 
wood burning stoves
 
subject: Database design for Role/User based access to the application..
 
Similar Threads
user authentication and dao
Don't understand defining roles in HFJS book
Login
Declarative security : mapping user to security roles
keeping different roles in an application