Issue: User Update Rule not applied, when COMMENTS field is full

[Murmur]

Hi everyone, 

I just stumbled upon the following behaviour.

Issue:

When the COMMENTS field of ONE user is full (VARCHAR 255), User Update Rules targeting that user are not executed properly for ALL users in scope.

Setup: 

- User Update Rule: Disable User on Condition ((date(a.enddate) < curdate()) AND a.statuskey=1) 
- Users: Several users match the above condition. One users COMMENTS field is full (255 characters)

When the User Update Rule is evaulated, the updates are properly appended to each user's Update History, but they are not applied (ALL users are still active).

Expected Behaviour: 

1. The Rule application works for all users, but the one that creates the issue
2. An error message is raised 
3. No false evidence is appended to Update History of the users

I hope this helps others running into the same issue until this is fixed.

I believe that the highlighted part in the Log Output below is responsible for the misbehaviour. 

Cheers 🙂

Log Output: 

"2023-11-06T15:10:28.357+00:00","ecm-worker","saviynt.ImportExternalDbService","quartzScheduler_Worker-4","DEBUG","Executing Query "

"2023-11-06T15:10:29.276+00:00","ecm-worker","","","","                    UPDATE USERS SET STATUSKEY = 0, COMMENTS=concat_ws(', ',comments, :user_comments), TERMDATE=now(),"

"2023-11-06T15:10:29.276+00:00","ecm-worker","","","","                    UPDATEDATE=now(), ENABLED=FALSE, UPDATEUSER=1, SAVUPDATEDATE=now() "

"2023-11-06T15:10:29.276+00:00","ecm-worker","","","","                    WHERE USERNAME IN"

"2023-11-06T15:10:29.276+00:00","ecm-worker","","","","                ('test111f.test111l','test105f.test105l','test91f.test91l','test100f.test100l','test85ftest85l','LOWERpreferedFirstName.UPPERcustomproperty35','test98f.test98l','test101f.test101l','lowerpreferedFirstName.lowercustomproperty35','schl.zuschlumpfhausen','test110f.test110l','test90f.test90l','test99f.test99l')"

"2023-11-06T15:10:29.276+00:00","ecm-worker","","","","06-Nov-2023 15:10:28.364 WARNING [quartzScheduler_Worker-4] groovy.sql.Sql.executeUpdate Failed to execute: "

"2023-11-06T15:10:29.276+00:00","ecm-worker","","","","                    UPDATE USERS SET STATUSKEY = 0, COMMENTS=concat_ws(', ',comments, :user_comments), TERMDATE=now(),"

"2023-11-06T15:10:29.276+00:00","ecm-worker","","","","                    UPDATEDATE=now(), ENABLED=FALSE, UPDATEUSER=1, SAVUPDATEDATE=now() "

"2023-11-06T15:10:29.276+00:00","ecm-worker","","","","                    WHERE USERNAME IN"

"2023-11-06T15:10:29.276+00:00","ecm-worker","","","","                ('test111f.test111l','test105f.test105l','test91f.test91l','test100f.test100l','test85ftest85l','LOWERpreferedFirstName.UPPERcustomproperty35','test98f.test98l','test101f.test101l','lowerpreferedFirstName.lowercustomproperty35','schl.zuschlumpfhausen','test110f.test110l','test90f.test90l','test99f.test99l') because: Data truncation: Data too long for column 'COMMENTS' at row 9"

"2023-11-06T15:10:28.364+00:00","ecm-worker","saviynt.ImportExternalDbService","quartzScheduler_Worker-4","ERROR","Exception in updating user--"

"2023-11-06T15:10:29.276+00:00","ecm-worker","","","","com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Data too long for column 'COMMENTS' at row 9"

"2023-11-06T15:10:29.276+00:00","ecm-worker","","",""," at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:104)"

"2023-11-06T15:10:29.276+00:00","ecm-worker","","",""," at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)"

"2023-11-06T15:10:29.276+00:00","ecm-worker","","",""," at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)"

"2023-11-06T15:10:29.276+00:00","ecm-worker","","",""," at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040)"

"2023-11-06T15:10:29.276+00:00","ecm-worker","","",""," at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1347)"

