JDBC Store Procedure provisoning issue

gayare · October 1, 2024, 8:02pm

Hi,

I have created JDBC connector and using below JDBC provisioning rule (Store procedures). when I tried to provision getting below error.

java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to ‘PR_INSERT_USER’ ORA-06550: line 1, column 7: PL/SQL: Statement ignored ORA-06550 - Database Error Messages

From DB we are able to create user successfully, but SailPoint it is getting failed.

import java.sql.CallableStatement;
	import java.sql.SQLException;
	import java.util.List;

	import sailpoint.object.ProvisioningPlan;
	import sailpoint.object.ProvisioningPlan.AccountRequest;
	import sailpoint.object.ProvisioningPlan.AttributeRequest;
	import sailpoint.object.ProvisioningResult;

	public String getAttributeRequestValue(AccountRequest acctReq, String attribute) {
	if (acctReq != null) {
		AttributeRequest attrReq = acctReq.getAttributeRequest(attribute);
		if (attrReq != null) {
			return (String) attrReq.getValue();
		}
	}
	return null;
	}
	ProvisioningResult result = new ProvisioningResult();
	CallableStatement statement;

	if (plan != null) {
		List accounts = plan.getAccountRequests();
		if ((accounts != null) && (accounts.size() > 0)) {
			for (AccountRequest account : accounts) {
				try {
					if (AccountRequest.Operation.Create.equals(account.getOperation())) {
						statement = connection.prepareCall("{call CSGSYSREP.CSGS_USER_ROLE_API_PKG.PR_INSERT_USER(?,?,?,?,?)}");
						statement.setString(1, (String) account.getNativeIdentity());
						// statement.setString ( 2, getAttributeRequestValue(account,"USER_ID") );
						statement.setString(2, getAttributeRequestValue(account, "FIRST_NAME"));
						statement.setString(3, getAttributeRequestValue(account, "LAST_NAME"));
						statement.setString(4, getAttributeRequestValue(account, "EMAIL_ID"));
						statement.setString(5, getAttributeRequestValue(account, "ROLE_NM"));
						statement.executeUpdate();

						result.setStatus(ProvisioningResult.STATUS_COMMITTED);
					}
				} catch (SQLException e) {
					result.setStatus(ProvisioningResult.STATUS_FAILED);
					result.addError(e);
				}
			}
		}
		return result;
	}

arjun_sengupta · October 1, 2024, 8:27pm

Hi Gayathri,

Is the number and data type of arguments passed in the function same as the stored procedure?

Regards
Arjun

gayare · October 2, 2024, 1:48am

How to find that, What type of data arguments procedure accepting ??

arjun_sengupta · October 2, 2024, 1:51am

Hi Gayathri,

You can check the stored procedure definition. First 2 are input arguments

Ex:

CREATE PROCEDURE uspUpdateEmpSalary
    @empId INT,
    @salary MONEY
AS
BEGIN
    UPDATE dbo.Employee
    SET Salary = @salary
    WHERE EmployeeID = @empId;
END

There are some return arguments in your screenshot which also needs to be passed and will be defined as Out arguments.

Regards
Arjun

pallavi · October 2, 2024, 2:50am

Hi Gayathri,

Is this issue resolved? If its still reproducible after fixing the number of input/output argument, then please check if its related to the type of argument. For e.g., we were receiving ORA-06550 error when the date column field value was provided in incorrect format.

Thanks,
Pallavi

gayare · October 10, 2024, 9:27pm

I updated the rule and tested the account is creating, but it is not replicating into DB the created account and I can see USER_ID is not populated in SailPoint. Any idea?

