PS Table that stores information about User Roles

Question

For history, I just recently wrote this question:

This has led to a follow up question. In the User Profile, "Roles" Tab (which I'm assuming is a table somewhere), is there a table I can report on that will get me what was changed and possibly by which user account (and maybe even the IP address of the user account)?

The path is: Main Menu->PeopleTools->Security->User Profiles (and then the 'Roles' Tab).

Ultimately I need to figure out what change was made (when a role name was added) and by what user account and as of what date/time. If possible I need to link it to the IP address (which I think is found here: PSACCESSLOG).

Answer 1

The PSROLEUSER table stores the roles a user is assigned. By default there is no history. To get that you would need to enable auditing, either record auditing through App Designer or setting up database trigger auditing. The database trigger audits can actually capture changes made either online or through the database so could be considered more complete, but can take some effort to get working properly. However, the database triggers also allow capturing some additional user information at the time of the transaction.

See http://peoplesoft.wikidot.com/auditing-user-profiles, particularly the section on using the the GET_PS_OPRID functions for Oracle or the SQL Server equivalent. Capturing the OSUSER and IP here would more reliable than trying to tie back to PSACCESSLOG.

Answer 2

what you need is audit on the PSROLEUSER table: you need to build an audit table for it and fill it either by a database trigger or through a PeopleSoft development: adding record audit to the PSROLEUSER table.