11 Replies Latest reply on Aug 3, 2006 4:48 PM by Sidney Zurch

    JDBC Realm

    Bjornn Faria Newbie

      Hi all,

      Following some hints, tips, articles I'm trying to develop a small sample using JAAS & JBoss.

      I think I have understood the concepts, but when I start to code, things became hard... hehehe.

      Maybe I have forgot something... I don't know exactaly.

      According what I have read, I proceed like this:

      Step 1: Declare a new application policy in login-config.xml at conf dir:

       <application-policy name = "jaas-webapp-domain">
       <authentication>
       <login-module code = "org.jboss.security.auth.spi.DatabaseServerLoginModule"
       flag = "required">
       <module-option name = "unauthenticatedIdentity">pirata</module-option>
       <module-option name = "dsJndiName">jdbc/jaasDS</module-option>
       <module-option name = "principalsQuery">SELECT PASSWORD FROM USERS WHERE LOGIN=?</module-option>
       <module-option name = "rolesQuery">SELECT LOGIN, 'Roles' FROM ROLES WHERE LOGIN=?</module-option>
       </login-module>
       </authentication>
       </application-policy>


      Step 2: Modify web.xml file:
       <security-constraint>
       <web-resource-collection>
       <web-resource-name>página dos pebas</web-resource-name>
       <url-pattern>/peba/*</url-pattern>
       </web-resource-collection>
       <auth-constraint>
       <role-name>peba</role-name>
       </auth-constraint>
       </security-constraint>
      
       <security-role>
       <role-name>peba</role-name>
       </security-role>
      
       <login-config>
       <auth-method>BASIC</auth-method>
       <realm-name>jaas-webapp-realm</realm-name>
       </login-config>


      Step 3: add jboss-web.xml inside WEB-INF dir. Like this:
      <?xml version="1.0" encoding="UTF-8"?>
       <jboss-web>
       <security-domain>java:/jaas/jaas-webapp-domain</security-domain>
       </jboss-web>


      I have tried to write de sql to query principals in a WRONG WAY (i.e. missing table name after from keywork, missing where keyword) just to see if jboss would complain, tell me that something was wrong.... But nothing happened. I think it isn't called...

      IE shows me the login window, I try 3 times, and I get a forbidden error... but no sql error appears in jboss log...

      Maybe tag X in file Y must have the same value as tag Z in file W and I didn't realize...

      What I'm doing wrong? should I find some reference to my security domain(java:/jaas/jaas-webapp-domain ) in jmx-console? I didn't found. My datasources are there... I found them...

      I hope that you can understand me e send me some help.
      Thanks in advance.

      Excuse me for any english mistakes.

        • 1. Re: JDBC Realm
          chris griffith Expert

          bjornn,

          So far all your config looks good. The only thing that looks odd is your queries. These must be made specific to your database. Specifically, your query...

          <module-option name = "rolesQuery">SELECT LOGIN, 'Roles' FROM ROLES WHERE LOGIN=?</module-option>
          


          just returns the login name. Is this correct? Other than checking these queries, you will want to look at trace logging output of the login event to see what is going on.

          good luck, griffith.

          • 2. Re: JDBC Realm
            Bjornn Faria Newbie

            I have copied the query from login-config.xml.... from the policy "JBossMQ, and just make the necessary modifications.... Both policies(mine and jbossmq) are in the same file, under conf dir.

            The JBossMQ policy looks like this:

             <!-- Security domain for JBossMQ -->
             <application-policy name = "jbossmq">
             <authentication>
             <login-module code = "org.jboss.security.auth.spi.DatabaseServerLoginModule"
             flag = "required">
             <module-option name = "unauthenticatedIdentity">guest</module-option>
             <module-option name = "dsJndiName">java:/DefaultDS</module-option>
             <module-option name = "principalsQuery">SELECT PASSWD FROM JMS_USERS WHERE USERID=?</module-option>
             <module-option name = "rolesQuery">SELECT ROLEID, 'Roles' FROM JMS_ROLES WHERE USERID=?</module-option>
             </login-module>
             </authentication>
             </application-policy>
            


            But wait... maybe i'm making a mistake... my tables contains these fields:
            user's table: login, password
            role's table: login(from user's table), role_name, role_group(I don't know the propose of this field, but in jboss wiki it seems mandatory to me)

            By looking the declaration of jbossmq policy, my role query must retrive the id of the role(login?!) and a static text 'roles'. Is this right? wrong? why?

            If my query looks bad, how it could be? Maybe a select *?

            Thanks.


            • 3. Re: JDBC Realm
              Sidney Zurch Novice

              Debugging authentication can be tricky. If you think JDBC is tricky, try LDAP.

              First, a point and a question:

              1) The JBoss Security documentation calls for the Data source to be defined as java:/jaasDS

              2) Do you have this datasource defined and is it visible in the jmx-console?
              You can find samples for your database in docs/examples/jca.

              Once the database is visible to JBoss, you will have to enable TRACE logging in conf/log4j.xml in order to diagnose authentication activity. You might have to change the "Threshold" for the log messages. Something like this should do it:



              <appender-ref ref="FILE"/>


              Let us know when you get it working.

              • 4. Re: JDBC Realm
                chris griffith Expert

                bjornn,

                Your problem then is your query. As I already mentioned, the sql configured in your security-domain must be specific for you database. For more details about the DatabaseServerLoginModule see http://wiki.jboss.org/wiki/Wiki.jsp?page=DatabaseServerLoginModule.

                cgriffith

                • 5. Re: JDBC Realm
                  Sidney Zurch Novice

                  Oops.

                  Forgot to escape the xml for conf/log4j.xml. (Haven't posted here for a while).

                   <category name="org.jboss.security.auth">
                   <priority value="TRACE" class="org.jboss.logging.XLevel"/>
                   <appender-ref ref="FILE"/>
                   </category>
                  



                  • 6. Re: JDBC Realm
                    Bjornn Faria Newbie

                    Hi j2ee_junkie, Hi zurchman.

                    Follow me:

                    "j2ee_junkie" wrote:
                    bjornn,

                    Your problem then is your query. As I already mentioned, the sql configured in your security-domain must be specific for you database. For more details about the DatabaseServerLoginModule see http://wiki.jboss.org/wiki/Wiki.jsp?page=DatabaseServerLoginModule.

                    cgriffith


                    I change my query to retrieve all columns of the user's and role's tables. look:
                     <application-policy name = "jaas-webapp-domain">
                     <authentication>
                     <login-module code = "org.jboss.security.auth.spi.DatabaseServerLoginModule"
                     flag = "required">
                     <module-option name = "unauthenticatedIdentity">pirata</module-option>
                     <module-option name = "dsJndiName">jdbc/jaasDS</module-option>
                     <module-option name = "principalsQuery">SELECT LOGIN, PASSWORD FROM USERS WHERE LOGIN=?</module-option>
                     <module-option name = "rolesQuery">SELECT LOGIN, ROLE_NAME, ROLE_GROUP FROM ROLES WHERE LOGIN=?</module-option>
                     </login-module>
                     </authentication>
                     </application-policy>
                    


                    Question: How jboss know what column represents the password, for example? How should it know? Same for role name... Maybe i'm missing some step in configuration...

                    I have modified my log4j.xml and get it when I try to login for the first time:
                    2006-08-02 16:20:35,141 TRACE [org.jboss.security.auth.login.XMLLoginConfigImpl] Begin getAppConfigurationEntry(jaas-webapp-domain), size=9
                    2006-08-02 16:20:35,141 TRACE [org.jboss.security.auth.login.XMLLoginConfigImpl] Begin getAppConfigurationEntry(jaas-webapp-domain), size=9
                    2006-08-02 16:20:35,141 TRACE [org.jboss.security.auth.login.XMLLoginConfigImpl] End getAppConfigurationEntry(jaas-webapp-domain), authInfo=AppConfigurationEntry[]:
                    [0]
                    LoginModule Class: org.jboss.security.auth.spi.DatabaseServerLoginModule
                    ControlFlag: LoginModuleControlFlag: required
                    Options:name=rolesQuery, value=SELECT LOGIN, ROLE_NAME, ROLE_GROUP FROM ROLES WHERE LOGIN=?
                    name=principalsQuery, value=SELECT LOGIN, PASSWORD FROM USERS WHERE LOGIN=?
                    name=unauthenticatedIdentity, value=pirata
                    name=dsJndiName, value=jdbc/jaasDS
                    
                    2006-08-02 16:20:35,141 TRACE [org.jboss.security.auth.login.XMLLoginConfigImpl] End getAppConfigurationEntry(jaas-webapp-domain), authInfo=AppConfigurationEntry[]:
                    [0]
                    LoginModule Class: org.jboss.security.auth.spi.DatabaseServerLoginModule
                    ControlFlag: LoginModuleControlFlag: required
                    Options:name=rolesQuery, value=SELECT LOGIN, ROLE_NAME, ROLE_GROUP FROM ROLES WHERE LOGIN=?
                    name=principalsQuery, value=SELECT LOGIN, PASSWORD FROM USERS WHERE LOGIN=?
                    name=unauthenticatedIdentity, value=pirata
                    name=dsJndiName, value=jdbc/jaasDS
                    
                    2006-08-02 16:20:35,141 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] initialize
                    2006-08-02 16:20:35,141 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] initialize
                    2006-08-02 16:20:35,157 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] Saw unauthenticatedIdentity=pirata
                    2006-08-02 16:20:35,157 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] Saw unauthenticatedIdentity=pirata
                    2006-08-02 16:20:35,157 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] DatabaseServerLoginModule, dsJndiName=jdbc/jaasDS
                    2006-08-02 16:20:35,157 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] DatabaseServerLoginModule, dsJndiName=jdbc/jaasDS
                    2006-08-02 16:20:35,157 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] principalsQuery=SELECT LOGIN, PASSWORD FROM USERS WHERE LOGIN=?
                    2006-08-02 16:20:35,157 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] principalsQuery=SELECT LOGIN, PASSWORD FROM USERS WHERE LOGIN=?
                    2006-08-02 16:20:35,157 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] rolesQuery=SELECT LOGIN, ROLE_NAME, ROLE_GROUP FROM ROLES WHERE LOGIN=?
                    2006-08-02 16:20:35,157 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] rolesQuery=SELECT LOGIN, ROLE_NAME, ROLE_GROUP FROM ROLES WHERE LOGIN=?
                    2006-08-02 16:20:35,157 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] login
                    2006-08-02 16:20:35,157 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] login
                    2006-08-02 16:20:35,157 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] abort
                    2006-08-02 16:20:35,157 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] abort
                    2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.login.XMLLoginConfigImpl] Begin getAppConfigurationEntry(jaas-webapp-domain), size=9
                    2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.login.XMLLoginConfigImpl] Begin getAppConfigurationEntry(jaas-webapp-domain), size=9
                    2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.login.XMLLoginConfigImpl] End getAppConfigurationEntry(jaas-webapp-domain), authInfo=AppConfigurationEntry[]:
                    [0]
                    LoginModule Class: org.jboss.security.auth.spi.DatabaseServerLoginModule
                    ControlFlag: LoginModuleControlFlag: required
                    Options:name=rolesQuery, value=SELECT LOGIN, ROLE_NAME, ROLE_GROUP FROM ROLES WHERE LOGIN=?
                    name=principalsQuery, value=SELECT LOGIN, PASSWORD FROM USERS WHERE LOGIN=?
                    name=unauthenticatedIdentity, value=pirata
                    name=dsJndiName, value=jdbc/jaasDS
                    
                    2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.login.XMLLoginConfigImpl] End getAppConfigurationEntry(jaas-webapp-domain), authInfo=AppConfigurationEntry[]:
                    [0]
                    LoginModule Class: org.jboss.security.auth.spi.DatabaseServerLoginModule
                    ControlFlag: LoginModuleControlFlag: required
                    Options:name=rolesQuery, value=SELECT LOGIN, ROLE_NAME, ROLE_GROUP FROM ROLES WHERE LOGIN=?
                    name=principalsQuery, value=SELECT LOGIN, PASSWORD FROM USERS WHERE LOGIN=?
                    name=unauthenticatedIdentity, value=pirata
                    name=dsJndiName, value=jdbc/jaasDS
                    
                    2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] initialize
                    2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] initialize
                    2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] Saw unauthenticatedIdentity=pirata
                    2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] Saw unauthenticatedIdentity=pirata
                    2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] DatabaseServerLoginModule, dsJndiName=jdbc/jaasDS
                    2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] DatabaseServerLoginModule, dsJndiName=jdbc/jaasDS
                    2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] principalsQuery=SELECT LOGIN, PASSWORD FROM USERS WHERE LOGIN=?
                    2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] principalsQuery=SELECT LOGIN, PASSWORD FROM USERS WHERE LOGIN=?
                    2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] rolesQuery=SELECT LOGIN, ROLE_NAME, ROLE_GROUP FROM ROLES WHERE LOGIN=?
                    2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] rolesQuery=SELECT LOGIN, ROLE_NAME, ROLE_GROUP FROM ROLES WHERE LOGIN=?
                    2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] login
                    2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] login
                    2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] abort
                    2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] abort
                    


                    These are aditional information that I got from the moment that I change log4j.xml.

                    Maybe I should take the source to debug...

                    Excuse me for boring...

                    • 7. Re: JDBC Realm
                      Bjornn Faria Newbie

                      hi,

                      I have tried to create a new file named sample-login-config.xml and put it into /conf dir.

                      I have created a service file named sample-login-config-service.xml mapping the new config file and put it into /depoy dir.

                      Nothing happened.... I don't know what to do...

                      Am I supposed to find any reference to java:/jaas in jmx-console? Because I didn't found any.... My Datasource is listed in jmx-console as it would be

                      My security domain is java:/jaas/jaas-webapp-domain and I think it would appear in jmx-console.

                      Please help me.

                      Thanks.

                      • 8. Re: JDBC Realm
                        Sidney Zurch Novice

                        I suspect that you do not have the datasource defined correcly: the examples would have you use java:/jaasDS.

                        And if there's no reference to jaasDS in jmx-console, you probably have not deployed it correctly.

                        Read over my earlier post for details.

                        • 9. Re: JDBC Realm
                          Bjornn Faria Newbie

                          Hi zurchman,

                          My datasource has some problemas, like as mismatch in url connection... but I was guess that jboss would complain... I work hard to discover it.

                          But I can't solve the problem yet. Now, after just one attempt I get a 403 error. The server log print this lines below just after my login fail:

                          2006-08-03 16:55:54,983 DEBUG [org.jboss.security.plugins.JaasSecurityManagerService] Created securityMgr=org.jboss.security.plugins.JaasSecurityManager@182b9b5
                          2006-08-03 16:55:54,983 DEBUG [org.jboss.security.plugins.JaasSecurityManager.JaasSampleDBRealm] CachePolicy set to: org.jboss.util.TimedCachePolicy@16e46f5
                          2006-08-03 16:55:54,983 DEBUG [org.jboss.security.plugins.JaasSecurityManagerService] setCachePolicy, c=org.jboss.util.TimedCachePolicy@16e46f5
                          2006-08-03 16:55:54,983 DEBUG [org.jboss.security.plugins.JaasSecurityManagerService] Added JaasSampleDBRealm, org.jboss.security.plugins.SecurityDomainContext@f4a376 to map
                          2006-08-03 16:55:54,999 DEBUG [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory] Using properties: {user=sa, password=--hidden--}
                          2006-08-03 16:55:54,999 DEBUG [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory] Checking driver for URL: jdbc:hsqldb:hsql://localhost:9001/jaas
                          2006-08-03 16:55:54,999 DEBUG [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory] Driver already registered for url: jdbc:hsqldb:hsql://localhost:9001/jaas
                          2006-08-03 16:55:55,014 DEBUG [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory] Using properties: {user=sa, password=--hidden--}
                          2006-08-03 16:55:55,077 DEBUG [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory] Using properties: {user=sa, password=--hidden--}
                          2006-08-03 16:55:55,077 DEBUG [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory] Using properties: {user=sa, password=--hidden--}
                          2006-08-03 16:55:55,092 DEBUG [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory] Using properties: {user=sa, password=--hidden--}


                          these lines are just after:
                          2006-08-03 16:53:16,587 INFO [org.jboss.system.server.Server] JBoss (MX MicroKernel) [4.0.2 (build: CVSTag=JBoss_4_0_2 date=200505022023)] Started in 34s:170ms
                          


                          Why does jboss try to "connect" four times using LocalManagedConnectionFactory(last 4 lines above)? Do you think I still have some problem with my datasource?

                          Thanks again!

                          • 10. Re: JDBC Realm
                            chris griffith Expert

                            bjornn,

                            You are making more work for yourself. I already told you that your query is not correct and pointed you to documentation on the LM. If the info you posted about your database is correct, and assuming your table names are USERS and ROLES, your queries should be as so...

                            ...
                            <module-option name = "principalsQuery">SELECT PASSWORD FROM USERS WHERE LOGIN=?</module-option>
                            <module-option name = "rolesQuery">SELECT ROLE_NAME, 'Roles' FROM ROLES WHERE LOGIN=?</module-option>
                            ...
                            


                            The example for jms is just that, an example. You must customize the queries to match your database. Also, since you have gone down the road of changing your datasource, make sure your datasource points to your database.

                            cgriffith

                            • 11. Re: JDBC Realm
                              Sidney Zurch Novice

                               

                              "bjornn" wrote:
                              Do you think I still have some problem with my datasource?


                              I think so, but it looks like you're trying to use Hypersonic for your user/password tables - which I've never done.

                              You might try this change and see what happens:

                              <module-option name = "dsJndiName">java:/DefaultDS</module-option>