11/3/14

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' 

No comments:

Post a Comment

Please refrain for marketing messages and unnecessary back links.

Workflow errors on New Hire and Add employment instance

User tries to hire a new person or tries to add a new organization relationship encounters a series of error messages related to workflow...

Trending in this Blog this month