Subversion Repositories DevTools

/* Access Control Static Permissions */
SELECT co.obj_name, perm.perm_id, perm.perm_value
  FROM (
  
            (
            /* All 'Y' User Permissions */        
                
                /* Get all 'Y' permissions */
                SELECT DISTINCT rp.OBJ_ID, rp.PERM_ID, rp.PERM_VALUE
                  FROM USER_ROLES ur,
                           ROLE_PRIVILEGES rp,
                           ROLES ro
         WHERE ur.ROLE_ID = ro.ROLE_ID
                   AND rp.ROLE_ID = ro.ROLE_ID
                   AND ur.USER_ID = :USER_ID    
                   AND rp.PERM_VALUE = 'Y'      
                   AND ro.APP_ID = :APP_ID         
                MINUS
                /* Revoke permissions if they set to 'N'*/
                SELECT DISTINCT rp.OBJ_ID, rp.PERM_ID, 'Y' AS PERM_VALUE
                  FROM USER_ROLES ur,
                           ROLE_PRIVILEGES rp,
                           ROLES ro
         WHERE ur.ROLE_ID = ro.ROLE_ID
                   AND rp.ROLE_ID = ro.ROLE_ID
                   AND ur.USER_ID = :USER_ID    
                   AND rp.PERM_VALUE = 'N'      
                   AND ro.APP_ID = :APP_ID
                   
                )
                
                MINUS    
                
                /* Role Variant Revokes */   
                SELECT DISTINCT rp.OBJ_ID, rp.perm_id, 'Y' AS perm_value
                  FROM role_privileges rp,
                       ROLES ro,
                       user_roles ur
                 WHERE ro.role_id = rp.role_id
                   AND ro.role_id = ur.role_id
                   AND rp.PERM_VALUE = 'N'
                   AND ro.IS_ROLE_VARIATION = 'Y'
                   AND ur.user_id = :USER_ID
                   AND ro.APP_ID = :APP_ID   
                   
           ) perm,
       control_objects co
       --page_control_objects pco,
       --application_pages ap    
 WHERE perm.obj_id = co.obj_id
   AND co.APP_ID = :APP_ID
 
  -- AND ap.APP_ID = co.APP_ID
  -- AND ap.APP_ID = :APP_ID
   --AND pco.obj_id = co.obj_id
   --AND pco.PAGE_ID = ap.PAGE_ID
   --AND ap.page_name = :PAGE_NAME 
   /* Return uncomented lines to include use of APPLICATION_PAGES table */