1 PACKAGE BODY gms_security AS
2 /* $Header: gmsseseb.pls 115.2 2002/07/04 11:22:32 gnema ship $ */
3
4
5 /* ----------------------------------------------------------------------
6 ||
7 || Function Name: Initialize
8 ||
9 || Input Parameters:
10 || X_user_id <-- identifier of the application user
11 || X_calling_module <-- hard-coded string that refers to the
12 || module that is calling gms_security
13 || functions
14 ||
15 || Description:
16 || This function is called to initialize package globals that are
17 || referenced by the security functions. Each form that uses
18 || views that are secured MUST execute this procedure during form
19 || startup, otherwise the logic in the security functions built into
20 || secured views is not executed, and the views return all rows.
21 ||
22 || This built-in default (return unsecured data if package globals
23 || are not enabled) enables system administrators using SQL*Plus to
24 || query data to have access all data.
25 ||
26 || In order to secure data in external applications or custom
27 || modules that are not part of core PA code, this procedure must
28 || be called before querying secured views.
29 ||
30 || ---------------------------------------------------------------------
31 */
32
33 PROCEDURE Initialize ( X_user_id IN NUMBER
34 , X_calling_module IN VARCHAR2 )
35 IS
36 BEGIN
37
38 IF ( X_user_id IS NOT NULL ) THEN
39 G_user_id := X_user_id;
40 G_person_id := pa_utils.GetEmpIdFromUser( G_user_id );
41 ELSE
42 G_person_id := NULL;
43 END IF;
44 G_module_name := X_calling_module;
45 G_query_allowed := NULL;
46 G_update_allowed := NULL;
47
48 END Initialize;
49
50
51
52 /* ----------------------------------------------------------------------
53 ||
54 || Function Name: allow_query
55 ||
56 || Input Parameters:
57 || X_award_id <-- award identifier
58 ||
59 || Description:
60 || This function determines whether a user has query access to a
61 || particular award.
62 ||
63 || The function first checks if the package variable G_query_allowed
64 || has been initiated with a value. This global variable is set
65 || during the Initialize procedure and is used to override normal
66 || validation in the allow_query function (this enables users who
67 || connect to the database in custom modules or in SQL*Plus to
68 || access all data in secured views without enforcing award-based
69 || security).
70 ||
71 || If the global variable is not set, then this function calls the
72 || client extension API to determine whether or not the user has
73 || query privileges for the given project.
74 ||
75 || The default validation that GMS seeds in the client extension for
76 || 'ALLOW_QUERY' is to verify that the user is an active key member
77 || for the award or is using a Cross-award responsibility.
78 ||
79 || There are only two valid values returned from the extension
80 || procedure: 'Y' or 'N'. If the value returned is not one of
81 || these values, then this function returns 'Y'.
82 ||
83 || ---------------------------------------------------------------------
84 */
85
86 FUNCTION allow_query ( X_award_id IN NUMBER) RETURN VARCHAR2
87 IS
88 V_allow_query VARCHAR2(1);
89
90 BEGIN
91
92 IF ( G_query_allowed IS NOT NULL ) THEN
93 RETURN( G_query_allowed );
94 END IF;
95
96 gms_security_extn.check_award_access(
97 X_award_id
98 , G_person_id
99 , G_module_name
100 , 'ALLOW_QUERY'
101 , V_allow_query );
102
103 IF ( V_allow_query IN ('Y', 'N') ) THEN
104 RETURN( V_allow_query );
105 ELSE
106 RETURN( 'Y' );
107 END IF;
108
109 END allow_query;
110
111
112
113
114 /* ----------------------------------------------------------------------
115 ||
116 || Function Name: allow_update
117 ||
118 || Input Parameters:
119 || X_award_id <-- award identifier
120 ||
121 || Description:
122 || This function determines whether a user has update privileges for
123 || a particular award.
124 ||
125 || The structure is identical to the allow_query function. In
126 || the client extension, however, there is NO seeded validation
127 || for 'ALLOW_UPDATE'. GMS assumes that if a person has query access
128 || for the award (ie, is an active key member), he/she may also
129 || update information on the award.
130 ||
131 || ---------------------------------------------------------------------
132 */
133
134 FUNCTION allow_update ( X_award_id IN NUMBER) RETURN VARCHAR2
135 IS
136 V_allow_update VARCHAR2(1);
137
138 BEGIN
139
140 IF ( G_update_allowed IS NOT NULL ) THEN
141 RETURN( G_update_allowed );
142 END IF;
143
144 gms_security_extn.check_award_access(
145 X_award_id
146 , G_person_id
147 , G_module_name
148 , 'ALLOW_UPDATE'
149 , V_allow_update );
150
151 -- insert into ttt values(X_award_id,G_person_id,G_module_name,V_allow_update);
152 -- commit;
153 IF ( V_allow_update IN ('Y', 'N') ) THEN
154 RETURN( V_allow_update );
155 ELSE
156 RETURN( 'Y' );
157 END IF;
158
159 END allow_update;
160
161 /* ----------------------------------------------------------------------
162 ||
163 || Procedure Name: set_value
164 ||
165 || Input Parameters:
166 || X_security_level <-- Hard-Coded value to specify which
167 || level of security to set global values
168 || X_value <-- The value to assign to the package
169 || global. Once set, this package global
170 || is returned when the security function
171 || for that level is called instead of
172 || executing the function validation code.
173 ||
174 || Description:
175 || This procedure is called to assign to a given value to the
176 || security package global variable specified. It is used in
177 || forms that drilldown to details for a specific award so that
178 || security validation is only executed once.
179 ||
180 || For example, labor cost security is implemented by embedding the
181 || view_labor_costs function in the view definition that displays
182 || expenditure item details for a project. Since the function is
183 || row dependent (ie, based on project and expenditure type), it is
184 || executed for each record queried. Since the Expenditure Inquiry
185 || form (PROJECT mode) queries expenditure items for a
186 || specific project, the logic in the view_labor_costs function
187 || needs to be executed only once. Therefore, the package global
188 || variable for view labor cost security is initialized when
189 || the project number/name specified in the form is validated. When
190 || expenditure items are queried, the security function returns
191 || the global value instead of executing its validation logic.
192 ||
193 || ---------------------------------------------------------------------
194 */
195
196 PROCEDURE set_value ( X_security_level IN VARCHAR2
197 , X_value IN VARCHAR2 )
198 IS
199 BEGIN
200
201 IF ( X_security_level = 'ALLOW_UPDATE' ) THEN
202 IF ( X_value IS NULL ) THEN
203 G_update_allowed := NULL;
204 ELSE
205 G_update_allowed := X_value;
206 END IF;
207 ELSIF ( X_security_level = 'ALLOW_QUERY' ) THEN
208 IF ( X_value IS NULL ) THEN
209 G_query_allowed := NULL;
210 ELSE
211 G_query_allowed := X_value;
212 END IF;
213 END IF;
214
215 END set_value;
216
217
218
219 /* ----------------------------------------------------------------------
220 ||
221 || Function Name: check_key_member
222 ||
223 || Input Parameters:
224 || X_person_id <-- Identifier of the person
225 || X_award_id <-- Identifier of the award
226 ||
227 || Return value:
228 || Y <-- Indicates that the person specified is an active key
229 || member for the award specified as of the current date
230 || N <-- The person is not an active key member for the award
231 || NULL <-- Return value if either input parameter is not given
232 ||
233 || ---------------------------------------------------------------------
234 */
235
236 FUNCTION check_key_member ( X_person_id IN NUMBER
237 , X_award_id IN NUMBER ) RETURN VARCHAR2
238 IS
239 dummy NUMBER;
240
241 BEGIN
242 IF ( X_person_id IS NULL OR X_award_id IS NULL ) THEN
243 RETURN( NULL );
244 END IF;
245
246 BEGIN
247 SELECT 1
248 INTO dummy
249 FROM dual
250 WHERE EXISTS (
251 SELECT NULL
252 FROM gms_personnel
253 WHERE award_id = X_award_id
254 AND person_id = X_person_id
255 AND TRUNC(sysdate) BETWEEN start_date_active
256 AND NVL(end_date_active, sysdate) );
257 RETURN( 'Y' );
258
259 EXCEPTION
260 WHEN NO_DATA_FOUND THEN
261 RETURN( 'N' );
262
263 END;
264
265 END check_key_member;
266
267
268
269 END gms_security;
[Home] [Help]