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.
A 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'
The getObjectSQL is used for verification of provisioning. Specifically, it is used by the JDBC connector to perform a targeted aggregation to confirm successful provisioning. It is also 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 re-aggregation against application using the identity link)
1. Create a certification with a remediation phase.
2. Revoke an account.
3. Enter the certification remediation phase.
4. Run the Perform Maintenance task with the option "Scan for completed revocations" enabled.
5. The 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 the account in the database, the revocation manager does not run again because the certification item is tagged as finished.
6. The account exists in the database, but the identity does not have a link (only an entitlement)
- correct getObjectSQL
(status changes to FINISHED)
(To contact database for a targeted re-aggregation, the LINK must exist)
