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)
Ok that is fine if your account matches to your identity but what if the account is different and you correlate using some other attribute. What variables are available to the query other than $(identity)?
You misunderstand "$(identity)". It does not relate to the IDENTITY name. It is the Native Identity attribute -- the attribute in the application schema marked as the "Identity Attribute". This attribute should be the unique identifier for the account on the application, so it should allow you to target the desired specific account. There are no other variables available to the query.
This is a great document, but I believe there is one correction needed...
This line: Currently, the GetObjectSQL is only used during the certification revoke process.
I don't believe that is true. If LCM is enabled and provisioning is enabled for a JDBC based application, I believe the GetObjectSQL is used when the 'Perform Identity Request Maintenance' task is run to verify access was properly granted.
In this scenario, after having granted an entitlement to a JDBC based account, when the Perform Identity Request Maintenance task is run, you will see the following results to the Access Request
an incorrect getObjectSQL will result in the Access Request remaining 'Verifying' and the link being removed from the identity
a correct getObjectSQL will result in the Access Request moving to 'Completed'
I believe you are partially correct. The Perform Identity Request Maintenance task does NOT query out to the endpoint system; it checks against the data recorded on the identity in IdentityIQ at the time the task runs. However, I believe you are correct that the getObjectSQL is used to do the targeted re-aggregation from the endpoint system immediately after provisioning, The connector uses this to verify that the provisioning did take place before it returns a "Committed" status to the PlanEvaluator and the provisioning workflow. I will update the doc.
