I'm pretty sure that if you enable debug-level logging on
org.apereo.services.persondir in */etc/cas/config/log4j2.xml*, you'll see
the SQL query in *cas.log*. You can do that most easily by changing this
line near the top of the file:

<Property name="cas.log.level" >warn</Property>

to:

<Property name="cas.log.level" >debug</Property>

You shouldn't even need to restart the server, just wait 5-10 seconds for
it to re-check the logging config file.

But you sparked my curiosity (not the least because I actually did figure
this out once a long time ago for CAS 3.5), so I dug around a bit. The
documentation for the SingleRowJdbcPersonAttributeDao (which is what you're
ultimately configuring) is here:
https://wiki.jasig.org/display/PDM15/JDBC+Attribute+Source.

According to that, the attributes are going to be fetched with a SQL query
like SELECT * FROM USER_DATA WHERE {0} and, by default, the {0} is going to
be replaced with username=*value* (where *value* is the name of the user
you're looking for). If the column in your database that contains the
username is called something other than username, you can change that with
a queryAttributeMapping definition:

    <constructor-arg index="1" value="SELECT * FROM USER_DATA WHERE {0}" />
    <property name="queryAttributeMapping">
        <map>
            <entry key="username" value="uid" />
        </map>
    </property>

(The above will change the {0} from username=*value* to uid=*value*.)

So, reading the CAS documentation here:
https://apereo.github.io/cas/development/installation/Configuration-Properties.html#jdbc
it looks to me like you need this setting:

cas.authn.attributeRepository.jdbc[0].sql=SELECT * FROM app_user WHERE {0}


(I know your current setting lists the columns you want, but I would
suggest starting with this until it works, and then tweak it down if you
really need to.) And, since your app_user table, as near as I can guess,
doesn't have a column named username, you need to set the column you want
to use (this is the equivalent of the queryAttributeMapping XML above):

cas.authn.attributeRepository.jdbc[0].username=id


I might be wrong about the setting above; you might want email in there
instead of id, since that's what your authentication query is using (the
value it's matching against is, I believe, whatever the user is typing in
as his/her username).

The other part of the SingleRowJdbcPersonAttributeDao discussed in the
documentation is the part that maps database column names (the keys) to
attribute names (the values):

    <property name="resultAttributeMapping">
        <map>
            <entry key="uid" value="username" />
            <entry key="first_name" value="first_name" />
            <entry key="last_name" value="last_name" />
            <entry key="email" value="email" />
        </map>
    </property>

That's covered by the other properties we talked about yesterday:

cas.authn.attributeRepository.jdbc[0].attributes.id=uid
cas.authn.attributeRepository.jdbc[0].attributes.first_name=givenName
cas.authn.attributeRepository.jdbc[0].attributes.email=emailaddress
cas.authn.attributeRepository.jdbc[0].attributes.last_name=surname


The database column names are on the left-hand side of the '=', and the
attribute names (what the client application gets) are on the right-hand
side.

Please note that the above is from my reading the documentation only (well,
and getting it working once several years ago on CAS 3.5). I don't have a
CAS-with-JDBC instance configured to try it out on. But hopefully it points
you in the right direction, at least.

Good luck,
--Dave

--

DAVID A. CURRY, CISSP
*DIRECTOR OF INFORMATION SECURITY*
INFORMATION TECHNOLOGY

71 FIFTH AVE., 9TH FL., NEW YORK, NY 10003
+1 212 229-5300 x4728 • david.cu...@newschool.edu

[image: The New School]



On Mon, May 21, 2018 at 10:26 PM John D Giotta <jdgio...@gmail.com> wrote:

> Is there any way to show the sql used to get user attributes?
>
> --
> - 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/e3453ba3-aa88-4e3f-bba8-d96114a6ab37%40apereo.org
> <https://groups.google.com/a/apereo.org/d/msgid/cas-user/e3453ba3-aa88-4e3f-bba8-d96114a6ab37%40apereo.org?utm_medium=email&utm_source=footer>
> .
>

-- 
- 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/CA%2Bd9XAOWst8n-UX6rYnjNraSQa0RbVM7t4-Oz47hw-YMousK%3Dw%40mail.gmail.com.

Reply via email to