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

    select Password from Principals where PrincipalID=?

    Jim Rand Newbie

      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

        • 1. Re: select Password from Principals where PrincipalID=?
          Luke Taylor Novice

          > 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.

          • 2. Re: select Password from Principals where PrincipalID=?
            Jim Rand Newbie

            ===============
            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'
            */

            • 3. Re: select Password from Principals where PrincipalID=?
              Luke Taylor Novice

              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.