Subversion Repositories DevTools

SELECT qry.TABLE_NAME,
           qry.REF_COLUMN_VAL,
           qry.PERM_ID,
           qry.PERM_VALUE
  FROM --APPLICATION_PAGES ap,
           --PAGE_CONTROL_OBJECTS pco,
           CONTROL_OBJECTS co,
           DATA_TABLES dt,
                (
                        
                /* Access Control Data Permissions */   
                 SELECT bl.DT_ID,
                                bl.TABLE_NAME,
                                bl.REF_COLUMN_VAL,
                                bl.PERM_ID,
                                DECODE ( yc.PERM_VALUE,
                                                 NULL, bl.PERM_VALUE,
                                                 yc.PERM_VALUE ) AS PERM_VALUE
                  FROM (
                        /* Get base list for Data Permissions */   
                                SELECT rol.ROLE_ID, dt.DT_ID, dt.TABLE_NAME, 0 AS REF_COLUMN_VAL, pt.PERM_ID, 'Y' AS PERM_VALUE 
                                  FROM CONTROL_OBJECTS co,
                                           DATA_TABLES dt,
                                           PERMISSION_TYPES pt,
                                           (
                                            /* Get User Roles for this Application */
                                            SELECT ro.*
                                                  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
                                           ) rol
                                 WHERE dt.OBJ_ID = co.OBJ_ID
                                   AND co.APP_ID = :APP_ID
                           ) bl,
                       (
                                 /* Find only 'Y' Permissions, which will change 'All' permission to 'N' */
                                 SELECT dt.DT_ID, 0 AS REF_COLUMN_VAL, dp.PERM_ID, 'N' AS PERM_VALUE, COUNT(*) AS YES_COUNT 
                                  FROM DATA_PERMISSIONS dp,
                                           DATA_TABLES dt,
                                           ROLES ro,
                                           USER_ROLES ur
                                 WHERE ur.ROLE_ID = ro.ROLE_ID
                                   AND dp.ROLE_ID = ro.ROLE_ID
                                   AND dp.DT_ID = dt.DT_ID       
                                   AND ro.APP_ID = :APP_ID 
                                   AND ur.USER_ID = :USER_ID 
                                   AND ro.IS_ROLE_VARIATION IS NULL
                                 GROUP BY dt.DT_ID, dp.PERM_ID
                                ) yc 
                  WHERE yc.DT_ID (+) = bl.DT_ID
                    AND yc.PERM_ID (+) = bl.PERM_ID
                UNION
                (
                /* Get Filter Settings for All Permission Types */ 
                SELECT dt.DT_ID, dt.TABLE_NAME, dp.REF_COLUMN_VAL, dp.PERM_ID, dp.PERM_VALUE 
                  FROM DATA_PERMISSIONS dp,
                           DATA_TABLES dt,
                           ROLES ro,
                           USER_ROLES ur
                 WHERE ur.ROLE_ID = ro.ROLE_ID
                   AND dp.ROLE_ID = ro.ROLE_ID
                   AND dp.DT_ID = dt.DT_ID       
                   AND ro.APP_ID = :APP_ID 
                   AND ur.USER_ID = :USER_ID
                MINUS  
                /* Overwrite Data Permissions using User Specific Role */
                SELECT DISTINCT
                           dt.DT_ID, 
                           dt.TABLE_NAME,
                           dp.REF_COLUMN_VAL,
                           dp.PERM_ID,
                           DECODE ( dp.PERM_VALUE,
                                                'Y', 'N', 'Y' ) AS PERM_VALUE
                  FROM DATA_PERMISSIONS dp,
                           DATA_TABLES dt,
                           ROLES ro,
                           USER_ROLES ur
                 WHERE ur.ROLE_ID = ro.ROLE_ID
                   AND dp.ROLE_ID = ro.ROLE_ID
                   AND dp.DT_ID = dt.DT_ID       
                   AND ro.APP_ID = :APP_ID 
                   AND ur.USER_ID = :USER_ID 
                   AND ro.IS_ROLE_VARIATION = 'Y'   
                )
                
                ) qry
  WHERE dt.OBJ_ID = co.OBJ_ID
        AND dt.DT_ID = qry.DT_ID
        AND co.APP_ID = :APP_ID
        
        --AND ap.APP_ID = :APP_ID
        --AND ap.PAGE_NAME = :PAGE_NAME 
        --AND pco.PAGE_ID = ap.PAGE_ID
    --AND pco.OBJ_ID = co.OBJ_ID
        /* Uncomment the lines above if you want to use APPLICATION_PAGES table */