How to fetch the entitlement attributes in JDBC Provisioning Rule

sai_krishna_L · September 16, 2024, 12:08pm

Hi Everyone,

How to fetch the entitlement attributes in JDBC Provisioning Rule.
Apart from the entitlement ID and entitlement Name from the below image.

Below is the attachment for the reference.

Any suggestions would be helpful.

Kind Regards,
Sai Krishna L

prashanthrns · September 16, 2024, 1:24pm

Can you pls provide more details ? Do you want to fetch the entitlement schema attributes or the Entitlement (ManagedAttribute) attribute values ?

Anshu_Kunal · September 16, 2024, 2:17pm

@sai_krishna_L
Please try below method that is available in IdnRuleUtil

Thanks

sai_krishna_L · September 17, 2024, 6:55am

Hi @prashanthrns & @Anshu_Kunal,

let me explain my problem statement in detailed way.
PFA, for the Account schema.

We have the entitlement types too. PFA, on the same.

In the JDBC Provisioning Rule there is a stored procedure which is used to grant and revoke the entitlements.
reference of the stored procedure is here:

"call PROV (?, ?, ?, ?, ?, ?, ?)";
cs.setString ( 1, "D");  //Grant or revoke attribute
cs.setString ( 2, account_name); // unique ID
cs.setString ( 3, ent_type); // It cannot be null. ENTITlEMENT_TYPE which is present in account schema.
cs.setString ( 4, entName); //RESOURCE_ID from account schema
cs.setString ( 5, action_ID); // ACTION_ID from account schema
cs.setString ( 6, ""); // Null
cs.registerOutParameter(7, Types.VARCHAR); // INOUT pararmeter which consists of Success or failure.

After performing the access request it was failed in the provisioning step. PFA, on the same.

In the logs I can see “ENTITlEMENT_TYPE” & “ACTION_ID” is populated as null and these parameters defined below.

String account_name = getAttributeRequestValue(account,"ACCOUNT_NAME");
String ent_type = getAttributeRequestValue(account,"ENTITLEMENT_TYPE");
String resource_ID = getAttributeRequestValue(account, "RESOURCE_ID");
String action_ID = getAttributeRequestValue(account, "ACTION_ID");

Please let me know how can I resolve this error “java.sql.SQLException: The number of parameter values set or registered does not match the number of parameters.”

Kind Regards,
Sai Krishna L

mohammedfavazhrb · September 17, 2024, 9:28am

Hi @sai_krishna_L ,

One error i can see is that, here you are setting the string as “resource_id”.
String resource_ID = getAttributeRequestValue(account, "RESOURCE_ID");

But in your set string code, you are trying to set it with entName which is not defined
cs.setString ( 4, entName);

Please correct it as cs.setString ( 4, resource_ID);

Thanks

sai_krishna_L · September 17, 2024, 9:52am

Hi @mohammedfavazhrb,

RESOURCE_ID is multivalued so I didn’t mention the entire code here, as i have defined an array and i am passing the same in the set string method…

Anshu_Kunal · September 17, 2024, 11:04am

These two attributes will not be available as part of provisioning plan and you would be able to get it by using ‘getAttributeRequestValue’ method.

Thanks

sai_krishna_L · September 17, 2024, 11:24am

Hi @Anshu_Kunal,

Please find the below code which I have already used.

String ent_type = getAttributeRequestValue(account,"ENTITLEMENT_TYPE");
String action_ID = getAttributeRequestValue(account, "ACTION_ID");

Thanks Regards,
Sai Krishna L

Anshu_Kunal · September 17, 2024, 12:03pm

Sorry @sai_krishna_L, small correction in my previous reply

Correction: These two attributes will not be available as part of provisioning plan and you would not be able to get it by using ‘getAttributeRequestValue’ method.

Provisioning plan object will only have value (Entitlement value) and name (Entitlement name) which you can retrieve using getAttributeRequestValue method in the rule.

Possible 2 options:

Use getManagedAttributeDetails method available via IdnRuleUtil (cloud rules only) and retrieve the required attributes using the entitlement value/name which is already available in the provisioning plan object.
Directly query the entitlement table and get those required column via standard java JDBC connection method.

Thanks

sai_krishna_L · September 19, 2024, 10:21am

Hi @Anshu_Kunal,

Could you please let me know where I can fetch this ManagedAttributeDetails object file for my source entitlements so that I can refer the attributes to be fetched.

Thanks Regards,
Sai Krishna L

Abhishek_1995 · September 19, 2024, 11:41am

Hi @sai_krishna_L
Please check the attribute you are providing it should be same as in store procedure list.
Check the column you have give should be same in provisioning plan.

Thank you!

Anshu_Kunal · September 19, 2024, 1:18pm

@sai_krishna_L

