Calling a sql server stored procedure for rule provisioning

castillo_heidi · March 31, 2022, 7:11pm

IdentityIQ, 8.2

For a one application, which is JDBC, I want to call a stored procedure and pass 3 parameters, which are global mappings. The store procedure will then insert or update in a specific table.
I tried this :
import sailpoint.object.ProvisioningPlan.AccountRequest;
import sailpoint.object.ProvisioningPlan.AttributeRequest;
import sailpoint.object.ProvisioningResult;
import org.apache.log4j.Logger;
import org.apache.log4j.Level;

Logger logger = Logger.getLogger(“rule. Provisioning Rule”);
logger.setLevel(Level.DEBUG);
logger.debug(“Provisioning Rule Starting>”);
ProvisioningResult provisioningResult = new ProvisioningResult();

if ((plan != null) && (connection != null) )
{
List accountRequests = plan.getAccountRequests(application.getName());
for (AccountRequest accountRequest : accountRequests)
{

                            if(accountRequest.getOperation().equals(AccountRequest.Operation.Create))
                            {

                                            logger.debug("In accountRequest creation operation for Id : "+accountRequest.getNativeIdentity());
                                            logger.debug("Plan to xml is >>"+plan.toXml());
                                            
                                            String stringspcall="call dbo.sp_name("+accountRequest.getAttributeRequest("employeeID")+","+accountRequest.getAttributeRequest("networkID")+","+accountRequest.getAttributeRequest("email")+")";



                                            logger.debug("The sp Query query is :"+insertQuery);
                                            int countRow=connection.prepareStatement(stringspcall).executeUpdate();

                                            if(countRow>0)
                                            {
                                                            provisioningResult.setStatus(ProvisioningResult.STATUS_COMMITTED);
                                                            logger.debug("The insert Query query executed successfully in target DB for Id: "+accountRequest.getNativeIdentity() );

                                            }
                                            else
                                            {
                                                            provisioningResult.setStatus(ProvisioningResult.STATUS_FAILED);
                                                            logger.error("Error in performing insert Query operation for Id : "+accountRequest.getNativeIdentity() );
                                            }
                                            

                            }
            }

}
logger.debug(“End Provisioning Rule”);
return provisioningResult;

I am getting this error:
An unexpected error occurred: java.lang.Exception: sailpoint.tools.GeneralException: BeanShell script error: bsh.EvalError: Sourced file: inline evaluation of: import sailpoint.object.ProvisioningPlan.AccountRequest; import sailpoint.objec . . . '' : Typed variable declaration : Attempt to resolve method: getName() on undefined variable or class name: application : at Line: 14 : in file: inline evaluation of: import sailpoint.object.ProvisioningPlan.AccountRequest; import sailpoint.objec . . . ‘’ : application .getName ( ) BSF info: TWkfc_AD_Mail_Xref at line: 0 column: columnNo

paul_wheeler · March 31, 2022, 7:52pm

I’m not sure why application.getName() is not working, because application should normally be passed to a JDBC provisioning rule. But you should not need this. Any provisioning plan being processed by the provision rule for this application will only have AccountRequests for this application. So you can do this instead:

List accountRequests = plan.getAccountRequests();

castillo_heidi · March 31, 2022, 8:15pm

Thank you for responding!

I changed the code to the below. I’m getting this error:

An unexpected error occurred: java.lang.Exception: sailpoint.tools.GeneralException: BeanShell script error: bsh.EvalError: Sourced file: inline evaluation of: import sailpoint.object.ProvisioningPlan.AccountRequest; import sailpoint.objec . . . '' : Typed variable declaration : Attempt to resolve method: getAccountRequests() on undefined variable or class name: plan : at Line: 14 : in file: inline evaluation of: import sailpoint.object.ProvisioningPlan.AccountRequest; import sailpoint.objec . . . ‘’ : plan .getAccountRequests ( ) BSF info: tableName at line: 0 column: columnNo

import sailpoint.object.ProvisioningPlan.AccountRequest;

import sailpoint.object.ProvisioningPlan.AttributeRequest;

import sailpoint.object.ProvisioningResult;

import org.apache.log4j.Logger;

import org.apache.log4j.Level;

Logger logger = Logger.getLogger(“rule. Provisioning Rule”);

logger.setLevel(Level.DEBUG);

logger.debug(“Provisioning Rule Starting>”);

ProvisioningResult provisioningResult = new ProvisioningResult();

if ((plan != null) && (connection != null) )

