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 [email protected].
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.
