Follow us on Facebook Follow us on Twitter Get the latest job postings quicker!
Follow us on Facebook and Twitter!

Like this Page? Share it!
HomeHomeManhattan Assoc...Manhattan Assoc...GeneralGeneralRole and access detail tablesRole and access detail tables
New Post
4/24/2018 4:34 AM


Being new to WMS, I was tasked with looking at the roles and the permissions they have been granted.  What I am looking for is the table(s) that contain the roles and the access details that makes up the role.  Does anyone have an idea what tables these would be located on?  Also, if anyone has any SQL with this report that would be very helpful.  Thank you, 

New Post
5/9/2018 6:03 AM



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) )
                    (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;


New Post
5/9/2018 10:32 AM


We are using 2016 and have LM in place.   I will run the code you provided and am hopeful it will provide the detail that I am looking for.  Thank you

New Post
6/6/2018 5:05 AM

Hi, after running the code, a comparison of what the users screen shows and the output of the results shows there is a lot of other permissions that appears.  I believe there has to be some type of identifier that will get rid of the transactions and will only show those that have been assigned.  Do you or anyone know of what table that identifier would be sitting? 

If I have my verbiage correct, the role has an application attached and there are transactions (those based on a URL) that are placed in the application.  These are the permissions that provide the role what functions they can perform.  I hope this is right, if not, please correct me. 

Thank you,





New Post
6/6/2018 6:03 AM



I'm not sure what other information appears. When I run it, I see this:


It sounds like whe you run it, you're seeing a lot more than that. I can't really say why that'd be, other than speculating that it might be something different with the setup of your permissions.

HomeHomeManhattan Assoc...Manhattan Assoc...GeneralGeneralRole and access detail tablesRole and access detail tables

Copyright 2010-2019 by WMS Support Forum   |  Privacy Statement  |  Terms Of Use