Revoke user access from selected groups in no activity found for 90days.

NirupamaN
Tera Contributor

‎09-20-2024 01:09 AM - edited ‎09-20-2024 07:07 AM

Here is the Problem statement: 

1. I have list of roles for ex: role1, role2, role3....
2. I have list of tables for ex: table1, table2, table3,table4....

3. List of user groups for ex: group1, group2

If any user who has one of the roles stated in above listed roles has not  modified the records in above listed tables in the past 90 days then the user access should be revoked from given groups. 

Note: I have used updated_by field in scripts and tried scheduled jobs but it will not work as updated by field only records last updated on/ updated_by on the table but not the previous user who have modified records within given time. 
So I am trying other way: 
I created one custom filed in sys_user table to capture the last update time of the user if they have modified any of the given tables. This will work for one table by creating a BR on that table. The challenge is I need to validate 30+ tables. 

By looking in to all the limitations , any suggestions would be appreciated!








0 Helpfuls
  • 621 Views
6 REPLIES 6

NirupamaN
Tera Contributor
In response to SP22

‎09-20-2024 02:53 AM

Thanks for taking time and responding to the problem. 

The links which you have shared are if user has not logged in for some days then access will be removed. But in my scenario user will login and we need to validate if user has modified given tables or not. If they have not modified the given tables ( they might have updated different tables) then the user should be removed from the groups. 

0 Helpfuls

Tai Vu
Kilo Patron
Kilo Patron

‎09-20-2024 02:16 AM - edited ‎09-20-2024 02:21 AM

Hi @NirupamaN 

Before building any script or flow to remove user roles/groups, it might be worth considering the feasibility and first figuring out how to identify users who are accessing and updating the tables.

The Sys Audits [sys_audit] table seems like a good option for this validation, but keep in mind that it typically doesn't track every update for every single field within a table.

 

Cheers,

Tai Vu

Eshwar Reddy
Kilo Sage

‎09-20-2024 02:22 AM - edited ‎09-20-2024 02:24 AM

Hi @NirupamaN 
Create a schedule job
Please use the below script


var roles = ['role1', 'role2', 'role3'];
var tables = ['table1', 'table2', 'table3', 'table4'];
var groups = ['group1', 'group2'];


var dateLimit = new GlideDateTime();
dateLimit.addDays(-90);

var userGr = new GlideRecord('sys_user');
userGr.addQuery('roles', 'IN', roles.join(','));
userGr.query();

while (userGr.next()) {
var userId = userGr.sys_id;
var hasModified = false;

for (var i = 0; i < tables.length; i++) {
var modGr = new GlideRecord(tables[i]);
modGr.addQuery('sys_updated_by', userId);
modGr.addQuery('sys_updated_on', '>=', dateLimit);
modGr.query();

if (modGr.hasNext()) {
hasModified = true;
break; 
}
}

if (!hasModified) {
for (var j = 0; j < groups.length; j++) {
var groupGr = new GlideRecord('sys_user_grmember');
groupGr.addQuery('user', userId);
groupGr.addQuery('group.name', groups[j]);
groupGr.query();

while (groupGr.next()) {
groupGr.deleteRecord();
}
}
}
}

Please mark this response as Correct and Helpful if it helps you can mark more that one reply as accepted solution

Thanks 
Eshwar

0 Helpfuls