Compare Permissions on Two Roles

By Chris Malek | Tue, Sep 26, 2023

Here is an example query to compare permissions on two roles. This is useful when you have a role that is a copy of another role and you want to see what permissions are different between the two roles.

  • Using the following as is will find permissions that are in role 1 but not in role 2
  • Change the following to an “exists” to find permissions that are in role 1 and in role 2
SELECT * 
FROM 
 PSAUTHITEM APP, 
PSROLECLASS RCA 

WHERE RCA.CLASSID = APP.CLASSID
AND rca.rolename = 'ROLE-1-REPLACE-ME'
-- Change this to "exists" to find permissions that are in role 1 and in role 2
-- Change this to "NOT exists" to find permissions that are in role 1 but not in role 2
AND NOT EXISTS
  (SELECT 1 FROM 
    PSAUTHITEM STU, 
      PSROLECLASS RCS
      WHERE RCS.ROLENAME = 'ROLE-2-REPLACE-ME' 
      and rcs.classid = stu.classid
     AND STU.MENUNAME = APP.MENUNAME
     AND stu.baritemname = app.baritemname 
     AND stu.barname = app.barname 
     AND stu.pnlitemname = app.pnlitemname 
     and stu.displayonly = app.displayonly 
     and stu.authorizedactions = app.authorizedactions);
Article Categories
Author Info
Chris Malek

Chris Malek is a PeopleTools® Technical Consultant with two decades of experience working on PeopleSoft enterprise software projects. He is available for consulting engagements.

About Chris Work with Chris
Looking for pain-free PeopleSoft web services? 😀
PeopleSoft Simple Web Services (SWS)

Introducing a small but powerful PeopleSoft bolt-on that makes web services very easy. If you have a SQL statement, you can turn that into a web service in PeopleSoft in a few minutes.

Book
Integration Broker - The Missing Manual

I am in the process of writing a book called "Integration Broker - The Missing Manual" that you can read online.