From: "Alarik Myrin" <alarik(a)zwift.com&gt; To: keycloak-user(a)lists.jboss.org Sent: Tuesday, November 18, 2014 6:37:31 PM Subject: Re: [keycloak-user] Users assigned to roles 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(a)zwift.com > wrote: Is there any easy way to see which users have been assigned a given role? _______________________________________________ keycloak-user mailing list keycloak-user(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/keycloak-user

From: "Marek Posolda" <mposolda(a)redhat.com&gt; To: "Alarik Myrin" <alarik(a)zwift.com&gt;, keycloak-user(a)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.

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(a)zwift.com > wrote: Is there any easy way to see which users have been assigned a given role? _______________________________________________ keycloak-user mailing list keycloak-user(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/keycloak-user _______________________________________________ keycloak-user mailing list keycloak-user(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/keycloak-user

From: "Marek Posolda" <mposolda(a)redhat.com&gt; To: "Stian Thorgersen" <stian(a)redhat.com&gt; Cc: "Alarik Myrin" <alarik(a)zwift.com&gt;, keycloak-user(a)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(a)redhat.com&gt; >> To: "Alarik Myrin" <alarik(a)zwift.com&gt;, keycloak-user(a)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:-)

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(a)zwift.com > wrote: >> >> >> >> Is there any easy way to see which users have been assigned a given role? >> >> >> >> _______________________________________________ >> keycloak-user mailing list keycloak-user(a)lists.jboss.org >> https://lists.jboss.org/mailman/listinfo/keycloak-user >> >> >> _______________________________________________ >> keycloak-user mailing list >> keycloak-user(a)lists.jboss.org >> https://lists.jboss.org/mailman/listinfo/keycloak-user