Users/Roles

Jenni

Hi All,

Thanks in advance. I am trying to get the list of users and their associated roles/groups. Can anyone tell me which tables I need to query to get this information?

I have the docusers table that would give me the list of all users. But how will I relate the docuser data to the role_profile table. There seems to be no relation. Am I looking into the wrong tables? Please suggest me an approach.

WorkSite 8.3
Windows 2003

Thanks!
Jenni

guido1

Presume you mean querying direct to the database, as opposed to via the API?

If so, to get group membership you need to join the docusers and groupmems tables using the USERNUM column. Like this:

select u.userid, u.fullname 'user_fullname',
 g.groupid, g.fullname 'group_fullname'
from mhgroup.docusers u
left join mhgroup.groupmems gm
 on u.usernum = gm.usernum
left join mhgroup.groups g
 on g.groupnum = gm.groupnum

For roles it's a similar story - join docusers and userroles on the USERID column (roles and user roles join on the ALIAS column):

select u.userid, u.fullname 'user_fullname',
 r.alias 'role_id', r.description 'role_description'
from mhgroup.docusers u
left join mhgroup.userroles ur
 on u.userid = ur.userid
left join mhgroup.roles r
 on r.alias = ur.alias