Hi,
What version of WMS are you using? In some of our facilities, we're using v2011, but only the LM portion of it. Still, if I understand correctly, the user access in the platform versions of Manh is all linked together. Here are some queries that we used during our implementations. I can't promise that they'll be the same for your version, but if not, hopefully they'll at least give you an idea where to look.
List all roles assigned to each user
WITH Ranked ( user_name, rnk, role_name )
AS ( SELECT user_name,
ROW_NUMBER() OVER( PARTITION BY user_name ORDER BY role_name ),
CAST( role_name AS VARCHAR(2000) )
FROM
(SELECT uu.user_name, ri.role_name FROM ucl_user uu INNER JOIN access_control ac ON uu.ucl_user_id = ac.ucl_user_id JOIN role ri ON ac.role_id = ri.role_id)),
AnchorRanked ( user_name, rnk, role_name )
AS ( SELECT user_name, rnk, role_name
FROM Ranked
WHERE rnk = 1 ),
RecurRanked ( user_name, rnk, role_name )
AS ( SELECT user_name, rnk, role_name
FROM AnchorRanked
UNION ALL
SELECT Ranked.user_name, Ranked.rnk,
(RecurRanked.role_name || ', ' || Ranked.role_name)
FROM Ranked
INNER JOIN RecurRanked
ON Ranked.user_name = RecurRanked.user_name
AND Ranked.rnk = RecurRanked.rnk + 1 )
SELECT user_name USERS, MAX( role_name ) ROLES
FROM RecurRanked
WHERE user_name LIKE '%%' and role_name like '%%' GROUP BY user_name order by user_name;
The above includes a recursion loop to display all roles for each user in a single record.
Core functionality of the above query
SELECT uu.user_name, ri.role_name FROM ucl_user uu
INNER JOIN access_control ac ON uu.ucl_user_id = ac.ucl_user_id
JOIN role ri ON ac.role_id = ri.role_id
WHERE uu.user_name LIKE '%%' and ri.role_name like '%%'
ORDER BY uu.user_name;
Permissions assigned to each role
SELECT ri.role_name ROLE, pe.permission_name PERMISSIONS
FROM role ri INNER JOIN role_app_mod_perm ram ON ri.role_id = ram.role_id
JOIN permission pe ON ram.permission_id = pe.permission_id
WHERE ri.role_name like '%%'
ORDER BY ri.role_name, pe.permission_name;