Is there a way to query for UserTypes? I've got an enumerated type implemented as a UserType, and I'd like to be able to select them based on some criteria.

The idea is this:

The UserType is SecurityRight

a User has a Many-to-Many relationship with SecurityRight

a User has a Many-to-Many relationship with SecurityGroup

a User has a Many-to-Many relationship with SecurityRole

a SecurityRole has a Many-to-Many relationship to SecurityRight

a SecurityGroup has a Many-to-Many relationship to SecurityRole

a SecurityGroup has a Many-to-Many relationship to SecurityRight

I want to get the set of SecurityRights granted to a user from any of these sources (and did I mention there's another Many-to-Many from User to SecurityRight with specifically denied SecurityRights? I need to subtract those).

I'm having trouble writing an HQL to query for SecurityRights because it's not a mapped type. I've got a stored proc that will get the correct set, and I can get Hibernate to call it as a named SQL query, but I can't get it to map back to SecurityRight objects.

Any ideas?

I've started to get there with this. Here's what I've got so far:

select securityright from User user inner join user.securityRights securityright where user.id = :userId

gets the security rights specifically granted to this user.

select securityright from User user inner join user.securityRoles role inner join role.securityRights securityright where user.id = :userId

gets the security rights associated with the security roles.

But since HQL doesn't support UNION there's no way to stich all the pieces together...

Uhm, throw the results into the same Set?

I created a view instead and mapped a Many-to-Many Set to it. I think this is a good example of why UNION would be good to add to HQL though.

Post it as a feature request for H3.