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