"2023-11-06T15:10:29.276+00:00","ecm-worker","","",""," at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1025)"

"2023-11-06T15:10:29.276+00:00","ecm-worker","","",""," at com.saviynt.ImportExternalDbService.terminateUser(ImportExternalDbService.groovy:1223)"

"2023-11-06T15:10:29.276+00:00","ecm-worker","","",""," at com.saviynt.ImportExternalDbService.terminateUser(ImportExternalDbService.groovy:1085)"

"2023-11-06T15:10:29.276+00:00","ecm-worker","","",""," at com.saviynt.ecm.services.changeaction.UserChangeActionService.disableUser(UserChangeActionService.groovy:1972)"

"2023-11-06T15:10:29.276+00:00","ecm-worker","","",""," at DetectiveProvisioningRulesJob$_execute_closure3_closure9.doCall(DetectiveProvisioningRulesJob.groovy:167)"

"2023-11-06T15:10:29.276+00:00","ecm-worker","","",""," at DetectiveProvisioningRulesJob$_execute_closure3.doCall(DetectiveProvisioningRulesJob.groovy:139)"

"2023-11-06T15:10:29.276+00:00","ecm-worker","","",""," at DetectiveProvisioningRulesJob.execute(DetectiveProvisioningRulesJob.groovy:131)"

"2023-11-06T15:10:29.276+00:00","ecm-worker","","",""," at org.quartz.core.JobRunShell.run(JobRunShell.java:199)"

"2023-11-06T15:10:29.276+00:00","ecm-worker","","",""," at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:546)"

Also opened an idea, if you want to vote for it: Issue: User Update Rule is not applied, when | Saviynt Ideas Portal

[Darshanjain]

Hi @Murmur 

This is an expected behaviour as the comments field will get updated when ever user is getting disabled, now in this user would have disabled and enabled multiple times and hence the comments field is text associated with it. now as it is varchar(255) only after that it will start giving error and rule run's will fail.

is there any particular reason why this user was rehired multiple times ? also as part of workaround now , you can clear the comments field and then update so that rule runs correctly.

Let me know if you have any more questions around it.

Thanks

Darshan

Thanks

Darshan

[Murmur]

Hi @Darshanjain,

Thanks for yor reply. This just happened in our DEV environment, nonetheless, one would expect to be able to use the COMMENTS field, without breaking the whole IGA logic in place. 

I understand that this is an expected behaviour, due to the sizing of COMMENT field. Nonetheless I believe this is handeled awfully for the following reasons: 

1. Not processing Users, solely because the COMMENT field is full is nothing that can be reasonably expected, neither is it documented in a easy-to-find spot. 

2. The User Update Rule stops processing right in the middle of the data set. If an error like this happens I'd expect, that only the affected user is not processed, unfortunately no other user after the affected one is processed. 

3. If you know of this limitation, then it is grossly negligent to use this field for automated purposes and fill it up unnecessarily.

4. If an issue appears, I'd expect to get an error message everywhere. It is not okay, that the Users Update History falsely claims that an action took place, when it didn't.

Is there a way to push this issue to the technical team / implementation team of Saviynt? I believe this issue could potentially lead to huge issues for several companies, when they expect their Users to be offboarded, while they are still active.  

Cheers 🙂

[Darshanjain]

HI @Murmur 

Yes, My point was only about the error as comments column was full . We can take this as a ideas portal and check this out to come out for a fix to check but then can be decided.

Also due to this user if its breaking for other users and its not processing the rules for other users- if you are seeing this behavior then please raise a ticket as this will be considered as bug as it shouldn't break for other users and  needs to be handled.

Thanks

Darshan

[Manu269]

@Darshanjain when you are suggesting for clearing the comment fields can you please help with the query ?

Also what is the best practice to handle this.

I understand this is due to multiple rehire scenario but this can be one of the corner case.

[Darshanjain]

You can do it from the UI itself, Right now  this is very corner case and it can be avoided after changing schema of comments or use it in code, but it can still be a very rare case to be considered. 

Thanks

Darshan  

[Murmur]

Thanks man 🙂 

Already did this - would be niice if you could vote for it: Issue: User Update Rule is not applied, when | Saviynt Ideas Portal

[Darshanjain]

Yep did it.