Hello, I was going to ask this here, if it is even possible to use this extension. The client application has web-constraints/auth-constraints roles-names in place for resources of the application. Those Roles are currently stored in database and can be selected with SQL query. In looking at singular-keycloak-database-federation it does say in the sql query help sections that you can pull those attributes:
Select to query all users you must return at least: "id". "username", "email" (optional), "firstName" (optional), "lastName" (optional). Any other parameter can be mapped by aliases to a realm scope
The select I have written as an example is:
select u.user_id as "id", u.user_name as "username", u.email_addr as "email", substr(u.full_name, 1, instr(u.full_name, ' ')-1) as "firstName", substr(u.full_name, instr(u.full_name, ' ')+1) as "lastName", r.role_name as "Roles"
from MY_USER u left outer join my_user_role ur on ur.user_id = u.user_id left outer join my_role r on r.role_id = ur.role_id where u.active_ind = 'Y' order by u.user_id
On inspection of a user searched for: the Roles are mapped to the user in the user's attributes section.
The issue is, how do I then pull those Roles as either realm_access.roles or realm_access.${client_id}.roles?
Is this the best way to handle roles from a database? Is there another extension that should be used?
Hello, I was going to ask this here, if it is even possible to use this extension. The client application has web-constraints/auth-constraints roles-names in place for resources of the application. Those Roles are currently stored in database and can be selected with SQL query. In looking at singular-keycloak-database-federation it does say in the sql query help sections that you can pull those attributes:
Select to query all users you must return at least: "id". "username", "email" (optional), "firstName" (optional), "lastName" (optional). Any other parameter can be mapped by aliases to a realm scope
The select I have written as an example is:
select u.user_id as "id", u.user_name as "username", u.email_addr as "email", substr(u.full_name, 1, instr(u.full_name, ' ')-1) as "firstName", substr(u.full_name, instr(u.full_name, ' ')+1) as "lastName", r.role_name as "Roles"
from MY_USER u left outer join my_user_role ur on ur.user_id = u.user_id left outer join my_role r on r.role_id = ur.role_id where u.active_ind = 'Y' order by u.user_id
On inspection of a user searched for: the Roles are mapped to the user in the user's attributes section.
The issue is, how do I then pull those Roles as either realm_access.roles or realm_access.${client_id}.roles?
Is this the best way to handle roles from a database? Is there another extension that should be used?