Hi Sebastian, >From dev of CAS, here's is an tutorial on how to set up jdbc in CAS 5.1.x: https://apereo.github.io/2017/02/22/cas51-dbauthn-tutorial/
Just for more reference, here is my settings: cas.authn.jdbc.query[0].sql=SELECT * FROM test_users WHERE uid=? cas.authn.jdbc.query[0].url=jdbc:mysql://mysql.yoursite.com/test cas.authn.jdbc.query[0].dialect=org.hibernate.dialect.MySQLDialect cas.authn.jdbc.query[0].user=test cas.authn.jdbc.query[0].password=testdb cas.authn.jdbc.query[0].driverClass=com.mysql.jdbc.Driver cas.authn.jdbc.query[0].fieldPassword=psw I can see that the main different between me and you is that you are using *{0}* and I am using *?* as the input value, maybe that's the problem? Or you take a step back and try the tutorial, make an working jdbc connection, then resume to making your own connection works again. Hope this is helpful to you! - Andy On Friday, 15 September 2017 16:10:49 UTC+8, SebastianU wrote: > > Hello, > > I'stuck by migrating the cas configuration from 4.x version to 5.1. > > In 4.x I got the following configuration for the attributes: > > <bean id="primaryPrincipalResolver" > > class="org.jasig.cas.authentication.principal.PersonDirectoryPrincipalResolver"> > <property name="attributeRepository" ref="attributeRepository"/> > </bean> > > <bean id="attributeRepository" > > class="org.jasig.services.persondir.support.jdbc.MultiRowJdbcPersonAttributeDao"> > <constructor-arg index="0" ref="dataSource"/> > <constructor-arg index="1" > value="SELECT 'USER_ROLE' as role_name, FUNCTION as ROLENAME FROM > FUNCTIONS WHERE {0}"/> > <property name="queryAttributeMapping"> > <map> > <entry key="username" value="ID"/> > </map> > </property> > <property name="nameValueColumnMappings"> > <map> > <entry key="role_name" value="ROLENAME"/> > </map> > </property> > </bean> > > <bean id="serviceRegistryDao" > class="org.jasig.cas.services.InMemoryServiceRegistryDaoImpl" > p:registeredServices-ref="registeredServicesList"/> > > <util:list id="registeredServicesList"> > <bean class="org.jasig.cas.services.RegisteredServiceImpl"> > <property name="id" value="0"/> > <property name="name" value="HTTPS Services"/> > <property name="description" value="YOUR HTTP Service"/> > <property name="serviceId" value="https://**"/> > <property name="allowedAttributes"> > <list> > <value>USER_ROLE</value> > </list> > </property> > </bean> > </util:list> > > > Now I'm trying to configure CAS 5.1 to use the same functionality and I'm > stuck... > > Log: > DEBUG > [org.apereo.services.persondir.support.jdbc.MultiRowJdbcPersonAttributeDao] > (default task-19) Executed 'SELECT 'USER_ROLE' as role_name, FUNCTION as > ROLENAME FROM FUNCTIONS WHERE {0}' with arguments [casuser] and got > results [{ID=casuser, ROLE_NAME=USER_ROLE, ROLENAME=EDITOR}, {ID=casuser, > ROLE_NAME=USER_ROLE, ROLENAME=VIEWER}] > 2017-09-14 18:29:24,391 DEBUG > [org.apereo.services.persondir.support.MergingPersonAttributeDaoImpl] > (default task-19) Retrieved > attributes='[NamedPersonImpl[name=CASUSER,attributes={}]]' for > query='{username=[casuser]}', isFirstQuery=false, > currentlyConsidering='org.apereo.services.persondir.support.jdbc.MultiRowJdbcPersonAttributeDao@7698881', > > resultAttributes='null' > DEBUG > [org.apereo.services.persondir.support.MergingPersonAttributeDaoImpl] > (default task-19) Aggregated search results > '[NamedPersonImpl[name=CASUSER,attributes={}]]' for > query='{username=[casuser]}' > DEBUG > [org.apereo.services.persondir.support.CachingPersonAttributeDaoImpl] > (default task-19) Retrieved query from wrapped IPersonAttributeDao and > stored in cache for scopedTarget.attributeRepository. > key='-265766846|-265796123', > results='[NamedPersonImpl[name=CASUSER,attributes={}]]' > DEBUG > [org.apereo.services.persondir.support.CachingPersonAttributeDaoImpl.statistics] > > (default task-19) Cache Stats scopedTarget.attributeRepository: queries=1, > hits=0, *misses=1* > DEBUG > [org.apereo.cas.authentication.principal.resolvers.PersonDirectoryPrincipalResolver] > > (default task-19) *Principal id [casuser] did not specify any attributes* > DEBUG > [org.apereo.cas.authentication.principal.resolvers.PersonDirectoryPrincipalResolver] > > (default task-19) *Returning the principal with id [casuser] without any > attributes* > > My configuration cas.properties is: > # config Authentication Attributes > cas.authn.attributeRepository.expireInMinutes=30 > cas.authn.attributeRepository.maximumCacheSize=10000 > cas.authn.attributeRepository.merger=MERGE > > cas.authn.attributeRepository.jdbc[0].attributes.uid=ROLENAME > > cas.authn.attributeRepository.jdbc[0].singleRow=false > cas.authn.attributeRepository.jdbc[0].order=0 > cas.authn.attributeRepository.jdbc[0].requireAllAttributes=true > cas.authn.attributeRepository.jdbc[0].caseCanonicalization=UPPER > # cas.authn.attributeRepository.jdbc[0].queryType=OR|AND > > # Used only when there is a mapping of many rows to one user > cas.authn.attributeRepository.jdbc[0].columnMappings.role_name=ROLENAME > # > cas.authn.attributeRepository.jdbc[0].columnMappings.columnAttrName2=columnAttrValue2 > # > cas.authn.attributeRepository.jdbc[0].columnMappings.columnAttrName3=columnAttrValue3 > cas.authn.attributeRepository.jdbc[0].sql=SELECT 'USER_ROLE' as > role_name, FUNCTION as ROLENAME FROM FUNCTIONS WHERE {0} > cas.authn.attributeRepository.jdbc[0].username=ID > cas.authn.attributeRepository.jdbc[0].isolateInternalQueries=false > cas.authn.attributeRepository.jdbc[0].failFast=true > > cas.authn.attributeRepository.jdbc[0].isolationLevelName=ISOLATION_READ_COMMITTED > cas.authn.attributeRepository.jdbc[0].leakThreshold=10 > > cas.authn.attributeRepository.jdbc[0].propagationBehaviorName=PROPAGATION_REQUIRED > cas.authn.attributeRepository.jdbc[0].batchSize=1 > cas.authn.attributeRepository.jdbc[0].autocommit=false > cas.authn.attributeRepository.jdbc[0].idleTimeout=5000 > cas.authn.attributeRepository.jdbc[0].pool.suspension=false > cas.authn.attributeRepository.jdbc[0].pool.minSize=6 > cas.authn.attributeRepository.jdbc[0].pool.maxSize=18 > cas.authn.attributeRepository.jdbc[0].pool.maxWait=2000 > > cas.authn.attributeRepository.defaultAttributesToRelease=USER_ROLE,role_name > > What am I missing? > > > Help appreciated! > > Sebastian > -- - Website: https://apereo.github.io/cas - Gitter Chatroom: https://gitter.im/apereo/cas - List Guidelines: https://goo.gl/1VRrw7 - Contributions: https://goo.gl/mh7qDG --- You received this message because you are subscribed to the Google Groups "CAS Community" group. To unsubscribe from this group and stop receiving emails from it, send an email to cas-user+unsubscr...@apereo.org. To view this discussion on the web visit https://groups.google.com/a/apereo.org/d/msgid/cas-user/5b5004ac-babf-407e-9d41-86089a2b724a%40apereo.org.