Resolution
Question:
What is the proper format and purpose of the JDBC application attribute "getObjectSQL"?
Answer:
The getObjectSQL configuration item on the JDBC application allows customers to specify the sql query that should be executed to fetch a single user. The value is specified as a string, and will be treated much like a template with one special token $(identity). The special $(identity) token will be replaced with the requested nativeIdentity at run-time when the getObject method is called on the JDBC Connector.
Simple example value for the getObjectSQL application attribute:
select * from test_accounts where id='$(identity)';
To test the above, you can use the connectorDebug command from the "iiq console" like so:
>connectorDebug yourJDBCApp get account foo
This command will generate the following SQL, using the getObjectSQL attribute value, and execute it:
select * from test_accounts where id='foo';
Implementation Note:
Currently, the getObjectSQL is only used during the certification revocation process. If a revocation period is enabled and in effect, identityIQ will scan on a configured basis (every 24 hours default) to see if the revocation is completed. For every identity that has a revocation that needs to be checked, this will perform a "targeted reaggregation" on the affected systems. This means that we try to aggregate the affected accounts using the getObject() call on the connector if the application supports it (ie - does not have the NO_RANDOM_ACCESS feature).
Example Scenario:
JDBC APPLICATION
(Will perform target reaggregation against application using the identity link)
1. create cert with remediation phase.
2. revoke account.
3. enter cert remediation phase.
4. run perform maintenance task with "Scan for completed revocations" enabled.
5. account still exists in database:
- no getObjectSQL implemented
(status doesn't change on revocation report, OPEN)
(connectorDebug <app> get account <username> throws error: Invalid object name 'account'.)
- incorrect getObjectSQL implemented
(status changes to FINISHED, link deleted, entitlement remains)
(connectorDebug <app> get account <username> throws error: Object not found.)
- correct getObjectSQL
(status doesn't change on revocation report until you remove account from database)
(When remediation manager finally detects delete, link deleted, entitlement remains)
Note: if you re-create account in database, revocation manager does not run again because the cert item is tagged as finished.
6. account exists in database, but identity does not have link (only entitlement)
- correct getObjectSQL
(status changes to FINISHED)
(To contact database for a targeted re-aggregation, the LINK must exist)