Wildfly 16 JDBC Realm Authentication with 2 Databases

[nschweig]

Hi

I want to use JDBC Realm Authentication. My database structure is the following:

Database 1 (act_user_id)

Username (ID_)     Password (PWD_)

Database2 (act_id_membership)

Username(USER_ID)      Rolename(GROUP_ID)

How can I configure the principal query to look in database1 and the roles query look in database 2?   (Same Datasource)

I tried to configure the roles query as second query via the web console, but I need a roles-query and it does not work.

Exception is:

Could not execute query "SELECT PWD_ FROM act_id_user WHERE ID_ = ?"
    at org.wildfly.security.auth.realm.jdbc.JdbcSecurityRealm$JdbcRealmIdentity.getIdentity(JdbcSecurityRealm.java:237)
    at org.wildfly.security.auth.realm.jdbc.JdbcSecurityRealm$JdbcRealmIdentity.verifyEvidence(JdbcSecurityRealm.java:175)
    at org.wildfly.security.auth.server.ServerAuthenticationContext$NameAssignedState.verifyEvidence(ServerAuthenticationContext.java:1978)
...
Caused by: java.sql.SQLException: Column Index out of range, 2 > 1. 

Does anyone have ideas? Thanks a lot.

Nicole

[mayerw01]

I am not sure understandig you correctly. But are you refering to 2 databases or 2 tables in the same database?
My understanding is that a datasource should point only to 1 database table (via the connection url).
And when refering to the JDBC Realm, did you set the clear-password-mapper to index 1?
I am also not sure what you intend to do in your 2nd query.

And if you want to extract the role I guess the query should be a bit different eg like mentioned in WildFly Elytron Security chap 11.5.3.

But if you are refering to 2 tables I dont think you would need 2 queries in your case.

So a query like

SELECT u.PWD_, r.GROUP_ID

  FROM act_user_id u, act_id_membership r

  WHERE u.ID_ = ?

    AND    u.ID_ = r.USER_ID

Should also be fine (chap 4.1.3)
In case you have  more than one database you would probably have to change the FROM part to
FROM database1.act_user_id u, database2.act_id_membership r

[nschweig]

Thanks for your help.

I am sorry, I was a little bit confused. Of course, it is one database and different tables.

It works like it is described in 11.5.3.