JDBC Provisioning Disable

gayare · July 12, 2024, 3:48am

Hi,

Below jdbc provision rule. We are doing below 3 scenarios, but when it disable it will trigger disable and immediately it set back enable in the SailPoint account tab

any idea what is the issue.

When user is hire in sailpoint and db it is set to Admin =1
once user is disable in the sailpoint and in db it is set to Admin = 0
When user is rehire will request it again account Admin =1

import java.sql.PreparedStatement;
	import java.sql.SQLException;
	import java.util.ArrayList;
	import java.util.Iterator;
	import java.util.List;

	import sailpoint.api.SailPointContext;
	import sailpoint.object.Identity;
	import sailpoint.object.ProvisioningPlan;
	import sailpoint.object.ProvisioningPlan.AccountRequest;
	import sailpoint.object.ProvisioningPlan.AttributeRequest;
	import sailpoint.object.ProvisioningResult;
	import sailpoint.tools.GeneralException;
	import sailpoint.tools.Util;
	import org.apache.log4j.Logger;
	
	ProvisioningResult result = new ProvisioningResult();
	if (plan != null && null != plan.getAccountRequests()) {
			for (AccountRequest accountRequest : plan.getAccountRequests()) {
				String accountName = null != accountRequest.getNativeIdentity() ? accountRequest.getNativeIdentity()
						: "";
				if (AccountRequest.Operation.Modify.equals(accountRequest.getOperation())) {
					Boolean adminEntRequest = false;
					Boolean adminEntRemoveRequest = false;
					String entitlementAttributeName = "Admin";
					List attributeRequestsList = null != accountRequest.getAttributeRequests()
							? (List) accountRequest.getAttributeRequests()
									: new ArrayList();
					if (!attributeRequestsList.isEmpty()) {
						Iterator attReqItr = attributeRequestsList.iterator();
						while (attReqItr.hasNext()) {
							AttributeRequest attrReq = (AttributeRequest) attReqItr.next();
							if (entitlementAttributeName.equalsIgnoreCase(attrReq.getName())) {
							if(attrReq.getValue() instanceof String) {
									if ("true".equalsIgnoreCase((String) attrReq.getValue())) {
										adminEntRequest = true;
									}
									if ("false".equalsIgnoreCase((String) attrReq.getValue())) {
										adminEntRemoveRequest = true;
									}
								}
							}
						}
					}

					if (adminEntRequest == true) {
						try {
							String SQL = "UPDATE ORIONUSERSMT SET ADMIN= ? WHERE NAME = ?";
							PreparedStatement statement = connection.prepareStatement(SQL);
							statement.setBoolean(1, true);
							statement.setString(2, accountName);
							statement.executeUpdate();
							result.setStatus(ProvisioningResult.STATUS_COMMITTED);

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

					if (adminEntRemoveRequest == true) {
						try {
							String SQL = "UPDATE ORIONUSERSMT SET ADMIN=? WHERE NAME = ?";
							PreparedStatement statement = connection.prepareStatement(SQL);
							statement.setBoolean(1, false);
							statement.setString(2, accountName);
							statement.executeUpdate();
							result.setStatus(ProvisioningResult.STATUS_COMMITTED);
						} catch (SQLException e) {
							result.setStatus(ProvisioningResult.STATUS_FAILED);
							result.addError(e);
						}
					}
				} else if (AccountRequest.Operation.Disable.equals(accountRequest.getOperation())) {
					try {
						String SQL = "UPDATE ORIONUSERSMT SET ADMIN=? WHERE NAME = ?";
						PreparedStatement statement = connection.prepareStatement(SQL);
						statement.setBoolean(1, false);
						statement.setString(2, accountName);
						statement.executeUpdate();
						result.setStatus(ProvisioningResult.STATUS_COMMITTED);
					} catch (SQLException e) {
						result.setStatus(ProvisioningResult.STATUS_FAILED);
						result.addError(e);
					}
				}
			}
		}
	return result;

gogubapu · July 12, 2024, 4:46am

Hi @gayare,

Using Provisioning Rule, we can be able to disable the account, but when we are performing account aggregation all accounts come in the source with Enable state only. We have JDBC Build Map rule to achieve this case. Find below reference link and try
JDBC BuildMap Rule | SailPoint Developer Community

Use below in the rule,
map.put(“IIQDisabled”, “true”);

Thank You.

iamnithesh · July 12, 2024, 12:48pm

Have you checked in the database table if updates are taking effect?

gayare · July 13, 2024, 2:15am

In table it is set to disable, but IDN only it is moved disable to enable.

iamnithesh · July 13, 2024, 4:56pm

You need to tell ISC when an account is to be set as disabled. You can either do it in the query used for account aggregation by adding a CASE statement, or you can follow what @gogubapu has suggested.

Query would look like

SELECT *, CASE WHEN...... AS IIQDisabled FROM tableName

where you will have to add the logic to return true or false as value for IIQDisabled attribute

gayare · July 16, 2024, 5:27am

I created below rule still getting the error.

sailpoint.connector.ConnectorException: The application script threw an exception: java.lang.ClassCastException: Cannot cast java.lang.Boolean to java.lang.String BSF info: BuildMap for JDBC at line: 0 column: columnNo

import sailpoint.connector.*;
     Map map = JDBCConnector.buildMapFromResultSet(result, schema);
     String admin = null != map.get("Admin") ? (String) map.get("Admin") : "";

     if("false".equalsIgnoreCase(admin)) {
          map.put("IIQDisabled", true);
     } 
     return map;

gogubapu · July 16, 2024, 9:31am

Hi @gayare,

The rule throwing classCastException, it means you’re trying to store Boolean value into String, can you explain the logic to keep user record disable.

you may try below code:
import sailpoint.connector.;
import java.sql.ResultSet;
import java.sql.;
import java.util.Map;
import java.sql.Connection;
import java.sql.DriverManager;
import sailpoint.object.Schema;
import sailpoint.connector.JDBCConnector;
import sailpoint.tools.Util;
import sailpoint.connector.ConnectorException;
import java.sql.SQLException;

 // Process the result set and populate the map 
 Map map = JDBCConnector.buildMapFromResultSet(result, schema);
	
 String admin = (String) map.get("Admin");
try 
{ 
	//if you have any other conditions, you can add below
	if( "false".equalsIgnoreCase(admin))
	{ 
		map.put("IIQDisabled","true"); 
        map.put("Admin","false");
	}
} 
catch (Exception e) 
{ 
    // Handle SQL exception 
	log.info("======Error======="+e); 
} 
	 
 return map;

Thank You.

gayare · July 16, 2024, 3:22pm

once we add this JDBC applicationwhen into identity profile provisining tab when the user is disable it should set to disable in sailpoint and DB side Admin=0

gayare · July 16, 2024, 6:15pm

Below is the use case.

1.when user will raise the entitlement true (name of the entitlement is true) in the DB it will set to admin =1 and will aggregate and the entitlement is attached to true.

when the user account is disable it will set the disabled in IDN and DB also it is set to Admin=0 as showing in the below screenshot.

but when I run the aggregation, it will set back to enabled in the IDN and entitlement is true. In DB it remains set to admin = 0

when I used your code, I was getting below error and modified the logic. still not working.

sailpoint.connector.ConnectorException: BeanShell script error: bsh.ParseException: Parse error at line 1, column 28. Encountered: ; BSF info: BuildMap McAfee at line: 0 column: columnNo


import java.sql.ResultSet;

	import java.util.Map;

	import sailpoint.connector.JDBCConnector;

	import sailpoint.object.Schema;
 
	Map map = JDBCConnector.buildMapFromResultSet(result, schema);

	try {

		String admin = null != map.get("Admin") ? (String) map.get("Admin") : "";

		if ("false".equalsIgnoreCase(admin)) {

			map.put("IIQDisabled", "true");

		}

	} catch (Exception e) {

		log.debug("**Exception**" + e);

	}

return map;

gogubapu · July 17, 2024, 5:09am

You may confirm after performing account aggregation, you’re getting ADMIN attribute value is 0 or 1, OR true or false. if you’re getting 0 or 1 change if condition accordingly.

You may try below rule

import sailpoint.connector.JDBCConnector;  import java.sql.ResultSet;  import java.util.Map;
import sailpoint.object.Schema;

Map map = JDBCConnector.buildMapFromResultSet(result, schema);

try {
	//admin=0 is false, admin=1 is true
	Boolean isAdmin = (Boolean) map.get("Admin");
	
	if (isAdmin != null )
	{
        if(isAdmin){
            map.put("Disabled", "true");
			map.put("IIQDisabled", "true");
        }
	}

} 	
catch (Exception e)
{

	log.debug("**Exception**" + e);

}  return map;

gayare · July 24, 2024, 2:31am

in IDN True and False and in the DB 1 and 0

When I Disable the user in IDN, it will provision the user and set to 0 in DB, but in IDN it will show true only. After 2 aggregation the account again set enable.


      import java.sql.ResultSet;

	import java.util.Map;

	import sailpoint.connector.JDBCConnector;

	import sailpoint.object.Schema;
 
	Map map = JDBCConnector.buildMapFromResultSet(result, schema);

	try {

		Boolean admin = null != map.get("Admin") ?  (Boolean) map.get("Admin") : false;

		if(admin == false) {

			map.put("IIQDisabled", true);

		}

	} catch (Exception e) {

		log.debug("**Exception**" + e);

	}

return map;

gogubapu · July 24, 2024, 4:26am

You may use above code recently posted. i saw some of your screenshots shared here and added some lines in above code: if condition satisfied you need use like below
map.put(“disabled”, true);
map.put(“IIQDisabled”, true);

gayare · July 24, 2024, 11:59pm

I tried above given code and by default all the user it will set the disabled.

gogubapu · July 25, 2024, 4:24am

You only said that if admin=1 attribute value user account should active, if admin=0 then account should in disabled state. can you confirm if admin=0 it is fetch true or false in the IDN.

in build map change like if admin=true keep account enable, if admin=false keep account disabled state.

can you share one user attributes to verify without buildmap rule aggregation, that user admin values, after performing disabled.

check your provisioning rule.

gogubapu · August 2, 2024, 4:30am

Hi @gayare,

Use below code to achieve your use case, below code works when user admin attribute value active and disable attribute value false, then the particular account in IDN active, if admin attribute value false and the disabled attribute true or false that account in IDN keeps inactive.

import sailpoint.connector.JDBCConnector;  
import java.sql.ResultSet;  
import java.util.Map;
import sailpoint.object.Schema;

Map map = JDBCConnector.buildMapFromResultSet(result, schema);

try {
	//admin=0 is false, admin=1 is true
	Boolean isAdmin = (Boolean) map.get("Admin");
	Boolean isDisabled = (Boolean) map.get("Disabled");
	Boolean Disabledfalse = new Boolean(false);
	Boolean Admintrue = new Boolean(true);
	if (isAdmin != null && isDisabled != null)
	{
		if(isDisabled.equals(false) && isAdmin.equals(Admintrue))
		{
           map.put("Disabled", "false");
        }
		else
		{
			map.put("Disabled", "true");
			map.put("IIQDisabled", "true");
		}
	}
} 	
catch (Exception e)
{

	log.debug("**Exception**" + e);

}  

return map;

Thank You.

kdfreeman · August 2, 2024, 6:43am

Add account create code inside Create block. Modify block if for addition and removal of entitlement for that account . Only if you remove or add entitlements, then you can see ADD or REMOVAL in plan that will come as Accountrequest Operation.

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

system · October 1, 2024, 6:44am

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