3 Replies Latest reply on Feb 17, 2002 2:32 PM by luke_t

    select Password from Principals where PrincipalID=?

    jimrand
    jimrand Feb 14, 2002 10:31 AM

      DatabaseServerLoginModule - Scott Stark's article using JBoss 2.4.4 and Sybase Adaptive Server Version 6.

      Server side auth.conf:

      This works:

      org.jboss.security.auth.spi.DatabaseServerLoginModule required
      dsJndiName="java:/AdvWorksDB" Note: Points to Sybase
      principalsQuery="select Password from DBA.Principals where PrincipalID=?"
      rolesQuery="select Role, RoleGroup from DBA.Roles where PrincipalID=?"
      unauthenticatedIdentity=nobody
      ;

      This doesn't work:


      principalsQuery="select Password from DBA.Users where PrincipalID=?"

      or any other variation for that matter - using a view with different field names, etc.

      In order to get the security to work, it appears that the table structure has to be identical to the example -- any variation causes it to fail.

      Any thoughts?

      Thanks

      • 4482 Views
      • Tags:
        • 1. Re: select Password from Principals where PrincipalID=?
          luke_t Feb 16, 2002 9:52 AM (in response to jimrand)

          > In order to get the security to work, it appears that the table structure has to be identical to the example -- any variation causes it to fail.

          > Any thoughts?

          Maybe you can tell use how it fails?? Any stack traces in the server log for example??

          Luke.

            • Actions
          • 2. Re: select Password from Principals where PrincipalID=?
            jimrand
            jimrand Feb 17, 2002 12:17 PM (in response to jimrand)

            ===============
            Set 1 Server Log
            ===============
            [12:02:17,288,Default] JBoss-2.4.4 Started in 0m:40s.308
            [12:02:30,678,LRUEnterpriseContextCachePolicy] Resized cache for bean PrivateSession: old capacity = 1000, new capacity = 50
            [12:02:37,868,Default] PublicSessionBean.ejbCreate() called
            [12:02:37,878,Default] PublicSessionBean.echo, arg=Hello
            [12:02:37,878,Default] PublicSessionBean.echo, callerPrincipal=caller_java
            [12:02:37,888,Default] PublicSessionBean.echo, isCallerInRole('EchoUser')=true
            [12:02:38,409,Default] PrivateSessionBean.ejbCreate() called
            [12:02:38,489,Default] PublicSessionBean.echo, created PrivateSession
            [12:02:38,509,Default] PrivateSessionBean.echo, arg=Hello
            [12:02:38,509,Default] PrivateSessionBean.echo, callerPrincipal=caller_java
            [12:02:38,519,Default] PrivateSessionBean.echo, isCallerInRole('InternalUser')=false
            [12:02:38,619,SecurityInterceptor] Insufficient method permissions, principal=java, method=create, requiredRoles=[InternalUser], principalRoles=[Echo]
            [12:02:38,629,PrivateSession] TRANSACTION ROLLBACK EXCEPTION:

            ================
            Set 2 Server Log
            ================
            [12:06:59,494,Default] JBoss-2.4.4 Started in 0m:23s.213
            [12:07:11,471,DatabaseServerLoginModule] Bad password for username=java
            [12:07:11,491,example3] Login failure
            javax.security.auth.login.FailedLoginException: Password Incorrect/Password Required
            at org.jboss.security.auth.spi.UsernamePasswordLoginModule.login(UsernamePasswordLoginModule.java:149)
            at java.lang.reflect.Method.invoke(Native Method)
            at javax.security.auth.login.LoginContext.invoke(LoginContext.java:595)
            at javax.security.auth.login.LoginContext.access$000(LoginContext.java:125)
            at javax.security.auth.login.LoginContext$3.run(LoginContext.java:531)
            at java.security.AccessController.doPrivileged(Native Method)
            at javax.security.auth.login.LoginContext.invokeModule(LoginContext.java:528)
            at javax.security.auth.login.LoginContext.login(LoginContext.java:449)
            at org.jboss.security.plugins.JaasSecurityManager.defaultLogin(JaasSecurityManager.java:394)
            at org.jboss.security.plugins.JaasSecurityManager.authenticate(JaasSecurityManager.java:361)
            at org.jboss.security.plugins.JaasSecurityManager.isValid(JaasSecurityManager.java:217)
            at org.jboss.ejb.plugins.SecurityInterceptor.checkSecurityAssociation(SecurityInterceptor.java:163)
            at org.jboss.ejb.plugins.SecurityInterceptor.invokeHome(SecurityInterceptor.java:91)
            at org.jboss.ejb.plugins.LogInterceptor.invokeHome(LogInterceptor.java:103)
            at org.jboss.ejb.StatelessSessionContainer.invokeHome(StatelessSessionContainer.java:268)
            at org.jboss.ejb.plugins.jrmp.server.JRMPContainerInvoker.invokeHome(JRMPContainerInvoker.java:387)
            at java.lang.reflect.Method.invoke(Native Method)
            at sun.rmi.server.UnicastServerRef.dispatch(Unknown Source)
            at sun.rmi.transport.Transport$1.run(Unknown Source)
            at java.security.AccessController.doPrivileged(Native Method)
            at sun.rmi.transport.Transport.serviceCall(Unknown Source)
            at sun.rmi.transport.tcp.TCPTransport.handleMessages(Unknown Source)
            at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(Unknown Source)
            at java.lang.Thread.run(Unknown Source)
            [12:07:11,491,SecurityInterceptor] Authentication exception, principal=java
            [12:07:11,872,DatabaseServerLoginModule] Bad password for username=java
            [12:07:11,872,example3] Login failure
            javax.security.auth.login.FailedLoginException: Password Incorrect/Password Required
            at org.jboss.security.auth.spi.UsernamePasswordLoginModule.login(UsernamePasswordLoginModule.java:149)
            at java.lang.reflect.Method.invoke(Native Method)
            at javax.security.auth.login.LoginContext.invoke(LoginContext.java:595)
            at javax.security.auth.login.LoginContext.access$000(LoginContext.java:125)
            at javax.security.auth.login.LoginContext$3.run(LoginContext.java:531)
            at java.security.AccessController.doPrivileged(Native Method)
            at javax.security.auth.login.LoginContext.invokeModule(LoginContext.java:528)
            at javax.security.auth.login.LoginContext.login(LoginContext.java:449)
            at org.jboss.security.plugins.JaasSecurityManager.defaultLogin(JaasSecurityManager.java:394)
            at org.jboss.security.plugins.JaasSecurityManager.authenticate(JaasSecurityManager.java:361)
            at org.jboss.security.plugins.JaasSecurityManager.isValid(JaasSecurityManager.java:217)
            at org.jboss.ejb.plugins.SecurityInterceptor.checkSecurityAssociation(SecurityInterceptor.java:163)
            at org.jboss.ejb.plugins.SecurityInterceptor.invokeHome(SecurityInterceptor.java:91)
            at org.jboss.ejb.plugins.StatefulSessionInstanceInterceptor.invokeHome(StatefulSessionInstanceInterceptor.java:119)
            at org.jboss.ejb.plugins.TxInterceptorCMT.invokeNext(TxInterceptorCMT.java:142)
            at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:347)
            at org.jboss.ejb.plugins.TxInterceptorCMT.invokeHome(TxInterceptorCMT.java:86)
            at org.jboss.ejb.plugins.LogInterceptor.invokeHome(LogInterceptor.java:103)
            at org.jboss.ejb.StatefulSessionContainer.invokeHome(StatefulSessionContainer.java:324)
            at org.jboss.ejb.plugins.jrmp.server.JRMPContainerInvoker.invokeHome(JRMPContainerInvoker.java:387)
            at java.lang.reflect.Method.invoke(Native Method)
            at sun.rmi.server.UnicastServerRef.dispatch(Unknown Source)
            at sun.rmi.transport.Transport$1.run(Unknown Source)
            at java.security.AccessController.doPrivileged(Native Method)
            at sun.rmi.transport.Transport.serviceCall(Unknown Source)
            at sun.rmi.transport.tcp.TCPTransport.handleMessages(Unknown Source)
            at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(Unknown Source)
            at java.lang.Thread.run(Unknown Source)
            [12:07:11,882,SecurityInterceptor] Authentication exception, principal=java
            [12:07:11,892,PrivateSession] TRANSACTION ROLLBACK EXCEPTION:
            javax.transaction.TransactionRolledbackException: checkSecurityAssociation; nested exception is:
            java.lang.SecurityException: Authentication exception, principal=java; nested exception is:
            java.rmi.RemoteException: checkSecurityAssociation; nested exception is:
            java.lang.SecurityException: Authentication exception, principal=java
            java.rmi.RemoteException: checkSecurityAssociation; nested exception is:
            java.lang.SecurityException: Authentication exception, principal=java
            java.lang.SecurityException: Authentication exception, principal=java
            at org.jboss.ejb.plugins.SecurityInterceptor.checkSecurityAssociation(SecurityInterceptor.java:167)
            at org.jboss.ejb.plugins.SecurityInterceptor.invokeHome(SecurityInterceptor.java:91)
            at org.jboss.ejb.plugins.StatefulSessionInstanceInterceptor.invokeHome(StatefulSessionInstanceInterceptor.java:119)
            at org.jboss.ejb.plugins.TxInterceptorCMT.invokeNext(TxInterceptorCMT.java:142)
            at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:347)
            at org.jboss.ejb.plugins.TxInterceptorCMT.invokeHome(TxInterceptorCMT.java:86)
            at org.jboss.ejb.plugins.LogInterceptor.invokeHome(LogInterceptor.java:103)
            at org.jboss.ejb.StatefulSessionContainer.invokeHome(StatefulSessionContainer.java:324)
            at org.jboss.ejb.plugins.jrmp.server.JRMPContainerInvoker.invokeHome(JRMPContainerInvoker.java:387)
            at java.lang.reflect.Method.invoke(Native Method)
            at sun.rmi.server.UnicastServerRef.dispatch(Unknown Source)
            at sun.rmi.transport.Transport$1.run(Unknown Source)
            at java.security.AccessController.doPrivileged(Native Method)
            at sun.rmi.transport.Transport.serviceCall(Unknown Source)
            at sun.rmi.transport.tcp.TCPTransport.handleMessages(Unknown Source)
            at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(Unknown Source)
            at java.lang.Thread.run(Unknown Source)


            ================
            Set 3 Server Log
            ================

            [12:13:00,513,Default] JBoss-2.4.4 Started in 0m:23s.524
            [12:13:09,807,DatabaseServerLoginModule] Bad password for username=java
            [12:13:09,827,example3] Login failure
            javax.security.auth.login.FailedLoginException: Password Incorrect/Password Required
            at org.jboss.security.auth.spi.UsernamePasswordLoginModule.login(UsernamePasswordLoginModule.java:149)
            at java.lang.reflect.Method.invoke(Native Method)
            at javax.security.auth.login.LoginContext.invoke(LoginContext.java:595)
            at javax.security.auth.login.LoginContext.access$000(LoginContext.java:125)
            at javax.security.auth.login.LoginContext$3.run(LoginContext.java:531)
            at java.security.AccessController.doPrivileged(Native Method)
            at javax.security.auth.login.LoginContext.invokeModule(LoginContext.java:528)
            at javax.security.auth.login.LoginContext.login(LoginContext.java:449)
            at org.jboss.security.plugins.JaasSecurityManager.defaultLogin(JaasSecurityManager.java:394)
            at org.jboss.security.plugins.JaasSecurityManager.authenticate(JaasSecurityManager.java:361)
            at org.jboss.security.plugins.JaasSecurityManager.isValid(JaasSecurityManager.java:217)
            at org.jboss.ejb.plugins.SecurityInterceptor.checkSecurityAssociation(SecurityInterceptor.java:163)
            at org.jboss.ejb.plugins.SecurityInterceptor.invokeHome(SecurityInterceptor.java:91)
            at org.jboss.ejb.plugins.LogInterceptor.invokeHome(LogInterceptor.java:103)
            at org.jboss.ejb.StatelessSessionContainer.invokeHome(StatelessSessionContainer.java:268)
            at org.jboss.ejb.plugins.jrmp.server.JRMPContainerInvoker.invokeHome(JRMPContainerInvoker.java:387)
            at java.lang.reflect.Method.invoke(Native Method)
            at sun.rmi.server.UnicastServerRef.dispatch(Unknown Source)
            at sun.rmi.transport.Transport$1.run(Unknown Source)
            at java.security.AccessController.doPrivileged(Native Method)
            at sun.rmi.transport.Transport.serviceCall(Unknown Source)
            at sun.rmi.transport.tcp.TCPTransport.handleMessages(Unknown Source)
            at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(Unknown Source)
            at java.lang.Thread.run(Unknown Source)
            [12:13:09,827,SecurityInterceptor] Authentication exception, principal=java
            [12:13:10,207,DatabaseServerLoginModule] Bad password for username=java
            [12:13:10,207,example3] Login failure
            javax.security.auth.login.FailedLoginException: Password Incorrect/Password Required
            at org.jboss.security.auth.spi.UsernamePasswordLoginModule.login(UsernamePasswordLoginModule.java:149)
            at java.lang.reflect.Method.invoke(Native Method)
            at javax.security.auth.login.LoginContext.invoke(LoginContext.java:595)
            at javax.security.auth.login.LoginContext.access$000(LoginContext.java:125)
            at javax.security.auth.login.LoginContext$3.run(LoginContext.java:531)
            at java.security.AccessController.doPrivileged(Native Method)
            at javax.security.auth.login.LoginContext.invokeModule(LoginContext.java:528)
            at javax.security.auth.login.LoginContext.login(LoginContext.java:449)
            at org.jboss.security.plugins.JaasSecurityManager.defaultLogin(JaasSecurityManager.java:394)
            at org.jboss.security.plugins.JaasSecurityManager.authenticate(JaasSecurityManager.java:361)
            at org.jboss.security.plugins.JaasSecurityManager.isValid(JaasSecurityManager.java:217)
            at org.jboss.ejb.plugins.SecurityInterceptor.checkSecurityAssociation(SecurityInterceptor.java:163)
            at org.jboss.ejb.plugins.SecurityInterceptor.invokeHome(SecurityInterceptor.java:91)
            at org.jboss.ejb.plugins.StatefulSessionInstanceInterceptor.invokeHome(StatefulSessionInstanceInterceptor.java:119)
            at org.jboss.ejb.plugins.TxInterceptorCMT.invokeNext(TxInterceptorCMT.java:142)
            at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:347)
            at org.jboss.ejb.plugins.TxInterceptorCMT.invokeHome(TxInterceptorCMT.java:86)
            at org.jboss.ejb.plugins.LogInterceptor.invokeHome(LogInterceptor.java:103)
            at org.jboss.ejb.StatefulSessionContainer.invokeHome(StatefulSessionContainer.java:324)
            at org.jboss.ejb.plugins.jrmp.server.JRMPContainerInvoker.invokeHome(JRMPContainerInvoker.java:387)
            at java.lang.reflect.Method.invoke(Native Method)
            at sun.rmi.server.UnicastServerRef.dispatch(Unknown Source)
            at sun.rmi.transport.Transport$1.run(Unknown Source)
            at java.security.AccessController.doPrivileged(Native Method)
            at sun.rmi.transport.Transport.serviceCall(Unknown Source)
            at sun.rmi.transport.tcp.TCPTransport.handleMessages(Unknown Source)
            at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(Unknown Source)
            at java.lang.Thread.run(Unknown Source)
            [12:13:10,207,SecurityInterceptor] Authentication exception, principal=java
            [12:13:10,217,PrivateSession] TRANSACTION ROLLBACK EXCEPTION:
            javax.transaction.TransactionRolledbackException: checkSecurityAssociation; nested exception is:
            java.lang.SecurityException: Authentication exception, principal=java; nested exception is:
            java.rmi.RemoteException: checkSecurityAssociation; nested exception is:
            java.lang.SecurityException: Authentication exception, principal=java
            java.rmi.RemoteException: checkSecurityAssociation; nested exception is:
            java.lang.SecurityException: Authentication exception, principal=java
            java.lang.SecurityException: Authentication exception, principal=java
            at org.jboss.ejb.plugins.SecurityInterceptor.checkSecurityAssociation(SecurityInterceptor.java:167)
            at org.jboss.ejb.plugins.SecurityInterceptor.invokeHome(SecurityInterceptor.java:91)
            at org.jboss.ejb.plugins.StatefulSessionInstanceInterceptor.invokeHome(StatefulSessionInstanceInterceptor.java:119)
            at org.jboss.ejb.plugins.TxInterceptorCMT.invokeNext(TxInterceptorCMT.java:142)
            at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:347)
            at org.jboss.ejb.plugins.TxInterceptorCMT.invokeHome(TxInterceptorCMT.java:86)
            at org.jboss.ejb.plugins.LogInterceptor.invokeHome(LogInterceptor.java:103)
            at org.jboss.ejb.StatefulSessionContainer.invokeHome(StatefulSessionContainer.java:324)
            at org.jboss.ejb.plugins.jrmp.server.JRMPContainerInvoker.invokeHome(JRMPContainerInvoker.java:387)
            at java.lang.reflect.Method.invoke(Native Method)
            at sun.rmi.server.UnicastServerRef.dispatch(Unknown Source)
            at sun.rmi.transport.Transport$1.run(Unknown Source)
            at java.security.AccessController.doPrivileged(Native Method)
            at sun.rmi.transport.Transport.serviceCall(Unknown Source)
            at sun.rmi.transport.tcp.TCPTransport.handleMessages(Unknown Source)
            at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(Unknown Source)
            at java.lang.Thread.run(Unknown Source)


            =========
            Auth.conf
            =========

            example3 {
            /* Same as example 2 except AdvWorks(Sybase) is used

            Set 1 (works)
            --------------
            principalsQuery="select Password from DBA.Principals where PrincipalID=?"
            rolesQuery="select Role, RoleGroup from DBA.Roles where PrincipalID=?"

            Set 2 (does not work)
            -----------------------
            principalsQuery="SELECT Password FROM dba.users WHERE principalID=?"
            rolesQuery="SELECT Role, RoleGroup FROM DBA.EJBRoles, DBA.Users WHERE DBA.EJBRoles.UserID = DBA.Users.UserID AND DBA.Users.PrincipalID=?"

            Set 3 (does not work)
            -----------------------
            principalsQuery="select Password from DBA.Users where PrincipalID=?"
            rolesQuery="select Role, RoleGroup from DBA.Roles where PrincipalID=?"


            */
            org.jboss.security.auth.spi.DatabaseServerLoginModule required
            dsJndiName="java:/AdvWorksDB"
            principalsQuery="select Password from DBA.Principals where PrincipalID=?"
            rolesQuery="select Role, RoleGroup from DBA.Roles where PrincipalID=?"
            unauthenticatedIdentity=nobody
            ;
            };


            ==========
            JBoss.jcml
            ==========


            org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl
            AdvWorksDB
            jdbc:sybase:Tds:randnt40ws002:2638?ServiceName=ADVWORKS
            dba
            sql



            ==================
            Database structure
            ==================

            CREATE TABLE "DBA"."Users"
            (
            "UserID" integer NOT NULL DEFAULT autoincrement,
            "PrincipalID" varchar(64) NOT NULL,
            "LastName" char(20) NOT NULL,
            "FirstName" char(15) NULL,
            "MiddleInitial" char(1) NULL,
            "Password" char(64) NOT NULL,
            "TS" timestamp NOT NULL DEFAULT timestamp,
            PRIMARY KEY ("UserID")
            )

            /*
            3,'java','javaLast','javaFirst',,'echoman',2002/02/14 21:06:28.098000
            4,'duke','dukeLast','dukeFirst',,'javaman',2002/02/14 21:06:28.119000
            */

            /*---------------------------------------------------------------------------------*/
            CREATE TABLE "DBA"."EJBRoles"
            (
            "EJBRolesID" integer NOT NULL DEFAULT autoincrement,
            "UserID" integer NOT NULL DEFAULT autoincrement,
            "Role" varchar(64) NOT NULL,
            "RoleGroup" varchar(64) NOT NULL,
            PRIMARY KEY ("EJBRolesID")
            )

            /*
            6,3,'Echo','Roles'
            7,3,'caller_java','CallerPrincipal'
            8,4,'Java','Roles'
            9,4,'Coder','Roles'
            12,4,'caller_duke','CallerPrincipal'
            */


            /*---------------------------------------------------------------------------------*/
            CREATE TABLE "DBA"."Principals"
            (
            "PrincipalID" varchar(64) NOT NULL,
            "Password" varchar(64) NULL,
            PRIMARY KEY ("PrincipalID")
            )

            /*
            'duke','javaman'
            'java','echoman'
            */

            /*---------------------------------------------------------------------------------*/
            CREATE TABLE "DBA"."Roles"
            (
            "PrincipalID" varchar(64) NULL,
            "Role" varchar(64) NULL,
            "RoleGroup" varchar(64) NULL
            )

            /*
            'java','Echo','Roles'
            'java','caller_java','CallerPrincipal'
            'duke','Java','Roles'
            'duke','Coder','Roles'
            'duke','caller_duke','CallerPrincipal'
            */

              • Actions
            • 3. Re: select Password from Principals where PrincipalID=?
              luke_t Feb 17, 2002 2:32 PM (in response to jimrand)

              OK, so your SQL isn't failing directly. You're probably getting a password returned from the database during login, but it's not matching the one obtained during login.

              This could mean that it is null, or that there's some encoding problem with your database.

              I would try instrumenting the login module code to print out more information on the passwords. You could just extend DatabaseServerLoginModule to do this.

              Luke.

                • Actions