Security Report - Users and SecGroupDesc

Epicor ERP 9
1

Is there a way to look up a User and which Security Groups are associated to that User by Security Group Description? The UserFile only lists the Security Codes but we are looking to pull in the description instead of the code. Tried linking the two tables but it isn’t working.

When managers request to mirror a new user with a current user, they want to see a report which lists all the security groups that the new user will be getting based on a current user’s access. What we are looking for is to enter a User Name and then the report displays all the security group (descriptions) associated to that user and then email that report to the manager for approval.

2

Yes, under System Management>Security Maintenance > Reports >User/Groups Report

image.png497×605 45.6 KB

1 Like
3

I might use a BAQ too.
Starting with tables UserFile and UserComp.

4

Would it be best to use an SSRS report to send this information or to use a scheduled task to send the report daily to a shared file location? I haven’t found the tables to re-create this in SSRS yet.

5

I have to ask…why do you want to send this our daily? Access shouldn’t be changing that much.

6

Access shouldn’t be changing that much but the managers want it as close to “real time” as possible without having to manually run the report as needed.

7

Probably a SSRS report and schedule it. This isn’t something we share regularly so not sure what others may be doing.

8

Would it be possible to export a report to a shared file location? I don’t recall if you can do this for reports but I know there is a BAQ export process.

9

I am sure there is way but i have never done that so I couldn’t tell you.

10

I couldn’t get it to link correctly with the UserFile, UserComp and SecGroup tables to pull in the security groups per user. It seems like the SecGroup table isn’t joined to any other tables.

11

You might want to start with just the UserFile.

Since you won’t be able to join SecGroup to User File
UserFile stores groups as comma separated values

You can join UserComp to UserFile if you need a list of plants - here again, plants will be stored as comma separated.

12

The UserFile only has the security group codes and not the descriptions. The User/Groups Report works but would like to export it maybe using the BAQ Export process.

13

Sorry for chiming in late, but isn’t the security groups field one of those fields that contain a tilde separated list?

image.png738×411 9 KB

14

If I had to do it just once …

  1. Export UserFile (via BAQ), (specifically DcdUserID and GroupList fields) to Excel
  2. Use Excel’s Text To Columns on the GroupList column, using the tilde as the delimiting character
  3. Create new rows for each user that has data in the newly created columns
  4. Export the SecGroup, and import to Excel
  5. Make a Vlookup column to connect the SecGroups to the parsed groups list
15

We’re looking to automate this process without having to do lookup in Excel. The User/Group report looks good but trying to automate it using a BAQ export process.

16

I think I have a solution. Which I can’t believe actually works

Make a BAQ with a amain Query and 1 sub Query

The Main is

image.png1108×568 33.2 KB

The secondary is just the table SecGroup, with the following fields

image.png829×378 12.5 KB

The calulated field named ‘code’ is just

This works by making the SecGroupCode have the ‘%’ in the front and back, and then linking the tables with the LIKE operator.

Set the link in the main query to Left Join to include all the users with no SecGroup Set

3 Likes
17

One caveat. You get bad results if one secID is entirely in another.

For example, having secGroupID’s ‘SEC1’ and ‘SEC10’

I have a solution for that, I’ll post later

18

The fix is to have the sub query return 3 calculated fields
code1 = SecGroupID + ‘~%’
code2 = ‘%~’ + SecGroupID + ‘~%’
code3 = ‘%~’ + SecGroupID

Then have 4 link parameters
UserFile.GroupList LIKE query2_code1
OR UserFile.GroupList LIKE query2_code2
OR UserFile.GroupList LIKE query2_code3
OR UserFile.GroupList = query2_SecGroupID

This allows to it to match the exact groupID if its the first, middle, last, or only member of the group lisy

19

I recall getting a copy of a security dashboard from vantage forums…Now where did I put that thing…

20

Ok here it is. I don’t recall using it too much, but might help a bit.Security Dashboard.dbd (435.6 KB)

and credit to whoever posted it originally in VF

1 Like