Relationship b/w user, role and user_has_role table

SNOW Learner09 · ‎10-23-2022

Hi Everyone, I am trying to understand how the relation b/w sys_user, sys_user_role and sys_user_has_role tables is configured. I have to implement the same related list setup but unable to do so.

On sys_user table, open a user profile, we can see a related list which consists of user contained roles but when we click on "Edit" on button related list, it will open slush buckets. Left side (Available list) slush bucket contains all roles and right side (Selected list) slush bucket contains already existing roles for the user. How come left side (Available list) is loading data from roles table even though Related List data is from sys_user_has_role table.

Please help to understand this functionality. @Ankur Bawiskar

#ITSM #OOB

Edgar10 · ‎10-24-2022

Hi SNOW Learner ,

That is an OOB Behaviour what you need to understand is that

sys_user table holds the maintained users in Snow possible OOB and custom

sys_user_role table holds the maintained roles possible OOB and custom

sys_user_has_role table contains the records related to the relationship of sys_user and sys_user_has_role

Regards,

H_9 · ‎10-24-2022

Hello,

1. sys_user table stores the records of the users in servicenow

2. sys_user_role stores all the roles available in the servicenow

3. sys_user_has_role stores the mapping records of user and the roles

ex: say auser ABC has 3 roles Role1, Role2, Role3 then the sys_user_has_role Table will have 3 records as follows:

ABC : Role 1

ABC : Role 2

ABC : Role 3

On your form, in the slush bucket, the roles records are coming from sys_user_role table and not from sys_user_has_role. You can check this by right clicking on the slush bucket field and configure dictionary field.

Following schema map will give you better understanding:

Please mark the answer correct if it helps. 🙂

Thanks. 🙂

SNOW Learner09 · ‎10-24-2022

Please check in your PDI, go to user profile and check if user has any roles. If user has roles, click on Edit button related list then you can see a new window with slush buckets. In the left hand side slush bucket (Available list) contains the records from sys_user_roles table and in the right hand side slush bucket (Already selected roles) are populating from sys_user_has_roles table. My question is how come two slush buckets are loading data from different tables. I have to implement the same functionality so I would like to understand this. Please suggests.

H_9 · ‎10-24-2022

Hi,

Following two links may help you,

You will have to create many to many relationship.

This page uses "sys_m2m_template" details of how it works are mentioned in the following 2 links, you will probably find your answer in the 2nd link (solved)

https://www.servicenow.com/community/now-platform-forum/how-do-you-use-sys-m2m-template-do/m-p/11066...

https://www.servicenow.com/community/developer-forum/what-purpose-does-the-sys-m2m-table-actually-se...

Hope this helps you.

Thanks. 🙂