Subversion Repositories DevTools

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
64 jtweddle 1 
SELECT qry.TABLE_NAME,
2 
 	   qry.REF_COLUMN_VAL,
3 
 	   qry.PERM_ID,
4 
 	   qry.PERM_VALUE
5 
  FROM --APPLICATION_PAGES ap,
6 
  	   --PAGE_CONTROL_OBJECTS pco,
7 
	   CONTROL_OBJECTS co,
8 
	   DATA_TABLES dt,
9 
		(
10 
 
11 
		/* Access Control Data Permissions */
12 
		 SELECT bl.DT_ID,
13 
		 		bl.TABLE_NAME,
14 
		 		bl.REF_COLUMN_VAL,
15 
		 		bl.PERM_ID,
16 
		 		DECODE ( yc.PERM_VALUE,
17 
		 	 		   	 NULL, bl.PERM_VALUE,
18 
		 			 	 yc.PERM_VALUE ) AS PERM_VALUE
19 
		  FROM (
20 
		       	/* Get base list for Data Permissions */
21 
			 	SELECT rol.ROLE_ID, dt.DT_ID, dt.TABLE_NAME, 0 AS REF_COLUMN_VAL, pt.PERM_ID, 'Y' AS PERM_VALUE
22 
			 	  FROM CONTROL_OBJECTS co,
23 
			 	  	   DATA_TABLES dt,
24 
			 		   PERMISSION_TYPES pt,
25 
			 		   (
26 
			 		    /* Get User Roles for this Application */
27 
			 		    SELECT ro.*
28 
			 			  FROM ROLES ro,
29 
			 			  	   USER_ROLES ur
30 
			 			 WHERE ur.ROLE_ID = ro.ROLE_ID
31 
			 			   AND ro.APP_ID = :APP_ID
32 
			 			   AND ur.USER_ID = :USER_ID
33 
			 		   ) rol
34 
			 	 WHERE dt.OBJ_ID = co.OBJ_ID
35 
			 	   AND co.APP_ID = :APP_ID
36 
		  	   ) bl,
37 
		       (
38 
		 		 /* Find only 'Y' Permissions, which will change 'All' permission to 'N' */
39 
		 		 SELECT dt.DT_ID, 0 AS REF_COLUMN_VAL, dp.PERM_ID, 'N' AS PERM_VALUE, COUNT(*) AS YES_COUNT
40 
		 		  FROM DATA_PERMISSIONS dp,
41 
		 		  	   DATA_TABLES dt,
42 
		 			   ROLES ro,
43 
		 			   USER_ROLES ur
44 
		 		 WHERE ur.ROLE_ID = ro.ROLE_ID
45 
		 		   AND dp.ROLE_ID = ro.ROLE_ID
46 
		 		   AND dp.DT_ID = dt.DT_ID
47 
		 		   AND ro.APP_ID = :APP_ID
48 
		 		   AND ur.USER_ID = :USER_ID
49 
		 		   AND ro.IS_ROLE_VARIATION IS NULL
50 
		 		 GROUP BY dt.DT_ID, dp.PERM_ID
51 
		 		) yc
52 
		  WHERE yc.DT_ID (+) = bl.DT_ID
53 
		    AND yc.PERM_ID (+) = bl.PERM_ID
54 
		UNION
55 
		(
56 
		/* Get Filter Settings for All Permission Types */
57 
		SELECT dt.DT_ID, dt.TABLE_NAME, dp.REF_COLUMN_VAL, dp.PERM_ID, dp.PERM_VALUE
58 
		  FROM DATA_PERMISSIONS dp,
59 
		  	   DATA_TABLES dt,
60 
			   ROLES ro,
61 
			   USER_ROLES ur
62 
		 WHERE ur.ROLE_ID = ro.ROLE_ID
63 
		   AND dp.ROLE_ID = ro.ROLE_ID
64 
		   AND dp.DT_ID = dt.DT_ID
65 
		   AND ro.APP_ID = :APP_ID
66 
		   AND ur.USER_ID = :USER_ID
67 
		MINUS
68 
		/* Overwrite Data Permissions using User Specific Role */
69 
		SELECT DISTINCT
70 
			   dt.DT_ID,
71 
			   dt.TABLE_NAME,
72 
			   dp.REF_COLUMN_VAL,
73 
			   dp.PERM_ID,
74 
			   DECODE ( dp.PERM_VALUE,
75 
			   		  	'Y', 'N', 'Y' ) AS PERM_VALUE
76 
		  FROM DATA_PERMISSIONS dp,
77 
		  	   DATA_TABLES dt,
78 
			   ROLES ro,
79 
			   USER_ROLES ur
80 
		 WHERE ur.ROLE_ID = ro.ROLE_ID
81 
		   AND dp.ROLE_ID = ro.ROLE_ID
82 
		   AND dp.DT_ID = dt.DT_ID
83 
		   AND ro.APP_ID = :APP_ID
84 
		   AND ur.USER_ID = :USER_ID
85 
		   AND ro.IS_ROLE_VARIATION = 'Y'
86 
		)
87 
 
88 
		) qry
89 
  WHERE dt.OBJ_ID = co.OBJ_ID
90 
	AND dt.DT_ID = qry.DT_ID
91 
	AND co.APP_ID = :APP_ID
92 
 
93 
	--AND ap.APP_ID = :APP_ID
94 
	--AND ap.PAGE_NAME = :PAGE_NAME
95 
	--AND pco.PAGE_ID = ap.PAGE_ID
96 
    --AND pco.OBJ_ID = co.OBJ_ID
97 
	/* Uncomment the lines above if you want to use APPLICATION_PAGES table */
98