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.
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'
No comments
Please refrain for marketing messages and unnecessary back links.