Roles Permission Data Extract

Hello

Does anyone know a quick way to extract the role permission details into a report? We would like to do a full system review on roles permissions but since we have so many roles, its difficult to do a proper audit without extracting the data first and we do not wish to document it manually.

Thanks

Find more posts tagged with

Accepted answers

All comments

Nick James

Are you using Hansen (IPS O&R) or Lawson (FSM, HCM, etc.)? Are you on-prem or SaaS?

Najin Hanna

Hi Nick. We are on IPS 11.2.2020.10 on prem

Nick James

If you have access to the install media, there is an EXE in there called AccessControlCleanup.exe that will do a broad sweeping cleanup of orphaned access control groups. You will need the database login credentials for this.

You could drop the following queries into a report (SSRS, Crystal, etc.):

select za.empid,za.USRKEY,za.USRNAME,ru.ROLEKEY,ar.ROLENAME,za.PASSEXPDTTM,ru.ADDBY,ru.adddttm from core_accesscontrol.zaccusr za
left join core_accesscontrol.roleusers ru on za.USRKEY = ru.USRKEY -- Mapping of User Key and Role Key.
left join CORE_ACCESSCONTROL.ACCESSROLES ar on ru.ROLEKEY = ar.ROLEKEY

select za.empid,za.USRKEY,za.USRNAME,ul.LICENSEKEY,l.LICENSENAME,za.PASSEXPDTTM from core_accesscontrol.zaccusr za
left join CORE_LICENSE.USERLICENSE ul on za.USRKEY = ul.USRKEY
left join CORE_LICENSE.LICENSE l on ul.LICENSEKEY = l.LICENSEKEY

Nick James

Najin,

Check with Support before you try this because they may have removed or redesigned the ability to create nested roles, but I went through and organized our roles by department (division within the Water Dept), and then created roles based their access needs. Ex: Supervisor, Inspector, Crew Leader, Dispatcher, etc.

Nick James

*WUFO = Water Utility Field Operations and includes our Water Distribution and Sewer Maintenance bureaus.

Najin Hanna

Thank you Nick! Very helpful. We are planning to upgrade, so I will reach out to Support to keep the new structure in mind. Any other recommendations or lessons learned you have in terms of organization of the roles?

Nick James

I created some "Read-Only" groups for anyone who needs access to IPS, but they do not have a need to edit any work items or assets. This works well for executives or persons from other departments that need to view data. When I create a new user, I add all the relevant "Read-Only" roles, and then add them to the department (division) and position specific roles. I added a "Misc" node for all of the roles I haven't tackled yet. I'm digging through my documentation, and I will post some files here once I make sure there is no sensitive data…

Nick James

Here's a worksheet I used to loosely track my changes. FYI, IPS does not support moving the Admin role to a parent node. Any time I do a patch, I have to do an update to the database table… I do not recommend doing this (but feel free to say Nick from Dayton said it was OK and let me know what Infor's reaction is) ;)

Access Control Re-Alignment - City of Dayton-Ohio.xlsx

Najin Hanna

LOL thanks Nick! This is awesome! If you dont mind, can I reach out to you if I have any more questions (after we have started down the rabbit hole)?

Nick James

Yes, reach out here or message me. You will definitely want to outline and plan before moving forward. A lot of what I did was in the database, because Infor does not provide any tools (at least not v2023.01 or prior) to move the roles around. You will see in my Excel sheets that I have some named zz_delete, those were roles that I was able to manually move to other roles, but I left them as a backup until I reached the final deployment. I did all of my testing in a dev environment.

Copyright © 2025 Infor. All rights reserved.