11 Replies Latest reply on May 25, 2006 9:14 AM by chris griffith

    help w/ DatabaseServerLoginModule

    William Newbie

      Hey all, I'm still trying to get my users to login to my web app. I followed the directions here ...
      http://wiki.jboss.org/wiki/Wiki.jsp?page=SecureAWebApplicationUsingACustomForm but so far, not having much luck. I've tried to modify for the DatabaseServerLoginModule. So if anyone sees anything wrong, any help much appreciated.

      I copied the jbossmq example policy inside the ~/server/default/conf/login-config.xml file and added it at the end of the file.

       <application-policy name = "my-app">
       <authentication>
       <login-module code = "org.jboss.security.auth.spi.DatabaseServerLoginModule"
       flag = "required">
       <module-option name = "unauthenticatedIdentity">guest</module-option>
       <module-option name = "dsJndiName">java:/PostgresDS</module-option>
       <module-option name = "principalsQuery">SELECT password FROM Principals WHERE principalId=?</module-option>
       <module-option name = "rolesQuery">SELECT roleId, 'Roles' FROM Roles WHERE principalId=?</module-option>
       </login-module>
       </authentication>
       </application-policy>
      


      I then added the following to my web.xml.
       <security-constraint>
       <web-resource-collection>
       <web-resource-name>All resources</web-resource-name>
       <description>Protects all resources</description>
       <url-pattern>/*</url-pattern>
       </web-resource-collection>
      
       <auth-constraint>
       <role-name>WebAppUser</role-name>
       </auth-constraint>
       </security-constraint>
      
       <security-role>
       <role-name>WebAppUser</role-name>
       </security-role>
      
       <login-config>
       <auth-method>FORM</auth-method>
       <form-login-config>
       <form-login-page>/login.html</form-login-page>
       <form-error-page>/errors.html</form-error-page>
       </form-login-config>
       </login-config>
      


      And here's my jboss-web.xml
      <jboss-web>
       <context-root>/</context-root>
       <security-domain>java:/jaas/my-app</security-domain>
      </jboss-web>
      


      And here's index.html includes the following.
      <form method="POST" action="j_security_check">
       User Name: <input type="text" name="j_username" /><br />
       Password: <input type="password" name="j_password" /><br />
       <input type="submit" value="Login" />
      </form>
      


      And here's the table structure in my database.
      CREATE SEQUENCE principals_seq START 1000;
      CREATE TABLE Principals (
       principalId INTEGER DEFAULT NEXTVAL('principals_seq') PRIMARY KEY,
       principalName VARCHAR(64) NOT NULL,
       password VARCHAR(32) NOT NULL
      );
      
      CREATE SEQUENCE roles_seq START 100;
      CREATE TABLE Roles (
       roleId INTEGER DEFAULT NEXTVAL('roles_seq') PRIMARY KEY,
       principalId INTEGER NOT NULL,
       roleName VARCHAR(32) NOT NULL,
       roleGroup VARCHAR(32) NOT NULL
      );
      


        • 1. Re: help w/ DatabaseServerLoginModule
          Yogendra Rampuria Newbie

          hi...
          I think you are taking the wronge datatype for principalid. perhaps you would like to make principalname(username) as the primary key in principal and foreignkey in Roles.
          The parameter in the prepared statments (principalQuery & role query) is substituted with username.
          I hope it works for your. It defenitily worked for me

          • 2. Re: help w/ DatabaseServerLoginModule
            William Newbie

            I used INTEGER types because they take up less space and I will store them in other tables as well to show what users relate to other data. Is it standard practice to use these tables for security only and nothing else? Then I'll just have to store user names again elsewhere.

            Yeah, I'm still a bit confused there. I thought that if I could change the prepared statements used in the <module-option> tags in the application policy, then I should be able to set up any database schema I like.

            The DatabaseServerLoginModule is like a black box to me and there's very little documentation it how it works.

            • 3. Re: help w/ DatabaseServerLoginModule
              William Newbie

              Also, I just found this link ... http://docs.jboss.org/jbossas/jboss4guide/r4/html/ch8.chapter.html#ch8.dabaseserverloginmodule.sect
              which is very helpful and wondering if there are example implementations of the DatabaseServerLoginModule w/ a web app somewhere's.

              • 4. Re: help w/ DatabaseServerLoginModule
                chris griffith Expert

                William,

                What Yogendra was trying to explain is that the DatabaseServerLoginModule will take the string username and insert it into your principalsQuery and rolesQuery. Your queries must accept this string parameter. I agree It is good design to use indexes in your DB tables. So you need to change your queries to allow your tables to be searched by username not id.

                principalsQuery: SELECT password FROM principals WHERE principalName=?
                
                rolesQuery: SELECT roleName, roleGroup FROM roles, principals WHERE roles.principalID=principals.principalID AND principals.principalName=?
                


                I am having problems getting this to come out pretty, but I think you see what I have done. Let me know if you need more help. Also don't forget this valuable resource http://wiki.jboss.org/wiki/Wiki.jsp?page=JBossSX.

                enjoy, cgriffith

                • 5. Re: help w/ DatabaseServerLoginModule
                  William Newbie

                  I think I got this working (because now I don't get errors in my server output when logging in), but what I don't know is how to redirect a logged in user to another page.

                  The action to my index.html page which shows the user/pass fields is "j_security_check". Anyone?

                  • 6. Re: help w/ DatabaseServerLoginModule
                    William Newbie

                    Ugh, I wish these posts could be edited. Anyways, I should have given a bit more detail in the last post ...

                    my index.html includes user/pass fields and the action to post to is 'j_security_check". Once the user logins in, I have some Spring controllers that return some views in my ~/WEB-INF/views directory.

                    And it's there that I would like the user to be redirected to after a successful login.

                    • 7. Re: help w/ DatabaseServerLoginModule
                      chris griffith Expert

                      William,

                      The way Tomcat's CMA works (with considerations that you are using FormAuthentication)...


                      1 un-authenticated user requests a securred resource A

                      2 container caches url A, redirects to login page

                      3 user either authentciates or does not

                      4 if they do not, then container forwards them to error page

                      5 if they do, then container forwards them to url A


                      So the point is that you have to work with this design to do what you want.
                      You can do something like have your index.html redirect to some view if the user has authenticated.

                      cgriffith

                      • 8. Re: help w/ DatabaseServerLoginModule
                        Yogendra Rampuria Newbie

                        excellent is what i can say on that. It really sums up the auth entirely.

                        I have a point to add. The whloe source of confusion is the terms used in a lot of examples about auth. Especially in the principalQuery and rolesQuery. they should rather mention principalName and roleName instead of principalid/userid and roleid respectively. (I appreciate j2ee_junkies terms).

                        Also, in the rolesQuery, one needs 2 collumns being returned, first one should be the role name (e.g. manager, admin, etc.), and second is constant 'Roles', yes its literal. Don't know why but for me it didnt work w/o that.

                        --yogi

                        • 9. Re: help w/ DatabaseServerLoginModule
                          chris griffith Expert

                          Thanks yogi,

                          I will make one small adjustment to your last statement. The second column must be a constant value of 'Roles' for all the roles you want JBoss to use for container managed security. However, there is no restrictions other than that. So you could potentially have more than one role group. The key is that those would not be used by JBoss. I am not sure if you could use any roles not having a role group name of 'Roles' elsewhere in your app.

                          enjoy, cgriffith

                          • 10. Re: help w/ DatabaseServerLoginModule
                            William Newbie

                            For some reason, I still can't get this to work. I'm using JBoss-4.0.4GA. I try to his a url like localhost:8080/orders.ftl which I expect to invoke a SpringMVC controller, but first the user needs to authenticate, so if I understand this correctly, this URL gets cached and he gets pointed to the <form-login-page> as indicated in the web.xml. And I do, I type in username and passwd, but then I get the following 403 error.

                            HTTP Status 403 - Access to the requested resource has been denied
                            type Status report
                            message Access to the requested resource has been denied
                            description Access to the specified resource (Access to the requested resource has been denied) has been forbidden.
                            Apache Tomcat/5.5.17
                            


                            Anyone have ideas, any help much appreciated.

                            Here's my web.xml ...

                             ...
                             <security-constraint>
                             <web-resource-collection>
                             <description>Protects all resources</description>
                             <web-resource-name>All resources</web-resource-name>
                             <url-pattern>/*</url-pattern>
                             </web-resource-collection>
                            
                             <auth-constraint>
                             <role-name>WebAppUser</role-name>
                             </auth-constraint>
                            
                             <!-- user-data-constraint>
                             <transport-guarantee>NONE</transport-guarantee>
                             </user-data-constraint -->
                             </security-constraint>
                            
                             <login-config>
                             <auth-method>FORM</auth-method>
                            
                             <form-login-config>
                             <form-login-page>/index.html</form-login-page>
                             <form-error-page>/errors.html</form-error-page>
                             </form-login-config>
                             </login-config>
                            


                            I have my tables setup like such ... I simplified them as much as I could.

                            CREATE TABLE Users (
                             userName VARCHAR(64) PRIMARY KEY,
                             passwd VARCHAR(64)
                            );
                            
                            INSERT INTO Users VALUES('Admin', 'system');
                            
                            CREATE TABLE UserRoles (
                             userName VARCHAR(32) NOT NULL,
                             userRoles VARCHAR(32) NOT NULL
                            );
                            
                            INSERT INTO UserRoles VALUES('admin', 'Administrator');
                            


                            And in my login-config.xml file, I put this at the end.

                             <application-policy name="myapp">
                             <authentication>
                             <login-module
                             code="org.jboss.security.auth.spi.DatabaseServerLoginModule"
                             flag="required">
                            
                             <module-option name="unauthenticatedIdentity">
                             guest
                             </module-option>
                            
                             <module-option name="dsJndiName">
                             java:/PostgresDS
                             </module-option>
                            
                             <module-option name="principalsQuery">
                             SELECT passwd FROM Users userName WHERE userName=?
                             </module-option>
                            
                             <module-option name="rolesQuery">
                             SELECT userRoles, 'Roles' FROM UserRoles WHERE userName=?
                             </module-option>
                             </login-module>
                             </authentication>
                             </application-policy>
                            



                            • 11. Re: help w/ DatabaseServerLoginModule
                              chris griffith Expert

                              look at your insert statements and look for the inconsistencies. You would also do better troubleshooting if you had some TRACE logging of jboss security layer.

                              cgriffith