Subversion Repositories DevTools

/* User Permissions */
SELECT co.OBJ_NAME,
           up.*
 FROM CONTROL_OBJECTS co,
                (
                
                SELECT DISTINCT
                           usrro.ROLE_ID,
                           usrro.ROLE_NAME,
                           usrro.IS_ROLE_VARIATION,
                           DECODE ( perm_active.OBJ_ID, 
                                                NULL, perm_visible.OBJ_ID,
                                                perm_active.OBJ_ID ) AS OBJ_ID,
--                              perm_active.OBJ_ID,
--                              perm_visible.OBJ_ID,                                               
                           perm_visible.PERM_ID AS perm_visible_id,
                           perm_visible.PERM_VALUE AS visible_value,
                           perm_active.PERM_ID AS perm_active_id,
                           perm_active.PERM_VALUE AS active_value
                  FROM 
                           (
                                /* Get All User Roles */
                            SELECT ro.ROLE_ID,
                                           ro.ROLE_NAME,
                                           ro.IS_ROLE_VARIATION
                                  FROM ROLES ro,
                                           USER_ROLES ur
                                 WHERE ur.ROLE_ID = ro.ROLE_ID 
                                   AND ro.APP_ID = :APP_ID
                                   AND ur.USER_ID = :USER_ID 
                           ) usrro,
                           (
                            /* Get "Visible" Settings */
                                SELECT rco.ROLE_ID,
                                           rco.OBJ_ID,
                                           rp.PERM_ID,
                                           rp.PERM_VALUE
                                  FROM ROLE_PRIVILEGES rp,
                                           (
                                            /* Get Role Controls for This Application */
                                            SELECT DISTINCT co.OBJ_ID, ro.ROLE_ID
                                                  FROM ROLES ro,
                                                       ROLE_PRIVILEGES rp,
                                                           CONTROL_OBJECTS co
                                             WHERE rp.ROLE_ID = ro.ROLE_ID
                                                   AND rp.OBJ_ID = co.OBJ_ID
                                                   AND ro.APP_ID = :APP_ID
                                           ) rco
                                 WHERE rp.ROLE_ID (+)= rco.ROLE_ID
                                   AND rp.OBJ_ID (+) = rco.OBJ_ID
                                   AND rp.PERM_ID (+)= 1
                           ) perm_visible,
                           (
                            /* Get "Active" Settings */
                            SELECT rco.ROLE_ID,
                                           rco.OBJ_ID,
                                           rp.PERM_ID,
                                           rp.PERM_VALUE
                                  FROM ROLE_PRIVILEGES rp,
                                           (
                                            /* Get Role Controls for This Application */
                                            SELECT DISTINCT co.OBJ_ID, ro.ROLE_ID
                                                  FROM ROLES ro,
                                                       ROLE_PRIVILEGES rp,
                                                           CONTROL_OBJECTS co
                                             WHERE rp.ROLE_ID = ro.ROLE_ID
                                                   AND rp.OBJ_ID = co.OBJ_ID
                                                   AND ro.APP_ID = :APP_ID
                                           ) rco
                                 WHERE rp.ROLE_ID (+)= rco.ROLE_ID
                                   AND rp.OBJ_ID (+) = rco.OBJ_ID
                                   AND rp.PERM_ID (+)= 2
                           ) perm_active
                 WHERE perm_active.ROLE_ID (+) = usrro.ROLE_ID 
                   AND perm_visible.ROLE_ID (+)= usrro.ROLE_ID 
                   AND perm_active.OBJ_ID = perm_visible.OBJ_ID 
                   
                ) up
 WHERE up.OBJ_ID = co.OBJ_ID               
   
ORDER BY UPPER(co.OBJ_NAME), up.IS_ROLE_VARIATION, UPPER(up.ROLE_NAME)