Find Users Who Have Access To PeopleSoft DataMover. Superfast!!

PeopleSoft Datamover is a very powerful tool and access to it should always be controlled especially in Production environments. In every organization, many users switch departments, roles and responsibilities over a period of time and every once in a while, you should review the security associated with these users to confirm that they don’t have any unwanted access.

Here are a couple of SQLs that can help you understand the security associated with PeopleSoft Datamover access.

SQL1 : This SQL will give the list of users who have access to Data Mover.

SELECT DISTINCT ROLEUSER
FROM PSROLEUSER A
WHERE A.ROLENAME IN
(SELECT DISTINCT ROLENAME
FROM PSROLECLASS A,
PSAUTHITEM B
WHERE A.CLASSID IN
(SELECT DISTINCT CLASSID FROM psauthitem WHERE MENUNAME='DATA_MOVER'
)
);

You should carefully review this list and remove any unwanted access.

Once you figure out the users who have access to the PeopleSoft data mover tool, you may want to find the roles and permission list  that granted data mover access to these users and then modify role/permission list accordingly.

SQL2: Below SQL will give you the list of user roles and associated permission lists that allows datamover access to the user. Run the below SQL for each user.

SELECT ROLENAME,
CLASSID
FROM PSROLECLASS
WHERE ROLENAME IN
(SELECT DISTINCT ROLENAME
FROM PSROLEUSER A
WHERE A.ROLENAME IN
(SELECT DISTINCT ROLENAME
FROM PSROLECLASS A,
PSAUTHITEM B
WHERE A.CLASSID IN
(SELECT DISTINCT CLASSID FROM psauthitem WHERE MENUNAME='DATA_MOVER'
)
)
AND A.ROLEUSER='OPRID' -- Replace with actual user id
)
AND CLASSID IN
( SELECT CLASSID FROM PSAUTHITEM WHERE MENUNAME='DATA_MOVER'
);

Review the results and assign the security accordingly. Same SQL can be used to identify access to other items like Application Designer(Menu Name: APPLICATION_DESIGNER) etc.

If you have some good SQLs and would like to share with others, please post in comments section below.

Also read: Unauthorized to PeopleTools objects in Application Designer.