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