Audits for User Access

Hello,

We are undergoing audits on our processes, and one of the gaps that have been identified is controls over who has access to Visual... more specifically is when a user was deleted after leaving the team or company.

I've not been able to find a table in Audit Maintenance that would track this information. Is anyone aware of where I should look, or have a query that I could run in the query tool?

Find more posts tagged with

Comments

Chris Chandler

At least on 7.1.2, you could audit user_pgm_authority for delete on user_id.

[Updated on 11/19/2015 12:18 PM]

marvin-schultz

Thank you, that's perfect for now and going forward.

However, if anyone knows of a query that I could run to find historical data, it would be much appreciated.

Chris Chandler

The trigger that is created by the audit will store the data in the HISTORY_DATA table.

SELECT
USER_ID, TBL_NAME, COL_NAME,
CREATE_DATE, PRIMARY_KEY, ACTION,
OLD_VALUE, NEW_VALUE
FROM HISTORY_DATA
WHERE TBL_NAME = 'USER_PGM_AUTHORITY'
ORDER BY CREATE_DATE;

Also, If the user is never granted or revoked access, they do not show up in the USER_PGM_AUTHORITY table so you have to make sure that all users get something added or taken away but Visual gives a lot they shouldn't have by default so you should take some things away anyway for all new users.

Chris Chandler

by the way, in testing on Oracle, that trigger didn't fire on delete of a user... So, may work in SQL Server but it did not in Oracle. I have no idea why.

Chris Chandler

OK, it did work in my second test. I added update to the audits but that should not have made any difference. You'll just have to try it out I guess. Good luck.

Copyright © 2025 Infor. All rights reserved.