Below method is only available for cloud rule so you would not be able to use this in JDBC provisioning rule (connector rule).

Would suggest to try directly getting those values from DB.

Thanks

sai_krishna_L · October 7, 2024, 5:55am

Hi @Anshu_Kunal,

Thanks for your response,

I will incorporate the query and check it.
But while I am trying to execute the stored procedure in the DB2 Database the am receiving the below error:
java.sql.SQLException: Data type mismatch. (For input string: “SQL0204 SQLSTATE42704 DRPGSCP in HLDE40 type *FILE not found.”)

Any idea how to resolve this issue.

Kind Regards,
Sai krishna L

Anshu_Kunal · October 7, 2024, 6:20am

@sai_krishna_L

Are you able to run the stored procedure directly on DB?

You might have to reach out to your application DB team.

This part of the message specifies that the database object DRPGSCP in the schema 'HLDE40' of type '*FILE' (which typically refers to a table or a physical file in some database systems) was not found.

Thanks

sai_krishna_L · October 7, 2024, 6:25am

Hi @Anshu_Kunal,

Yes I did reach out with Application DB team, they did execute the SP manually with my integration account and it got success from there end.
Is there anything which I am missing here in the rule?

Kind Regards,
Sai Krishna L

Anshu_Kunal · October 7, 2024, 6:27am

@sai_krishna_L

Is this stored procedure for provisioning request? How are you passing the values to stored procedure? What format?

Thanks

sai_krishna_L · October 7, 2024, 6:40am

Hi @Anshu_Kunal,

Yes the SP is for the provisioning request. Below is the call information

CallableStatement cs = connection.prepareCall("call PROV (?, ?, ?, ?, ?, ?, ?)");
 cs.setString ( 1, String.valueOf('I'));  //Grant or revoke attribute
cs.setString ( 2, account_name); // unique ID
cs.setString ( 3, "OFFICE-CODE"); // It cannot be null. ENTITlEMENT_TYPE which is present in account schema.As of now it's hard coded.
cs.setString ( 4, entName); //RESOURCE_ID from account schema. ent value
cs.setString ( 5, "P"); // ACTION_ID from account schema
cs.setNull ( 6, java.sql.Types.VARCHAR); // Null. As of now it's hard coded.
cs.setString ( 7, "ABC");
cs.registerOutParameter(7, Types.VARCHAR); // INOUT pararmeter which consists of Success or failure.
cs.executeUpdate(); // I have tried cs.execute(); as well.

I have tried multiple ways with hard coded values, but I am receiving error.

SP definition and the sample call statement:


call PROV('I','UserId','OFFICE-CODE','TWAN','A','',?)

PROV

(In Action Char(1),           
	    UserId char(10),          
	    Type Varchar(13),         
	    Option Varchar(10),       
	    Authority Char(1),        
	    Deprovision Varchar(12),  
InOut Rtn VarChar (200))

Kind Regards,
Sai krishna L

Anshu_Kunal · October 7, 2024, 10:22am

Might have debug more by increasing the log level.

Can you try sql query as below?

String sql = "{CALL HLDE40.PROV(?, ?, ?, ?, ?, ?, ?)}";

Thanks

uday_kilambiCTS · October 7, 2024, 12:47pm

Hi @sai_krishna_L ,

I see that you used the same index 7 for twice.

cs.setString ( 7, "ABC");
cs.registerOutParameter(7, Types.VARCHAR); // INOUT pararmeter which consists of Success or failure.`

Have the registerOutParameter as 8th one(cs.registerOutParameter(8, Types.VARCHAR)) as 7 indexes are used to fill in the variables.

Regards,
Uday Kilambi

sai_krishna_L · October 7, 2024, 12:55pm

Hi @uday_kilambiCTS,

As the last parameter is an INOUT, so I have used same index 7, even the call definition has 6 input parameters and one INOUT parameter.

Kind regards,
Sai Krishna L

Topic		Replies	Views
Retrieve account attributes in JDBC Provision Rule IIQ Discussion and Questions identity-security-cloud , identityiq , provisioning	13	314	August 20, 2024
JDBC Provisioning Rule Enquiry ISC Discussion and Questions rules , identity-security-cloud , provisioning	6	97	October 21, 2024
Retrieving Account Attributes in JDBC Provision Rule ISC Discussion and Questions identity-security-cloud , jdbc-connector	14	1004	November 7, 2023
JDBC Provisioning Rule showing NULL values for Roles/Entitlements ISC Discussion and Questions rules , identity-security-cloud , provisioning , entitlements , jdbc-connector	7	713	May 20, 2024
JDBC Provisioning Guide ISC Discussion and Questions identity-security-cloud , provisioning , jdbc-connector	6	176	January 13, 2025

How to fetch the entitlement attributes in JDBC Provisioning Rule

Related topics