{

List accountRequests = plan.getAccountRequests();

for (AccountRequest accountRequest : accountRequests)

{

if(accountRequest.getOperation().equals(AccountRequest.Operation.Create))

{

logger.debug("In accountRequest creation operation for Id : "+accountRequest.getNativeIdentity());

logger.debug(“Plan to xml is >>”+plan.toXml());

String stringspcall=“call dbo.dbo.sp_name(”+accountRequest.getAttributeRequest(“employeeID”)+","+accountRequest.getAttributeRequest(“networkID”)+","+accountRequest.getAttributeRequest(“email”)+")";

logger.debug(“The sp Query query is :”+insertQuery);

int countRow=connection.prepareStatement(stringspcall).executeUpdate();

if(countRow>0)

{

provisioningResult.setStatus(ProvisioningResult.STATUS_COMMITTED);

logger.debug("The insert Query query executed successfully in target DB for Id: "+accountRequest.getNativeIdentity() );

}

else

{

provisioningResult.setStatus(ProvisioningResult.STATUS_FAILED);

logger.error("Error in performing insert Query operation for Id : "+accountRequest.getNativeIdentity() );

}

logger.debug(“End Provisioning Rule”);

return provisioningResult;

I’m wondering if I am configuring the connector correctly?

Here’s the connection settings. I just did a select to the table that the insert will happen. Then on the rulesàcreation Rule, I added the code above

Many thanks!

Heidi.

paul_wheeler · March 31, 2022, 8:33pm

A creation rule is for creating an identity from an aggregated account. Isn’t this a provision rule, for provisioning to the JDBC database? Try adding the code as the provision rule on the application.

castillo_heidi · March 31, 2022, 8:58pm

I placed it in this section and I’m still getting an error

The error reads

An unexpected error occurred: java.lang.Exception: sailpoint.tools.GeneralException: BeanShell script error: bsh.EvalError: Sourced file: inline evaluation of: import sailpoint.object.ProvisioningPlan.AccountRequest; import sailpoint.objec . . . '' : Typed variable declaration : Attempt to resolve method: getAccountRequests() on undefined variable or class name: plan : at Line: 14 : in file: inline evaluation of: import sailpoint.object.ProvisioningPlan.AccountRequest; import sailpoint.objec . . . ‘’ : plan .getAccountRequests ( ) BSF info: TWkfc_AD_Mail_Xref at line: 0 column: columnNo

Thanks again!

paulo_urcid · March 31, 2022, 9:07pm

The error is most likely caused by trying to use a different Rule type that is not a JDBCProvisioning or JDBCOperationProvisioning type

paul_wheeler · March 31, 2022, 9:13pm

An after provisioning rule is a different type of rule. You want to set it as the Provision Rule, which is a type of rule specifically for JDBC applications.

castillo_heidi · April 1, 2022, 3:33pm

Hi Paul,

I changed to rule under: Rulesà”Connector Rules” sectionà**”Provision Rule”** for the specific jdbc connector.

I stripped the code to only:

import sailpoint.object.ProvisioningResult;

import sailpoint.object.ProvisioningPlan;

import sailpoint.object.ProvisioningPlan.AccountRequest;

import sailpoint.object.ProvisioningPlan.AttributeRequest;

import java.sql.PreparedStatement;

ProvisioningResult provisioningResult = new ProvisioningResult();

provisioningResult.setStatus(ProvisioningResult.STATUS_FAILED);

return provisioningResult;

and I still get the same error :

An unexpected error occurred: java.lang.Exception: sailpoint.tools.GeneralException: BeanShell script error: bsh.EvalError: Sourced file: inline evaluation of: import sailpoint.object.ProvisioningPlan.AccountRequest; import sailpoint.objec . . . '' : Typed variable declaration : Attempt to resolve method: getAccountRequests() on undefined variable or class name: plan : at Line: 14 : in file: inline evaluation of: import sailpoint.object.ProvisioningPlan.AccountRequest; import sailpoint.objec . . . ‘’ : plan .getAccountRequests ( ) BSF info: TWkfc_AD_Mail_Xref at line: 0 column: columnNo

The error is referring to the table that I have under the SQL Statement configuration. Should I be configuring this portion differently?

Thank you,

Heidi.

paul_wheeler · April 1, 2022, 3:55pm

No, you have a select staement instead of a stored procedure. You would normally have something like this:

EXEC <stored procedure name>

However, that is not what is causing your current problem. The error is with the Beanshell in a rule calling a method on the plan variable, when the plan variable is not available for that rule. You do not even reference the plan variable in your stripped down code for the provision rule, so I’m guessing you have left one of the previous rules in place (creation rule or before provisioning rule). Please remove these.

castillo_heidi · April 1, 2022, 4:21pm

I removed all of the rules already. My guess is that it’s with the store procedure configuration?

It has 3 parameters. How would I call that, would I need to have an output ? Currently, it accepts 3 parameters, employeeID, networkID, emailaddress. It performs the insert or update in the table based on if it exists or does not exist. I thought that the best way to do this would be with the provisioning rule.

What I would like is when a user is created or the network , email address changes, then for the store procedure to run for the JDBC connection. We need to run the sp because the store procedure does a look up on other database specific identity keys that sailpoint does not have.

I really appreciate the help!

Heidi.

paul_wheeler · April 1, 2022, 5:27pm

Hi Heidi,

The error you are seeing is related to beanshell code in a rule somewhere, not to your stored procedure. Check your application xml in the debug page to see if there are any rules associated with the application that could be causing this.

But the stored procedure under “SQL Statement” is for aggregation only - it is not used for provisioning. Provisioning is handled by the provision rule. See the example JDBCProvision rule here: https://community.sailpoint.com/t5/Technical-White-Papers/Rules-in-IdentityIQ-7-0-7-2/ta-p/78176
I’ve never seen a stored procedure called from that rule, but it could be done. There is an example of the code you would need here: JDBC Examples for Calling Stored Procedures (MySQL)

castillo_heidi · April 14, 2022, 7:47pm

Hi Paul,

I got it working on insert and with modify when all 3 values are given.

I am having problems with handling null values on modify. For example, if networkID is different, but email is the same, the plan is sending a null value for email. I need to send the identity global email value for the given identity. Basically all 3 values are required values in the store procedure.

The line that i am getting an error is with
account.add(new AttributeRequest(“INTL_EMAIL_ADDR”,ProvisioningPlan.Operation.Set,getattribute(“email”)));

this is the error

`import java.sql.Connection; import java.sql.DriverManager; import java.sql.Cal . . . ‘’ : getattribute ( “email” )

Current Code

import java.sql.CallableStatement;
import java.sql.Statement;
import java.sql.SQLException;
import java.util.List;
import sailpoint.object.Application;
import sailpoint.object.ProvisioningPlan;
import sailpoint.object.ProvisioningPlan.AccountRequest;
import sailpoint.object.ProvisioningPlan.AttributeRequest;
import sailpoint.object.ProvisioningPlan.PermissionRequest;
import sailpoint.object.ProvisioningResult;

public Object getAttributeRequestValue(AccountRequest acctReq, String attribute) {
if ( acctReq != null ) {
AttributeRequest attrReq = acctReq.getAttributeRequest(attribute);
if ( attrReq != null ) {
return attrReq.getValue();
}
}
return null;
}

/*last test BZumar1//

ProvisioningResult result = new ProvisioningResult();

if ( plan != null ) {
logger.error("*** \n The Provisioning Plan being passed in = \n***\n" + plan.toXml() + “\n****************************************”);
List accounts = plan.getAccountRequests();

if ( ( accounts != null ) && ( accounts.size() > 0 ) ) {



	for ( AccountRequest account : accounts ) {

		try {
			if (AccountRequest.Operation.Create.equals(account.getOperation()) || AccountRequest.Operation.Modify.equals(account.getOperation())) {
						logger.error("Operation - " + account.getOperation());
						PreparedStatement empodsstatement = connection.prepareCall("{call dbo.sp_name(?, ?, ?)}");
						
						
						logger.error("Parameter plan nativeidentity - " + (String) plan.getNativeIdentity());
						
						logger.error("Parameter 1 - " + (String) account.getNativeIdentity());
						logger.error("Parameter 2 - " + getAttributeRequestValue(account,"NETWORK_ID"));
					
					
						if (getAttributeRequestValue(account,"INTL_EMAIL_ADDR")==null)
						{
							
							account.add(new AttributeRequest("INTL_EMAIL_ADDR", ProvisioningPlan.Operation.Set,getattribute("email")));
						
							 empodsstatement.setString(3,getAttributeRequestValue(account,"INTL_EMAIL_ADDR"));
						}
						else {
							empodsstatement.setString(3,getAttributeRequestValue(account,"INTL_EMAIL_ADDR"));
							logger.error("Parameter 3 - " + getAttributeRequestValue(account,"INTL_EMAIL_ADDR"));
						}
						
						logger.error("Parameter 1 - " + (String) account.getNativeIdentity());
						
						logger.error("Parameter 2 - " + getAttributeRequestValue(account,"NETWORK_ID"));

						empodsstatement.setString(1, (String) account.getNativeIdentity());
						empodsstatement.setString(2,(String) plan.getNativeIdentity());
						
						
						empodsstatement.execute();
						provisioningResult.setStatus(ProvisioningResult.STATUS_COMMITTED);
			
			} 

		} catch (SQLException e) {

                    System.out.println("Error: " + e);

                    result.setStatus(ProvisioningResult.STATUS_FAILED);

                    result.addError(e);

		} 

	}    // account request loop

}     // if account requests exist

} // if plan not null

return result;

vita4501 · April 25, 2022, 6:14pm

HI
what is this method – getattribute(“email”)

Topic		Replies	Views
JDBC provisioning Error in Global Provisioning Rule IIQ Discussion and Questions identityiq , provisioning	7	80	December 2, 2024
Updating role information in database using global provisioning rule IIQ Discussion and Questions rules , identityiq , provisioning , access-requests , jdbc-connector	5	83	December 21, 2024
JDBC Store Procedure provisoning issue ISC Discussion and Questions rules , identity-security-cloud , provisioning	7	102	December 10, 2024
Jdbc rule provisioning IIQ Discussion and Questions identityiq , provisioning	7	1113	December 9, 2023
JDBC Provisioning Rule showing NULL values for Roles/Entitlements ISC Discussion and Questions rules , identity-security-cloud , provisioning , entitlements , jdbc-connector	7	730	May 20, 2024

Calling a sql server stored procedure for rule provisioning

Related topics