How should I write the getObjectSQL field in a JDBC Application?

[kyunghoon (ahn kyunghoon)]

Which IIQ version are you inquiring about?

Version 8.3

Please share any images or screenshots, if relevant.

Please share any other relevant files that may be required (for example, logs).

Share all details related to your problem, including any error messages you may have received.

I have a JDBC Application.

It consists of user,user_role table.

But there is one problem.

If i delete a entitlement when there is only one entitlement , the entitlement will be deleted from the Entitlements tab of the Identity details screen and also from the Application Accounts tab.
In the real DB, User is not deleted, but user_role is deleted.
I only want to delete entitlement, but even the account has been deleted from the screen.

My getObjectSQL : select user_id,name,email,role_id from user a,user_role b WHERE a.user_id=b.user_id AND a.user_id=‘$(identity)’

My Application.xml file
application.txt (7.6 KB)

[officialamitguptaa (Amit Gupta)]

Hi @kyunghoon -
Below is the use case scenario I’m assuming -
Let say on the database side we have two tables -

*** user**
*** user_role**

Now a user may be a member of a role or may not be having any role at all. In that case you must use the below query.

select user_id,name,email,role_id from user a left join user_role b on a.user_id=b.user_id where a.user_id='$(identity)';

In this case you must also define the merging if the user may be having access to multiple roles.

Refer the screenshot below -

In the above screenshot we are merging grpmembership attribute based on dbID. In your case it will be role_id based on user_id

Mark it as solved, If it helps.

Thank you!

[kyunghoon (ahn kyunghoon)]

@officialamitguptaa

y not be having an

Thanks for reply.

I replaced getObjectSQL field.
select * from user a left join user_role b on a.user_id=b.user_id where a.user_id=‘$(identity)’

I already defined the merging.

when i remove entitlement, entitlement does not disappear in screen.

[officialamitguptaa (Amit Gupta)]

Could you share the provisioning rule here?

[kyunghoon (ahn kyunghoon)]

Thanks for reply.

attached rule file.

rule.txt (12.6 KB)

[Jarin_James]

Hi @kyunghoon ,

First of all, I would suggest you change getObjectSQL as @officialamitguptaa mentioned and also change SQL Statement to as below.

select user_id,name,email,role_id from user a left join user_role b on a.user_id=b.user_id ;

This will make sure that user is not removed even if the role doesn’t exist during aggregation.

Couple of things I noticed while I was skimming through was, the code is fetching name, email, user_id etc from plan but I didn’t find any provisioning policy for the same.
Secondly, you are considering multiple add role request in your code but multiple role remove request was not checked. It was not looking for a list instance and taking action on remove.

Please let us know if you are still facing issues.

[kyunghoon (ahn kyunghoon)]

Thanks for reply.

I already defined SQL Statement,getObjectSQL.

SQL : select * from user a left join user_role b on a.user_id=b.user_id ORDER BY a.user_id

getObjectSQL : select * from user a left join user_role b on a.user_id=b.user_id where a.user_id='$(identity)'

If i remove an entitlement from a user who has one entitlement, isn’t it correct that only the entitlement is removed and the account remains?

The entitlement has been deleted from the actual DB, but remains on the IIQ screen.

When i execute the Perform Identity Request Maintenance task, it disappears from the IIQ entitlement screen.

Is it correct to run this task every time?

[chriskk]

The entitlement has been deleted from the actual DB, but remains on the IIQ screen.

This could be a normal sync issue with IIQ.
Have you tried to run the AccountAggregation-Task for your connector, after your change?

We have similar cases and the identites are in sync, after AccountAggregation was running.
In LCM workflow exists a “doRefresh” flag, but without traceable impact

[kyunghoon (ahn kyunghoon)]

Thanks for reply.

After i run AccountAggregation-Task and Refresh identity cube-Task, the entitlement disappears from the screen.

[kyunghoon (ahn kyunghoon)]

I’m trying to solve it by using code that runs the “Perform Identity Request Maintenance” task in the after provisoing rule.

Currently, this method seems to be the best.

If there is another solution, I would appreciate it if you could let me know.

Thanks.