We have a below requirement.
1. We have a CA Directory user store that we have configured in siteminder for authorization purpose.
2. We are sending the responses to the application once the user is authorized based on the conditions specified in domain policy, users tab.
3. In responses, we are sending the user profile attributes (such as roles, first name, last name etc.).
4. In addition to that, we have a new requirement to send a response (from SQL database) to the application. In this, we need to run a query based on a particular roles to fetch the business functions associated with those roles. We then need to send those business functions to the application as a response.
Solution: We are currently exploring the options. One thought i had in mind is, we can define a variable definition to fetch the value from the SQL database based on SQL query and then send a response to the application team.
Other thought was using sql query schemes, I am not sure whether how we can use SQL query schemes to handle this scenario.
If nothing above is possible, we can anytime go for a active response to fetch the value from SQL database and send it as a response to the application team.
Any suggestions or assistance around the approach to acheive this will be really helpful.
One solution would be to use a product like Radiant One to make both your CA Directory and the DB appear as if they were a single user store to SiteMinder. In this situation the User Directory object in SiteMinder would refer to the Radiant One, and then Radiant One would collect the data from CA Directory and the DB as necessary.
As you mentioned, another solution would be to write an active response that creates its own pool of DB connections and submits queries over those connections.
The above two solutions are the only solutions that I know would work.
You mentioned variables and Query Schemes. You should investigate them further, but unless a new capability has been added to variables I don't think they will work. The reason is that SiteMinder only maintains one UserContext at a time, a UserContext is tied to one account in one User Directory, and user attribute data for responses and variables is only available via a UserContext. So if you authenticate to CA Directory, then normally only data from the user's account in CA Directory would be available in a response. If you set up authorization mapping to make your DB available, then at authorization time data from your DB would be available, but the data from your CA Directory would not be available because the UserContext for authorizations would be based solely on the user's account in the DB.
An eTelligent rule variable populated by an services call from the web agent would be possible, but I would not recommend it because the performance would probably be so bad as to be unworkable.
There is an OOB feature in CA SSO called "IDENTITY_MAP".
IDENTITY_MAP is a reserve word which can be used in an expression to trigger an IDENTITYMAPPING Object without having to select a mapping within a realm in the Policy Domain.
In the IDENTITYMAPPING Object you'd have CA Directory as AuthDir and SQL as AzDir (Check Support Matrix for UD compatibility).
But this feature is broken in R12.51 / R12.52 / R12.6 / R12.7 / R12.8, with no documentation. We received a fix in R12.52 SP1 CR04 / 05 as a DevFix. We have requested a fix in R12.8 and the fix will be back ported probably in next release of R12.7 and R12.52.
I'd have recommended you to use and test this feature, but the overhead to get the feature working it too much. Raise support case, get the devfix, do functional test, do Load and performance test (because it has been unused and broken for a long time) and then PoC your usecase. If you are willing to do through the entire process, then Yes it is something I'd recommend to look at OOB.
Reference : https://communities.ca.com/ideas/235714647-identity-mapping-for-federation