How to find which users have permission and do not have access to a PeopleSoft Component Interface?

Many a times we create number of batch processes based on Component Interfaces. No matter how much carefully we try to take care of the security settings we often overlook on CI permissions.
Here are couple of SQL queries that could assist finding the Users and Permission List having permission and not having access to a Component Interface.


For example, 'USERMAINT_SELF' - Component Interface is based on the My Profile Component. It allows only the current user to access it. This Component Interface must definitely have permission to the components like: Forgot My Password, Change Password - (CHANGE_PASSWORD component), and Change Expired Password.

To find out users already having permission to a Component interface(in this case - USERMAINT_SELF CI) the below SQL statement will be useful:
SELECT DISTINCT oprid,
                oprclass
FROM   psoprcls
WHERE  oprclass IN (SELECT classid
                    FROM   psauthbuscomp
                    WHERE  bcname = 'USERMAINT_SELF'
                           AND bcmethod = 'ChangePassword')
       AND oprclass <> 'PSADMIN' ;


To find out what users do not have access to this component interface run this statement:
SELECT DISTINCT oprid,
                oprclass
FROM   psoprcls
WHERE  oprclass NOT IN (SELECT classid
                        FROM   psauthbuscomp
                        WHERE  bcname = 'USERMAINT_SELF'
                               AND bcmethod = 'ChangePassword')
       AND oprclass <> 'PSADMIN';