import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.PreparedStatement;
  import java.sql.SQLException;
  import java.sql.Types;
  import java.util.List;
  import sailpoint.api.SailPointContext;
  import sailpoint.connector.JDBCConnector;
  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;
  import sailpoint.object.Schema;

  public String getAttributeRequestValue(AccountRequest acctReq, String attribute) {

    if ( acctReq != null ) {
      AttributeRequest attrReq = acctReq.getAttributeRequest(attribute);
      if ( attrReq != null ) {
        return attrReq.getValue();
      }
    }
    return null;
  }

  ProvisioningResult result = new ProvisioningResult();
  PreparedStatement statement;

  if ( plan != null ) {

    List accounts = plan.getAccountRequests();
    if ( ( accounts != null ) && ( accounts.size() > 0 ) ) {
      for ( AccountRequest account : accounts ) {
        try {
          if ( AccountRequest.Operation.Create.equals( account.getOperation() ) ) 
		  {
				result.setStatus( ProvisioningResult.STATUS_FAILED );
				result.addError("Create Account Block is not available in the JDBC provisining rule.");
					

          }
		  else if ( AccountRequest.Operation.Modify.equals( account.getOperation() ) ) {

            // Modify account request -- change role

            PreparedStatement statement = connection.prepareStatement( "UPDATE ORIONUSERSMT SET ADMIN= ? WHERE NAME = ?" );
            statement.setString ( 2, (String) account.getNativeIdentity() );
			try
			{
				if ( account != null ) {
				  AttributeRequest attrReq = account.getAttributeRequest("groups");
					if(attrReq != null && attrReq.getValue() instanceof String) 
					{
					    if ( attrReq != null && ProvisioningPlan.Operation.Remove.equals(attrReq.getOperation()) ) {
							statement.setString ( 1, "0" );
							statement.executeUpdate();
							//result.setStatus( ProvisioningResult.STATUS_COMMITTED );
						} 
						else {
							statement.setString(1, attrReq.getValue());
							statement.executeUpdate();
							//result.setStatus( ProvisioningResult.STATUS_COMMITTED );
						}	
					}
					else{
						//result.setStatus( ProvisioningResult.STATUS_COMMITTED );
					}
				}
			}
			catch (SQLException e) 
			{
				result.setStatus(ProvisioningResult.STATUS_FAILED);
				result.addError(e);
			}
            result.setStatus( ProvisioningResult.STATUS_COMMITTED );

          } else if ( AccountRequest.Operation.Disable.equals( account.getOperation() ) ) {

           
			PreparedStatement statement = connection.prepareStatement( "UPDATE ORIONUSERSMT SET ADMIN= ? WHERE NAME = ?" );
			try
			{
				statement.setString ( 2, (String) account.getNativeIdentity() );
				statement.setString ( 1, "0" );
				statement.executeUpdate();
				result.setStatus( ProvisioningResult.STATUS_COMMITTED );
			}
			catch (SQLException e) 
			{
				result.setStatus(ProvisioningResult.STATUS_FAILED);
				result.addError(e);
			}
          }
        }
        catch( SQLException e ) {
          result.setStatus( ProvisioningResult.STATUS_FAILED );
          result.addError( e );
        }
        finally {
          if(statement != null) {
            statement.close();
          }
        }
      }
    }
  }

  return result;

rkhade · October 11, 2024, 5:39am

Hi @gayare ,

I have used Below rule in JDBC connector to create accounts using stored procedure, can you try this once

Please modify the rule as per the requirement,

import sailpoint.object.ProvisioningPlan;
import sailpoint.object.ProvisioningPlan.AccountRequest;
import sailpoint.object.ProvisioningPlan.AttributeRequest;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;



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

if ( plan != null ) {
	
    List accounts = plan.getAccountRequests();
	
    if ( ( accounts != null ) && ( accounts.size() > 0 ) ) {
        for ( AccountRequest account : accounts ) {
			
			if ( AccountRequest.Operation.Create.equals( account.getOperation() ) ) {
					String employeeID = account.getNativeIdentity();
					String displayName =  getAttributeRequestValue(account,"Full Name");
					String deptName = getAttributeRequestValue(account,"Department");
					int role = getAttributeRequestValue(account,"Role");

					CallableStatement st = connection.prepareCall("? = call dbo.up_addUserAccess(?,?,?,?,?)");
					st.registerOutParameter(1, java.sql.Types.INTEGER);
					st.setString(2, employeeID);
					st.setString(3, displayName);
					st.setInt(4, role);
					st.setString(5, "8190");
					st.setString(6, deptName);
					st.execute();
					result.setStatus(ProvisioningResult.STATUS_COMMITTED);
				}
		}
	}
}
return result;

Let me know how it goes

Thank you

system · December 10, 2024, 5:40am

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
JDBC provisioning Error in Global Provisioning Rule IIQ Discussion and Questions identityiq , provisioning	7	63	December 2, 2024
JDBC Provisioning Rule showing NULL values for Roles/Entitlements ISC Discussion and Questions rules , identity-security-cloud , provisioning , entitlements , jdbc-connector	7	693	May 20, 2024
JDBC Provisioining Rule for create operation ISC Discussion and Questions identity-security-cloud , jdbc-connector	3	319	May 26, 2024
Unknown error: null JDBC Before Provisioning Rule IIQ Discussion and Questions rules , identityiq , jdbc-connector	3	137	July 27, 2024
cloudPreviousValues in JDBC rule ISC Discussion and Questions rules , identity-security-cloud	11	97	October 13, 2024

JDBC Store Procedure provisoning issue

Related topics