[keycloak-user] Users assigned to roles

[Stian Thorgersen]

----- Original Message -----
> From: "Marek Posolda" <mposolda at redhat.com>
> To: "Stian Thorgersen" <stian at redhat.com>
> Cc: "Alarik Myrin" <alarik at zwift.com>, keycloak-user at lists.jboss.org
> Sent: Wednesday, November 19, 2014 12:23:19 PM
> Subject: Re: [keycloak-user] Users assigned to roles
> 
> On 19.11.2014 09:59, Stian Thorgersen wrote:
> >
> > ----- Original Message -----
> >> From: "Marek Posolda" <mposolda at redhat.com>
> >> To: "Alarik Myrin" <alarik at zwift.com>, keycloak-user at lists.jboss.org
> >> Sent: Wednesday, November 19, 2014 9:39:02 AM
> >> Subject: Re: [keycloak-user] Users assigned to roles
> >>
> >> I don't think we have this at this moment. Feel free to create jira, but
> >> no
> >> promise if/when we provide it...
> >>
> >> btv. it seems that your query doesn't handle composite roles, but really
> >> just
> >> those roles, which are directly assigned to user in user_role_mapping
> >> table.
> >> I don't know if you care about it... Fact is that doing single SQL query
> >> including composite roles is probably not doable unless you are on Oracle,
> >> which allows some kind of hierarchical queries afaik.
> > I reckon you could do it with an inner select that gets all roles that maps
> > to the given role or is the role.
> hmm... it seems to me that this will handle just role and it's parent
> roles, but not whole hierarchy. Like if I have 'roleA', which has
> composite subrole 'roleB' and this has another one 'roleC'. Now when I
> want to know users with permission of 'roleC', then with inner join, I
> am able to find users mapped in user_role_mappings table to 'roleC' and
> 'roleB' but those mapped to 'roleA' won't be returned imo.
> 
> Recursive hierarchical queries are supported by Oracle and I just
> figured from
> http://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL
> that there are more databases like PostgreSQL and MSSQL with some
> support via "Common table expression" . It might be interesting SQL
> exercise to return all users in single query though:-)

Yep, you're right

> 
> Marek
> >
> >> Marek
> >>
> >> On 18.11.2014 18:37, Alarik Myrin wrote:
> >>
> >>
> >>
> >> I was able to piece together a SQL query:
> >>
> >>
> >>
> >> SELECT u.username, u.first_name, u.last_name, role.name AS role
> >>
> >> FROM keycloak_role role
> >>
> >> INNER JOIN realm ON role.realm = realm.id
> >>
> >> INNER JOIN user_role_mapping rm ON role.id = rm.role_id
> >>
> >> INNER JOIN user_entity u ON rm.user_id = u.id
> >>
> >> WHERE realm.name =:1 AND role.name = :2
> >>
> >> It might be nice to do this in the web console though...
> >>
> >> On Tue, Nov 18, 2014 at 12:28 PM, Alarik Myrin < alarik at zwift.com > wrote:
> >>
> >>
> >>
> >> Is there any easy way to see which users have been assigned a given role?
> >>
> >>
> >>
> >> _______________________________________________
> >> keycloak-user mailing list keycloak-user at lists.jboss.org
> >> https://lists.jboss.org/mailman/listinfo/keycloak-user
> >>
> >>
> >> _______________________________________________
> >> keycloak-user mailing list
> >> keycloak-user at lists.jboss.org
> >> https://lists.jboss.org/mailman/listinfo/keycloak-user
> 
>