Subversion Repositories DevTools

/* Role Object Pairs for User Permissions */
SELECT DISTINCT ro.ROLE_ID,
       rp.OBJ_ID
  FROM ROLES ro,
           USER_ROLES ur,
       ROLE_PRIVILEGES rp
 WHERE ur.ROLE_ID = ro.ROLE_ID
   AND rp.ROLE_ID = ro.ROLE_ID  
   AND ro.APP_ID = :APP_ID
   AND ur.USER_ID = :USER_ID 


MINUS

/* Take away roles overwritten by role variation */
SELECT DISTINCT ro.ROLE_ID,
       rp.OBJ_ID
  FROM ROLES ro,
           USER_ROLES ur,
       ROLE_PRIVILEGES rp,
           (
            /* Objects for Role Variation Only */
                SELECT rp.OBJ_ID
                  FROM ROLES ro,
                           USER_ROLES ur,
                           ROLE_PRIVILEGES rp
                 WHERE ur.ROLE_ID = ro.ROLE_ID
                   AND rp.ROLE_ID = ro.ROLE_ID  
                   AND ro.APP_ID = :APP_ID
                   AND ur.USER_ID = :USER_ID
                   AND ro.IS_ROLE_VARIATION = 'Y'
           ) rvco
 WHERE ur.ROLE_ID = ro.ROLE_ID
   AND rp.ROLE_ID = ro.ROLE_ID  
   AND ro.APP_ID = :APP_ID
   AND ur.USER_ID = :USER_ID
   AND rp.OBJ_ID = rvco.OBJ_ID
   AND ro.IS_ROLE_VARIATION IS NULL