Database view

[Preethi26]

Hi Team,

I have doubt in database views I am trying to merge user, group, role to do reporting so i have created database view.

User A is part of Northeast division [group which has one role(virtual agent)]  role from this group is inherited and another 2 roles added to him manually(bad pratice) so total he is 3 roles and he is part of one group.

In database view created its shows correct groups that user is part of when grouped by, but the group role mapping is incorrect.

In screenshot User belongs to Northeast division group with virtual agent role and why other 2 roles that are not part of this  group are mapped under this group. 

Guess i made mistake in database view.

Please see attached and  help me to correct the issue.

Thank You

[Brad Bowman]

Each Table should have a different Order.  I would think you would want to join grmember to user, not ...has_role, with a left join to get all users and roles regardless if they are in any groups like this:

Note that because the has_role table does not have any relationship to group or grmember, the view will show one row for each role that the user has, and the group name will be present also but these are not related.  The Inherited column will help you to see that the role was not granted via a group, or you can explore the inheritance map and inheritance count columns.  This is why the Role related list on the User record does not show a Group.

[Preethi26]

Hi Brad,

Many thanks for guiding. After joining grmember to user with left join also same details appear no change. As you told has_role table does not have any relationship to group or grmember whether this can be corrected to get exact report? or we need use inherited field for reporting purpose.

Kindly suggest.

[Brad Bowman]

The inherited field, and maybe the inheritance map field will be more useful in a report than the group name since that is misleading.

[kriangkrai]

Hi @Preethi26 

It might not be done in single DB View as you're going to create Many to Many relationships.

As a result, you got Northeast Division with unrelated roles.

In my opinion you might create 2 DB Views:

• sys_user + sys_user_has_role

With this view, you mgiht focus on role that directly added to user (Inherited = false).

• sys_user + sys_user_grmember + sys_group_has_role

This view shows all users with their groups and roles that related to the group.

DB Views

Result

Another solution to create Visualize User's Role Inheritance Map .

Hope this help.

Regards,

Oat