JDBC Connector and NativeIdentity

moss_snake_01 · January 18, 2024, 8:19pm

Hello,

I am trying to create a DB2 JDBC Connector to perform some attribute syncs. I’ve been trying to utilize the following code to ensure I am passing the EEID correctly for the requests it is iterating through. I did some research and found that setting the attribute to the nativeIdentity should return the EEID, However, after reviewing the trace in DB2, it looks like its sending the NETID instead (this maps to SearchSam).

   import java.sql.Types;
    import java.sql.CallableStatement;
    import java.sql.PreparedStatement;
    import java.sql.Date;
    import java.sql.Decimal;

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

    import sailpoint.tools.Util;

    // Get a string value from an attribute request object
    public String getAttributeRequestValue(AccountRequest acctReq, String attribute) {
      String val = null;
      if ( acctReq != null ) {
        AttributeRequest attrReq = acctReq.getAttributeRequest(attribute);
        if ( attrReq != null && attrReq.getValue() instanceof String ) {
          val = attrReq.getValue();
        }
      }
      log.debug(attribute + \" = \" + val);
      return val;
    }

    // Start of iSeries Provision rule
    log.debug(\"Enter iSeries JDBC Provisioning\");

    ProvisioningResult result = new ProvisioningResult();
    CallableStatement cStatement = null;

    // Check if plan is null
    if ( plan instanceof ProvisioningPlan ) {
      // Get all account requests from plan
      List accounts = plan.getAccountRequests();
      // If the plan contains one or more account requests, we'll iterate through them
      if ((accounts != null) && (accounts.size() > 0)) {
        for (AccountRequest account : accounts) {
          String nativeId = (String) account.getNativeIdentity();
          log.debug(\"native identity: \" + nativeId);
          log.debug(\"application: \" + account.getApplicationName());
          log.debug(\"operation: \" + account.getOperation());

          try {
            // CREATE procedure
            if ( AccountRequest.Operation.Create == account.getOperation() ) {
              log.debug(\"Entering Create Operation\");

              cStatement = connection.prepareCall(\"CALL OBJECT.EX850SP (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\");

              // Add attributes from account create profile
              log.debug(\"setting value for index 1\");
              cStatement.setString(1, getAttributeRequestValue(account, \"CUSTS\")); //employment status code
              log.debug(\"setting value for index 2\");
              cStatement.setString(2, getAttributeRequestValue(account, \"CUETYP\")); //employee type code
              log.debug(\"setting value for index 3\");
              cStatement.setString(3, getAttributeRequestValue(account, \"CULOCN\")); //location code
              log.debug(\"setting value for index 4\");
              cStatement.setString(4, getAttributeRequestValue(account, \"CUEMAD\")); //work email
              log.debug(\"setting value for index 5\");
              cStatement.setString(5, getAttributeRequestValue(account, \"CUEMPL\")); //company code
              log.debug(\"setting value for index 6\");
              cStatement.setString(6, getAttributeRequestValue(account, \"CUFNAM\")); //legal first name
              log.debug(\"setting value for index 7\");
              cStatement.setString(7, getAttributeRequestValue(account, \"CULNAM\")); //last name
              log.debug(\"setting value for index 8\");
              cStatement.setString(8, getAttributeRequestValue(account, \"CUSPRV\")); //manager name
              log.debug(\"setting value for index 9\");
              cStatement.setString(9, nativeId); //search sam upper (CUNETU)
              log.debug(\"setting value for index 10\");
              cStatement.setString(10, getAttributeRequestValue(account, \"CUDPDS\")); //department description
              log.debug(\"setting value for index 11\");
              cStatement.setString(11, getAttributeRequestValue(account, \"CUJBDS\")); //job title
              log.debug(\"setting value for index 12\");
              cStatement.setString(12, getAttributeRequestValue(account, \"CULEAD\")); //employee management level
              log.debug(\"setting value for index 13\");
              cStatement.setString(13, getAttributeRequestValue(account, \"CUODCD\")); //org level 1 code
              log.debug(\"setting value for index 14\");
              if ( getAttributeRequestValue(account, \"CUEXDT\") != null ) {
                log.debug(\"setting CUEXDT to \" + Util.atoi(getAttributeRequestValue(account, \"CUEXDT\")));
                cStatement.setInt(14, Util.atoi(getAttributeRequestValue(account, \"CUEXDT\"))); //iSeries term date
              } else {
                log.debug(\"setting CUEXDT to 0\");
                cStatement.setInt(14, 0);
              }
              log.debug(\"setting value for index 15\");
              cStatement.setString(15, getAttributeRequestValue(account, \"CULUDT\")); //iSeries update date
              log.debug(\"setting value for index 16\");
              cStatement.setString(16, getAttributeRequestValue(account, \"CULUBY\")); //service user
              log.debug(\"setting value for index 17\");
              cStatement.setString(17, (String) account.getNativeIdentity()); //employee id
              //cStatement.setString(17, identityNumber); //employee id
              //Statement.setString(17, getAttributeRequestValue(account, \"CUEEID\")); //employee id
              log.debug(\"setting value for index 18\");
              cStatement.setString(18, getAttributeRequestValue(account, \"CUROLE\")); //role
              log.debug(\"EX850SP full statement: \" + cStatement.toString());

              cStatement.executeUpdate();

              // Successful Create, so mark result as COMMITTED
              log.debug(\"Create Account Execution successfully completed\");
              result.setStatus(ProvisioningResult.STATUS_COMMITTED);

              log.debug(\"Exiting Create Operation\");
            } else if ( AccountRequest.Operation.Modify == account.getOperation() ) {
              log.debug(\"Entering Modify Operation\");

              cStatement = connection.prepareCall(\"CALL OBJECT.EX850SP (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\");

              // Add attributes from account modify profile
              log.debug(\"setting value for index 1\");
              cStatement.setString(1, getAttributeRequestValue(account, \"CUSTS\")); //employment status code
              log.debug(\"setting value for index 2\");
              cStatement.setString(2, getAttributeRequestValue(account, \"CUETYP\")); //employee type code
              log.debug(\"setting value for index 3\");
              cStatement.setString(3, getAttributeRequestValue(account, \"CULOCN\")); //location code
              log.debug(\"setting value for index 4\");
              cStatement.setString(4, getAttributeRequestValue(account, \"CUEMAD\")); //work email
              log.debug(\"setting value for index 5\");
              cStatement.setString(5, getAttributeRequestValue(account, \"CUEMPL\")); //company code
              log.debug(\"setting value for index 6\");
              cStatement.setString(6, getAttributeRequestValue(account, \"CUFNAM\")); //legal first name
              log.debug(\"setting value for index 7\");
              cStatement.setString(7, getAttributeRequestValue(account, \"CULNAM\")); //last name
              log.debug(\"setting value for index 8\");
              cStatement.setString(8, getAttributeRequestValue(account, \"CUSPRV\")); //manager netid
              log.debug(\"setting value for index 9\");
              cStatement.setString(9, nativeId); //search sam upper (CUNETU)
              log.debug(\"setting value for index 10\");
              String cudpds = getAttributeRequestValue(account, \"CUDPDS\");
              if ( cudpds != null && cudpds.length() > 30 )
                cStatement.setString(10, cudpds.substring(0,30)); // department description
              else
                cStatement.setString(10, cudpds); //none
              log.debug(\"setting value for index 11\");
              cStatement.setString(11, getAttributeRequestValue(account, \"CUJBDS\")); //job title
              log.debug(\"setting value for index 12\");
              cStatement.setString(12, getAttributeRequestValue(account, \"CULEAD\")); //employee management level
              log.debug(\"setting value for index 13\");
              cStatement.setString(13, getAttributeRequestValue(account, \"CUODCD\")); //org level 1 code
              log.debug(\"setting value for index 14\");
              if ( getAttributeRequestValue(account, \"CUEXDT\") != null )
                cStatement.setInt(14, Util.atoi(getAttributeRequestValue(account, \"CUEXDT\"))); //iSeries term date
              else
                cStatement.setInt(14, 0);
              log.debug(\"setting value for index 15\");
              cStatement.setString(15, getAttributeRequestValue(account, \"CULUDT\")); // iSeries update date
              log.debug(\"setting value for index 16\");
              cStatement.setString(16, getAttributeRequestValue(account, \"CULUBY\")); //service user
              log.debug(\"setting value for index 17\");
              //cStatement.setString(17, identityNumber); //employee id
              cStatement.setString(17, (String) account.getNativeIdentity()); //employee id
              log.debug(\"setting value for index 18\");
              cStatement.setString(18, getAttributeRequestValue(account, \"CUROLE\")); //role
              log.debug(\"EX851SP full statement: \" + cStatement.toString());

              cStatement.executeUpdate();

              // Successful Modify, so mark result as COMMITTED
              log.debug(\"Modify Account Execution successfully completed\");
              result.setStatus(ProvisioningResult.STATUS_COMMITTED);

              log.debug(\"Exiting Modify Operation\");
            }
          } catch(Exception e) {
            result.setStatus(ProvisioningResult.STATUS_FAILED);
            result.addError(e);
            log.error(\"iSeries JDBC Provisioning SQL Error: \" + e.getMessage());
          } finally {
            if(cStatement != null) {
              cStatement.close();
            }
          }
        }
      }
    }

    log.debug(\"Exit iSeries JDBC Provisioning\");
    return result;

DFuyal · January 18, 2024, 9:51pm

NativeIdentity is populated from the plan. Check your plan output for that activity. Also, in the above rule in Modify Section, you have to specify for which modify action, the databse insertion is happening. There are different modify action such as ADD , REMOVE etc. Your code is inserting into the database for every modify actions. Check on that as well.

moss_snake_01 · January 18, 2024, 10:16pm

Ah yeah, the DB2 developer I have been working with coded a sproc to handle both CREATE and MODIFY operations, that’s why I am calling it the same in both.

SProc:

CREATE PROCEDURE EX850SP                          
       (IN inp_vcusts varchar(1) DEFAULT NULL,    
        IN inp_vcuetyp varchar(1) DEFAULT NULL,   
        IN inp_vculocn varchar(10) DEFAULT NULL,  
        IN inp_vcuemad varchar(100) DEFAULT NULL, 
        IN inp_vcuempl varchar(3) DEFAULT NULL,   
        IN inp_vcufnam varchar(25) DEFAULT NULL,  
        IN inp_vculnam varchar(25) DEFAULT NULL,      
        IN inp_vcusprv varchar(10) DEFAULT NULL,      
        IN inp_vcunetu varchar(10) DEFAULT NULL,      
        IN inp_vcudpds varchar(30) DEFAULT NULL,      
        IN inp_vcujbds varchar(30) DEFAULT NULL,      
        IN inp_vculead varchar(1) DEFAULT NULL,       
        IN inp_vcuodcd varchar(10) DEFAULT NULL,     
        IN inp_vcuexdt numeric(8, 0),                
        IN inp_vculudt varchar(26) DEFAULT NULL,     
        IN inp_vculuby varchar(10) DEFAULT NULL,     
        IN inp_vcueeid varchar(20) DEFAULT NULL,     
        IN inp_vcurole varchar(20) DEFAULT NULL)     
                                                     
                                                     
LANGUAGE SQL                                         

SET OPTION COMMIT = *CHG                                      
                                                              
BEGIN                                                         
                                                              
   IF EXISTS (SELECT *                                        
            FROM   CUEMSID                                    
            WHERE    CUEEID    =   inp_vcueeid  ) THEN        
                ----Update statement for all input parameters.
                    UPDATE  CUEMSID                           
                     SET                                                                    
                        CUSTS = NVL(UPPER(inp_vcusts), CUSTS),     
                        CUETYP = NVL(UPPER(inp_vcuetyp), CUETYP),  
                        CULOCN = NVL(inp_vculocn, CULOCN),         
                        CUEMAD = NVL(UPPER(inp_vcuemad), CUEMAD),  
                        CUEMPL = NVL(UPPER(inp_vcuempl), CUEMPL),  
                        CUFNAM = NVL(UPPER(inp_vcufnam), CUFNAM),   
                        CULNAM = NVL(UPPER(inp_vculnam), CULNAM),  
                        CUSPRV = NVL(UPPER(inp_vcusprv), CUSPRV),    
                        CUNETU = NVL(UPPER(inp_vcunetu), CUNETU),      
                        CUDPDS = NVL(UPPER(inp_vcudpds), CUDPDS),    
                        CUJBDS = NVL(UPPER(inp_vcujbds), CUJBDS),    
                        CULEAD = NVL(UPPER(inp_vculead), CULEAD),    
                        CUODCD = NVL(inp_vcuodcd, CUODCD),           
                        CUEXDT = inp_vcuexdt,                        
                        CULUDT = NVL(inp_vculudt, CULUDT),      
                        CULUBY = NVL(inp_vculuby, CULUBY),                                     
                        CUEEID = NVL(inp_vcueeid, CUEEID)                                      
                      WHERE CUEEID = inp_vcueeid ;                                              
                                                                                                
             ELSE                                                                               
                     --- If record doesn't exist insert it                                      
                     INSERT INTO                                                                
        CUEMSID (CUSTS, CUETYP, CULOCN, CUEMAD, CUEMPL, CUFNAM, CULNAM,          
         CUSPRV, CUNETU, CUDPDS, CUJBDS, CULEAD, CUODCD, CUEXDT, CULUDT,
         CULUBY, CUEEID, CUROLE)     
        values(                                
         NVL(UPPER(inp_vcusts), CUSTS),            
         NVL(UPPER(inp_vcuetyp), CUETYP),          
         NVL(inp_vculocn, CULOCN),                 
         NVL(UPPER(inp_vcuemad), CUEMAD),          
         NVL(UPPER(inp_vcuempl), CUEMPL),          
         NVL(UPPER(inp_vcufnam), CUFNAM),          
         NVL(UPPER(inp_vculnam), CULNAM),          
         NVL(UPPER(inp_vcusprv), CUSPRV),  
         NVL(UPPER(inp_vcunetu), CUNETU),           
         NVL(UPPER(inp_vcudpds), CUDPDS),  
         NVL(UPPER(inp_vcujbds), CUJBDS),  
         NVL(UPPER(inp_vculead), CULEAD),  
         NVL(inp_vcuodcd, CUODCD),         
         inp_vcuexdt,                      
         NVL(inp_vculudt, CULUDT),    
         NVL(inp_vculuby, CULUBY),    
         NVL(inp_vcueeid, CUEEID),    
         NVL(inp_vcurole, CUROLE));   
END IF;                               
END;

Thanks for the guidance!

moss_snake_01 · January 22, 2024, 8:18pm

This did the trick – Thank you!

system · March 22, 2024, 8:18pm

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

Topic		Replies	Views
Provisioning plan attributes ISC Discussion and Questions rules , provisioning	8	2625	July 19, 2023
Update NativeIdentity within AccountRequest of ProvisioningPlan ISC Discussion and Questions	5	2103	July 19, 2023
JDBC Provisioning rule to read the nativeIdentity that gets generated in the Database ISC Discussion and Questions	4	3279	July 26, 2023
JDBC BuildMap Rule ISC Discussion and Questions	3	2049	July 19, 2023
JDBC Provisioning - JDBC Account not reflected or linked to Identity ISC Discussion and Questions rules , identity-security-cloud , provisioning , jdbc-connector	6	279	February 2, 2024

JDBC Connector and NativeIdentity

Related Topics