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 • [email protected] [image: The New School] On Mon, May 21, 2018 at 10:26 PM John D Giotta <[email protected]> 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 [email protected]. > 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 [email protected]. 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.
