1 PACKAGE BODY fnd_access_control_util AS
2 /* $Header: AFACUTLB.pls 120.3 2011/10/11 06:32:40 srinnakk ship $ */
3
4 --
5 -- Name
6 -- Get_Org_Name
7 -- Purpose
8 -- Return the Organization Name based on the passed ORG_ID
9 -- This function is called in the multi-org stripped views
10 -- to display organization name without joining to the HR table
11 -- Since the multi-org stripped views are used as base tables,
12 -- to use function in the select clause can have better performance
13 -- Arguments
14 -- p_org_id
15 --
16 FUNCTION Get_Org_Name( p_org_id NUMBER )
17 RETURN VARCHAR2
18 IS
19 l_return hr_all_organization_units_tl.name%TYPE;
20 BEGIN
21
22 BEGIN
23 SELECT name
24 INTO l_return
25 FROM hr_all_organization_units_tl
26 WHERE organization_id = p_org_id
27 AND language = userenv('LANG');
28
29 /* Commented out the following lines of code since exception
30 is not raised properly
31 IF SQL%NOTFOUND
32 THEN
33 l_return := NULL;
34 END IF;
35 */
36 EXCEPTION
37 WHEN no_data_found THEN
38 l_return := NULL;
39 END;
40
41 RETURN l_return;
42
43 END Get_Org_Name;
44
45
46 --
47 -- Name
48 -- Policy_Exists
49 -- Purpose
50 -- Check if a policy is already attached to the object.
51 -- This function is called in the Drop_Policy and
52 -- the Add_Policy procedure
53 -- Arguments
54 -- p_object_schema
55 -- p_object_name
56 -- p_policy_name
57 --
58 FUNCTION Policy_Exists(
59 p_object_schema IN VARCHAR2
60 , p_object_name IN VARCHAR2
61 , p_policy_name IN VARCHAR2
62 ) RETURN VARCHAR2
63 IS
64
65 l_return VARCHAR2(10) := 'FALSE';
66
67 CURSOR c_policy_exists
68 ( xp_object_schema VARCHAR2
69 , xp_object_name VARCHAR2
70 , xp_policy_name VARCHAR2
71 )
72 IS
73 SELECT 'TRUE'
74 FROM sys.dual
75 WHERE EXISTS
76 (SELECT 1
77 FROM sys.dba_policies
78 WHERE object_owner = UPPER(xp_object_schema)
79 AND object_name = UPPER(xp_object_name)
80 AND policy_name = UPPER(xp_policy_name)
81 );
82
83 BEGIN
84
85 OPEN c_policy_exists(p_object_schema, p_object_name, p_policy_name);
86 FETCH c_policy_exists INTO l_return;
87
88 CLOSE c_policy_exists;
89
90 RETURN l_return;
91
92 END Policy_Exists;
93
94
95 --
96 -- Name
97 -- Add_Policy
98 -- Purpose
99 -- This is a wrapper for the DBMS_RLS.Add_Policy procedure
100 -- This procedure will check if a policy exists before
101 -- adding a policy
102 -- The add_policy procedure should be executed in the en phase
103 -- during AutoInstall processing
104 -- Arguments
105 -- p_object_schema
106 -- p_object_name
107 -- p_policy_name
108 -- p_function_schema
109 -- p_policy_function
110 -- p_statement_types
111 -- p_update_check
112 -- p_enable
113 --
114 PROCEDURE Add_Policy(
115 p_object_schema IN VARCHAR2
116 , p_object_name IN VARCHAR2
117 , p_policy_name IN VARCHAR2
118 , p_function_schema IN VARCHAR2
119 , p_policy_function IN VARCHAR2
120 , p_statement_types IN VARCHAR2 := 'SELECT, INSERT, UPDATE, DELETE'
121 , p_update_check IN BOOLEAN := FALSE
122 , p_enable IN BOOLEAN := TRUE
123 )
124 IS
125 BEGIN
126
127 ADD_POLICY(
128 p_object_schema => p_object_schema,
129 p_object_name => p_object_name,
130 p_policy_name => p_policy_name,
131 p_function_schema => p_function_schema,
132 p_policy_function => p_policy_function,
133 p_statement_types => p_statement_types,
134 p_update_check => p_update_check,
135 p_enable => p_enable,
136 p_static_policy => FALSE
137 );
138
139 END Add_Policy;
140
141
142 --
143 -- Name
144 -- Drop_Policy
145 -- Purpose
146 -- This is a wrapper for the DBMS_RLS.Drop_Policy procedure
147 -- This procedure will check if a policy exists before
148 -- droping a policy
149 -- The drop_policy procedure should be executed in the con phase
150 -- during AutoInstall processing
151 -- Arguments
152 -- p_object_schema
153 -- p_object_name
154 -- p_policy_name
155 --
156 PROCEDURE drop_policy(
157 p_object_schema IN VARCHAR2
158 , p_object_name IN VARCHAR2
159 , p_policy_name IN VARCHAR2
160 )
161 IS
162 BEGIN
163
164 IF policy_exists( p_object_schema
165 , p_object_name
166 , p_policy_name ) = 'TRUE'
167 THEN
168 DBMS_RLS.DROP_POLICY(
169 p_object_schema
170 , p_object_name
171 , p_policy_name
172 );
173 END IF;
174
175 END drop_policy;
176
177 --
178 -- Name
179 -- Add_Policy
180 -- Purpose
181 -- This is a wrapper for the DBMS_RLS.Add_Policy procedure
182 -- This procedure will check if a policy exists before
183 -- adding a policy
184 -- The add_policy procedure should be executed in the en phase
185 -- during AutoInstall processing
186 -- p_policy_type has been added for bug#13059469, since the default
187 -- for p_policy_type in DBMS_RLS package is null same is being passed here.
188 -- Arguments
189 -- p_object_schema
190 -- p_object_name
191 -- p_policy_name
192 -- p_function_schema
193 -- p_policy_function
194 -- p_statement_types
195 -- p_update_check
196 -- p_enable
197 -- p_static_policy
198 -- p_policy_type
199 --
200 PROCEDURE Add_Policy(
201 p_object_schema IN VARCHAR2
202 , p_object_name IN VARCHAR2
203 , p_policy_name IN VARCHAR2
204 , p_function_schema IN VARCHAR2
205 , p_policy_function IN VARCHAR2
206 , p_statement_types IN VARCHAR2 := 'SELECT, INSERT, UPDATE, DELETE'
207 , p_update_check IN BOOLEAN := FALSE
208 , p_enable IN BOOLEAN := TRUE
209 , p_static_policy IN BOOLEAN
210 , p_policy_type IN BINARY_INTEGER
211 )
212 IS
213
214 BEGIN
215
216 IF policy_exists( p_object_schema
217 , p_object_name
218 , p_policy_name ) = 'TRUE'
219 THEN
220 DBMS_RLS.DROP_POLICY(
221 p_object_schema
222 , p_object_name
223 , p_policy_name
224 );
225 END IF;
226
227 DBMS_RLS.ADD_POLICY (
228 object_schema => p_object_schema,
229 object_name => p_object_name,
230 policy_name => p_policy_name,
231 function_schema => p_function_schema,
232 policy_function => p_policy_function,
233 statement_types => p_statement_types,
234 update_check => p_update_check,
235 enable => p_enable,
236 static_policy => p_static_policy,
237 policy_type => p_policy_type
238 );
239
240 END Add_Policy;
241
242
243 END fnd_access_control_util;
[Home] [Help]