Quickly Find Users Who Have Access To DataMover

by Prashant on September 13, 2015

in PeopleSoft Troubleshooting

Datamover is a very powerful tool and access to it should always be controlled especially in PRD 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 couple of SQLs that can help you understand the security associated with 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 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’
)
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.

Also Read:  How PeopleSoft Single Signon Works?

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

{ 1 comment… read it below or add one }

mosaad2 July 6, 2017 at 6:03 am

thanks

Reply

Leave a Comment

Previous post:

Next post: