1: package body ad_general_permissions
2: as
3: function user_has_row_permission_p (
4: v_user_id general_permissions.user_id%TYPE,
5: v_permission_type general_permissions.permission_type%TYPE,
6: v_on_what_id general_permissions.on_what_id%TYPE,
7: v_on_which_table general_permissions.on_which_table%TYPE
8: )
9: return char
10: is
11: user_has_row_permission_p char(1) := 'f';
12: begin
13:
14: -- Return true if the user is a system administrator
15: -- or if the permission has been granted to at least one of:
16: --
17: -- * all users
18: -- * registered users if the user is logged in
19: -- * the user directly
20: -- * a role in a user group that the user plays
21: -- * an entire user group of which the user is a member
22: --
23: select ad_group_member_p(v_user_id, system_administrator_group_id)
24: into user_has_row_permission_p
25: from dual;
26:
27: if user_has_row_permission_p = 'f' then
28: select decode(count(*), 0, 'f', 't')
29: into user_has_row_permission_p
30: from general_permissions gp
31: where gp.on_what_id = v_on_what_id
32: and gp.on_which_table = lower(v_on_which_table)
33: and gp.permission_type = lower(v_permission_type)
34: and ((gp.scope = 'all_users')
35: or (gp.scope = 'registered_users'
36: and v_user_id > 0)
37: or (gp.scope = 'group'
38: and exists (select 1
39: from user_group_map ugm
40: where ugm.user_id = v_user_id
41: and ugm.group_id = gp.group_id))
42: or (gp.scope = 'group_role'
43: and exists (select 1
44: from user_group_map ugm
45: where ugm.user_id = v_user_id
46: and ugm.group_id = gp.group_id
47: and ugm.role = gp.role))
48: or (gp.scope = 'user'
49: and gp.user_id = v_user_id))
50: and rownum < 2;
51: end if;
52:
53: return user_has_row_permission_p;
54: end user_has_row_permission_p;
55:
56: function grant_permission_to_user (
57: v_user_id general_permissions.user_id%TYPE,
58: v_permission_type general_permissions.permission_type%TYPE,
59: v_on_what_id general_permissions.on_what_id%TYPE,
60: v_on_which_table general_permissions.on_which_table%TYPE
61: )
62: return general_permissions.permission_id%TYPE
63: is
64: v_permission_id general_permissions.permission_id%TYPE;
65: begin
66: select gp_id_sequence.nextval into v_permission_id from dual;
67:
68: insert into general_permissions
69: (permission_id, on_what_id, on_which_table,
70: scope, user_id, permission_type)
71: values
72: (v_permission_id, v_on_what_id, v_on_which_table,
73: 'user', v_user_id, v_permission_type);
74:
75: return v_permission_id;
76: end grant_permission_to_user;
77:
78: function grant_permission_to_role (
79: v_group_id general_permissions.group_id%TYPE,
80: v_role general_permissions.role%TYPE,
81: v_permission_type general_permissions.permission_type%TYPE,
82: v_on_what_id general_permissions.on_what_id%TYPE,
83: v_on_which_table general_permissions.on_which_table%TYPE
84: )
85: return general_permissions.permission_id%TYPE
86: is
87: v_permission_id general_permissions.permission_id%TYPE;
88: begin
89: select gp_id_sequence.nextval into v_permission_id from dual;
90:
91: insert into general_permissions
92: (permission_id, on_what_id, on_which_table,
93: scope, group_id, role, permission_type)
94: values
95: (v_permission_id, v_on_what_id, v_on_which_table,
96: 'group_role', v_group_id, v_role, v_permission_type);
97:
98: return v_permission_id;
99: end grant_permission_to_role;
100:
101: function grant_permission_to_group (
102: v_group_id general_permissions.group_id%TYPE,
103: v_permission_type general_permissions.permission_type%TYPE,
104: v_on_what_id general_permissions.on_what_id%TYPE,
105: v_on_which_table general_permissions.on_which_table%TYPE
106: )
107: return general_permissions.permission_id%TYPE
108: is
109: v_permission_id general_permissions.permission_id%TYPE;
110: begin
111: select gp_id_sequence.nextval into v_permission_id from dual;
112:
113: insert into general_permissions
114: (permission_id, on_what_id, on_which_table,
115: scope, group_id, permission_type)
116: values
117: (v_permission_id, v_on_what_id, v_on_which_table,
118: 'group', v_group_id, v_permission_type);
119:
120: return v_permission_id;
121: end grant_permission_to_group;
122:
123: function grant_permission_to_reg_users (
124: v_permission_type general_permissions.permission_type%TYPE,
125: v_on_what_id general_permissions.on_what_id%TYPE,
126: v_on_which_table general_permissions.on_which_table%TYPE
127: )
128: return general_permissions.permission_id%TYPE
129: is
130: v_permission_id general_permissions.permission_id%TYPE;
131: begin
132: select gp_id_sequence.nextval into v_permission_id from dual;
133:
134: insert into general_permissions
135: (permission_id, on_what_id, on_which_table,
136: scope, permission_type)
137: values
138: (v_permission_id, v_on_what_id, v_on_which_table,
139: 'registered_users', v_permission_type);
140:
141: return v_permission_id;
142: end grant_permission_to_reg_users;
143:
144: function grant_permission_to_all_users (
145: v_permission_type general_permissions.permission_type%TYPE,
146: v_on_what_id general_permissions.on_what_id%TYPE,
147: v_on_which_table general_permissions.on_which_table%TYPE
148: )
149: return general_permissions.permission_id%TYPE
150: is
151: v_permission_id general_permissions.permission_id%TYPE;
152: begin
153: select gp_id_sequence.nextval into v_permission_id from dual;
154:
155: insert into general_permissions
156: (permission_id, on_what_id, on_which_table,
157: scope, permission_type)
158: values
159: (v_permission_id, v_on_what_id, v_on_which_table,
160: 'all_users', v_permission_type);
161:
162: return v_permission_id;
163: end grant_permission_to_all_users;
164:
165: procedure revoke_permission (
166: v_permission_id general_permissions.permission_id%TYPE
167: )
168: is
169: begin
170: delete from general_permissions
171: where permission_id = v_permission_id;
172: end revoke_permission;
173:
174: function user_permission_id (
175: v_user_id general_permissions.user_id%TYPE,
176: v_permission_type general_permissions.permission_type%TYPE,
177: v_on_what_id general_permissions.on_what_id%TYPE,
178: v_on_which_table general_permissions.on_which_table%TYPE
179: )
180: return general_permissions.permission_id%TYPE
181: is
182: v_permission_id general_permissions.permission_id%TYPE;
183: begin
184: select permission_id
185: into v_permission_id
186: from general_permissions
187: where on_what_id = v_on_what_id
188: and on_which_table = lower(v_on_which_table)
189: and scope = 'user'
190: and user_id = v_user_id
191: and permission_type = lower(v_permission_type);
192:
193: return v_permission_id;
194:
195: exception when no_data_found then
196: return 0;
197: end user_permission_id;
198:
199: function group_role_permission_id (
200: v_group_id general_permissions.group_id%TYPE,
201: v_role general_permissions.role%TYPE,
202: v_permission_type general_permissions.permission_type%TYPE,
203: v_on_what_id general_permissions.on_what_id%TYPE,
204: v_on_which_table general_permissions.on_which_table%TYPE
205: )
206: return general_permissions.permission_id%TYPE
207: is
208: v_permission_id general_permissions.permission_id%TYPE;
209: begin
210: select permission_id
211: into v_permission_id
212: from general_permissions
213: where on_what_id = v_on_what_id
214: and on_which_table = lower(v_on_which_table)
215: and scope = 'group_role'
216: and group_id = v_group_id
217: and role = v_role
218: and permission_type = lower(v_permission_type);
219:
220: return v_permission_id;
221:
222: exception when no_data_found then
223: return 0;
224: end group_role_permission_id;
225:
226: function group_permission_id (
227: v_group_id general_permissions.group_id%TYPE,
228: v_permission_type general_permissions.permission_type%TYPE,
229: v_on_what_id general_permissions.on_what_id%TYPE,
230: v_on_which_table general_permissions.on_which_table%TYPE
231: )
232: return general_permissions.permission_id%TYPE
233: is
234: v_permission_id general_permissions.permission_id%TYPE;
235: begin
236: select permission_id
237: into v_permission_id
238: from general_permissions
239: where on_what_id = v_on_what_id
240: and on_which_table = lower(v_on_which_table)
241: and scope = 'group'
242: and group_id = v_group_id
243: and permission_type = lower(v_permission_type);
244:
245: return v_permission_id;
246:
247: exception when no_data_found then
248: return 0;
249: end group_permission_id;
250:
251: function reg_users_permission_id (
252: v_permission_type general_permissions.permission_type%TYPE,
253: v_on_what_id general_permissions.on_what_id%TYPE,
254: v_on_which_table general_permissions.on_which_table%TYPE
255: )
256: return general_permissions.permission_id%TYPE
257: is
258: v_permission_id general_permissions.permission_id%TYPE;
259: begin
260: select permission_id
261: into v_permission_id
262: from general_permissions
263: where on_what_id = v_on_what_id
264: and on_which_table = lower(v_on_which_table)
265: and scope = 'registered_users'
266: and permission_type = lower(v_permission_type);
267:
268: return v_permission_id;
269:
270: exception when no_data_found then
271: return 0;
272: end reg_users_permission_id;
273:
274: function all_users_permission_id (
275: v_permission_type general_permissions.permission_type%TYPE,
276: v_on_what_id general_permissions.on_what_id%TYPE,
277: v_on_which_table general_permissions.on_which_table%TYPE
278: )
279: return general_permissions.permission_id%TYPE
280: is
281: v_permission_id general_permissions.permission_id%TYPE;
282: begin
283: select permission_id
284: into v_permission_id
285: from general_permissions
286: where on_what_id = v_on_what_id
287: and on_which_table = lower(v_on_which_table)
288: and scope = 'all_users'
289: and permission_type = lower(v_permission_type);
290:
291: return v_permission_id;
292:
293: exception when no_data_found then
294: return 0;
295: end all_users_permission_id;
296:
297:
298: procedure copy_permissions (
299: v_old_on_what_id general_permissions.on_what_id%TYPE,
300: v_new_on_what_id general_permissions.on_what_id%TYPE,
301: v_on_which_table general_permissions.on_which_table%TYPE,
302: v_user_id1 general_permissions.user_id%TYPE,
303: v_user_id2 general_permissions.user_id%TYPE
304: )
305: is
306: begin
307: insert into general_permissions
308: (permission_id, on_what_id, on_which_table, scope, user_id,
309: group_id, role, permission_type)
310: select gp_id_sequence.nextval, v_new_on_what_id, lower(v_on_which_table),
311: scope, user_id, group_id, role, permission_type
312: from general_permissions
313: where on_what_id = v_old_on_what_id and
314: on_which_table = lower(v_on_which_table) and
315: (user_id is null or not user_id in (v_user_id1, v_user_id2));
316: end copy_permissions;
317: end ad_general_permissions;
|