RHN_ENTITLEMENTS
DDL scriptPackage source
Legend: string keyword reserved word operator
1: package rhn_entitlements
2: is
3: body_version varchar2(100) := '$Id: universe.satellite.sql,v 1.2 2008/06/09 08:37:56 mmraka Exp $';
4: type ents_array is varray(10) of rhnServerGroupType.label%TYPE;
5: procedure remove_org_entitlements (
6: org_id_in number
7: );
8: function entitlement_grants_service (
9: entitlement_in in varchar2,
10: service_level_in in varchar2
11: ) return number;
12: function lookup_entitlement_group (
13: org_id_in in number,
14: type_label_in in varchar2 := 'sw_mgr_entitled'
15: ) return number;
16: function create_entitlement_group (
17: org_id_in in number,
18: type_label_in in varchar2 := 'sw_mgr_entitled'
19: ) return number;
20: function can_entitle_server (
21: server_id_in in number,
22: type_label_in in varchar2
23: )
24: return number;
25: function can_switch_base (
26: server_id_in in integer,
27: type_label_in in varchar2
28: )
29: return number;
30: function find_compatible_sg (
31: server_id_in in number,
32: type_label_in in varchar2,
33: sgid_out out number
34: )
35: return boolean;
36: procedure entitle_server (
37: server_id_in in number,
38: type_label_in in varchar2 := 'sw_mgr_entitled'
39: );
40: procedure remove_server_entitlement (
41: server_id_in in number,
42: type_label_in in varchar2 := 'sw_mgr_entitled',
43: repoll_virt_guests in number := 1
44: );
45: procedure unentitle_server (
46: server_id_in in number
47: );
48: procedure repoll_virt_guest_entitlements(
49: server_id_in in number
50: );
51: function get_server_entitlement (
52: server_id_in in number
53: ) return ents_array;
54: procedure modify_org_service (
55: org_id_in in number,
56: service_label_in in varchar2,
57: enable_in in char
58: );
59: procedure set_customer_enterprise (
60: customer_id_in in number
61: );
62: procedure set_customer_provisioning (
63: customer_id_in in number
64: );
65: procedure set_customer_nonlinux (
66: customer_id_in in number
67: );
68: procedure unset_customer_enterprise (
69: customer_id_in in number
70: );
71: procedure unset_customer_provisioning (
72: customer_id_in in number
73: );
74: procedure unset_customer_nonlinux (
75: customer_id_in in number
76: );
77: procedure assign_system_entitlement(
78: group_label_in in varchar2,
79: from_org_id_in in number,
80: to_org_id_in in number,
81: quantity_in in number
82: );
83: procedure assign_channel_entitlement(
84: channel_family_label_in in varchar2,
85: from_org_id_in in number,
86: to_org_id_in in number,
87: quantity_in in number
88: );
89: procedure activate_system_entitlement(
90: org_id_in in number,
91: group_label_in in varchar2,
92: quantity_in in number
93: );
94: procedure activate_channel_entitlement(
95: org_id_in in number,
96: channel_family_label_in in varchar2,
97: quantity_in in number
98: );
99: procedure set_group_count (
100: customer_id_in in number,
101: type_in in char,
102: group_type_in in number,
103: quantity_in in number
104: );
105: procedure set_family_count (
106: customer_id_in in number,
107: channel_family_id_in in number,
108: quantity_in in number
109: );
110: procedure entitle_last_modified_servers (
111: customer_id_in in number,
112: type_label_in in varchar2,
113: quantity_in in number
114: );
115: procedure prune_everything (
116: customer_id_in in number
117: );
118: procedure subscribe_newest_servers (
119: customer_id_in in number
120: );
121: end rhn_entitlements;
Package body source
Legend: string keyword reserved word operator
1: package body rhn_entitlements
2: is
3: body_version varchar2(100) := '$Id: universe.satellite.sql,v 1.2 2008/06/09 08:37:56 mmraka Exp $';
4: procedure remove_org_entitlements(
5: org_id_in in number
6: )
7: is
8: cursor system_ents is
9: select sg.id, sg.max_members, sg.group_type
10: from rhnServerGroup sg
11: where group_type is not null
12: and org_id = org_id_in;
13: cursor channel_subs is
14: select pcf.channel_family_id, pcf.max_members
15: from rhnChannelFamily cf,
16: rhnPrivateChannelFamily pcf
17: where pcf.org_id = org_id_in
18: and pcf.channel_family_id = cf.id
19: and cf.org_id is null;
20: begin
21: for system_ent in system_ents loop
22: update rhnServerGroup
23: set max_members = max_members + system_ent.max_members
24: where org_id = 1
25: and group_type = system_ent.group_type;
26: end loop;
27: update rhnServerGroup
28: set max_members = 0
29: where org_id = org_id_in;
30: for channel_sub in channel_subs loop
31: update rhnPrivateChannelFamily
32: set max_members = max_members + channel_sub.max_members
33: where org_id = 1
34: and channel_family_id = channel_sub.channel_family_id;
35: end loop;
36: update rhnPrivateChannelFamily
37: set max_members = 0
38: where org_id = org_id_in;
39: end remove_org_entitlements;
40: function entitlement_grants_service (
41: entitlement_in in varchar2,
42: service_level_in in varchar2
43: ) return number is
44: begin
45: if service_level_in = 'provisioning' then
46: if entitlement_in = 'provisioning_entitled' then
47: return 1;
48: else
49: return 0;
50: end if;
51: elsif service_level_in = 'management' then
52: if entitlement_in = 'enterprise_entitled' then
53: return 1;
54: else
55: return 0;
56: end if;
57: elsif service_level_in = 'monitoring' then
58: if entitlement_in = 'monitoring_entitled' then
59: return 1;
60: end if;
61: elsif service_level_in = 'updates' then
62: return 1;
63: else
64: return 0;
65: end if;
66: end entitlement_grants_service;
67: function lookup_entitlement_group (
68: org_id_in in number,
69: type_label_in in varchar2 := 'sw_mgr_entitled'
70: ) return number is
71: cursor server_groups is
72: select sg.id server_group_id
73: from rhnServerGroup sg,
74: rhnServerGroupType sgt
75: where sgt.label = type_label_in
76: and sgt.id = sg.group_type
77: and sg.org_id = org_id_in;
78: begin
79: for sg in server_groups loop
80: return sg.server_group_id;
81: end loop;
82: return rhn_entitlements.create_entitlement_group(
83: org_id_in,
84: type_label_in
85: );
86: end lookup_entitlement_group;
87: function create_entitlement_group (
88: org_id_in in number,
89: type_label_in in varchar2 := 'sw_mgr_entitled'
90: ) return number is
91: sg_id_val number;
92: begin
93: select rhn_server_group_id_seq.nextval
94: into sg_id_val
95: from dual;
96: insert into rhnServerGroup (
97: id, name, description, max_members, current_members,
98: group_type, org_id
99: ) (
100: select sg_id_val, sgt.label, sgt.label,
101: 0, 0, sgt.id, org_id_in
102: from rhnServerGroupType sgt
103: where sgt.label = type_label_in
104: );
105: return sg_id_val;
106: end create_entitlement_group;
107: function can_entitle_server (
108: server_id_in in number,
109: type_label_in in varchar2 )
110: return number is
111: cursor addon_servergroups (base_label_in in varchar2,
112: addon_label_in in varchar2) is
113: select
114: addon_id
115: from
116: rhnSGTypeBaseAddonCompat
117: where base_id = lookup_sg_type (base_label_in)
118: and addon_id = lookup_sg_type (addon_label_in);
119: previous_ent rhn_entitlements.ents_array;
120: is_base_in char := 'N';
121: is_base_current char := 'N';
122: i number := 0;
123: sgid number := 0;
124: begin
125: previous_ent := rhn_entitlements.ents_array();
126: previous_ent := rhn_entitlements.get_server_entitlement(server_id_in);
127: select distinct is_base
128: into is_base_in
129: from rhnServerGroupType
130: where label = type_label_in;
131: if previous_ent.count = 0 then
132: if (is_base_in = 'Y' and rhn_entitlements.find_compatible_sg (server_id_in, type_label_in, sgid)) then
133: return 1;
134: else
135: return 0;
136: end if;
137: elsif is_base_in = 'Y' then
138: return 0;
139: else
140: is_base_current := 'N';
141: i := 0;
142: while is_base_current = 'N' and i <= previous_ent.count
143: loop
144: i := i + 1;
145: select is_base
146: into is_base_current
147: from rhnServerGroupType
148: where label = previous_ent(i);
149: end loop;
150: if is_base_current = 'N' then
151: return 0;
152: end if;
153: for addon_servergroup in addon_servergroups (previous_ent(i), type_label_in) loop
154: if rhn_entitlements.find_compatible_sg (server_id_in, type_label_in, sgid) then
155: return 1;
156: else
157: return 0;
158: end if;
159: end loop;
160: end if;
161: return 0;
162: end can_entitle_server;
163: function can_switch_base (
164: server_id_in in integer,
165: type_label_in in varchar2
166: ) return number is
167: type_label_in_is_base char(1);
168: sgid number;
169: begin
170: begin
171: select is_base into type_label_in_is_base
172: from rhnServerGroupType
173: where label = type_label_in;
174: exception
175: when no_data_found then
176: rhn_exception.raise_exception ( 'invalid_entitlement' );
177: end;
178: if type_label_in_is_base = 'N' then
179: rhn_exception.raise_exception ( 'invalid_entitlement' );
180: elsif rhn_entitlements.find_compatible_sg ( server_id_in,
181: type_label_in, sgid ) then
182: return 1;
183: else
184: return 0;
185: end if;
186: end can_switch_base;
187: function find_compatible_sg (
188: server_id_in in number,
189: type_label_in in varchar2,
190: sgid_out out number
191: ) return boolean is
192: cursor servergroups is
193: select sg.id id
194: from rhnServerGroupType sgt,
195: rhnServerGroup sg,
196: rhnServer s,
197: rhnServerServerGroupArchCompat ssgac
198: where s.id = server_id_in
199: and s.org_id = sg.org_id
200: and sgt.label = type_label_in
201: and sg.group_type = sgt.id
202: and ssgac.server_group_type = sgt.id
203: and ssgac.server_arch_id = s.server_arch_id
204: and not exists (
205: select 1
206: from rhnServerGroupMembers sgm
207: where sgm.server_group_id = sg.id
208: and sgm.server_id = s.id);
209: begin
210: for servergroup in servergroups loop
211: sgid_out := servergroup.id;
212: return true;
213: end loop;
214: sgid_out := 0;
215: return false;
216: end find_compatible_sg;
217: procedure entitle_server (
218: server_id_in in number,
219: type_label_in in varchar2 := 'sw_mgr_entitled'
220: ) is
221: sgid number := 0;
222: is_virt number := 0;
223: begin
224: begin
225: select 1 into is_virt
226: from rhnServerEntitlementView
227: where server_id = server_id_in
228: and label in ('virtualization_host', 'virtualization_host_platform');
229: exception
230: when no_data_found then
231: is_virt := 0;
232: end;
233: if is_virt = 0 and (type_label_in = 'virtualization_host' or
234: type_label_in = 'virtualization_host_platform') then
235: is_virt := 1;
236: end if;
237: if rhn_entitlements.can_entitle_server(server_id_in,
238: type_label_in) = 1 then
239: if rhn_entitlements.find_compatible_sg (server_id_in,
240: type_label_in, sgid) then
241: insert into rhnServerHistory ( id, server_id, summary, details )
242: values ( rhn_event_id_seq.nextval, server_id_in,
243: 'added system entitlement ',
244: case type_label_in
245: when 'enterprise_entitled' then 'Management'
246: when 'sw_mgr_entitled' then 'Update'
247: when 'provisioning_entitled' then 'Provisioning'
248: when 'monitoring_entitled' then 'Monitoring'
249: when 'virtualization_host' then 'Virtualization'
250: when 'virtualization_host_platform' then
251: 'Virtualization Platform' end );
252: rhn_server.insert_into_servergroup (server_id_in, sgid);
253: if is_virt = 1 then
254: rhn_entitlements.repoll_virt_guest_entitlements(server_id_in);
255: end if;
256: else
257: rhn_exception.raise_exception ('no_available_server_group');
258: end if;
259: else
260: rhn_exception.raise_exception ('invalid_entitlement');
261: end if;
262: end entitle_server;
263: procedure remove_server_entitlement (
264: server_id_in in number,
265: type_label_in in varchar2 := 'sw_mgr_entitled',
266: repoll_virt_guests in number := 1
267: ) is
268: group_id number;
269: type_is_base char;
270: is_virt number := 0;
271: begin
272: begin
273: begin
274: select 1 into is_virt
275: from rhnServerEntitlementView
276: where server_id = server_id_in
277: and label in ('virtualization_host', 'virtualization_host_platform');
278: exception
279: when no_data_found then
280: is_virt := 0;
281: end;
282: select sg.id, sgt.is_base
283: into group_id, type_is_base
284: from rhnServerGroupType sgt,
285: rhnServerGroup sg,
286: rhnServerGroupMembers sgm,
287: rhnServer s
288: where s.id = server_id_in
289: and s.id = sgm.server_id
290: and sgm.server_group_id = sg.id
291: and sg.org_id = s.org_id
292: and sgt.label = type_label_in
293: and sgt.id = sg.group_type;
294: if ( type_is_base = 'Y' ) then
295: unentitle_server ( server_id_in );
296: else
297: insert into rhnServerHistory ( id, server_id, summary, details )
298: values ( rhn_event_id_seq.nextval, server_id_in,
299: 'removed system entitlement ',
300: case type_label_in
301: when 'enterprise_entitled' then 'Management'
302: when 'sw_mgr_entitled' then 'Update'
303: when 'provisioning_entitled' then 'Provisioning'
304: when 'monitoring_entitled' then 'Monitoring'
305: when 'virtualization_host' then 'Virtualization'
306: when 'virtualization_host_platform' then
307: 'Virtualization Platforrm' end );
308: rhn_server.delete_from_servergroup(server_id_in, group_id);
309: if type_label_in = 'monitoring_entitled' then
310: DELETE
311: FROM state_change
312: WHERE o_id IN (SELECT probe_id
313: FROM rhn_check_probe
314: WHERE host_id = server_id_in);
315: DELETE
316: FROM time_series
317: WHERE SUBSTR(o_id, INSTR(o_id, '-') + 1,
318: (INSTR(o_id, '-', INSTR(o_id, '-') + 1) - INSTR(o_id, '-')) - 1)
319: IN (SELECT probe_id
320: FROM rhn_check_probe
321: WHERE host_id = server_id_in);
322: DELETE
323: FROM rhn_probe
324: WHERE recid IN (SELECT probe_id
325: FROM rhn_check_probe
326: WHERE host_id = server_id_in);
327: end if;
328: if is_virt = 1 and repoll_virt_guests = 1 then
329: rhn_entitlements.repoll_virt_guest_entitlements(server_id_in);
330: end if;
331: end if;
332: exception
333: when no_data_found then
334: rhn_exception.raise_exception('invalid_server_group_member');
335: end;
336: end remove_server_entitlement;
337: procedure unentitle_server (server_id_in in number) is
338: cursor servergroups is
339: select distinct sgt.label, sg.id server_group_id
340: from rhnServerGroupType sgt,
341: rhnServerGroup sg,
342: rhnServer s,
343: rhnServerGroupMembers sgm
344: where s.id = server_id_in
345: and s.org_id = sg.org_id
346: and sg.group_type = sgt.id
347: and sgm.server_group_id = sg.id
348: and sgm.server_id = s.id;
349: is_virt number := 0;
350: begin
351: begin
352: select 1 into is_virt
353: from rhnServerEntitlementView
354: where server_id = server_id_in
355: and label in ('virtualization_host', 'virtualization_host_platform');
356: exception
357: when no_data_found then
358: is_virt := 0;
359: end;
360: for servergroup in servergroups loop
361: insert into rhnServerHistory ( id, server_id, summary, details )
362: values ( rhn_event_id_seq.nextval, server_id_in,
363: 'removed system entitlement ',
364: case servergroup.label
365: when 'enterprise_entitled' then 'Management'
366: when 'sw_mgr_entitled' then 'Update'
367: when 'provisioning_entitled' then 'Provisioning'
368: when 'monitoring_entitled' then 'Monitoring'
369: when 'virtualization_host' then 'Virtualization'
370: when 'virtualization_host_platform' then
371: 'Virtualization Platform' end );
372: rhn_server.delete_from_servergroup(server_id_in,
373: servergroup.server_group_id );
374: end loop;
375: if is_virt = 1 then
376: rhn_entitlements.repoll_virt_guest_entitlements(server_id_in);
377: end if;
378: end unentitle_server;
379: procedure repoll_virt_guest_entitlements(server_id_in in number)
380: is
381: cursor families is
382: select distinct cfs.channel_family_id
383: from
384: rhnChannelFamilyServers cfs,
385: rhnVirtualInstance vi
386: where
387: vi.host_system_id = server_id_in
388: and vi.virtual_system_id = cfs.server_id;
389: cursor group_types is
390: select distinct sg.group_type, sgt.label
391: from
392: rhnServerGroupType sgt,
393: rhnServerGroup sg,
394: rhnServerGroupMembers sgm,
395: rhnVirtualInstance vi
396: where
397: vi.host_system_id = server_id_in
398: and vi.virtual_system_id = sgm.server_id
399: and sgm.server_group_id = sg.id
400: and sg.group_type = sgt.id;
401: cursor virt_servers_cfam(family_id_in in number, quantity_in in number) is
402: select virtual_system_id
403: from (
404: select rownum, vi.virtual_system_id
405: from
406: rhnChannelFamilyMembers cfm,
407: rhnServerChannel sc,
408: rhnVirtualInstance vi
409: where
410: vi.host_system_id = server_id_in
411: and vi.virtual_system_id = sc.server_id
412: and sc.channel_id = cfm.channel_id
413: and cfm.channel_family_id = family_id_in
414: order by sc.modified desc
415: )
416: where rownum <= quantity_in;
417: cursor virt_servers_sgt(group_type_in in number, quantity_in in number) is
418: select virtual_system_id
419: from (
420: select rownum, vi.virtual_system_id
421: from
422: rhnServerGroup sg,
423: rhnServerGroupMembers sgm,
424: rhnVirtualInstance vi
425: where
426: vi.host_system_id = server_id_in
427: and vi.virtual_system_id = sgm.server_id
428: and sgm.server_group_id = sg.id
429: and sg.group_type = group_type_in
430: order by sgm.modified desc
431: )
432: where rownum <= quantity_in;
433: org_id_val number;
434: max_members_val number;
435: current_members_calc number;
436: sg_id number;
437: begin
438: select org_id
439: into org_id_val
440: from rhnServer
441: where id = server_id_in;
442: for family in families loop
443: current_members_calc :=
444: rhn_channel.channel_family_current_members(family.channel_family_id,
445: org_id_val);
446: select max_members
447: into max_members_val
448: from rhnPrivateChannelFamily
449: where channel_family_id = family.channel_family_id
450: and org_id = org_id_val;
451: if current_members_calc > max_members_val then
452: for virt_server in virt_servers_cfam(family.channel_family_id,
453: current_members_calc - max_members_val) loop
454: rhn_channel.unsubscribe_server_from_family(
455: virt_server.virtual_system_id,
456: family.channel_family_id);
457: end loop;
458: if current_members_calc > max_members_val then
459: set_family_count(org_id_val,
460: family.channel_family_id,
461: max_members_val);
462: end if;
463: end if;
464: rhn_channel.update_family_counts(family.channel_family_id,
465: org_id_val);
466: end loop;
467: for a_group_type in group_types loop
468: select max_members, id
469: into max_members_val, sg_id
470: from rhnServerGroup
471: where group_type = a_group_type.group_type
472: and org_id = org_id_val;
473: select count(sep.server_id) into current_members_calc
474: from rhnServerEntitlementPhysical sep
475: where sep.server_group_id = sg_id
476: and sep.server_group_type_id = a_group_type.group_type;
477: if current_members_calc > max_members_val then
478: for virt_server in virt_servers_sgt(a_group_type.group_type,
479: current_members_calc - max_members_val) loop
480: rhn_entitlements.remove_server_entitlement(virt_server.virtual_system_id, a_group_type.label);
481: current_members_calc := current_members_calc - 1;
482: end loop;
483: end if;
484: update rhnServerGroup set current_members = current_members_calc
485: where org_id = org_id_val
486: and group_type = a_group_type.group_type;
487: end loop;
488: end repoll_virt_guest_entitlements;
489: function get_server_entitlement (
490: server_id_in in number
491: ) return ents_array is
492: cursor server_groups is
493: select sgt.label
494: from rhnServerGroupType sgt,
495: rhnServerGroup sg,
496: rhnServerGroupMembers sgm
497: where 1=1
498: and sgm.server_id = server_id_in
499: and sg.id = sgm.server_group_id
500: and sgt.id = sg.group_type
501: and sgt.label in (
502: 'sw_mgr_entitled','enterprise_entitled',
503: 'provisioning_entitled', 'nonlinux_entitled',
504: 'monitoring_entitled', 'virtualization_host',
505: 'virtualization_host_platform'
506: );
507: ent_array ents_array;
508: begin
509: ent_array := ents_array();
510: for sg in server_groups loop
511: ent_array.extend;
512: ent_array(ent_array.count) := sg.label;
513: end loop;
514: return ent_array;
515: end get_server_entitlement;
516: procedure modify_org_service (
517: org_id_in in number,
518: service_label_in in varchar2,
519: enable_in in char
520: ) is
521: type roles_v is varray(10) of rhnUserGroupType.label%TYPE;
522: roles_to_process roles_v;
523: cursor roles(role_label_in in varchar2) is
524: select label, id
525: from rhnUserGroupType
526: where label = role_label_in;
527: cursor org_roles(role_label_in in varchar2) is
528: select 1
529: from rhnUserGroup ug,
530: rhnUserGroupType ugt
531: where ugt.label = role_label_in
532: and ug.org_id = org_id_in
533: and ugt.id = ug.group_type;
534: type ents_v is varray(10) of rhnOrgEntitlementType.label%TYPE;
535: ents_to_process ents_v;
536: cursor ents(ent_label_in in varchar2) is
537: select label, id
538: from rhnOrgEntitlementType
539: where label = ent_label_in;
540: cursor org_ents(ent_label_in in varchar2) is
541: select 1
542: from rhnOrgEntitlements oe,
543: rhnOrgEntitlementType oet
544: where oet.label = ent_label_in
545: and oe.org_id = org_id_in
546: and oet.id = oe.entitlement_id;
547: create_row char(1);
548: begin
549: ents_to_process := ents_v();
550: roles_to_process := roles_v();
551: if service_label_in = 'enterprise' or
552: service_label_in = 'management' then
553: ents_to_process.extend;
554: ents_to_process(ents_to_process.count) := 'sw_mgr_enterprise';
555: roles_to_process.extend;
556: roles_to_process(roles_to_process.count) := 'org_admin';
557: roles_to_process.extend;
558: roles_to_process(roles_to_process.count) := 'system_group_admin';
559: roles_to_process.extend;
560: roles_to_process(roles_to_process.count) := 'activation_key_admin';
561: roles_to_process.extend;
562: roles_to_process(roles_to_process.count) := 'org_applicant';
563: elsif service_label_in = 'provisioning' then
564: ents_to_process.extend;
565: ents_to_process(ents_to_process.count) := 'rhn_provisioning';
566: roles_to_process.extend;
567: roles_to_process(roles_to_process.count) := 'system_group_admin';
568: roles_to_process.extend;
569: roles_to_process(roles_to_process.count) := 'activation_key_admin';
570: roles_to_process.extend;
571: roles_to_process(roles_to_process.count) := 'config_admin';
572: if enable_in = 'Y' then
573: ents_to_process.extend;
574: ents_to_process(ents_to_process.count) := 'sw_mgr_enterprise';
575: end if;
576: elsif service_label_in = 'monitoring' then
577: ents_to_process.extend;
578: ents_to_process(ents_to_process.count) := 'rhn_monitor';
579: roles_to_process.extend;
580: roles_to_process(roles_to_process.count) := 'monitoring_admin';
581: elsif service_label_in = 'virtualization' then
582: ents_to_process.extend;
583: ents_to_process(ents_to_process.count) := 'rhn_virtualization';
584: roles_to_process.extend;
585: roles_to_process(roles_to_process.count) := 'config_admin';
586: elsif service_label_in = 'virtualization_platform' then
587: ents_to_process.extend;
588: ents_to_process(ents_to_process.count) := 'rhn_virtualization_platform';
589: roles_to_process.extend;
590: roles_to_process(roles_to_process.count) := 'config_admin';
591: elsif service_label_in = 'nonlinux' then
592: ents_to_process.extend;
593: ents_to_process(ents_to_process.count) := 'rhn_nonlinux';
594: roles_to_process.extend;
595: roles_to_process(roles_to_process.count) := 'config_admin';
596: end if;
597: if enable_in = 'Y' then
598: for i in 1..ents_to_process.count loop
599: for ent in ents(ents_to_process(i)) loop
600: create_row := 'Y';
601: for oe in org_ents(ent.label) loop
602: create_row := 'N';
603: end loop;
604: if create_row = 'Y' then
605: insert into rhnOrgEntitlements(org_id, entitlement_id)
606: values (org_id_in, ent.id);
607: end if;
608: end loop;
609: end loop;
610: for i in 1..roles_to_process.count loop
611: for role in roles(roles_to_process(i)) loop
612: create_row := 'Y';
613: for o_r in org_roles(role.label) loop
614: create_row := 'N';
615: end loop;
616: if create_row = 'Y' then
617: insert into rhnUserGroup(
618: id, name, description, current_members,
619: group_type, org_id
620: ) (
621: select rhn_user_group_id_seq.nextval,
622: ugt.name || 's',
623: ugt.name || 's for Org ' ||
624: o.name || ' ('|| o.id ||')',
625: 0, ugt.id, o.id
626: from rhnUserGroupType ugt,
627: web_customer o
628: where o.id = org_id_in
629: and ugt.id = role.id
630: );
631: end if;
632: end loop;
633: end loop;
634: else
635: for i in 1..ents_to_process.count loop
636: for ent in ents(ents_to_process(i)) loop
637: delete from rhnOrgEntitlements
638: where org_id = org_id_in
639: and entitlement_id = ent.id;
640: end loop;
641: end loop;
642: end if;
643: end modify_org_service;
644: procedure set_customer_enterprise (
645: customer_id_in in number
646: ) is
647: begin
648: modify_org_service(customer_id_in, 'enterprise', 'Y');
649: end set_customer_enterprise;
650: procedure set_customer_provisioning (
651: customer_id_in in number
652: ) is
653: begin
654: modify_org_service(customer_id_in, 'provisioning', 'Y');
655: end set_customer_provisioning;
656: procedure set_customer_monitoring (
657: customer_id_in in number
658: ) is
659: begin
660: modify_org_service(customer_id_in, 'monitoring', 'Y');
661: end set_customer_monitoring;
662: procedure set_customer_nonlinux (
663: customer_id_in in number
664: ) is
665: begin
666: modify_org_service(customer_id_in, 'nonlinux', 'Y');
667: end set_customer_nonlinux;
668: procedure unset_customer_enterprise (
669: customer_id_in in number
670: ) is
671: begin
672: modify_org_service(customer_id_in, 'enterprise', 'N');
673: end unset_customer_enterprise;
674: procedure unset_customer_provisioning (
675: customer_id_in in number
676: ) is
677: begin
678: modify_org_service(customer_id_in, 'provisioning', 'N');
679: end unset_customer_provisioning;
680: procedure unset_customer_monitoring (
681: customer_id_in in number
682: ) is
683: begin
684: modify_org_service(customer_id_in, 'monitoring', 'N');
685: end unset_customer_monitoring;
686: procedure unset_customer_nonlinux (
687: customer_id_in in number
688: ) is
689: begin
690: modify_org_service(customer_id_in, 'nonlinux', 'N');
691: end unset_customer_nonlinux;
692: procedure prune_group (
693: group_id_in in number,
694: type_in in char,
695: quantity_in in number
696: ) is
697: cursor usergroups is
698: select user_id, user_group_id, ugt.label
699: from rhnUserGroupType ugt,
700: rhnUserGroup ug,
701: rhnUserGroupMembers ugm
702: where 1=1
703: and ugm.user_group_id = group_id_in
704: and ugm.user_id in (
705: select user_id
706: from (
707: select rownum row_number,
708: user_id,
709: time
710: from (
711: select user_id,
712: modified time
713: from rhnUserGroupMembers
714: where user_group_id = group_id_in
715: order by time asc
716: )
717: )
718: where row_number > quantity_in
719: )
720: and ugm.user_group_id = ug.id
721: and ug.group_type = ugt.id;
722: cursor servergroups is
723: select server_id, server_group_id, sgt.id group_type_id, sgt.label
724: from rhnServerGroupType sgt,
725: rhnServerGroup sg,
726: rhnServerGroupMembers sgm
727: where 1=1
728: and sgm.server_group_id = group_id_in
729: and sgm.server_id in (
730: select server_id
731: from (
732: select rownum row_number,
733: server_id,
734: time
735: from (
736: select sep.server_id,
737: sep.modified time
738: from
739: rhnServerEntitlementPhysical sep
740: where
741: sep.server_group_id = group_id_in
742: order by time asc
743: )
744: )
745: where row_number > quantity_in
746: )
747: and sgm.server_group_id = sg.id
748: and sg.group_type = sgt.id;
749: type_is_base char;
750: begin
751: if type_in = 'U' then
752: update rhnUserGroup
753: set max_members = quantity_in
754: where id = group_id_in;
755: for ug in usergroups loop
756: rhn_user.remove_from_usergroup(ug.user_id, ug.user_group_id);
757: end loop;
758: elsif type_in = 'S' then
759: update rhnServerGroup
760: set max_members = quantity_in
761: where id = group_id_in;
762: for sg in servergroups loop
763: remove_server_entitlement(sg.server_id, sg.label);
764: select is_base
765: into type_is_base
766: from rhnServerGroupType sgt
767: where sgt.id = sg.group_type_id;
768: if ( type_is_base = 'Y' ) then
769: rhn_channel.clear_subscriptions(sg.server_id);
770: end if;
771: end loop;
772: end if;
773: end prune_group;
774: procedure assign_system_entitlement(
775: group_label_in in varchar2,
776: from_org_id_in in number,
777: to_org_id_in in number,
778: quantity_in in number
779: )
780: is
781: prev_ent_count number;
782: to_org_prev_ent_count number;
783: new_ent_count number;
784: new_quantity number;
785: group_type number;
786: begin
787: begin
788: select max_members
789: into prev_ent_count
790: from rhnServerGroupType sgt,
791: rhnServerGroup sg
792: where sg.org_id = from_org_id_in
793: and sg.group_type = sgt.id
794: and sgt.label = group_label_in;
795: exception
796: when NO_DATA_FOUND then
797: rhn_exception.raise_exception(
798: 'not_enough_entitlements_in_base_org');
799: end;
800: begin
801: select max_members
802: into to_org_prev_ent_count
803: from rhnServerGroupType sgt,
804: rhnServerGroup sg
805: where sg.org_id = to_org_id_in
806: and sg.group_type = sgt.id
807: and sgt.label = group_label_in;
808: exception
809: when NO_DATA_FOUND then
810: to_org_prev_ent_count := 0;
811: end;
812: begin
813: select id
814: into group_type
815: from rhnServerGroupType
816: where label = group_label_in;
817: exception
818: when NO_DATA_FOUND then
819: rhn_exception.raise_exception(
820: 'invalid_server_group');
821: end;
822: new_ent_count := prev_ent_count - quantity_in;
823: if prev_ent_count > new_ent_count then
824: new_quantity := to_org_prev_ent_count + quantity_in;
825: end if;
826: if new_ent_count < 0 then
827: rhn_exception.raise_exception(
828: 'not_enough_entitlements_in_base_org');
829: end if;
830: rhn_entitlements.set_group_count(from_org_id_in,
831: 'S',
832: group_type,
833: new_ent_count);
834: rhn_entitlements.set_group_count(to_org_id_in,
835: 'S',
836: group_type,
837: new_quantity);
838: if group_label_in = 'enterprise_entitled' then
839: if new_quantity > 0 then
840: set_customer_enterprise(to_org_id_in);
841: else
842: unset_customer_enterprise(to_org_id_in);
843: end if;
844: end if;
845: if group_label_in = 'provisioning_entitled' then
846: if new_quantity > 0 then
847: set_customer_provisioning(to_org_id_in);
848: else
849: unset_customer_provisioning(to_org_id_in);
850: end if;
851: end if;
852: if group_label_in = 'monitoring_entitled' then
853: if new_quantity > 0 then
854: set_customer_monitoring(to_org_id_in);
855: else
856: unset_customer_monitoring(to_org_id_in);
857: end if;
858: end if;
859: end assign_system_entitlement;
860: procedure assign_channel_entitlement(
861: channel_family_label_in in varchar2,
862: from_org_id_in in number,
863: to_org_id_in in number,
864: quantity_in in number
865: )
866: is
867: prev_ent_count number;
868: new_ent_count number;
869: to_org_prev_ent_count number;
870: new_quantity number;
871: cfam_id number;
872: begin
873: begin
874: select max_members
875: into prev_ent_count
876: from rhnChannelFamily cf,
877: rhnPrivateChannelFamily pcf
878: where pcf.org_id = from_org_id_in
879: and pcf.channel_family_id = cf.id
880: and cf.label = channel_family_label_in;
881: exception
882: when NO_DATA_FOUND then
883: rhn_exception.raise_exception(
884: 'not_enough_entitlements_in_base_org');
885: end;
886: begin
887: select max_members
888: into to_org_prev_ent_count
889: from rhnChannelFamily cf,
890: rhnPrivateChannelFamily pcf
891: where pcf.org_id = to_org_id_in
892: and pcf.channel_family_id = cf.id
893: and cf.label = channel_family_label_in;
894: exception
895: when NO_DATA_FOUND then
896: to_org_prev_ent_count := 0;
897: end;
898: begin
899: select id
900: into cfam_id
901: from rhnChannelFamily
902: where label = channel_family_label_in;
903: exception
904: when NO_DATA_FOUND then
905: rhn_exception.raise_exception(
906: 'invalid_channel_family');
907: end;
908: new_ent_count := prev_ent_count - quantity_in;
909: if prev_ent_count > new_ent_count then
910: new_quantity := to_org_prev_ent_count + quantity_in;
911: end if;
912: if new_ent_count < 0 then
913: rhn_exception.raise_exception(
914: 'not_enough_entitlements_in_base_org');
915: end if;
916: rhn_entitlements.set_family_count(from_org_id_in,
917: cfam_id,
918: new_ent_count);
919: rhn_entitlements.set_family_count(to_org_id_in,
920: cfam_id,
921: new_quantity);
922: end assign_channel_entitlement;
923: procedure activate_system_entitlement(
924: org_id_in in number,
925: group_label_in in varchar2,
926: quantity_in in number
927: )
928: is
929: prev_ent_count number;
930: prev_ent_count_sum number;
931: group_type number;
932: begin
933: begin
934: select current_members
935: into prev_ent_count
936: from rhnServerGroupType sgt,
937: rhnServerGroup sg
938: where sg.group_type = sgt.id
939: and sgt.label = group_label_in
940: and sg.org_id = org_id_in;
941: exception
942: when NO_DATA_FOUND then
943: prev_ent_count := 0;
944: end;
945: begin
946: select id
947: into group_type
948: from rhnServerGroupType
949: where label = group_label_in;
950: exception
951: when NO_DATA_FOUND then
952: rhn_exception.raise_exception(
953: 'invalid_server_group');
954: end;
955: if quantity_in < prev_ent_count then
956: rhn_exception.raise_exception(
957: 'not_enough_entitlements_in_base_org');
958: else
959: rhn_entitlements.set_group_count(org_id_in,
960: 'S',
961: group_type,
962: quantity_in);
963: end if;
964: end activate_system_entitlement;
965: procedure activate_channel_entitlement(
966: org_id_in in number,
967: channel_family_label_in in varchar2,
968: quantity_in in number
969: )
970: is
971: prev_ent_count number;
972: prev_ent_count_sum number;
973: cfam_id number;
974: begin
975: begin
976: select current_members
977: into prev_ent_count
978: from rhnChannelFamily cf,
979: rhnPrivateChannelFamily pcf
980: where pcf.org_id = org_id_in
981: and pcf.channel_family_id = cf.id
982: and cf.label = channel_family_label_in;
983: exception
984: when NO_DATA_FOUND then
985: prev_ent_count := 0;
986: end;
987: begin
988: select id
989: into cfam_id
990: from rhnChannelFamily
991: where label = channel_family_label_in;
992: exception
993: when NO_DATA_FOUND then
994: rhn_exception.raise_exception(
995: 'invalid_channel_family');
996: end;
997: if quantity_in < prev_ent_count then
998: rhn_exception.raise_exception(
999: 'not_enough_entitlements_in_base_org');
1000: else
1001: rhn_entitlements.set_family_count(org_id_in,
1002: cfam_id,
1003: quantity_in);
1004: end if;
1005: end activate_channel_entitlement;
1006: procedure set_group_count (
1007: customer_id_in in number,
1008: type_in in char,
1009: group_type_in in number,
1010: quantity_in in number
1011: ) is
1012: group_id number;
1013: quantity number;
1014: begin
1015: quantity := quantity_in;
1016: if quantity is not null and quantity < 0 then
1017: quantity := 0;
1018: end if;
1019: if type_in = 'U' then
1020: select rug.id
1021: into group_id
1022: from rhnUserGroup rug
1023: where 1=1
1024: and rug.org_id = customer_id_in
1025: and rug.group_type = group_type_in;
1026: elsif type_in = 'S' then
1027: select rsg.id
1028: into group_id
1029: from rhnServerGroup rsg
1030: where 1=1
1031: and rsg.org_id = customer_id_in
1032: and rsg.group_type = group_type_in;
1033: end if;
1034: rhn_entitlements.prune_group(
1035: group_id,
1036: type_in,
1037: quantity
1038: );
1039: exception
1040: when no_data_found then
1041: if type_in = 'U' then
1042: insert into rhnUserGroup (
1043: id, name, description, max_members, current_members,
1044: group_type, org_id, created, modified
1045: ) (
1046: select rhn_user_group_id_seq.nextval, name, name,
1047: quantity, 0, id, customer_id_in,
1048: sysdate, sysdate
1049: from rhnUserGroupType
1050: where id = group_type_in
1051: );
1052: elsif type_in = 'S' then
1053: insert into rhnServerGroup (
1054: id, name, description, max_members, current_members,
1055: group_type, org_id, created, modified
1056: ) (
1057: select rhn_server_group_id_seq.nextval, name, name,
1058: quantity, 0, id, customer_id_in,
1059: sysdate, sysdate
1060: from rhnServerGroupType
1061: where id = group_type_in
1062: );
1063: end if;
1064: end set_group_count;
1065: procedure prune_family (
1066: customer_id_in in number,
1067: channel_family_id_in in number,
1068: quantity_in in number
1069: ) is
1070: cursor serverchannels is
1071: select sc.server_id,
1072: sc.channel_id
1073: from rhnServerChannel sc,
1074: rhnChannelFamilyMembers cfm
1075: where 1=1
1076: and cfm.channel_family_id = channel_family_id_in
1077: and cfm.channel_id = sc.channel_id
1078: and server_id in (
1079: select server_id
1080: from (
1081: select server_id,
1082: time,
1083: rownum row_number
1084: from (
1085: select rs.id server_id,
1086: rcfm.modified time
1087: from
1088: rhnServerChannel rsc,
1089: rhnChannelFamilyMembers rcfm,
1090: rhnServer rs
1091: where 1=1
1092: and rs.org_id = customer_id_in
1093: and rs.id = rsc.server_id
1094: and rsc.channel_id = rcfm.channel_id
1095: and rcfm.channel_family_id =
1096: channel_family_id_in
1097: and exists (
1098: select 1
1099: from rhnChannelFamilyServerPhysical cfsp
1100: where cfsp.server_id = rs.id
1101: and cfsp.channel_family_id =
1102: channel_family_id_in
1103: )
1104: order by time asc
1105: )
1106: )
1107: where row_number > quantity_in
1108: );
1109: begin
1110: if customer_id_in is null then
1111: return;
1112: end if;
1113: update rhnPrivateChannelFamily
1114: set max_members = quantity_in
1115: where 1=1
1116: and org_id = customer_id_in
1117: and channel_family_id = channel_family_id_in;
1118: for sc in serverchannels loop
1119: rhn_channel.unsubscribe_server(sc.server_id, sc.channel_id, 1, 1);
1120: end loop;
1121: end prune_family;
1122: procedure set_family_count (
1123: customer_id_in in number,
1124: channel_family_id_in in number,
1125: quantity_in in number
1126: ) is
1127: cursor privperms is
1128: select 1
1129: from rhnPrivateChannelFamily
1130: where org_id = customer_id_in
1131: and channel_family_id = channel_family_id_in;
1132: cursor pubperms is
1133: select o.id org_id
1134: from web_customer o,
1135: rhnPublicChannelFamily pcf
1136: where pcf.channel_family_id = channel_family_id_in;
1137: quantity number;
1138: done number := 0;
1139: begin
1140: quantity := quantity_in;
1141: if quantity is not null and quantity < 0 then
1142: quantity := 0;
1143: end if;
1144: if customer_id_in is not null then
1145: for perm in privperms loop
1146: rhn_entitlements.prune_family(
1147: customer_id_in,
1148: channel_family_id_in,
1149: quantity
1150: );
1151: update rhnPrivateChannelFamily
1152: set max_members = quantity
1153: where org_id = customer_id_in
1154: and channel_family_id = channel_family_id_in;
1155: return;
1156: end loop;
1157: insert into rhnPrivateChannelFamily (
1158: channel_family_id, org_id, max_members, current_members
1159: ) values (
1160: channel_family_id_in, customer_id_in, quantity, 0
1161: );
1162: return;
1163: end if;
1164: for perm in pubperms loop
1165: if quantity = 0 then
1166: rhn_entitlements.prune_family(
1167: perm.org_id,
1168: channel_family_id_in,
1169: quantity
1170: );
1171: if done = 0 then
1172: delete from rhnPublicChannelFamily
1173: where channel_family_id = channel_family_id_in;
1174: end if;
1175: end if;
1176: done := 1;
1177: end loop;
1178: if done != 1 then
1179: insert into rhnPublicChannelFamily (
1180: channel_family_id
1181: ) values (
1182: channel_family_id_in
1183: );
1184: end if;
1185: end set_family_count;
1186: procedure entitle_last_modified_servers (
1187: customer_id_in in number,
1188: type_label_in in varchar2,
1189: quantity_in in number
1190: ) is
1191: cursor servers(cid_in in number, quant_in in number) is
1192: select server_id
1193: from (
1194: select rownum row_number,
1195: server_id
1196: from (
1197: select rs.id server_iD
1198: from rhnServer rs
1199: where 1=1
1200: and rs.org_id = cid_in
1201: and not exists (
1202: select 1
1203: from rhnServerGroup sg,
1204: rhnServerGroupMembers rsgm
1205: where rsgm.server_id = rs.id
1206: and rsgm.server_group_id = sg.id
1207: and sg.group_type is not null
1208: )
1209: and not exists (
1210: select 1
1211: from rhnVirtualInstance vi
1212: where vi.virtual_system_id =
1213: rs.id
1214: ) order by modified desc
1215: )
1216: )
1217: where row_number <= quant_in;
1218: begin
1219: for server in servers(customer_id_in, quantity_in) loop
1220: rhn_entitlements.entitle_server(server.server_id, type_label_in);
1221: end loop;
1222: end entitle_last_modified_servers;
1223: procedure prune_everything (
1224: customer_id_in in number
1225: ) is
1226: cursor everything is
1227: select sg.id id,
1228: 'S' type,
1229: sg.max_members quantity
1230: from rhnServerGroup sg
1231: where sg.org_id = customer_id_in
1232: union
1233: select ug.id id,
1234: 'U' type,
1235: ug.max_members quantity
1236: from rhnUserGroup ug
1237: where ug.org_id = customer_id_in
1238: union (
1239: select cfp.channel_family_id id,
1240: 'C' type,
1241: cfp.max_members quantity
1242: from rhnOrgChannelFamilyPermissions cfp
1243: where cfp.org_id = customer_id_in
1244: union
1245: select cfm.channel_family_id id,
1246: 'C' type,
1247: 0 quantity
1248: from rhnChannelFamily cf,
1249: rhnChannelFamilyMembers cfm,
1250: rhnServerChannel sc,
1251: rhnServer s
1252: where s.org_id = customer_id_in
1253: and s.id = sc.server_id
1254: and sc.channel_id = cfm.channel_id
1255: and cfm.channel_family_id = cf.id
1256: and cf.org_id is not null
1257: and cf.org_id != customer_id_in
1258: and not exists (
1259: select 1
1260: from rhnOrgChannelFamilyPermissions cfp
1261: where cfp.org_id = customer_id_in
1262: and cfp.channel_family_id = cfm.channel_family_id
1263: )
1264: );
1265: begin
1266: for one in everything loop
1267: if one.type in ('U','S') then
1268: prune_group(one.id, one.type, one.quantity);
1269: else
1270: prune_family(customer_id_in, one.id, one.quantity);
1271: end if;
1272: end loop;
1273: end prune_everything;
1274: procedure subscribe_newest_servers (
1275: customer_id_in in number
1276: ) is
1277: cursor servers(cid_in in number) is
1278: select s.id
1279: from rhnServer s
1280: where 1=1
1281: and s.org_id = cid_in
1282: and not exists (
1283: select 1
1284: from rhnChannel c,
1285: rhnServerChannel sc
1286: where sc.server_id = s.id
1287: and sc.channel_id = c.id
1288: and c.parent_channel is null
1289: )
1290: and not exists (
1291: select 1
1292: from rhnVirtualInstance vi
1293: where vi.virtual_system_id = s.id
1294: )
1295: order by s.modified desc;
1296: channel_id number;
1297: begin
1298: for server in servers(customer_id_in) loop
1299: channel_id := rhn_channel.guess_server_base(server.id);
1300: if channel_id is not null then
1301: begin
1302: rhn_channel.subscribe_server(server.id, channel_id);
1303: commit;
1304: exception
1305: when others then
1306: null;
1307: end;
1308: end if;
1309: end loop;
1310: end subscribe_newest_servers;
1311: end rhn_entitlements;