Oracle Derived Attributes

[Chuck Kimber]

I have an oracle db connection which pulls things from our standard Banner
DB and I wish to present the results as an attribute that the SP and IdP
agree upon.  My problem seems to be that the result keeps getting filtered
out of the return.  Now it's Oracle and I've read that I need to do column
mappings, but as soon as I do that, the whole thing explodes.  The googling
and reading I've done on the forums suggests that I have to have the xml in
the right order for the DataConnecter.  I've double checked this many
times.  I'm confident I get results from the query, until I try to do a
column mapping.  I know I'm missing something obvious and I don't have any
dependencies, so it should just be very simple and straight forward.  What
am I missing?

attribute-resolver.xml:

    <resolver:AttributeDefinition id="bUser" xsi:type="Simple"
xmlns="urn:mace:shibboleth:2.0:resolver:ad" sourceAttributeID="bUser">
        <resolver:Dependency ref="myBanner" />
        <resolver:AttributeEncoder xsi:type="SAML1StringNameIdentifier"
xmlns="urn:mace:shibboleth:2.0:attribute:encoder"
nameFormat="urn:oasis:names:tc:SAML:1.1:nameid-format:unspecified" />
        <resolver:AttributeEncoder xsi:type="SAML2StringNameID"
xmlns="urn:mace:shibboleth:2.0:attribute:encoder"
nameFormat="urn:oasis:names:tc:SAML:1.1:nameid-format:unspecified" />
    </resolver:AttributeDefinition>

    <resolver:DataConnector id="myBanner" xsi:type="dc:RelationalDatabase">
             <dc:ApplicationManagedConnection
jdbcDriver="oracle.jdbc.OracleDriver"

jdbcURL="jdbc:oracle:thin:@(DESCRIPTION
=(ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip.banner.usu.edu)(PORT =
1521))(ADDRESS = (PROTOCOL = TCP)(HOST = node2-vip.banner.usu.edu)(PORT =
1521))(LOAD_BALANCE = yes)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME =
bannerProd)))"
                                              jdbcUserName="shibb_user"
                                              jdbcPassword="********" />
             <dc:QueryTemplate>
                <![CDATA[
                        SELECT
baninst1.z_shibboleth.f_oracle_username('$requestContext.principalName') AS
BANNERUSERNAME
                        FROM DUAL
                ]]>
              </dc:QueryTemplate>
              <!--
              <Column columnName="BANNERUSERNAME" attributeID="bUser"
type="String" />
              -->
    </resolver:DataConnector>

Quick explanation.  We run Banner on Oracle RAC, so our jdbcURL looks funny
to most, but I assure you it works here and elsewhere over jdbc just fine.
The query is actually a package/function execution, but not explicitly
called as a function, but rather in query form.  And yes, I know the column
mapping is commented out, but it's just so I can show you it's there, in the
right xml parent-child order, and to show the DEBUG logs where I get a query
result, and then followed by what happens when I uncomment it.

So in the idp-process.log, while running DEBUG, I can see the query get
results that are later filtered out by the return.

16:06:53.489 - INFO
[edu.internet2.middleware.shibboleth.common.config.attribute.resolver.AbstractResolutionPlugInBeanDefinit
ionParser:54] - Parsing configuration for AttributeDefinition plugin with
ID: bUser
16:06:53.489 - DEBUG
[edu.internet2.middleware.shibboleth.common.config.attribute.resolver.AbstractResolutionPlugInBeanDefini
tionParser:61] - Dependencies for plugin bUser: [myBanner]
16:06:53.489 - DEBUG
[edu.internet2.middleware.shibboleth.common.config.attribute.resolver.attributeDefinition.BaseAttributeD
efinitionBeanDefinitionParser:57] - Setting source attribute ID for
attribute definition bUser to: BANNERUSERNAME
16:06:53.490 - DEBUG
[edu.internet2.middleware.shibboleth.common.config.attribute.resolver.attributeDefinition.BaseAttributeD
efinitionBeanDefinitionParser:81] - Attribute definition bUser produces
attributes that are only dependencies: false

16:11:58.109 - DEBUG
[edu.internet2.middleware.shibboleth.common.attribute.resolver.provider.ShibbolethAttributeResolver:288]
 - Resolving attribute bUser for principal a00016883
16:11:58.109 - DEBUG
[edu.internet2.middleware.shibboleth.common.attribute.resolver.provider.ShibbolethAttributeResolver:328]
 - Resolving data connector myBanner for principal a00016883
16:11:58.111 - DEBUG
[edu.internet2.middleware.shibboleth.common.attribute.resolver.provider.dataConnector.RDBMSDataConnector
:261] - RDBMS data connector myBanner - Search Query: SELECT
baninst1.z_shibboleth.f_oracle_username('a00016883') AS BANNERUSERNAME
                        FROM DUAL
16:11:58.113 - DEBUG
[edu.internet2.middleware.shibboleth.common.attribute.resolver.provider.dataConnector.RDBMSDataConnector
:322] - RDBMS data connector myBanner - Querying database for attributes
with query SELECT  baninst1.z_shibboleth.f_oracle_us
ername('a00016883') AS BANNERUSERNAME
                        FROM DUAL
16:11:58.236 - DEBUG
[edu.internet2.middleware.shibboleth.common.attribute.resolver.provider.dataConnector.RDBMSDataConnector
:331] - RDBMS data connector myBanner - Retrieved attributes:
[BANNERUSERNAME]
16:11:58.237 - DEBUG
[edu.internet2.middleware.shibboleth.common.attribute.resolver.provider.ShibbolethAttributeResolver:310]
 - *Resolved attribute bUser containing 1 values*

16:11:58.261 - DEBUG
[edu.internet2.middleware.shibboleth.common.attribute.resolver.provider.ShibbolethAttributeResolver:136]
 - shibboleth.AttributeResolver resolved, for principal a00016883, the
attributes: [eduPersonAffiliation, department, surname
, eduPersonScopedAffiliation, givenName, eduPersonNickname,
eduPersonPrincipleName, bUser, postalCode, initials, stateProvi
nce, postOfficeBox, commonName, transientId, eduPersonTargetedID, email,
jpegPhoto, telephoneNumber, locality,
displayName, comment]

16:11:58.272 - DEBUG
[edu.internet2.middleware.shibboleth.common.attribute.filtering.provider.ShibbolethAttributeFilteringEng
ine:159] - Processing permit value rule for attribute bUser for principal
a00016883

16:11:58.277 - DEBUG
[edu.internet2.middleware.shibboleth.common.attribute.filtering.provider.ShibbolethAttributeFilteringEng
ine:104] -* Removing attribute from return set, no more values: bUser*



It seems clear that the query is getting a result, it's just stripped out.
It is listed in the filter as a releasable attribute, so I'm presuming it's
some kind of quirk with case sensitivity on the column names or some such
nonsense that the docs elude to.  I uncomment my column mapping and am met
with:

20:34:05.555 - ERROR
[edu.internet2.middleware.shibboleth.common.config.BaseService:187] -
Configuration was not loaded for s
hibboleth.AttributeResolver service, error creating components.  The root
cause of this error was: org.xml.sax.SAXParseExcept
ion: cvc-complex-type.2.4.a: Invalid content was found starting with element
'Column'. One of '{"urn:mace:shibboleth:2.0:reso
lver:dc":Column, "urn:mace:shibboleth:2.0:resolver:dc":ResultCache}' is
expected.


Any thoughts on what is happening here?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://shibboleth.net/pipermail/users/attachments/20111009/19e24693/attachment-0001.html