2

Our former security admin team off-boarded terminated users by deleting their user profiles from the system. We have changed that policy but there is potential for duplicates. I am attempting to find user ids on the various module tables that are not on the OPRDEFN but having no luck.

I would like to query the major tables to return a list of all user ids to compare to the current OPRDEFN. From there, I can either have them added or create a reference list the admins to use prior to creating a new user id.

Does anyone have any tips or already written SQL? I am not the best SQL writer, I've tried several different things but nothings works.

Any help would be greatly appreciated.

Walucas
  • 2,549
  • 1
  • 21
  • 44
Cynthia White
  • 21
  • 2

1 Answers1

3

Here you have a list of tables using the OPRID field. I have already formatted it in a way you can just run the result to get the Oprids. Also, consider other columns like OPERATOR

SELECT 'SELECT DISTINCT OPRID FROM PS_'||RECNAME||';' FROM PSRECDEFN WHERE RECTYPE=0
AND RECNAME IN 
(SELECT RECNAME FROM PSRECFIELD WHERE FIELDNAME IN ('OPRID','OPERATOR'))
AND RECNAME NOT LIKE '%AET'
AND RECNAME NOT LIKE '%TMP'

Also, you may just look at PSACCESSLOG, it will show you when someone access, so you may save time by querying it only

Walucas
  • 2,549
  • 1
  • 21
  • 44