Showing results for 
Show  only  | Search instead for 
Did you mean: 

getObjectSQL attribute for JDBC applications

getObjectSQL attribute for JDBC applications



What is the proper format and purpose of the JDBC application attribute "getObjectSQL"?



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

(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)



This is a great question to post on the IdentityIQ Forums so that other members of the community can see it and respond.

Please relocate this question to the IdentityIQ Forums.

Thank you

I have moved them to forum.

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.

Very useful article and thanks for posting it.

Hi All,

I am getting Invalid Column Name 'Value' when we do Target Aggregations in Workflow Step which uses connector.getObject method  on JDBC Applications.

My GetObjectSQL Query : SELECT * FROM TermDB where distingName = "$(identity)";

and IdentityAttribute is set to distingName.

How to debug this issue? What might be wrong here?


Please post this as a question in the forums, not with this article. Also provide some more information about your application setup (schema, etc.).

Hi Menno,

Moved this  and added few more details :

JDBC Application - GetObjectSQL Query

Version history
Revision #:
3 of 3
Last update:
‎Jul 14, 2023 09:03 PM
Updated by: