Prevent duplicate records in sys_user_grmember

[richardyaxl]

In our ServiceNow instance, I have noticed that it is possible to insert duplicate records into the sys_user_grmember table. However, in my out of the box PDI there seems to be something in place that prevents this, and throws and "Invalid insert" error when trying to add a user to a group that is already a member.

I have looked at Business rules, ACLs, and indexing but I am struggling to identify where this is set so that I can duplicate it. Please can somebody help me to identify how this is configured?

[Brad Bowman]

Interesting.  It looks like this must have been introduced in Zurich.  I'm not sure if this is another thing ServiceNow is hiding from us, or if it's a new platform-level feature, but we can easily re-create the functionality with a Business Rule that looks like this:

The Script on the Advanced tab would look like this - with an optional bonus line for a better user experience:

(function executeRule(current, previous /*null when async*/) {
	var grp = new GlideRecord('sys_user_grmember');
	grp.addQuery('user', current.user);
	grp.addQuery('group', current.group);
	grp.query();
	if (grp.next()) {
		gs.addErrorMessage(current.user.name + ' is already a member of ' + current.group.name);
		current.setAbortAction(true);
	}
})(current, previous);

[richardyaxl]

Hi Brad,

Thanks for your response. It seems this was caused by the database index configuration on our sys_user_grmember table (see my response to Philippe below). My PDI is still in the Xanadu instance, so I don't believe this was introduced in Zurich, but certainly something that must have changed at some point that we missed out on!

[Philippe Casidy · Accepted solution]

Hi,

I have this behavior in Yokohama release.
When I try to insert an already existing member in sys_user_grmember, I got this "Invalid Insert".

Looking at the log I can read:

txid=83... SUA_47f3... SEVERE *** ERROR *** FAILED TRYING TO EXECUTE ON CONNECTION glide.. (connpid=3...): INSERT INTO sys_user_grmember (...)
Unique Key violation detected by database ((conn=34.) Duplicate entry 'd7eec1d01b4a2159cc4bcb33-50e5c4fc038d4b...' for key 'group')
java.sql.SQLIntegrityConstraintViolationException: (conn=346..) Duplicate entry 'd7eec1d0159cc4bcb33-50e5c49dbcfc038d4b...' for key 'group'
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:229)
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:165)
at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:238)

I looked at the sys_user_gr_member table and could in see in the related lists:

And I guess, that you cannot have duplicates for the index "group".

I have to pause on this for now but would love to read the results of your investigations.

(and yes, you can always create a BR if required)

Regards,

Philippe

[richardyaxl]

Hi Philippe,

Thanks for your response. This log message is really useful, and one that I didn't spot. You are correct that this behaviour is coming from the indexing, specifically the "group, user" column as per your above screenshot. In our instance we were missing this, I therefore created a new index on group,user and ensured "Unique" was set to true. This resolved my issue, and now if we try to create a duplicate it shows the "Invalid insert" message.

Note - before you can create the unique index, first you have to remove all duplicate records from the sys_user_grmember table