SQL query to find out list of users who have access to a Page and information on which Permission list enables the user to access the page:
SELECT DISTINCT oprid, oprclass FROM psoprcls WHERE oprclass IN (SELECT DISTINCT classid FROM psauthitem WHERE pnlitemname = :1)
This query can be modified as below to see if a user has access to a particular component and if yes through which permission list.
SELECT DISTINCT oprid, oprclass FROM psoprcls WHERE oprclass IN ( SELECT DISTINCT classid FROM psauthitem WHERE pnlitemname = :1--required component name) AND oprid = :2 --required userid
How to Query Which Components Are Accessed by Which Roles? For example, for component "JOB_DATA", to find out which role should be given to a user which gives them access to that component or page use the following SQL.
SELECT * FROM psauthitem A, psroleclass B, psroleuser C WHERE A.baritemname IN ( :1 ) AND A.classid = B.classid AND C.rolename = B.rolename AND C.roleuser = 'PS'
SELECT DISTINCT oprid, oprclass FROM psoprcls WHERE oprclass IN (SELECT DISTINCT classid FROM psauthitem WHERE pnlitemname = :1)
This query can be modified as below to see if a user has access to a particular component and if yes through which permission list.
SELECT DISTINCT oprid, oprclass FROM psoprcls WHERE oprclass IN ( SELECT DISTINCT classid FROM psauthitem WHERE pnlitemname = :1--required component name) AND oprid = :2 --required userid
How to Query Which Components Are Accessed by Which Roles? For example, for component "JOB_DATA", to find out which role should be given to a user which gives them access to that component or page use the following SQL.
SELECT * FROM psauthitem A, psroleclass B, psroleuser C WHERE A.baritemname IN ( :1 ) AND A.classid = B.classid AND C.rolename = B.rolename AND C.roleuser = 'PS'