2 Replies Latest reply on Dec 4, 2019 3:18 AM by Nicole Müller

    Wildfly 16 JDBC Realm Authentication with 2 Databases

    Nicole Müller Novice

      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

        • 1. Re: Wildfly 16 JDBC Realm Authentication with 2 Databases
          Wolfgang Mayer Master

          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

          • 2. Re: Wildfly 16 JDBC Realm Authentication with 2 Databases
            Nicole Müller Novice

            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.