getObjectSQL attribute for JDBC applications

[#1]

• Question
• Answer
• Example Scenario

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)

[manojmattam]

We marked UserID attribute as identity attribute on application schema but the value passed from connectorDebug wasn't populated when query was executed. When we used identity as attribute name query worked fine. Is it this query supports identity variable only? Is identity attribute a global settings and where can we check to confirm?

[menno_pieters]

$(identity) will be replace with the value of the attribute that is, on the schema, defined as the "identity attribute", whatever that attribute is called.

[00816585]

throws 'Object not found:SELECT *' right after the account provisioning and the link is gone, after the aggregation the link is established. thoughts?

Note: Modify and Disable works as IIQ is able to the native Identity from the link but for creation the link is not established yet so it passed blank value in the where clause and that caused "object not found'.

[shiva_kumar]

Hello

On termination we delete the account in target application. Which is working as expected, but after deletion getObject is executed which throws expection "Object not found". Is there a way to suppress this getObject on disable/delete?

We are seeing this after IdentityIQ upgrade to 8.1p3 from 7.3p3.

Regards,

Shiva

[sahilmahajan]

We are getting object not found on IIQ 8.1p3 also. Any fix as IIQ failing the access request right away. Previously it used to be provisioning and completed after.

[carlodevittoria]

What worked for us was:

1) remove the featureString  NO_RANDOM_ACCESS

2) check the getObject query in the configuration tab  was correctly using the '$(identity)' 

The connectorDebug in the iiq console helped spotting a syntax error 

I think what changed in 8.1p3 is the way IIQ is reading NO_RANDOM_ACCESS featureString but it would be good to have a Sailpoint employee to look into this

[chuckm89]

@sahilmahajan @00816585 Did you ever get resolution on this?  I am seeing the exact same thing.  The connectorDebug works, but on provisioning I am getting an object not found because $(identity) is not getting populated.

[sahilmahajan]

@chuckm89  : Fixed in IIQ 8.1p4

[Shaik_Nadeem]

What happens if we pass "select * from test accounts;", Instead of select * from test accounts where id='$(identity)'*; in 

get Object SQL