How to grant access(group/role) to user automatically for the users who exists in other custom table

[Priya Bonam]

Users table = x

custom table = y

Requirement is like query or code should run daily and checks if user exists in custom table then  provide access (one group with read only access for custom app) automatically to those users who dont have it already.

[Community Alums]

Hi @Priya Bonam ,

You can write a before Update BR with trigger as your_field changes with below code , just replace sys_user with your custom table and add another line of code to provide the role which you want:

var grSysUser = new GlideRecord('sys_user');
grSysUser.addEncodedQuery("user_name="+current.your_fieldname_here);
grSysUser.query();
if (!grSysUser.next()) {
current.setAbortAction(true);
}

[Voona Rohila]

Hi @Priya Bonam 

Did you try anything and got stuck?

You can achieve this with flow designer schedule as trigger or with a scheduled job.

[Priya Bonam]

Stuck to grant access with script .

var ulist = new GlideRecord('sys_user');

ulist.addQuery('active', 'true');

ulist.query();

while(ulist.next())

{

var st = new GlideRecord('u_stakeholders');

st.addQuery('u_stakeholders','=', ulist.getValue('name'));

st.query();

while(st.next())

{

gs.print("users :"+st.getDisplayValue('u_stakeholders'));

}

}

need to add the script like if the above list of users didnt have particular access then grnat them else abort.

Please help.

[Riya Verma]

Hi @Priya Bonam ,

To achieve this, you can follow the steps outlined below:

1. Create a scheduled job or script in ServiceNow that runs daily.

2. write a script  in scheduled job to query the custom table (Table Y) and check if each user exists in the Users table (Table X) and if they do not already have access to the custom app.

3. try to use below reference script :

// Step 2: Define the Script Logic
// Query the custom table (Table Y) for users
var customUsers = new GlideRecord('table_y');
customUsers.query();

// Loop through the custom users and check for access
while (customUsers.next()) {
  var userId = customUsers.getValue('user_id');

  // Check if the user exists in the Users table (Table X)
  var user = new GlideRecord('sys_user');
  user.addQuery('user_id', userId);
  user.query();

  // Grant access if the user exists and doesn't have it already
  if (user.next() && !user.isMemberOf('group_read_only_access')) {
    // Assign the user to the predefined group with read-only access to the custom app
    user.addToGroup('group_read_only_access');
  }
}
​

If you want to use own script , then create a array to push all the users belong to that custom table lets say Y . And then create a read ACL and cal your script include function and then if user is part of array then set answer to true to grant access .