Click HERE to see how Saviynt Intelligence is transforming the industry. |
09/03/2024 02:38 PM - last edited on 09/03/2024 10:53 PM by Sunil
We have a DB integration where the input parameter is a database array type. Below is a example of the call:
call FASDDBA01.PKG_VERT_IDM.CREATE_MODIFY_USER(XXXXXXX, 'Shantanu', 'Test', 'null@domain.com', 'Not Available','Business User Controller','Energy Infra:Oil_and_Gas:O&G Digital Solutions:O&G Digital Solutions'),
The last IN parameter is a array and doesn't accept anything else.
The existing IGA integration use java code to convert the list of roles to array: responsibilityList = convertConnection().createOracleArray("FASDDBA01.FIELDARRAY", respValue4);
Is there a way we can get generate a database array from a list or comma separated values.
We see below error message: Error Message = ORA-06553: PLS-306: wrong number or types of arguments in call to 'CREATE_MODIFY_USER'
[This post has been edited by a Moderator as we do not allow the tagging of other forum users or employees unless they have already involved themselves in the forum thread..]
09/03/2024 02:39 PM - last edited on 09/03/2024 10:57 PM by Sunil
09/03/2024 02:56 PM
Share DB stored procedure screenshot
09/03/2024 03:42 PM
@ShantanuKumar
Can you also share the database you are using along with above asked info.
09/03/2024 06:02 PM
I have asked the App team to share the stored procedure.
09/03/2024 06:40 PM
@ShantanuKumar
Can you also tell me the database you are using.
Oracle,
MySQL,
sqlserver
09/03/2024 08:26 PM
I need screenshot from db when you running stored procedure from database
09/03/2024 06:41 PM
It's Oracle.
09/03/2024 07:03 PM - last edited on 09/03/2024 10:58 PM by Sunil
Try using
Execute FASDDBA01.PKG_VERT_IDM.CREATE_MODIFY_USER (XXXXXXX, 'Shantanu', 'Test', 'null@domain.com', 'Not Available','Business User Controller','Energy Infra:Oil_and_Gas:O&G Digital Solutions:O&G Digital Solutions')
09/03/2024 07:10 PM - last edited on 09/03/2024 10:59 PM by Sunil
I can try this but this is not the problem. The store procedure expect below input:
Userid - This is VARCHAR
First Name - This is VARCHAR
Last Name - This is VARCHAR
Mail - This is VARCHAR
Phone Number - This is VARCHAR
Business Name - This is VARCHAR
Entitlement Names - This is Database Array
Execute FASDDBA01.PKG_VERT_IDM.CREATE_MODIFY_USER
(XXXXXXX,
'Shantanu',
'Test',
'null@domain.com',
'Not Available',
'Business User Controller',
'Energy Infra:Oil_and_Gas:O&G Digital Solutions:O&G Digital Solutions') - How do we send a array in the last argument. The array would have all the entitlement which user has and the newly requested entitlements.
09/03/2024 07:46 PM - edited 09/03/2024 07:47 PM
@ShantanuKumar
Execute FASDDBA01.PKG_VERT_IDM.CREATE_MODIFY_USER ('${user.username}','${user.firstname}','${user.lastname}','${user.email}','Not Available','Business User Controller','${task.entitlement_valueKey.entitlement_value}')
If the value is stored in one of the CP you can pass it.
09/03/2024 08:30 PM - last edited on 09/03/2024 10:59 PM by Sunil
call FASDDBA01.PKG_VERT_IDM.CREATE_MODIFY_USER(XXXXXXX, 'Shantanu', 'Test', 'null@domain.com', 'Not Available','Business User Controller',"'Energy Infra:Oil_and_Gas:O&G Digital Solutions:O&G Digital Solutions'"),
09/04/2024 05:29 AM
I tried below JSON:
{
"createaccountqry": "call FASDDBA01.PKG_VERT_IDM.CREATE_MODIFY_USER(${user.username}, '${user.firstname}', '${user.lastname}', '${user.email}', '${user.phonenumber==null? 'Not Available': user.phonenumber}','${requestAccessAttributes.get('AccessRole').split(':')[0]}',"'${requestAccessAttributes.get('OPUnit')}'")"
}
I see out put as:
call FASDDBA01.PKG_VERT_IDM.CREATE_MODIFY_USER(XXXXXXXX, 'Shantanu', 'Test', 'null@domain.com', 'Not Available','Technical Center of Excellence',"'Aviation:Aviation:Aviation:Aviation'")
below is the error message:
at org.codehaus.groovy.grails.web.json.JSONTokener.syntaxError(JSONTokener.java:470)| at org.codehaus.groovy.grails.web.json.JSONObject.<init>(JSONObject.java:262)| at org.codehaus.groovy.grails.web.json.JSONTokener.nextValue(JSONTokener.java:328)| at grails.converters.JSON.parse(JSON.java:283)| ... 82 more|"
09/04/2024 07:08 AM
{
"createaccountqry": "call FASDDBA01.PKG_VERT_IDM.CREATE_MODIFY_USER('${user.username}', '${user.firstname}', '${user.lastname}', '${user.email}', '${user.phonenumber == null ? 'Not Available' : user.phonenumber}', '${requestAccessAttributes.get('AccessRole').split(':')[0]}', '${requestAccessAttributes.get('OPUnit').replaceAll(':', '\\\\:')}' )"
}
09/04/2024 08:48 AM
Here is the new JSON:
"2024-09-04T15:04:28.898+00:00","ecm-worker","provisoning.DBProvisioningService","quartzScheduler_Worker-2-t8cdm","DEBUG","Create account JSON String is {|"createaccountqry": "call FASDDBA01.PKG_VERT_IDM.CREATE_MODIFY_USER('${user.username}', '${user.firstname}', '${user.lastname}', '${user.email}', '${user.phonenumber == null ? 'Not Available' : user.phonenumber}', '${requestAccessAttributes.get('AccessRole').split(':')[0]}', '${requestAccessAttributes.get('OPUnit').replaceAll(':', '\\\\:')}' )"|}"
Here is the error message:
org.springframework.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:196)| at org.codehaus.groovy.grails.plugins.quartz.GrailsJobFactory$GrailsTaskClassJob.execute(GrailsJobFactory.java:72)| at org.quartz.core.JobRunShell.run(JobRunShell.java:199)| at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:546)|Caused by: Error : 6553, Position : 5, SQL = call FASDDBA01.PKG_VERT_IDM.CREATE_MODIFY_USER('503508020', 'Shantanu', 'Test', 'null@ge.com', 'Not Available', 'Technical Center of Excellence', 'Aviation:Aviation:Aviation:Aviation' ), Original SQL = call FASDDBA01.PKG_VERT_IDM.CREATE_MODIFY_USER('XXXXXXXXX', 'Shantanu', 'Test', 'null@domain.com', 'Not Available', 'Technical Center of Excellence', 'Aviation:Aviation:Aviation:Aviation' ), Error Message = ORA-06553: PLS-306: wrong number or types of arguments in call to 'CREATE_MODIFY_USER'| at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:710)| ... 116 more|"
Looks like we are sending VARCHAR but it is expecting a Array. I am still waiting for stored procedure details from the App team. Also trying to submit a request in current IGA platform to see how it is being sent now. But here is the java code used to construct the array:
public Array getRequestedRoles(List<String> groupOrRolesList) throws SQLException {
count = groupOrRolesList.size();
String[][] respValue4 = new String[count][4];
log.debug("count" + count);
int i = 0;
for (String attrValList : groupOrRolesList) {
roleList = this.splitEntitVal(attrValList);
log.debug("roleList" + roleList.size());
log.debug("roleList Values" + roleList.toString());
respValue4[i][0] = roleList.get(0);
respValue4[i][1] = roleList.get(1);
respValue4[i][2] = roleList.get(2);
respValue4[i][3] = roleList.get(3);
log.debug("in getRequestedRoles" + respValue4);
i++;
}
responsibilityList = convertConnection().createOracleArray("FASDDBA01.FIELDARRAY", respValue4);
return responsibilityList;
}
09/04/2024 08:51 AM
Please ask app team to create new SP
CREATE OR REPLACE PACKAGE PKG_VERT_IDM AS
TYPE field_array IS TABLE OF VARCHAR2(4000);
PROCEDURE CREATE_MODIFY_USER (
p_username IN VARCHAR2,
p_firstname IN VARCHAR2,
p_lastname IN VARCHAR2,
p_email IN VARCHAR2,
p_phonenumber IN VARCHAR2,
p_accesrole IN field_array,
p_opunit IN VARCHAR2
);
END PKG_VERT_IDM;
09/04/2024 12:52 PM
I have got the stored procedure details from App team. I think they have created a array of 4 varchar like below:
CREATE OR REPLACE TYPE FASDDBA01.FIELDARRAY AS OBJECT ( field1 VARCHAR2(100), field2 VARCHAR2(100), field3 VARCHAR2(100), field4 VARCHAR2(100) );
The java code extract existing entitlement list, add the entitlement for grant access, split the entitlement based on ":" character and convert them into a array.
The java code extract existing entitlement list, remove the entitlement for revoke access, split the entitlement based on ":" character and convert them into a array.
The store procedure first delete all the entitlements, loop through the no of entries in the Array and insert to a table.
For example:
The entitlement would like below:
Energy Infra:Oil_and_Gas:O&G Digital Solutions:O&G Digital Solutions
Aviation:Aviation:Aviation:Aviation
The array would like below:
responsibilityList := FIELDARRAY('Energy Infra', 'Oil_and_Gas', 'O&G Digital Solutions', 'O&G Digital Solutions'),
('Aviation', 'Aviation', 'Aviation', 'Aviation'));
Stored procedure sample(it's not the full stored procedure):
PROCEDURE CREATE_MODIFY_USER(str_ssoId IN VARCHAR2,
str_Responsibilites in FIELDARRAY)
IS
num_PersonId NUMBER := 0;
num_Temp_Var NUMBER :=0;
SELECT PERSON_ID
INTO num_PersonId
FROM T_VERT_PERSON
WHERE SSO = str_ssoId;
FOR idx IN 1..str_Responsibilites.COUNT LOOP
--str_Responsibilites(idx)(1)
BEGIN
-- Validates the OpUnit is valid
SELECT COUNT(OP.ELEMENT_ID) INTO num_Temp_Var
FROM T_VERT_HIERARCHY OP,
T_VERT_HIERARCHY SB,
T_VERT_HIERARCHY BS,
T_VERT_HIERARCHY SG
WHERE OP.PARENT_ELEMENT_ID = SB.ELEMENT_ID
AND SB.PARENT_ELEMENT_ID = BS.ELEMENT_ID
AND BS.PARENT_ELEMENT_ID = SG.ELEMENT_ID
AND OP.ELEMENT_ID = str_Responsibilites(idx)(4);
IF (num_Temp_Var = 0) THEN
RAISE INVALID_OP_UNIT_VALUE;
END IF;
--Loads the Responsibility based on OpUnit
INSERT into T_VERT_RESPONSABILITY(ELEMENT_ID, Person_ID, Created_By, Created_On)
VALUES(str_Responsibilites(idx)(4), num_PersonId, 'IDM', sysdate);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
-- Do Nothing
num_Temp_Var := 0;
END;
num_Temp_Var := 0;
END LOOP;
END CREATE_MODIFY_USER;
Java code to split:
public List<String> splitEntitVal(String value) {
List<String> argList = new ArrayList();
StringTokenizer st = new StringTokenizer(value, ":");
while (st.hasMoreTokens()) {
argList.add(st.nextToken());
}
return argList;
}
Java code to convert to Array:
public Array getRequestedRoles(List<String> groupOrRolesList) throws SQLException {
count = groupOrRolesList.size();
String[][] respValue4 = new String[count][4];
log.debug("count" + count);
int i = 0;
for (String attrValList : groupOrRolesList) {
roleList = this.splitEntitVal(attrValList);
log.debug("roleList" + roleList.size());
log.debug("roleList Values" + roleList.toString());
respValue4[i][0] = roleList.get(0);
respValue4[i][1] = roleList.get(1);
respValue4[i][2] = roleList.get(2);
respValue4[i][3] = roleList.get(3);
log.debug("in getRequestedRoles" + respValue4);
i++;
}
responsibilityList = convertConnection().createOracleArray("FASDDBA01.FIELDARRAY", respValue4);
return responsibilityList;
}
09/04/2024 12:58 PM
Ask them to take separate input
09/04/2024 01:03 PM
You want them to ask to modify the stored procedure to take only 1 entitlement for each call? Right now then are getting the modified entitlements list as array from IGA system and just process them.
This might be a big change where they just don't need the single input parameter of the entitlement but also the operation like addition or deletion.
09/04/2024 01:06 PM
Ask for separate stored procedure
09/04/2024 01:32 PM
Rushikesh, One quick question, if user request for multiple entitlement, Saviynt creates separate add access or remove access task for each entitlement. Is there a way to create just 1 add or remove access for multiple entitlement?
I can ask them to create separate store procedure for created account, add and removes access or just 1 stored procedure to take the action as a input with the only 1 entitlement. But the problem is this would change whole design from their side and not sure if they would agree. In current scenario the IGA system just sending them the list of entitlement required for the user and they just simply delete existing and add the new list but in this case they need to insert or delete based on the operation type.
DO you think is there any other way to acheive this?
09/04/2024 02:05 PM
Its better to task 1 store procedures which will be consumed by saviynt
09/04/2024 02:07 PM
Ok, could you please answer below:
One quick question, if user request for multiple entitlement, Saviynt creates separate add access or remove access task for each entitlement. Is there a way to create just 1 add or remove access for multiple entitlement?
09/04/2024 04:00 PM - edited 09/04/2024 04:01 PM
It’s possible in REST. DB you need to try