Find roles not associated with groups or inheritance that have users assigned directly

[tzvik15]

Hello

We discovered while researching a different question that one of the roles in our system (sn_change_cab.cab_manager) was set up originally (some years ago), for whatever reason, such that it is not associated with any group, and users have been added to this role directly.

We know this is not best practice, but we also suspect that if there is one, there may be other such roles as well. I am asking for help trying to find such roles. My current idea is to run a report against the sys_user_role table with related lists where groups == 0 AND users having role > 0. To do that, the developers need to open up that table for reporting.

My ask: is there a better way you can suggest to find these roles? 

My plan for those roles is to create groups and assign the roles to those groups, and then add those users who currently have the role to those groups.

Thanks!

[Bert_c1]

Hi,

I suggest you look at the sys_group_has_role table. You may write a script to process sys_user_role and then query sys_group_has_role for those. To find Roles that are not associated with a group.

// find roles that are not associated with a group
var sur = new GlideRecord('sys_user_role');
sur.query();
gs.info("Processing " + sur.getRowCount() + " role records.");
var rCount = 0;
while (sur.next()) {
   var sghr = new GlideRecord('sys_group_has_role');
   sghr.addQuery('role', sur.sys_id.toString());
   sghr.query();
   if (!sghr.next()) {
      gs.info("Role: " + sur.name + " is not associated with a group.");
      rCount++;
   }
}
gs.info("Found " + rCount + " roles not associated with a group.");

I see 911 out 0f 970 from the above in my PDI. I don't know how useful that is.

For Inheritance problems, you may look at the sys_user_has_role table. And filter on the 'Inherited' and/or Inheritance Count fields. Servicenow Support can run a script "VerifyAndFixRoles" for you to see what records exist that don't match Inheritance criteria. It can be run to see what changes may be applied, and report those.  If desired, after you review the output, you can ask that the script is run in non-debug mode to make changes.

[tzvik15]

Thanks! I will pass this on to the dev team.

[Bert_c1]

@tzvik15 

You should have the two plugins: "Contextual Security: Role Management" and  "Contextual Security: Role ManagementV2" activated in your instances. the latter is new and both are present on new instances. Since it appears you instances were created "some years ago", you may not have both. When the latter is activated, the Inheritance 'fix script' runs. So testing is needed.

On roles being used, I added to the script above a check for users having any of the defined roles. And a comparison of the two arrays. See below:

// find roles that are not associated with a group
var sur = new GlideRecord('sys_user_role');
var rolesNoGroup = [];
sur.query();
gs.info("Processing " + sur.getRowCount() + " role records.");
var rCount = 0;
while (sur.next()) {
   var sghr = new GlideRecord('sys_group_has_role');
   sghr.addQuery('role', sur.sys_id.toString());
   sghr.query();
   if (!sghr.next()) {
      gs.info("Role: " + sur.name + " is not associated with a group.");
      rCount++;
      rolesNoGroup.push(sur.name.toString());
   }
}
gs.info("Found " + rCount + " roles not associated with a group.");

// find roles that are not associated to a user
var usersNoGroup = [];
var sur = new GlideRecord('sys_user_role');
sur.query();
gs.info("Processing " + sur.getRowCount() + " role records.");
var rCount = 0;
while (sur.next()) {
   var suhr = new GlideRecord('sys_user_has_role');
   suhr.addQuery('role', sur.sys_id.toString());
   suhr.query();
   if (!suhr.next()) {
      gs.info("Role: " + sur.name + " is not associated with a user.");
      rCount++;
      usersNoGroup.push(sur.name.toString());
   }
}
gs.info("Found " + rCount + " roles not associated with a user.");
gs.info("rolesNoGroup = " + rolesNoGroup);
gs.info("usersNoGroup = " + usersNoGroup);

var au = new ArrayUtil();
var rolesWithNeither = au.intersect(rolesNoGroup, usersNoGroup);
gs.info("Role with no group or user:");
for (i = 0; i < rolesWithNeither.length; i++)
   gs.info(rolesWithNeither[i]);

I hope this helps in your analysis. seems a tedious task. but good luck. the last part makes use of the OOB ArrayUtil:

ArrayUtil API