Hi all,

I'm writing a simple access management app and ran into a weird mapping scenario.

There're 3 entities -- Role, Resource, Permission -- and the relationship b/w them is: a Role has 0 to N Permissions on a Resource.

Implementing this as a link table and I get something like --


The problem I'm facing is (RES_ID, PERM_ID) is represented as a PermissionSet class that has a Resource and a collection of Permissions, and I have no idea how to map this in Hibernate!

I've tried separating this into smaller tables but that didn't work any better, plus I end up letting database tables dictate my object model. According to this article (see 4.1.1), my situation is unavoidable and is OK, so I'm assuming Hibernate has a way to handle it. Right?

Any help is greatly appreciated.

jd

I have traditionally implemented user/role/permission security using 5 tables. One per above-mentioned item, and two link tables that implement many-to-many relationships. This allows flexibility in the security model and keeps the roles manageable. Certainly this is one of a bazillion ways to do it.

Without posting the whole ball of wax, here's the role->permission mapping snippet:



Hope this gives you some sort of guidance.

-Chris

Perhaps my previous example was a bit misleading (my apologies :D), but there is a relationship b/w Resource and Permission as well --



That means a Role may have access to N Resources, and each Resource has a set of Permissions telling the Role on what action can be performed on it. Having independent link tables from Role to Resource and Permission doesn't quite reflect the need to specify which set of Permissions apply to which Resource....

jd

Yes, I was only giving you an example of how you'd map the many-to-many.

Your example squashes three entities into one table, at the cost of very heavy duplication of data. Unless your schema is dictated I would not do it that way.

In normalized form, it takes 5 tables to cleanly implement many-to-many relationships between 3 entities (unless I'm really missing something).

Perhaps someone else can shed some light?

-Chris

The "5 tables" approach works fine when Resource and Permission are independent. My situation is a bit twisted because a Role can't have Permissions directly, meaning Permissions must be assigned in the context of a Resource.

For example, with the "5 tables" schema, I can model:

* Role R has access to Resource A, B, and C.
* Role R has Permission X, Y, and Z.

But my app needs to know things like:

* Does Role R has Permission X on Resource A?
* What Permissions does Role R have on Resource B?

I've thought about doing this 2 ways:

1) Move (RES_ID, PERM_ID) to its own table. This results in the following:

But this layout doesn't work any better than before because RES_ID and PERM_ID are still duplicated in table PERMISSION_SET, so why not just keep them with ROLE_ID? Moreover, there's the problem of how to map PERMISSION_SET to Java class PermissionSet because Resource is duplicated:


2) Move (ROLE_ID, RES_ID) to its own table. This only groups the columns differently and has the same problem with #1. Plus, it destroys the object model in which PermissionSet has a Resource and its associated Permissions.

Am I just missing something obvious? Is the design itself flawed?

jd

It looks to me like a three-level hierarchy:

Roles have Resources.
Resources have Permissions.

It also *looks*, though it's not clear from your schema, as though Resources are not shared among Roles, and Permissions are not shared among Resources. i.e. it would not be valid for Role 1 to have Resource 100 and for Role 2 to *also* have Resource 100.

Hence, it seems like a straight one-to-many parent-child structure. I would map it with Role as the parent class, with an inverse one-to-many to Resource, and Resource with the complementary many-to-one to Role. Then likewise with Resource as the parent and Permission as the child.

This would wind up with five tables, Role, role_to_resource, Resource, resource_to_permission, and Permission.

If Resources *are* shared, then this won't work. If that's the case, then you need to clarify the intended model a bit more.

Cheers,
Rob

Oh, yes: you certainly could just implement this with three tables, Role, Resource (many-to-one to Role), and Permission (two many-to-ones to Resource *and* Role). The main problem with that would be that changing the Role of a given Resource would leave all the Permissions for that Resource stuck with inconsistent role_ids. In other words, it's very denormalized. (Are you familiar with normalization theory? If not, now's a real good time to start ;-)

Cheers,
Rob

Resources *are* shared (e.g. server, printer), and it's not a straight parent/child relationship.

In my model a Role must be assigned Permissions in the context of a Resource, meaning:

* A Role must be assigned a Resource and its Permissions at the same time.
* Assigning Permissions to a Resource w/o a Role is meaningless, because Resource can be shared and I need to know which Role has this combination of Resource and Permissions.

For example, if I give Role A access to Resource S with Permissions (X,Y,Z), it doesn't mean Role B has access to S, and if it does, it doesn't mean Role B has the same Permissions for accessing S. This is why I have the problem with mapping a 3-way relationship.

Any idea? :)

jd

OK, so you really *do* want the model you said you wanted :-D Go figure!

So what about this? Role has a collection of ResourcePermissions (a one-to-many map with the Role as the index, the Resource as the key, and ResourcePermissions as the elements). A ResourcePermission is an object that has a many-to-one to a Resource, and a Set of Permissions.

So to give a Role a set of permissions on a Resource, you first create a ResourcePermissions object which associates those Permissions with that Resource, then you associate those ResourcePermissions with the Role.

That would certainly work. The main downside of it is that ResourcePermissions become first-class objects with IDs of their own, which isn't exactly what you wanted, but if you have a strict parent-child relationship from Role to ResourcePermission (with cascade="all") then Hibernate will handle most of the grunt work for you.

If you really, really, really want to have the table mapped the way you originally had it, then you are looking at mapping a ternary association. This is something I personally tried to do with Hibernate 2.1.2, but it was badly broken back then.

Your problem is that you actually have a multi-valued map -- i.e. there are multiple permissions for a given role/resource combination. The issue there is not really with the database, but actually with Java -- there is no way to get Java to store multiple values in a Map for a given key.

So that's one way to think about your problem: you are trying to use Hibernate to map your relational structure into Java. What do you want your Java code that manipulates these associations to look like? In other words, if you were writing POJO code to implement this, forgetting all about the database, what would its structure be? If you can get a happy answer to this, you'll probably see your way to implementing it in Hibernate.

If you want to get into mega-rocket science, check out Hibernate UserCollectionTypes which let you get almost full control over collection semantics. In *fact*, this reminds me that I actually had an almost identical problem!!! Here's the thread on that one including some comments from Gavin King.

Finally, if this has been at all helpful, could you please rate my answers accordingly? ;-)

Cheers!
Rob

Hi Rob,

That's some really good pointers you gave, I'll definitely check them out. And don't worry about the credit points, I always give credit where credit is due! :)


jd