RHN_USER
DDL scriptPackage source
Legend: string keyword reserved word operator
1: package rhn_user
2: is
3: version varchar2(100) := '$Id: universe.satellite.sql,v 1.2 2008/06/09 08:37:56 mmraka Exp $';
4: function check_role(user_id_in in number, role_in in varchar2) return number;
5: PRAGMA RESTRICT_REFERENCES(check_role, WNDS, RNPS, WNPS);
6: function check_role_implied(user_id_in in number, role_in in varchar2) return number;
7: PRAGMA RESTRICT_REFERENCES(check_role_implied, WNDS, RNPS, WNPS);
8: function get_org_id(user_id_in in number) return number;
9: PRAGMA RESTRICT_REFERENCES(get_org_id, WNDS, RNPS, WNPS);
10: function find_mailable_address(user_id_in in number) return varchar2;
11: procedure add_servergroup_perm(
12: user_id_in in number,
13: server_group_id_in in number
14: );
15: procedure remove_servergroup_perm(
16: user_id_in in number,
17: server_group_id_in in number
18: );
19: procedure add_to_usergroup(
20: user_id_in in number,
21: user_group_id_in in number
22: );
23: procedure add_users_to_usergroups(
24: user_id_in in number
25: );
26: procedure remove_from_usergroup(
27: user_id_in in number,
28: user_group_id_in in number
29: );
30: procedure remove_users_from_servergroups(
31: user_id_in in number
32: );
33: end rhn_user;
Package body source
Legend: string keyword reserved word operator
1: package body rhn_user
2: is
3: body_version varchar2(100) := '$Id: universe.satellite.sql,v 1.2 2008/06/09 08:37:56 mmraka Exp $';
4: function check_role(user_id_in in number, role_in in varchar2)
5: return number
6: is
7: throwaway number;
8: begin
9: select 1 into throwaway
10: from rhnUserGroupType UGT,
11: rhnUserGroup UG,
12: rhnUserGroupMembers UGM
13: where UGM.user_id = user_id_in
14: and UGM.user_group_id = UG.id
15: and UG.group_type = UGT.id
16: and UGT.label = role_in;
17: return 1;
18: exception
19: when no_data_found
20: then
21: return 0;
22: end check_role;
23: function check_role_implied(user_id_in in number, role_in in varchar2)
24: return number
25: is
26: throwaway number;
27: begin
28: if rhn_user.check_role(user_id_in, role_in) = 1
29: then
30: return 1;
31: end if;
32: if role_in = 'config_admin' and rhn_user.check_role(user_id_in, 'org_admin') = 1
33: then
34: return 1;
35: end if;
36: if role_in = 'channel_admin' and rhn_user.check_role(user_id_in, 'org_admin') = 1
37: then
38: return 1;
39: end if;
40: return 0;
41: end check_role_implied;
42: function get_org_id(user_id_in in number)
43: return number
44: is
45: org_id_out number;
46: begin
47: select org_id into org_id_out
48: from web_contact
49: where id = user_id_in;
50: return org_id_out;
51: end get_org_id;
52: function find_mailable_address(user_id_in in number)
53: return varchar2 is
54: PRAGMA AUTONOMOUS_TRANSACTION;
55: cursor addrs is
56: select ea.state_id, ea.address
57: from rhnEmailAddressState eas,
58: rhnEmailAddress ea
59: where ea.user_id = user_id_in
60: and eas.label = 'verified'
61: and ea.state_id = eas.id
62: union all
63: select ea.state_id, ea.address
64: from rhnEmailAddressState eas,
65: rhnEmailAddress ea
66: where ea.user_id = user_id_in
67: and eas.label = 'unverified'
68: and ea.state_id = eas.id
69: union all
70: select ea.state_id, ea.address
71: from rhnEmailAddressState eas,
72: rhnEmailAddress ea
73: where ea.user_id = user_id_in
74: and eas.label = 'pending'
75: and ea.state_id = eas.id
76: union all
77: select ea.state_id, ea.address
78: from rhnEmailAddressState eas,
79: rhnEmailAddress ea
80: where ea.user_id = user_id_in
81: and eas.label = 'pending_warned'
82: and ea.state_id = eas.id
83: union all
84: select ea.state_id, ea.address
85: from rhnEmailAddressState eas,
86: rhnEmailAddress ea
87: where ea.user_id = user_id_in
88: and eas.label = 'needs_verifying'
89: and ea.state_id = eas.id
90: union all
91: select -1 state_id,
92: email address
93: from web_user_personal_info
94: where web_user_id = user_id_in;
95: retval rhnEmailAddress.address%TYPE;
96: begin
97: for addr in addrs loop
98: retval := addr.address;
99: if addr.address is null then
100: update web_user_contact_permission
101: set email = 'N'
102: where web_user_id = user_id_in;
103: commit;
104: return null;
105: end if;
106: if addr.state_id = -1 then
107: insert into rhnEmailAddress (
108: id, address,
109: user_id, state_id
110: ) (
111: select rhn_eaddress_id_seq.nextval, addr.address,
112: user_id_in, eas.id
113: from rhnEmailAddressState eas
114: where eas.label = 'unverified'
115: );
116: end if;
117: commit;
118: return retval;
119: end loop;
120: return null;
121: end;
122: procedure add_servergroup_perm(
123: user_id_in in number,
124: server_group_id_in in number
125: ) is
126: cursor orgs_match is
127: select 1
128: from rhnServerGroup sg,
129: web_contact u
130: where u.id = user_id_in
131: and sg.id = server_group_id_in
132: and sg.org_id = u.org_id;
133: begin
134: for okay in orgs_match loop
135: insert into rhnUserServerGroupPerms(user_id, server_group_id)
136: values (user_id_in, server_group_id_in);
137: rhn_cache.update_perms_for_user(user_id_in);
138: return;
139: end loop;
140: rhn_exception.raise_exception('usgp_different_orgs');
141: exception when dup_val_on_index then
142: rhn_exception.raise_exception('usgp_already_allowed');
143: end add_servergroup_perm;
144: procedure remove_servergroup_perm(
145: user_id_in in number,
146: server_group_id_in in number
147: ) is
148: cursor perms is
149: select 1
150: from rhnUserServerGroupPerms
151: where user_id = user_id_in
152: and server_group_id = server_group_id_in;
153: begin
154: for perm in perms loop
155: delete from rhnUserServerGroupPerms
156: where user_id = user_id_in
157: and server_group_id = server_group_id_in;
158: rhn_cache.update_perms_for_user(user_id_in);
159: return;
160: end loop;
161: rhn_exception.raise_exception('usgp_not_allowed');
162: end remove_servergroup_perm;
163: procedure add_to_usergroup(
164: user_id_in in number,
165: user_group_id_in in number
166: ) is
167: cursor perm_granting_usergroups is
168: select user_group_id_in
169: from rhnUserGroup ug,
170: rhnUserGroupType ugt
171: where ugt.label in ('org_admin')
172: and ug.id = user_group_id_in
173: and ug.group_type = ugt.id;
174: begin
175: insert into rhnUserGroupMembers(user_id, user_group_id)
176: values (user_id_in, user_group_id_in);
177: for ug in perm_granting_usergroups loop
178: rhn_cache.update_perms_for_user(user_id_in);
179: return;
180: end loop;
181: end add_to_usergroup;
182: procedure add_users_to_usergroups(
183: user_id_in in number
184: ) is
185: cursor ugms is
186: select element user_id,
187: element_two user_group_id
188: from rhnSet
189: where user_id = user_id_in
190: and label = 'user_group_list';
191: begin
192: for ugm in ugms loop
193: rhn_user.add_to_usergroup(ugm.user_id, ugm.user_group_id);
194: end loop;
195: end add_users_to_usergroups;
196: procedure remove_from_usergroup(
197: user_id_in in number,
198: user_group_id_in in number
199: ) is
200: cursor perm_granting_usergroups is
201: select label
202: from rhnUserGroupType ugt,
203: rhnUserGroupMembers ugm,
204: rhnUserGroup ug
205: where 1=1
206: and ug.id = user_group_id_in
207: and ugm.user_group_id = user_group_id_in
208: and ug.group_type = ugt.id
209: and ugm.user_id = user_id_in;
210: begin
211: for ug in perm_granting_usergroups loop
212: delete from rhnUserGroupMembers
213: where user_id = user_id_in
214: and user_group_id = user_group_id_in;
215: if ug.label in ('org_admin') then
216: rhn_cache.update_perms_for_user(user_id_in);
217: end if;
218: end loop;
219: end remove_from_usergroup;
220: procedure remove_users_from_servergroups(
221: user_id_in in number
222: ) is
223: cursor ugms is
224: select element user_id,
225: element_two user_group_id
226: from rhnSet
227: where user_id = user_id_in
228: and label = 'user_group_list';
229: begin
230: for ugm in ugms loop
231: rhn_user.remove_from_usergroup(ugm.user_id, ugm.user_group_id);
232: end loop;
233: end remove_users_from_servergroups;
234: end rhn_user;