OIDC Claims are not returned for an OpenId app

Products

SITEMINDER CA BCS Premier for CA Single Sign-On CA Single Sign On Agents (SiteMinder) CA Single Sign On Federation (SiteMinder) CA Single Sign On Secure Proxy Server (SiteMinder) CA Single Sign On SOA Security Manager (SiteMinder) CA Single Sign-On

Issue/Introduction

An OpenID application configured to use a DB2 Database as the User Directories.

The OIDC authentication is working fine, but the OIDC Claims are not returned for an OpenId app.

The attribute application is interested in return is userid, which is mapped to DB2 Database table entry USER_ID.

Environment

Release : 12.8.06

Cause

There could be configuration issues at several different OIDC components.

Trace log from both CA Access Gateway (SPS) and Policy Server should be collected in order to determine the root cause.

The Policy Server trace log reviewed ODBC error while the Policy Server looks up the user from Database, which should not have happened.

[04/21/2023][14:27:04][14:27:04.409][][][][][][15252][12820][][][][][][][][][][CDb.cpp:511][CSmRecordset::Open][][][][][][][][][][][][][][][][][][][][][Internal Error: Database error. Code is -4007 (DBMSG: <<<State = 42S22 Internal Code = -206 - s>>>)][][][][][][Caught an exception 'Internal Error: Database error. Code is -4007 (DBMSG: <<<State = 42S22 Internal Code = -206 - s>>>)'][][][][][][][][][][][][][][][]

[04/21/2023][14:27:04][14:27:04.409][][][][][][15252][12820][][][][][][][][][][SmDsOdbcProvider.cpp:1173][][][][][][][][][][][][][][][][][][][][][][][][][][][][LogMessage:ERROR:[sm-Odbc-00170] Database Error executing query ( 'SELECT <USER_ID>, '<User>' AS <Class> FROM <USER> WHERE <USER_ID> = '<USER_ID test1>' UNION SELECT <GROUP_NAME>, '<Group>' AS <Class> FROM <LOG> WHERE <Name> = '<USER_ID test1>'' ORDER BY <Class>'). Error: Internal Error: Database error. Code is -4007 (DBMSG: <<<State = 42S22 Internal Code = -206 - s>>>) .][][][][][][][][][][][][][][][]

[04/21/2023][14:27:04][14:27:04.409][][][][][][15252][12820][][][][][][][][][][SmDsDir.cpp:627][CSmDsDir::Lookup][][][][][][][][][][][][][][][][][][][0][][][][][][][][Return from call Lookup.][][][][][][][][][][][][][][][]

[04/21/2023][14:27:04][14:27:04.409][][][][][][15252][12820][][][][][][][][][][FedUserDisambigApi.cpp:1932][FedUserDisambigApi::findUserByNameIDwithSessionSpec][][][][][][][][][][][][][][][][][][][][][][][][][][][Lookup of user failed][][][][][][][][][][][][][][][]

Resolution

To return OIDC Claims, the OIDC application must call pre-configured /oidc/sample_client/userinfo endpoint.

That is by design how claim attribute should be returned, and /oidc/sample_client/userinfo endpoint is not the same as Authorization endpoint /CASSO/oidc/sample_client/authorize (1).

In addition, the user must be found from the SiteMinder User Store via SQL lookup query like ".... WHERE <USER_ID= test1>" within SiteMinder SQL query scheme.

There are two locations where the user lookup is defined. Item one below only applies when DataBase is used.

OIDC Authorization provider object. This configuration is documented in Search Specification (2).
Verify "lookup" entry within the SQL query scheme. Due to the nature that SQL query scheme must match with DB table content, so its SQL syntax has to be correct. Out of box sample may not work or fit every specifics DB tables.

This task could require some trial and error effort. The Policy Server trace log should confirm the changes done is effective or not.

The goal is to ensure that SQL query finds the user and no more DB failure. Once the user is found, the attribute userid or USER_ID, should be returned.

Note: Customize SQL query scheme is out of core support' scope. Support's ability to further advise on the SQL query scheme is limited. Any such customization could result to HCL service engagement.

Additional Information

Authentication Using Authorization Code Flow
https://techdocs.broadcom.com/us/en/symantec-security-software/identity-security/siteminder/12-8/configuring/use-siteminder-as-openid-connect-provider/authentication-using-authorization-code-flow.html
Authorization Provider Dialog
https://techdocs.broadcom.com/us/en/symantec-security-software/identity-security/siteminder/12-8/using/administrative-ui/openid-connect-provider-reference/authorization-provider-dialog.html